5.PL_SQL——變量的種類以及如何聲明變量（autoprint, %TYPE）

一、變量的種類

        PL/SQL中變量主要有以下幾類：

Scalar：標量，單一變量，如Integer，Character等等，最為常見，使用最廣；

Composite：復合變量，里面還有多種子結構，如數組等，使用也比較廣泛；

Reference：引用類型，類似指針，指向某個類型

Large object（LOB）：CLOB（CharacterLarge Object，存儲文檔）,BLOB（Binary Large Object，存儲二進制文件，如圖片、聲音等）

Non-PL/SQL variables： Bindvariables：綁定變量，環境變量。所謂環境就是指的PL/SQL所允許的環境，因為PL/SQL可以運行在C語言，JAVA語言等等語言中，C語言和JAVA語言就是PL/SQL的環境，有環境就需要對環境進行一些定義和配置，這就是這種變量的作用。

二、聲明和初始化變量的標準

        變量聲明和初始化應遵循以下標準

1. 遵循命名規范，這樣有利于代碼的可維護性

2. 見名知意，使用有意義的名字

3. 每一行定義一個變量，以便與代碼的維護

4.對于定義為非空或常量的變量進行初始化

5. 使用":="或者“DEFAULT”來賦初值

6.不要使用列名來作為變量名以防止混淆：因為如果一個變量和列名一樣，PL/SQL則優先將其解釋為列名。如下面這個例子：

SQL> edit

DECLARE
   employee_id NUMBER(6);
BEGIN
   SELECT employee_id
   INTO       employee_id
   -- 變量名和列名重復了，容易發生混淆
   FROM    employees
   WHERE   last_name = 'Kochhar';
   DBMS_OUTPUT.PUT_LINE('The resultis ' || employee_id);
END;

SQL> /

Theresult is 101
PL/SQLprocedure successfully completed.

           其中變量名和列名重合了，雖然有時候可以運行，但無法保證結果正確。

           7. 如果constraint定義為NOT NULL，則聲明時必須為其賦初值。

三、各類型變量詳解

1. Scalar Variable標量

    Scalar變量是使用最廣泛的一種變量，它只包含單一的值，內部沒有更復雜的結構，例如：

                TRUE——Boolean；

                25-JAN-01——Date；

                256120.08——Number；

    “Atlanta”——String；

              “The Soul ofthe lazy man desires, and he has nothing; but the soul of the diligent shall bemade rich.”——String（如果該值不長，比如CHAR或VARCHAR通常最多可存儲2000到4000個字符，則可以作為字符串，否則最好作為CLOB類型，即無結構文檔）；

常見的Scalar變量主要有以下幾種：

                CHAR [(maximum_length)] ——長度不可變

                VARCHAR (maximum_length) ——長度可變

                NUMBER [(precision，scale)]

                BINARY_INTEGER

                BINARY_FLOAT

                BINARY_DOUBLE

                PLS_INTEGER

                BOOLEAN

                DATE

                --------------以下幾類在內部其實都存儲為數字-----

                TIMESTAMP

                TIMESTAMP WITH TIME ZONE

                TIMESTAMP WITH LOCAL TIME ZONE

                INTERVAL YEAR TO MONTH

                INTERVAL DAY TO SECOND

                下這個例子演示了如何定義Scalar變量：

  DECLARE
     v_emp_job            VARCHAR2(9)
     v_count_loop         BINARY_INTEGER := 0;
     v_dept_total_sal     NUMBER(9,2) := 0;
     v_orderdate          DATE := SYSDATE + 7;
     c_tax_rate           CONSTANT NUMBER(3,2):= 8.25;
     v_valid              BOOLEAN NOTNULL := TRUE;
     ...

      Scalar變量中的布爾型變量有以下幾個特點：

                1. Oracle中BOOLEAN型的變量和其他編程語言不同，它除了TRUE和FALSE以外，還有NULL值；

                2. 在進行條件判斷時，可以使用邏輯操作符： AND, OR和NOT；

                3. 數字、字符和表達式都可以返回布爾型的值。

2. Bind Variable綁定變量

