PLSQL 之類型、變量和結構

1、類型

在《.Net程序員學用Oracle系列(5)：三大數據類型》一文中詳細地講述了 Oracle 的基本數據類型，文中還提到，除基本數據類型之外，Oracle 還在語法上支持一些非固有數值類型。

事實上，Oracle 在語法上支持的數據類型遠不止于此，Oracle 還支持一些復雜而強大的數據類型。如屬性類型%TYPE和%ROWTYPE，記錄類型 RECORD，集合類型 VARRAY 和 TABLE 等。本節將會介紹實用的屬性類型和靈活的記錄類型。

1.1、屬性類型

屬性類型是一種可以直接引用數據庫中列的數據類型來描述變量類型的類型。Oracle 提供了兩種屬性類型，分別是%TYPE和%ROWTYPE，下文將逐一說明。

%TYPE：該屬性允許在聲明中引用數據庫中的列或先前聲明的變量的數據類型，而不是硬編碼類型名稱。在聲明常量、變量和參數時，都可以使用%TYPE屬性作為數據類型說明符。如果引用的類型被更改，則聲明也將自動更新，這點有利于后期代碼維護。

示例：

DECLARE
  v_staff_name t_staff.staff_name%TYPE;BEGIN
  SELECT t.staff_name INTO v_staff_name FROM demo.t_staff t WHERE t.staff_id=5;
  DBMS_OUTPUT.PUT_LINE(v_staff_name);END;

%ROWTYPE：該屬性可以表示數據庫中表或游標的行的記錄類型。使用%ROWTYPE聲明的變量可以存儲從表中選擇或從游標或游標變量獲取的整行數據，且變量記錄中的字段和每行中的相應列具有相同的名稱和數據類型。

示例：

DECLARE
  v_staff t_staff%ROWTYPE;BEGIN
  SELECT t.* INTO v_staff FROM demo.t_staff t WHERE t.staff_id=5;
  DBMS_OUTPUT.PUT_LINE(v_staff.staff_name);END;

想要更多的了解屬性類型可參考：《Database PL/SQL User's Guide and Reference: %TYPE Attribute》和《Database PL/SQL User's Guide and Reference: %ROWTYPE Attribute》。

1.2、記錄類型

記錄類型是由單行多列標量構成的復合結構。可以看做是一種用戶自定義的數據類型，提供了將一個或多個標量封裝成一個對象進行操作的能力。在使用記錄數據類型的變量時，需要在聲明部分先定義記錄的成員變量，然后在執行部分引用該記錄變量本身或其中的成員。但不可以對記錄做整體性的比較運算，如判斷記錄類型的變量是否為 NULL。

示例：

DECLARE
  TYPE staff_type IS RECORD(
    staff_name VARCHAR2(50),
    gender VARCHAR2(2)
  );
  v_staff staff_type;BEGIN
  SELECT t.staff_name,DECODE(t.gender,1,'男',0,'女','兩性') INTO v_staff.staff_name,v_staff.gender  FROM demo.t_staff t WHERE t.staff_id=5;
  DBMS_OUTPUT.PUT_LINE(v_staff.staff_name||'|'||v_staff.gender);END;

記錄類型和%ROWTYPE屬性在用途上比較相似，區別在于前者是自定義結構，而后者為表結構，前者比較靈活，而后者比較方便。

2、變量

一般計算機編程語言中都有變量的概念，PL/SQL 也不例外，變量用于存儲計算結果和表示可變狀態，本節將著重介紹在 PL/SQL 中變量如何定義及賦值。另外，PL/SQL 中也有常量，只是極少有人使用，有興趣的讀者可以參考《Oracle Database PL/SQL Language Reference: Constant》。

2.1、變量類型

在 PL/SQL 中定義變量的可選類型非常多，包括 Oracle 中常見的三大類基本數據類型，以及 Oracle 在語法上支持的諸多非固有數據類型。如×××(INT/INTEGER)、布爾類型(BOOLEAN)等 Oracle 本身并不支持，卻在 PL/SQL 中可用的數據類型。

2.2、變量定義

在 PL/SQL 中定義變量與 C# 中定義變量本質上并無區別，不同的是 PL/SQL 中的變量得集中定義，變量定義區域得用DECLARE關鍵字開頭，且每行只能定義一個變量。如果是 SQL*Plus 環境則必須用VAR[IABLE]開頭。

語法：

