How to declare variable and use it in the same Oracle SQL script?

Question

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.

Attempts

Use a DECLARE section and insert the following SELECT statement in BEGIN and END;. Acces the variable using &stupidvar.

Use the keyword DEFINE and access the variable.

Using the keyword VARIABLE and access the variable.

But I am getting all kinds of errors during my tries (Unbound variable, Syntax error, Expected SELECT INTO...).

Answer 1

To declare variables in SQL*Plus scripts, several ways are there as follows: You can use VAR. The way of 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>

The VAR will be useful when you will call the stored procedure which has OUT parameters or the function. Otherwise, you can use subsitution variables. It'll be 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>

If you are writing the script which calls other scripts then it'll be useful to DEFine the variables upfront like this:

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>

Ultimately, there's the anonymous PL/SQL block. Since we can still assign values to declared variables interactively. Try this:

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>

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized SQL training.