【SQL*Plus】中&非绑定变量

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/