variable_name datatype [[NOT NULL] {:= | DEFAULT} expression];

如果使用了 NOT NULL 則必須給變量賦初始值。另外，在命名變量的時候還需要遵守以下命名規則：

• 1、變量名必須以字母開頭。

• 2、變量名長度不能超過 30 個字符。

• 3、變量名中不能含有空格。

• 4、同一語句塊內變量名不能重復。

• 5、變量名不能與查詢中的列名相同。

2.3、變量賦值

給 PL/SQL 變量賦值的寫法與給 C# 字段賦值寫法基本一樣，既可以在定義變量時就賦給它一個初始值，也可以在使用之前給它賦值，如果不賦值也會有默認值。唯一的區別就是，C# 中類型不同默認值也不同，而 PL/SQL 中所有類型的默認值都一樣，都是 NULL。

在 PL/SQL 中還有一點比較怪的就是，可能所有編程語言的賦值操作符都是=，而 PL/SQL 中的賦值操作符卻偏偏是:=。而且 Oracle 相關的 API 中參數寫法也與其它大多數數據庫不同。

示例一（普通 PL/SQL 環境）：

DECLARE
  v1 NUMBER;
  v2 NUMBER(5,2);
  v3 NUMBER := 50.20;
  v4 NUMBER(4) := 1998;
  v5 VARCHAR2(4) DEFAULT 'A';
  v6 DATE NOT NULL := fn_now;BEGIN
  v1 := 100;
  v2 := 99.99;
  v5 := 'A5';
  v6 := SYSDATE;  DBMS_OUTPUT.PUT_LINE(v1||'|'||v2||'|'||v3||'|'||v4||'|'||v5||'|'||v6);END;

示例二（SQL*Plus 環境）：

VARIABLE v1 NUMBERBEGIN
  :v1 := 12;
  DBMS_OUTPUT.PUT_LINE(:v1);END;/

3、結構

和普通編程語言一樣，PL/SQL 中也有常見的三大控制結構以及順序控制語句——GOTO。本節將重點講述被廣泛接受的三大控制結構，至于不受待見的 GOTO 語句，有興趣的讀者可以參考《Oracle Database PL/SQL User's Guide and Reference: Using the GOTO Statement》。

3.1、順序結構

順序結構是面向過程編程中最基本、最簡單、最常用的程序控制結構。順序結構用于表示若干個依次執行的處理步驟，表現形式就是線性結構，一個方向走下去、不拐彎。使用時只要按照解決問題的順序寫出相應的語句就行，它的執行順序是自上而下、依次執行。

3.2、選擇結構

PL/SQL 中提供了兩種選擇結構，分別是IF結構和CASE結構。其中IF結構有三種變體，CASE結構有兩種變體。下文將逐一講述各個選擇語句：

IF 結構變體一：

語法：

IF condition THEN
  {...statements to execute when condition is TRUE...}END IF;

示例：

BEGIN
  IF 1>0 THEN
    DBMS_OUTPUT.PUT_LINE('executed');  END IF;END;

IF 結構變體二：

語法：

IF condition THEN
  {...statements to execute when condition is TRUE...}ELSE
  {...statements to execute when condition is FALSE...}END IF;

示例：

BEGIN
  IF 1>2 THEN
    DBMS_OUTPUT.PUT_LINE('The result is true');
  ELSE
    DBMS_OUTPUT.PUT_LINE('The result is false');  END IF;END;

IF 結構變體三：

語法：

IF condition1 THEN
  {...statements to execute when condition1 is TRUE...}ELSIF condition2 THEN
  {...statements to execute when condition2 is TRUE...}[ELSE
  {...statements to execute when both condition1 and condition2 are FALSE...}]END IF;

示例：

BEGIN
  IF 1>2 THEN
    DBMS_OUTPUT.PUT_LINE('1>2 branch');
  ELSIF 1<2 THEN
    DBMS_OUTPUT.PUT_LINE('1<2 branch');  ELSE
    DBMS_OUTPUT.PUT_LINE('1=2 branch');  END IF;END;

注意：IF 結構變體三中有個巨坑，就是 IF 和 ELSE 之間的分支寫法，不是 ELSE IF 也不是 ELSEIF 而是 ELSIF。盡管你寫成 ELSE IF 編輯器也有智能提示，但當你執行的時候就會報ORA-06550的錯，而且這個錯誤的描述正常人基本沒可能看懂。

CASE 結構變體一

