ORACLE中聲明變量：define variable declare

在sqlplus 環境中，聲明變量的關鍵字：define variable declare

一、define關鍵字（host變量）

host變量的作用是一個替換作用，是主機環境與oracle進行互動的變量，定義host變量時必須同時指定變量名和變量的值，定義變量不可以指定資料類型，define定義的變量預設其資料類型都是char。該變量隻在目前session起作用

1、定義文法：

define var_name =value

2、define指令

>檢視已定義的所有變量及其值：define

SQL> define

DEFINE _DATE = "10-3月 -00" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)

DEFINE _USER = "user" (CHAR)

DEFINE _PRIVILEGE = "" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)

DEFINE _EDITOR = "Notepad" (CHAR)

DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)

DEFINE _O_RELEASE = "1102000100" (CHAR)

>檢視已定義的某一個變量

SQL> define num

DEFINE NUM = "3" (CHAR)

3、變量的引用

定義變量後，可在sql語句中直接使用，需要在變量前加“&”符号

4、關閉打開define變量定義。

SQL> set define on

SQL> define b=10

SQL> select * from user_tables where rownum=&b;

原值 1: select * from user_tables where rownum=&b

新值 1: select * from user_tables where rownum=10

SQL> set define off;

SQL> define a=10

SQL> select * from user_tables where rownum=&a;

SP2-0552: 未聲明綁定變量 "A"。

5、臨時變量

上述是顯示的定義變量方式，另一種是隐式定義的臨時變量，臨時變量隻是目前sql語句可用。

SQL> select * from user_tables where rownum=&var;

輸入 var 的值: 10

原值 1: select * from user_tables where rownum=&var

SQL> select * from user_tables where rownum=&var;

輸入 var 的值:

二、variable關鍵字

variable定義的變量即綁定變量，一般用于存儲過程有out類型的參數時。定義時需要定于變量的資料資料類型，支援的資料類型如下。同define一樣，隻在目前會話中有效。

SQL> help variable

VARIABLE

--------

Declares a bind variable that can be referenced in PL/SQL, or

lists the current display characteristics for a single variable

or all variables.

VAR[IABLE] [variable [type]]

where type represents one of the following:

NUMBER CHAR CHAR (n [CHAR|BYTE])

NCHAR NCHAR (n) VARCHAR2 (n [CHAR|BYTE])

NVARCHAR2 (n) CLOB NCLOB

REFCURSOR BINARY_FLOAT BINARY_DOUBLE

1、定于文法：

var[iable] var_name type;

簡寫var，（sqlplus支援不少于三個字元的表示指令）。var定于的變量，必須同時指定資料類型，定義時var變量不能指派。

2、引用

引用var定義變量，需要在前面加“：”。

3、var指令，同define。

4、var變量的初始化。

使用存儲過程初始化、函數初始化。

call指令調用函數把結果傳給綁定變量，

函數初始化：call function(參數清單) into ：綁定變量1

