Geek asks and answers

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

In 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 comfortable. I tried to do the same in PL/SQL, but this will not work.

DECLARE id number;
select 1000 into id from dual;

Do you know how to do something like this? The easiest method is my goal.

+6
source share
5 answers

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 simply

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
+9
source

An alternative to the DECLARE block is to use the 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)

, WITH, . :

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)
+1

Oracle :

> DEF x = TOTO
> SELECT '&x' FROM dual;

: TOTO

0

PL/SQL

var_nm datatype [NOT NULL: = var_value];

  • var_nn - .
  • datatype - PL/SQL.
  • NOT NULL , .
  • var_value DEFAULT , .
  • .

:

  • (. var_nm:= var_value;)
  • Usage select from(e.g.. SELECT col_nm INTO var_nm FROM tbl_nm [WHERE clause];)

In your case, as Justin's Cave already mentioned, it could 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 : '/' ie A backslash after the END keyword indicates the execution of the above PL / SQL block.

0
source

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

SQL> variable id number = 1000

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

Oracle calls this input binding.

If you must do this in PL / SQL, others have given the answer.

0
source

More articles:


All Articles