示例：

DECLARE
  v_grade VARCHAR2(1);BEGIN
  v_grade:='B';  CASE v_grade
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('甲');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('乙');
    WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('丙');    ELSE DBMS_OUTPUT.PUT_LINE('丁');  END CASE;END;

CASE 結構變體二

示例：

DECLARE
  v_score NUMBER(3);
BEGIN
  v_score:=78;  CASE
    WHEN v_score>=80 THEN DBMS_OUTPUT.PUT_LINE('優');
    WHEN v_score>=70 THEN DBMS_OUTPUT.PUT_LINE('良');
    WHEN v_score>=60 THEN DBMS_OUTPUT.PUT_LINE('中');
    ELSE DBMS_OUTPUT.PUT_LINE('差');
  END CASE;END;

與多分支的 IF 語句相比，CASE 語句更可讀、更高效，所以當程序分支較多時，應盡可能的使用 CASE 而不是 IF。CASE 語句的 ELSE 子句是可選的。但如果省略 ELSE 字句，PL/SQL 將為 CASE 語句添加以下隱式的 ELSE 子句：

ELSE RAISE CASE_NOT_FOUND;

換句話說，如果你省略了 ELSE 子句，且 CASE 語句與 WHEN 子句不匹配，PL/SQL 就會引發預定義的異常CASE_NOT_FOUND。

3.3、循環結構

PL/SQL 中提供了三種循環結構，分別是LOOP、WHILE LOOP和FOR LOOP。另外 PL/SQL 還提供了EXIT語句用于退出當前循環。下文將逐一講述各個循環語句：

LOOP 循環

語法：

LOOP  {...statements...}
  EXIT [ WHEN boolean_condition ];END LOOP;

示例一：

DECLARE
  v_counter BINARY_INTEGER := 0;
BEGIN  LOOP
    v_counter := v_counter + 1;
    DBMS_OUTPUT.PUT_LINE(v_counter); -- 輸出結果：1、2、3、4、5、6、7、8、9
    IF v_counter >= 9 THEN
      EXIT;    END IF;
    -- 上面的 IF 語句塊還可以由“EXIT WHEN v_counter >= 9;”代替  END LOOP;END;

示例二（嵌套循環）：

DECLARE  i BINARY_INTEGER := 0;  j BINARY_INTEGER := 0;
BEGIN
  LOOP    i := i + 1;    j := 0;
    
    LOOP      j := j + 1;
      DBMS_OUTPUT.PUT_LINE('i*j=('||i||'*'||j||')='||i*j);
      EXIT WHEN j >= 3;
    END LOOP;
    
    EXIT WHEN i >= 4;
  END LOOP;
END;

示例三（標記循環）：

DECLARE  i BINARY_INTEGER := 0;  j BINARY_INTEGER := 0;
BEGIN
  <<outer_loop>>
  LOOP    i := i + 1;    j := 0;
    <<inner_loop>>
    LOOP      j := j + 1;
      DBMS_OUTPUT.PUT_LINE('i*j=('||i||'*'||j||')='||i*j);
      EXIT inner_loop WHEN j >= 3;
      EXIT outer_loop WHEN i >= 4;
    END LOOP inner_loop;
  END LOOP outer_loop;
END;

WHILE LOOP 循環

語法：

WHILE condition
LOOP  {...statements...}END LOOP;

示例：

DECLARE
  v_score NUMBER(3) := 0;BEGIN
  WHILE v_score < 60 LOOP
    v_score := v_score + 10;
    DBMS_OUTPUT.PUT_LINE(v_score); -- 輸出結果：10、20、30、40、50、60
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('over');END;

FOR LOOP 循環

語法：

FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP  {...statements...}END LOOP;

示例一（正向循環）：

BEGIN
  FOR i IN 3..7 LOOP
    DBMS_OUTPUT.PUT_LINE(i); -- 輸出結果：3、4、5、6、7
  END LOOP;END;

示例二（反向循環）：

BEGIN
  FOR i IN REVERSE 3..7 LOOP
    DBMS_OUTPUT.PUT_LINE(i); -- 輸出結果：7、6、5、4、3
  END LOOP;END;

注意：FOR LOOP 循環中的計數器（變量）可以被讀取，但不能被修改。另外，在 LOOP 循環的示例中用到的 EXIT 和循環標記，同樣可用于 WHILE LOOP 循環和 FOR LOOP 循環中