How to declare variable and use it in the same Oracle SQL script?

Distinction between Bind and Substitution variables

In Oracle SQL, you will often come across two types of variables—bind variables and substitution variables. Bind variables are especially helpful when running non-interactive SQL scripts, and substitution variables shine in interactive scripts.

Bind Variables Explained

Bind variables, declared using the VAR keyword, act as placeholders for actual runtime values. These variables help prevent repeated SQL parsing, increasing performance. You use the EXEC command to assign values to these variables:

VAR num_var NUMBER  -- I'm the VAR guy!
EXEC :num_var := 42  -- I'm setting a value, not reality 🌌
PRINT num_var   -- I'm the communicator here!


Substitution Variables Explained

Substitution variables are handy in interactive scripts. They replace &var syntax with the value assigned. To avoid interactive prompting, use DEFINE to register them upfront:

DEFINE str_var = 'Hello World'  -- Hello, it's me! 🌍
SELECT '&str_var' as greeting FROM DUAL;  -- I'm a chameleon, I adapt!


Logical Structuring with Nested PL/SQL blocks

Sometimes, to construct more sophisticated control flow logic, it's advisable to use nested BEGIN-END blocks. However, be cautious of potential pitfalls like Unbound variable or Syntax error that are often due to misplacements or wrong scoping.

BEGIN
  -- Kicking off our first operation
  BEGIN
    -- Nested block for a specific task
  END;  -- A clean and gentle end

  -- Time for our second operation
  BEGIN
    -- Another nested block
  END;  -- Byeeee!
END;  -- It was nice while it lasted, wasn't it?


Handling Data Operations Efficiently

A frequently undertaken operation is updating tables using declared variables. When you insert data using variables, remember to issue a COMMIT to save changes permanently:

DECLARE
  v_id NUMBER := 1001;  -- Hi, I'm 1001. My brother, 1000, always steals the limelight 🙄
  v_name VARCHAR2(50) := 'Sample Name';  -- No, not John Doe again! 😝
BEGIN
  INSERT INTO employees (employee_id, employee_name)
  VALUES (v_id, v_name);  -- Moving the pawns to position.
  COMMIT;  -- This is real, fellas. No Ctrl+Z! 😎
END;
/


Streamlining Interaction amongst SQL scripts

If you have SQL scripts that need to interact with each other, define variables upfront using the DEF keyword. This ensures minimal interruption and seamless value passage between scripts.

Cognitive Caveats and Resolutions

It is important to be mindful of syntax correctness and context appropriate variable usage. Even a minuscule error such as a misplaced quote or incorrect variable typing could sway your SQL script from perfect to imperfect.

Oracle version considerations

Oracle SQL's variable usage can subtly differ across Oracle versions. Always consult the documentation specific to your database version for diligently implementing the best practices.