存儲過程初始化:exec pro_name(var_name=>:綁定變量）；

三、declare--聲明部分

plsql程式塊的聲明部分，内部變量，隻在目前程式塊有效。常量、變量、遊标的聲明都在declare部分。

Oracle定義變量的方式有3種：

聲明并使用變量；

使用%TYPE聲明變量類型；

使用%ROWTYPE聲明變量類型

可以通過sys登入，使用"v$reserved_words"資料字典檢視全部關鍵字

聲明并使用變量

變量聲明基本規則：

PL/SQL是強類型語言，變量都必須在它聲明之後才可以使用；

變量不區分大小寫；

變量都要在DECLARE部分聲明；

變量名稱可以由字母，數字，_（下劃線），$，#組成；

所有的變量名稱要求以字母開頭，不能是Oracle保留字（關鍵字）；

變量的長度最多30個字元；

所有的變量均為局部作用域，它們隻在給定的PL/SQL塊中有效；

變量的預設值為NULL；

示例：

定義變量不設定預設值

DECLARE

v_result VARCHAR2(30) ; -- 此處沒有指派

BEGIN

DBMS_OUTPUT.put_line('v_result的内容〖' || v_result || '〗') ;

END ;

/

輸出結果為空

變量聲明文法：

變量名稱 [constant] 類型 [not null] [:=value];

語句解析：

constant：定義常量，必須在聲明時為其賦予預設值；

not null：表示此變量不允許為null;

:=value：在變量聲明示，設定初始資料；

示例：定義變量

v_resultA NUMBER := 100 ; -- 定義一個變量同時指派

v_resultB NUMBER ; -- 定義一個變量沒有設定内容

v_resultb := 30 ; -- 沒有區分大小寫

DBMS_OUTPUT.put_line('計算的結果是：' || (v_resultA + v_resultB) ) ;

運作結果：130

分析：此程式采用了兩種方式為聲明的變量進行指派：

1.在DECLARE中定義變量的時候直接指派：v_resultA number := 100;

2.在DECLARE中定義變量，而後在BEGIN中為變量指派：v_resultb:=30;

3.v_resultB在指派時寫成了v_resultb依然不影響執行結果；

定義非空變量：

v_resultA NUMBER NOT NULL := 100 ; -- 定義一個非空變量v_resultA，同時指派

DBMS_OUTPUT.put_line('v_resultA變量内容：' || (v_resultA) ) ;

運作結果：v_resultA變量内容：100

定義常量：

v_resultA CONSTANT NUMBER NOT NULL := 100 ; -- 定義一個常量同時指派

DBMS_OUTPUT.put_line('v_resultA常量内容：' || (v_resultA) ) ;

1.使用constant定義的常量不能在程式中對其參數進行修改。此sql使用了NOT NULL進行定義，實際上是沒有任何實際意義的。

使用%TYPE聲明變量類型

該聲明方式，可以使某一個變量與指定資料表中某一列的類型一樣；

文法：

變量定義表名稱.字段名稱%TYPE

示例：使用"%TYPE"定義變量

v_eno emp.empno%TYPE ; -- 與empno類型相同

v_ename emp.ename%TYPE ; -- 與ename類型相同

DBMS_OUTPUT.put_line('請輸入雇員編号：') ;

v_eno := &empno ; -- 由鍵盤輸入雇員編号

SELECT ename INTO v_ename FROM emp WHERE empno= v_eno ;

DBMS_OUTPUT.put_line('編号為：' || v_eno || '雇員的名字為：'|| v_ename) ;

運作：輸入empno的值：7369

輸出結果：編号為：7369雇員的名字為：SMITH

分析：此程式主要示範了%TYPE變量的使用方式。定義的eno和ename兩個變量的資料類型參考了emp表的empno和ename兩個字段

此标記可以定義表中一行記錄的類型；

使用"select…..into…."将表中的一行記錄設定到了ROWTYPE類型的變量中時，可以利用"rowtype變量.表字段"的方式取得表中每行的對應列資料

示例：使用ROWTYPE裝載一行記錄

v_deptRow dept%ROWTYPE ; -- 裝載一行dept記錄

SELECT * INTO v_deptRow FROM dept WHERE deptno=10 ;

DBMS_OUTPUT.put_line('部門編号：'|| v_deptRow.deptno || '，名稱：' || v_deptRow.dname || '，位置：' || v_deptRow.loc) ;

運作結果：部門編号：10，名稱：ACCOUNTING，位置：NEW YORK

分析：此sql定義了deptRow的ROWTYPE類型變量，之後使用限定查詢，查詢出10部門的完整資訊，并将此行資訊設定到deptRow變量中，然後分别利用列名稱取得此行中的每列内容。

補充：通過定義專門的集合類型也可以完成與ROWTYPE類型的功能

通過自定義類型接收一行記錄：

TYPE dept_type IS RECORD (

dno dept.deptno%TYPE ,

dna dept.dname%TYPE ,

dlo dept.loc%TYPE) ; -- 定義一個新的類型

v_deptRow dept_type ; -- 裝載一行dept記錄

DBMS_OUTPUT.put_line('部門編号：'|| v_deptRow.dno || '，名稱：' || v_deptRow.dna || '，位置：' || v_deptRow.dlo) ;

​

-- 找出哪個資料庫使用者用什麼程式在最近三天執行過delete或truncate table的操作      

SELECT c.username,
         a.program,
         b.sql_text,
         b.command_type,
         a.sample_time
    FROM dba_hist_active_sess_history a
         JOIN dba_hist_sqltext b
            ON a.sql_id = b.sql_id
         JOIN dba_users c
            ON a.user_id = c.user_id
   WHERE     a.sample_time BETWEEN SYSDATE - 3 AND SYSDATE
         AND b.command_type IN (7, 85)
ORDER BY a.sample_time DESC;      

SELECT c.username,
         a.program,
         b.sql_text,
         b.command_type,
         a.sample_time
    FROM dba_hist_active_sess_history a
         JOIN dba_hist_sqltext b
            ON a.sql_id = b.sql_id
         JOIN dba_users c
            ON a.user_id = c.user_id
   WHERE     a.sample_time BETWEEN SYSDATE - 3 AND SYSDATE
         AND b.command_type IN (7, 85)
ORDER BY a.sample_time DESC;