How do I use variables in Oracle SQL Developer?

sql

bind-variables

substitution-variables

plsql

byAnton Shumikhin·Sep 12, 2024

-- Bind variable for dynamic queries:
SELECT * FROM employees WHERE employee_id = :emp_id;  -- Good old Joe from accounting

-- Script-wide constant:
DEFINE country = 'US';  -- Land of freedom and fastfood
SELECT * FROM locations WHERE country_id = '&country';  -- Looking for McDonalds location, right?

Bind variables are your best choice for dynamic and repeated queries, maintaining efficiency. Script-wide constants, defined with DEFINE, help maintain and organize your script by centralizing value definitions.

Fundamentals of handling variables in SQL Developer

Elevating scripts with bind variables

Bind variables, marked as :var, allow repeated use of SQL statements with different values, circumventing recompilation. Best for interactive scripts or applications where query parameters depend on user input:

SELECT name, salary FROM employees WHERE department_id = :dept_id;  -- Hunting down the highest earners, eh?

Executing this script, SQL Developer asks for :dept_id, so it's perfect for integrating user input into the process.

Crafting flexible scripts with substitution variables

Substitution variables combined with DEFINE or &&variable_name syntax work wonders for creating adaptive SQL scripts:

DEFINE maximum_salary = 100000;  -- Sorry, no six-figure salaries today
SELECT * FROM employees WHERE salary < &maximum_salary;  -- HR's secret list

This strategy is akin to global constants in programming languages. Define the value once, use it everywhere.

Assigning values to variables in PL/SQL

Within anonymous blocks or stored procedures in PL/SQL, variable assignment looks like this:

DECLARE
  v_employee_id NUMBER := 100;  -- Employee of the month ID goes here
BEGIN
  -- Reveal the secrets of the chosen one here...
  NULL;  
END;

Smoothing out complex queries with CTEs

The WITH clause or Common Table Expressions (CTE) are perfect for structuring complex queries. SQL Server-users might see a familiar face:

WITH region_sales AS 
  (SELECT region_id, SUM(amount) AS total_sales
  FROM sales
  GROUP BY region_id)
SELECT *
FROM region_sales
WHERE total_sales > :threshold;  -- Thanks for paying my bills!

In this case, :threshold is a bind variable to specify at execution.

Dangers and nuances with substitution and bind variables

Tread carefully when using substitution variables with special characters or certain number formats. Also, be sure to correctly use the data types to avoid query drama.

Interactive data manipulation

Bind variables in SQL Developer become invaluable when creating interactive scripts:

SELECT customer_name, transaction_date, amount
FROM transactions
WHERE transaction_date BETWEEN :start_date AND :end_date; -- Time travelers only, please.

PL/SQL procedures and variable interplay

In PL/SQL blocks, variables are commonly used as parameters:

CREATE OR REPLACE PROCEDURE update_salary(emp_id IN NUMBER, new_salary IN NUMBER) AS
BEGIN
  -- Start the salary bump here...
END;

Variables in SQL*Plus for script management

SQL*Plus commands like VARIABLE can declare bind variables in SQL Developer:

VARIABLE g_employee_id NUMBER;
EXEC :g_employee_id := 10; -- Lucky number ten.

PRINT then displays this freshly assigned value:

PRINT g_employee_id;  -- Please, no zeros.

Variable nuances during script execution

Choose "Run Script" (F5 command) to execute SQL scripts. This caters to both binding and substitution variables. However, "Run Statement" (Ctrl+Enter) does not cater to substitution variables.

Tip: Clear and informative comments in your SQL scripts help your future-self and others understand the intention of each variable.