CODEWITHSUNDEEP
oracle-databasevariablesplsql
user595234
user595234

Reputation: 6259

What is the simplest way to define a local variable in Oracle?

In the SQL Server, I can define local variables like this.

declare @id number := 1000

select * from tbl_A where id = @id;
select * from tbl_B where id = @id;

It is very convenient. I tried to do same thing in PL/SQL but it doesn't work.

DECLARE id number;
select 1000 into id from dual;

Do you know how to do something similar? The simplest method is my objective.

Upvotes: 10

Views: 28931

Answers (5)

Slim Aloui
Slim Aloui

Reputation: 480

Solution for Oracle SQL

DEF x = foo
SELECT '&x' FROM dual;

The result will be : foo

NB: The variable will keep the value even after execution. To clear variable run UNDEFINE x.

Upvotes: 3

yong321
yong321

Reputation: 101

(Just stumbled across this thread.) Beginning with SQL*Plus 12.2, you can declare and assign a value at the same time:

SQL> var id number = 1000

SQL> select * from tbl_A where id = :id;

Oracle calls it input binding.

If you must do it in PL/SQL, the answer was given by others.

Upvotes: 0

jeromerg
jeromerg

Reputation: 3135

An alternative to DECLARE Block is to use a WITH Clause:

WITH my_params AS (
    SELECT 123 AS min_id FROM DUAL
) 
SELECT * 
FROM some_table 
WHERE id > (SELECT min_id FROM my_params)

It is more portable as many vendors support the WITH clause and you can change seamless from parameter to dynamic value. For example:

WITH my_params AS (
    SELECT min(id) AS min_id FROM some_id_table
) 
SELECT * 
FROM some_table 
WHERE id > (SELECT min_id FROM my_params)

Upvotes: 3

Aniket Thakur
Aniket Thakur

Reputation: 68945

General syntax to declare variable in PL/SQL is

var_nm datatype [NOT NULL := var_value ];

  • var_nn is the name of the variable.
  • datatype is a valid PL/SQL datatype.
  • NOT NULL is an optional specification on the variable which this variable cannot be assigned null value.
  • var_value or DEFAULT value is also an optional specification, where you can initialize a variable with some specific value.
  • Each variable declaration is a separate statement and must be terminated by a semicolon.

We can assign value to variables in one of the following two ways -

  1. direct assignment (Eg. var_nm:= var_value;)
  2. Using select from (Eg. SELECT col_nm INTO var_nm FROM tbl_nm [WHERE clause];)

In you case as Justin Cave has already mentioned it can be

DECLARE 
 id number; 
BEGIN
 SELECT 1000 into id from dual;
 dbms_output.put_line('id : '|| id ); 
END; 
/

OR

DECLARE 
 id number := 1000; 
BEGIN
 dbms_output.put_line('id : '|| id ); 
END; 
/

NOTE: '/' i.e Back slash after END keyword indicates to execute the above PL/SQL Block.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231681

If you want to define a local variable in PL/SQL, you need a complete PL/SQL block

DECLARE
  id NUMBER;
BEGIN
  SELECT 1000
    INTO id
    FROM dual;
END;

or just

DECLARE
  id NUMBER := 1000;
BEGIN
  <<do something that uses the local variable>>
END;

If you want to declare a variable in SQL*Plus

SQL> variable id number
SQL> begin
       select 1000 into :id from dual;
     end;
     /

SQL> print id

        ID
----------
      1000

SQL> SELECT * FROM tbl_a WHERE id = :id

Upvotes: 14

Related Questions