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,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);

3begin

4select '&MYDEF' into x from dual;

5end;

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

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

lList 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);

3begin

4 x :='abcd';

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

6DBMS_OUTPUT.PUT_LINE(x);

7 end;

8 /

AAAA

PL/SQL proceduresuccessfully completed.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!