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

一、變量的種類

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

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

Composite：復(fù)合變量，里面還有多種子結(jié)構(gòu)，如數(shù)組等，使用也比較廣泛；

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

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

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

二、聲明和初始化變量的標(biāo)準(zhǔn)

        變量聲明和初始化應(yīng)遵循以下標(biāo)準(zhǔn)

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

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

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

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

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

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

SQL> edit

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

SQL> /

Theresult is 101
PL/SQLprocedure successfully completed.

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

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

三、各類型變量詳解

1. Scalar Variable標(biāo)量

    Scalar變量是使用最廣泛的一種變量，它只包含單一的值，內(nèi)部沒有更復(fù)雜的結(jié)構(gòu)，例如：

                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類型，即無結(jié)構(gòu)文檔）；

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

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

                VARCHAR (maximum_length) ——長度可變

                NUMBER [(precision，scale)]

                BINARY_INTEGER

                BINARY_FLOAT

                BINARY_DOUBLE

                PLS_INTEGER

                BOOLEAN

                DATE

                --------------以下幾類在內(nèi)部其實都存儲為數(shù)字-----

                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. 數(shù)字、字符和表達式都可以返回布爾型的值。

2. Bind Variable綁定變量

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

     1. 在特定環(huán)境中創(chuàng)建：如在C語言中，或者是在JAVA語言中；

     2. 由于它是在環(huán)境中創(chuàng)建的，故也稱為宿主變量；

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

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

     5.即使PL/SQL塊執(zhí)行結(jié)束了，該環(huán)境變量仍然能夠訪問，因為即使程序結(jié)束了，環(huán)境是不會結(jié)束的；

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

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

SQL> VARIABLE b_salary NUMBER;

-- 在sql*plus中定義環(huán)境變量，使用VARIABLE關(guān)鍵字

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塊已經(jīng)運行完畢了，仍然能夠訪問到變量b_salary；
-- PRINT是sql*plus的命令，且現(xiàn)在不在PL/SQL塊中，故引用該變量時無需加上冒號
  B_SALARY
----------
      7000

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

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

如果設(shè)置autoprint為打開狀態(tài)，則可以將結(jié)果自動打印到屏幕上

SQL> VARIABLE b_emp_salary NUMBER

SQL> SET AUTOPRINT ON

# 設(shè)置自動打印功能

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 數(shù)據(jù)類型

LOB類型的數(shù)據(jù)主要有以下幾種：

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

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

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

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

4. Composite 復(fù)合變量

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

四、 %TYPE的作用

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

                SELECT first_name

                INTO v_fname

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

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

                1. 和數(shù)據(jù)庫中某一列類型相同；

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

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

                1. 數(shù)據(jù)庫中的表名和列名；

                2. 已經(jīng)定義過的變量名。

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

                identifier table.colum_name%TYPE;

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

例1.

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

例2.            

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