sqlplus中define定义的常量和variable定义的变量已经column给变量赋值的区别

前两天看到有人在pub上问在sqlplus中通过define和variable定义,以及column定义的变量的区别。其实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
column定义:column colvar NEW_VALUE sqlvar,告诉sqlplus 取得colvar列中的值,并将这个值赋给替换变量sqlvar.
SQL> column colvar NEW_VALUE sqlvar;
SQL> select 'val' colvar from dual; -- sqlvar的值被赋值为val.
COL
---
val
SQL> select '&sqlvar' xxx from dual; --可以看到sqlvar的值为val.
old   1: select '&sqlvar' xxx from dual
new   1: select 'val' xxx from dual
XXX
---
val
通常用来设置SQL提示符的方法:
SQL> column global_name new_value gname;
SQL> select lower(user) || '@' || substr ( global_name,1,
decode (dot,0,length(global_name), dot-1)) global_name
from (select global_name,instr(global_name,'.')dot from global_name );
set sqlprompt '&gname>';

plsql@ORCL>

在http://warehouse.itpub.net/post/777/458719的基础上加了column的用法