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博客 ” ，链接：https://blog.itpub.net/685769/viewspace-741542/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
