What is the simplest way to define a local variable in Oracle?
23,895
Solution 1
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
Solution 2
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
.
Solution 3
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)
Author by
user595234
Updated on July 09, 2022Comments
-
user595234 almost 2 years
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.