Oracle PL/SQL Variables

There are 3-type of variables can be usedin sqlplus.

-         Define variable and bind variableare sqlplus variable, they are valid during a sqlplus session.

-         Declare variable are PL/SQLblock variable.

1.      Define variable

Works like C/C++ language #define micro; there is in fact, astring replacement operation when a define variable is used; so its value can onlybe a string value.

l  List all define variable

SQL>define

DEFINE_DATE           = "2015/06/10"(CHAR)

DEFINE_CONNECT_IDENTIFIER = "batch006" (CHAR)

DEFINE_USER           = "SCOTT"(CHAR)

DEFINE_PRIVILEGE      = "" (CHAR)

DEFINE_SQLPLUS_RELEASE = "1102000100" (CHAR)

DEFINE_EDITOR         = "ed" (CHAR)

DEFINE_O_VERSION      = "Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With thePartitioning, OLAP, Data Mining and Real Application Testing options"(CHAR)

DEFINE _O_RELEASE      = "1102000100" (CHAR)

l  Add a define variable

SQL> define MYDEF=ABC

SQL> define

DEFINE_DATE           = "2015/06/10"(CHAR)

DEFINE_CONNECT_IDENTIFIER = "batch006" (CHAR)

DEFINE_USER           = "SCOTT"(CHAR)

DEFINE_PRIVILEGE      = "" (CHAR)

DEFINE_SQLPLUS_RELEASE = "1102000100" (CHAR)

DEFINE_EDITOR         = "ed" (CHAR)

DEFINE_O_VERSION      = "Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With thePartitioning, OLAP, Data Mining and Real Application Testing options"(CHAR)

DEFINE_O_RELEASE      = "1102000100"(CHAR)

DEFINE MYDEF           = "ABC" (CHAR)

l  Check a given define variable

SQL> define MYDEF

DEFINE MYDEF           = "ABC" (CHAR)

l  Remove a define variable

SQL> undefine MYDEF

SQL> define MYDEF

SP2-0135: symbol mydef is UNDEFINED

l  Use a define variable in sqlplus

SQL> select '&MYDEF'from dual;                                  #use a ‘&’ to refer a define variable

old   1: select'&MYDEF' from dual

new   1: select'ABC' from dual

         

'ABC'

---------

ABC

l  Use a define variable in block

SQL> declare

  2  xvarchar2(10);

     3 begin

     4 select '&MYDEF' into x from dual;

       5 end;

       6  /

old   4: select '&MYDEF'into x from dual;

new   4: select'ABC' into x from dual;

PL/SQL procedure successfully completed.

2.      Bind variable, i.e,. hostvariable

l  List all bind variable

SQL> print

SP2-0568: No bind variables declared.

l  Add a bind variable

SQL> var MYBIND varchar2(10);

SQL> print

MYBIND

--------------------------------------------------------------------------------

l  List a single bind variable

SQL> exec :MYBIND := to_char(sysdate);             # use a ':' to refer a bind variable

PL/SQL procedure successfully completed.

SQL> print MYBIND

MYBIND

--------------------------------------------------------------------------------

2015/06/11

l  Assign a bind variable

SQL> execute :MYBIND := 'aaa';

PL/SQL procedure successfully completed.

l  Assign a bind variable in block

SQL> begin

  2  :MYBIND:='bbb';

  3  end;

  4  /

PL/SQL procedure successfully completed.

l  Use a bind variable

SQL> select :MYBIND VAR from dual;

VAR

--------------------------------------------------------------------------------

bbb

l  Use a bind variable in block

SQL> declare

  2  x varchar2(10);

  3  begin

  4  select :MYBIND into x from dual;

  5  end;

  6  /

PL/SQL procedure successfully completed.

l  The end

3.      Declare variable

Declare variableis a block variable, or parameter variable.

SQL> SETSERVEROUTPUT ON;

SQL> declare

  2  xvarchar2(10);

  3 begin

  4  x :='abcd';

  5 select 'AAAA' into x from dual where x = 'abcd';

  6 DBMS_OUTPUT.PUT_LINE(x);

  7  end;

  8  /

AAAA

PL/SQL proceduresuccessfully completed.