Posted By: Anonymous
I want to write reusable code and need to declare some variables at the beginning and reuse them in the script, such as:
DEFINE stupidvar = 'stupidvarcontent'; SELECT stupiddata FROM stupidtable WHERE stupidcolumn = &stupidvar;
How can I declare a variable and reuse it in statements that follow such as in using it SQLDeveloper.
- Use a DECLARE section and insert the following SELECT statement in
END;. Acces the variable using
- Use the keyword
DEFINEand access the variable.
- Using the keyword
VARIABLEand access the the variable.
But I am getting all kinds of errors during my tries (Unbound variable, Syntax error, Expected
There are a several ways of declaring variables in SQL*Plus scripts.
The first is to use VAR, to declare a bind variable. The mechanism for assigning values to a VAR is with an EXEC call:
SQL> var name varchar2(20) SQL> exec :name := 'SALES' PL/SQL procedure successfully completed. SQL> select * from dept 2 where dname = :name 3 / DEPTNO DNAME LOC ---------- -------------- ------------- 30 SALES CHICAGO SQL>
A VAR is particularly useful when we want to call a stored procedure which has OUT parameters or a function.
Alternatively we can use substitution variables. These are good for interactive mode:
SQL> accept p_dno prompt "Please enter Department number: " default 10 Please enter Department number: 20 SQL> select ename, sal 2 from emp 3 where deptno = &p_dno 4 / old 3: where deptno = &p_dno new 3: where deptno = 20 ENAME SAL ---------- ---------- CLARKE 800 ROBERTSON 2975 RIGBY 3000 KULASH 1100 GASPAROTTO 3000 SQL>
When we’re writing a script which calls other scripts it can be useful to DEFine the variables upfront. This snippet runs without prompting me to enter a value:
SQL> def p_dno = 40 SQL> select ename, sal 2 from emp 3 where deptno = &p_dno 4 / old 3: where deptno = &p_dno new 3: where deptno = 40 no rows selected SQL>
Finally there’s the anonymous PL/SQL block. As you see, we can still assign values to declared variables interactively:
SQL> set serveroutput on size unlimited SQL> declare 2 n pls_integer; 3 l_sal number := 3500; 4 l_dno number := &dno; 5 begin 6 select count(*) 7 into n 8 from emp 9 where sal > l_sal 10 and deptno = l_dno; 11 dbms_output.put_line('top earners = '||to_char(n)); 12 end; 13 / Enter value for dno: 10 old 4: l_dno number := &dno; new 4: l_dno number := 10; top earners = 1 PL/SQL procedure successfully completed. SQL>