綁定變量也稱為環境變量，PL/SQL中的環境變量有以下特點：

     1. 在特定環境中創建：如在C語言中，或者是在JAVA語言中；

     2. 由于它是在環境中創建的，故也稱為宿主變量；

     3. 如果是在sql*plus中使用，則用關鍵字 VARIABLE來定義它；

     4. 它可以用在SQL語句中，也可以用在PL/SQL塊中；

     5.即使PL/SQL塊執行結束了，該環境變量仍然能夠訪問，因為即使程序結束了，環境是不會結束的；

     6. 引用環境變量時，要在環境變量前面加上冒號“：”

下面來舉例演示如何使用綁定變量：

SQL> VARIABLE b_salary NUMBER;

-- 在sql*plus中定義環境變量，使用VARIABLE關鍵字

SQL> edit

BEGIN  
        SELECT salary
        INTO  :b_salary
     -- 在PL/SQL塊中引用綁定變量b_salary時，需要在變量前面加上冒號
        FROM  employees
        WHERE employee_id = 178;
END;

PL/SQLprocedure successfully completed.

SQL> PRINT b_salary;

--PL/SQL塊已經運行完畢了，仍然能夠訪問到變量b_salary；
-- PRINT是sql*plus的命令，且現在不在PL/SQL塊中，故引用該變量時無需加上冒號
  B_SALARY
----------
      7000

SQL> SELECT first_name, last_name FROMemployees WHERE salary =:b_salary;

 -- 由于b_salary是環境變量，故還能訪問到；這里執行的是SQL語句，故需要使用冒號表示引用
FIRST_NAME           LAST_NAME
---------------------------------------------
Oliver               Tuvault
Sarath               Sewall
Kimberely            Grant

如果設置autoprint為打開狀態，則可以將結果自動打印到屏幕上

SQL> VARIABLE b_emp_salary NUMBER

SQL> SET AUTOPRINT ON

# 設置自動打印功能

SQL> edit

  1 DECLARE
  2    v_empno NUMBER(6) :=&empno;
  3 BEGIN
  4    SELECT salary
  5    INTO :b_emp_salary
  6              FROM employees
  7     WHERE  employee_id = v_empno;
 8  END;
 9  /

Entervalue for empno: 178
old   2:       v_empno NUMBER(6) :=&empno;
new   2:     v_empno NUMBER(6) :=178;
PL/SQLprocedure successfully completed. 
B_EMP_SALARY
------------
 7000

3. LOB Large Object 數據類型

LOB類型的數據主要有以下幾種：

                A. CLOB——用來存放字符串；

                B. BLOB——用來存放二進制文件，如圖片等；

                C. BFILE——因為這類文件體積很大，故存放在磁盤上，而數據庫中存放的只是指針，而不是文件本身，它的訪問速度可能會比BLOB更快

                D. NCLOB——用來存放一些非英文字母的文字

4. Composite 復合變量

       復合型變量中可以存放多種其他類型的變量，如RECORD, AREA,TABLE等類型的變量，其中還可以存放布爾型、日期型、字符型、LOB型的其他變量。

四、 %TYPE的作用

%TYPR是PL/SQL中獨有的一種屬性。比如以下這個語句：

                SELECT first_name

                INTO v_fname

其中fist_name和v_fname的值是同一列，為了避免hardcode，即將代碼寫死在程序里，就可以用到%TYPE，在聲明變量時使用%TYPE來表明該變量的數據類型和數據庫中某張表某一列的數據類型是一致的。

以下兩種情況可以用到%TYPE：

                1. 和數據庫中某一列類型相同；

                2. 和另外一個已經聲明過了變量類型相同。

使用%TYPE時需要加上前綴，這些前綴可以是：

                1. 數據庫中的表名和列名；

                2. 已經定義過的變量名。

使用%TYPE來定義變量的格式如下：

                identifier table.colum_name%TYPE;

下面來舉例說明%TYPE的用法：

例1.

 ...
 emp_lname       employees.last_name%TYPE;
-- 定義一個變量emp_lname，它的數據類型和employees這個表中的last_name這一列是一致的
 ...

例2.            

    ...
    balance      NUMBER(7,2);
    -- 定義第一個變量 balance，其數據類型為NUMBER
    min_balance  balance%TYPE:=1000；
    -- 定義第二個變量min_balance，其數據類型引用前一個變量balance的數據類型
    ...