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

define相当于定义一个字符常量，通过define定义之后，在通过&或者&&引用的时候不需要输入了。oracle在执行的时候自动用值进行了替换；
variable定义的是绑定变量。

(1) define

SQL> alter session set nls_language = american;

Session altered.

SQL> define
DEFINE _DATE = "03-OCT-09" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "myoracle" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "Notepad" (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)
SQL>

SQL> define a
SP2-0135: symbol a is UNDEFINED
SQL> define a = 1
SQL>
SQL> define
DEFINE _DATE = "03-OCT-09" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "myoracle" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "Notepad" (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 A = "1" (CHAR)

SQL> select * from dept where deptno = &a;
old 1: select * from dept where deptno = &a
new 1: select * from dept where deptno = 1

no rows selected

SQL>
SQL> column dname new_value a
SQL> select * from dept;

DEPTNO DNAME LOC ID
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> define
DEFINE _DATE = "03-OCT-09" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "myoracle" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "Notepad" (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 A = "OPERATIONS" (CHAR)
SQL>
SQL> select * from dept where dname = &a;
old 1: select * from dept where dname = &a
new 1: select * from dept where dname = OPERATIONS
select * from dept where dname = OPERATIONS
*
ERROR at line 1:
ORA-00904: "OPERATIONS": invalid identifier

SQL> select * from dept where dname = '&a';
old 1: select * from dept where dname = '&a'
new 1: select * from dept where dname = 'OPERATIONS'

DEPTNO DNAME LOC ID
---------- -------------- ------------- ----------
40 OPERATIONS BOSTON

SQL> define a
DEFINE A = "OPERATIONS" (CHAR)
SQL>
SQL> print a
SP2-0552: Bind variable "A" not declared.

(2) variable

SQL> variable a number;
SQL> print a;

A
----------

SQL> exec :a := 10;

PL/SQL procedure successfully completed.

SQL> print a;

A
----------
10

SQL> select * from dept where deptno = :a ;

DEPTNO DNAME LOC ID
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK