sqlplus中define定义的常量和variable定义的变量的区别

前两天看到有人在pub上问在sqlplus中通过define和variable定义的变量的区别。其实define定义的我理解不是变量而是字符常 量，通过define定义之后，在通过&或者&&引用的时候不需要输入了，仅此而已。oracle在执行的时候自动用值进行了替 换；而variable定义的是绑定变量。

C:>sqlplus xys/manager

SQL*Plus: Release 11.1.0.6.0 - Production on 星期二 4月 1 14:03:00 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> define
DEFINE _DATE = "01-4月 -08" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)
DEFINE _USER = "XYS" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.
6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE = "1101000600" (CHAR)
SQL> select *from tt;

ID NAME
---------- ----------
1 a
2 a
3 "abc"

SQL> define a
SP2-0135: 符号 a 未定义
SQL> define a=1
SQL> define
DEFINE _DATE = "01-4月 -08" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)
DEFINE _USER = "XYS" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.
6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE = "1101000600" (CHAR)
DEFINE A = "1" (CHAR)
--通过上面显示define定义的应该是字符（串）常量。

SQL> select * from tt where id=&a;
原值 1: select * from tt where id=&a
新值 1: select * from tt where id=1

ID NAME
---------- ----------
1 a

SQL> select * from tt where id=&&a;
原值 1: select * from tt where id=&&a
新值 1: select * from tt where id=1

ID NAME
---------- ----------
1 a

SQL> define b='a';
SQL> define
DEFINE _DATE = "01-4月 -08" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)
DEFINE _USER = "XYS" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.
6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE = "1101000600" (CHAR)
DEFINE A = "1" (CHAR)
DEFINE B = "a" (CHAR)

--如果是字符类型那么在引用时别忘了加上单引号，另外通过define定义之后在引用时不需要输入了。

SQL> select * from tt where name=&&b;
原值 1: select * from tt where name=&&b
新值 1: select * from tt where name=a
select * from tt where name=a
*
第 1 行出现错误:
ORA-00904: "A": 标识符无效

SQL> select * from tt where name='&&b';
原值 1: select * from tt where name='&&b'
新值 1: select * from tt where name='a'

ID NAME
---------- ----------
1 a
2 a

SQL> select * from tt where name='&b';
原值 1: select * from tt where name='&b'
新值 1: select * from tt where name='a'

ID NAME
---------- ----------
1 a
2 a

--执行sql时进行了替换

SQL> select sql_text from v$sql where sql_text like 'select * from tt where name
=%';

SQL_TEXT
--------------------------------------------------------------------------------

select * from tt where name=1
select * from tt where name='a'

SQL>

--==============================================

--variable定义的是绑定变量

SQL> variable a number;
SQL> print a;

A
----------

SQL> exec :a:=1;

PL/SQL 过程已成功完成。

SQL> select * from tt where id=:a;

ID NAME
---------- ----------
1 a

SQL> select sql_text from v$sql where sql_text like 'select * from tt where id=:
a%';

SQL_TEXT
--------------------------------------------------------------------------------

select * from tt where id=:a

SQL> print a;

A
----------
1

SQL>

Powered by ScribeFire.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9933980/viewspace-627702/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9933980/viewspace-627702/