欣雅的回答：前两天看到有人在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> 登高望远的回答：这是定义一个变量的意思啊！ 在sqlplus命令行之间定义变量要variable！！！！ |
