sqlplus中define定義的常量和variable定義的變數的區別！

前兩天看到有人在pub上問在sqlplus中透過define和variable定義的變數的區別。其實define定義的我理解不是變數而是字元常量，透過define定義之後，在透過&或者&&引用的時候不需要輸入了，僅此而已。oracle在執行的時候自動用值進行了替換；而variable定義的是繫結變數。[@more@]

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>