There are 3-type of variables can be usedin sqlplus.
@H_502_4@-Define variable and bind variableare sqlplus variable,they are valid during a sqlplus session.
@H_502_4@-Declare variable are PL/sqlblock variable.
@H_502_4@
1.Define variable
@H_502_4@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.
@H_502_4@
l List all define variable
@H_502_4@sql>define
@H_502_4@DEFINE_DATE = "2015/06/10"(CHAR)
@H_502_4@DEFINE_CONNECT_IDENTIFIER = "batch006" (CHAR)
@H_502_4@DEFINE_USER = "SCOTT"(CHAR)
@H_502_4@DEFINE_PRIVILEGE = "" (CHAR)
@H_502_4@DEFINE_sqlPLUS_RELEASE = "1102000100" (CHAR)
@H_502_4@DEFINE_EDITOR = "ed" (CHAR)
@H_502_4@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)
@H_502_4@DEFINE _O_RELEASE = "1102000100" (CHAR)
@H_502_4@l Add a define variable
@H_502_4@sql> define MYDEF=ABC
@H_502_4@sql> define
@H_502_4@DEFINE_DATE = "2015/06/10"(CHAR)
@H_502_4@DEFINE_CONNECT_IDENTIFIER = "batch006" (CHAR)
@H_502_4@DEFINE_USER = "SCOTT"(CHAR)
@H_502_4@DEFINE_PRIVILEGE = "" (CHAR)
@H_502_4@DEFINE_sqlPLUS_RELEASE = "1102000100" (CHAR)
@H_502_4@DEFINE_EDITOR = "ed" (CHAR)
@H_502_4@DEFINE_O_VERSION = "Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
@H_502_4@With thePartitioning,Data Mining and Real Application Testing options"(CHAR)
@H_502_4@DEFINE_O_RELEASE = "1102000100"(CHAR)
@H_502_4@DEFINE MYDEF = "ABC" (CHAR)
@H_502_4@l Check a given define variable
@H_502_4@sql> define MYDEF
@H_502_4@DEFINE MYDEF = "ABC" (CHAR)
@H_502_4@l Remove a define variable
@H_502_4@sql> undefine MYDEF
@H_502_4@sql> define MYDEF
@H_502_4@SP2-0135: symbol mydef is UNDEFINED
@H_502_4@l Use a define variable in sqlplus
@H_502_4@sql> select '&MYDEF'from dual; #use a ‘&’ to refer a define variable
@H_502_4@old 1: select'&MYDEF' from dual
@H_502_4@new 1: select'ABC' from dual
@H_502_4@@H_502_4@
'ABC'
@H_502_4@---------
@H_502_4@ABC
@H_502_4@l Use a define variable in block
@H_502_4@sql> declare
@H_502_4@2 xvarchar2(10);
@H_502_4@3begin
@H_502_4@4select '&MYDEF' into x from dual;
@H_502_4@5end;
@H_502_4@6 /
@H_502_4@old 4: select '&MYDEF'into x from dual;
@H_502_4@new 4: select'ABC' into x from dual;
@H_502_4@
PL/sql procedure successfully completed.
@H_502_4@
2.Bind variable,i.e,. hostvariable
@H_502_4@l List all bind variable
@H_502_4@sql> print
@H_502_4@SP2-0568: No bind variables declared.
@H_502_4@
l Add a bind variable
@H_502_4@sql> var MYBIND varchar2(10);
@H_502_4@sql> print
@H_502_4@MYBIND
@H_502_4@--------------------------------------------------------------------------------
@H_502_4@lList a single bind variable
@H_502_4@sql> exec :MYBIND := to_char(sysdate); # use a ':' to refer a bind variable
@H_502_4@PL/sql procedure successfully completed.
@H_502_4@sql> print MYBIND
@H_502_4@MYBIND
@H_502_4@--------------------------------------------------------------------------------
@H_502_4@2015/06/11
@H_502_4@
l Assign a bind variable
@H_502_4@sql> execute :MYBIND := 'aaa';
@H_502_4@PL/sql procedure successfully completed.
@H_502_4@
l Assign a bind variable in block
@H_502_4@sql> begin
@H_502_4@2 :MYBIND:='bbb';
@H_502_4@3 end;
@H_502_4@4 /
@H_502_4@PL/sql procedure successfully completed.
@H_502_4@l Use a bind variable
@H_502_4@sql> select :MYBIND VAR from dual;
@H_502_4@VAR
@H_502_4@--------------------------------------------------------------------------------
@H_502_4@bbb
@H_502_4@l Use a bind variable in block
@H_502_4@sql> declare
@H_502_4@2 x varchar2(10);
@H_502_4@3 begin
@H_502_4@4 select :MYBIND into x from dual;
@H_502_4@5 end;
@H_502_4@6 /
@H_502_4@PL/sql procedure successfully completed.
@H_502_4@l The end
@H_502_4@
3.Declare variable
@H_502_4@Declare variableis a block variable,or parameter variable.
@H_502_4@
