1)在sqlplus中直接输入define查看一下定义的常量
SYS@ORCL>define
DEFINE _DATE = "2012-08-21 23:50:55" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ORCL" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
SYS@ORCL>
2)设置常量num为10
SYS@ORCL>define num=10
3)再次查看,已经设置成功
SYS@ORCL>define
DEFINE _DATE = "2012-08-21 23:51:16" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ORCL" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
DEFINE NUM = "10" (CHAR)
SYS@ORCL>
4)执行sql命令,用&方式引用常量num
SYS@ORCL>select * from scott.dept where deptno=#
old 1: select * from scott.dept where deptno=&num
new 1: select * from scott.dept where deptno=10
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SYS@ORCL>
5)如果我们之前没有定义value,那么当直接用&引用时,则需要手工输入相应的值
SYS@ORCL>select * from scott.dept where deptno=&value;
Enter value for value: 20
old 1: select * from scott.dept where deptno=&value
new 1: select * from scott.dept where deptno=20
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
SYS@ORCL>
6)定义变量
SYS@ORCL>var v_num number;
SYS@ORCL>exec :v_num:=30;
PL/SQL procedure successfully completed.
SYS@ORCL>
7)执行sql语句引用变量
SYS@ORCL>select * from scott.dept where deptno=:v_num;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
SYS@ORCL>
8)查看刚刚执行的sql语句,很明显发现,var定义的变量才是绑定变量
SYS@ORCL>select sql_text from v$sqlarea where sql_text like 'select * from scott.dept where deptno=%';
SQL_TEXT
------------------------------------------------------
select * from scott.dept where deptno=:v_num
select * from scott.dept where deptno=20
select * from scott.dept where deptno=10
SYS@ORCL>
小结:
好多人都说&就是绑定变量,通过这个测试证明是错误的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/685769/viewspace-741542/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/685769/viewspace-741542/