There are 3-type of variables can be usedin sqlplus.
-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.
