sqlplus變數定義, 呼叫過程

oracle定義變數（常量）常用：declare、define、variable

1）define、variable用於sqlplus中，在整個sqlplus連線中都生效(until exit,disc是cut down session)，而declare用於pl/sql中。

2）variable（var）和define區別在於，前者用於繫結變數，後者是用於&或&&進行變數替換(使用場合,拿來當輸入引數)。

SQL> define x='SCOTT'
SQL> define
DEFINE _DATE           = "29-9月 -13" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "myorcl11" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vim" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE X               = "SCOTT" (CHAR)
SQL> select distinct owner from dba_segments where wner='&x';
old   1: select distinct owner from dba_segments where wner='&x'
new   1: select distinct owner from dba_segments where wner='SCOTT'

OWNER
------------------------------------------------------------
SCOTT

SQL> var y numb
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
SQL> var y varchar2(20)
SQL> var
variable   y
datatype   VARCHAR2(20)
SQL> exec :y := 'SCOTT'

PL/SQL procedure successfully completed.

SQL> print :y

Y
-------
SCOTT

SQL> select distinct owner from dba_segments where owner=':y'; --wrong

no rows selected

SQL> select distinct owner from dba_segments where wner=:y;

OWNER
------------------------------------------------------------
SCOTT