Oracle資料庫--實用操作(3) PL/SQL

標籤：

                                Oracle----PL/SQL                        

PL/SQL 是過程語言(Procedural Language)與結構化查詢語言 (SQL)(SQL)結合而成的程式設計語言

PL/SQL 是Oracle特有的，是對 SQL 的擴充。不同資料庫廠商都有類似的“方言”，提供的新特性。

支援多種資料類型，如大對象和集合類型，可使用條件和迴圈等控制結構

可用於建立預存程序、觸發器和程式包，給SQL語句的執行添加程式邏輯

與Oracle 伺服器和Oracle 工具緊密整合，具備可移植性、靈活性和安全性

PL/SQL 的優點

支援 SQL，在 PL/SQL 中可以使用：

–  資料操縱命令

–  事務控制命令

–  遊標控制

–  SQL 函數和SQL 運算子

支援物件導向編程 (OOP)

可移植性，可運行在任何作業系統和平台上的Oralce資料庫

更佳的效能，PL/SQL 經過編譯執行

 

PL/SQL體繫結構

PL/SQL[代碼]塊是構成 PL/SQL 程式的基本單元

將邏輯上相關的聲明和語句組合在一起

PL/SQL 分為三個部分，聲明部分、可執行部分和異常處理部分

 

PL/SQL中的複合符號

:=        賦值操作符

||         串連操作符

--        單行注釋

/**/      多行注釋

<<>>  標籤分隔字元

..         範圍操作符

**        求冪操作符

變數和常量

PL/SQL 塊中可以使用變數和常量

1.  在聲明部分聲明，使用前必須先聲明

2.  聲明時必須指定資料類型，每行聲明一個標識符

3.  在可執行部分的SQL 陳述式和過程語句中使用

聲明變數和常量的文法：

identifier [CONSTANT] datatype[NOT NULL]  

                   [:= |DEFAULT expr];

給變數賦值有兩種方法：

1使用賦值語句:=

2使用 SELECTINTO 語句

DECLARE

  icode VARCHAR2(6);

  p_catg VARCHAR2(20);

  p_rate NUMBER;

  c_rate CONSTANT NUMBER :=0.10;

BEGIN

  ...

  icode := ‘i205‘;

  SELECT p_category, itemrate* c_rate

  INTO  p_catg, p_rate

  FROM itemfile WHERE itemcode= icode;

  ...

END;

DECLARE vv number:=100;   --匿名代碼塊，不會儲存

Begin

  SELECT SAL into vv  from emp where ename=‘KING‘;                 --查出KING的工資，並賦值給vv變數

 

  DBMS_OUTPUT.PUT_LINE(‘SAL‘||vv);   --控制台輸出

end;

資料類型

數字資料類型

字元資料類型

字元資料類型包括：

1.  CHAR

2.  VARCHAR2

3.  LONG

4.  RAW

5.  LONG RAW

日期時間類型

1.       儲存日期和時間資料

2.       常用的兩種日期時間類型

①  DATE

②  TIMESTAMP

布林值資料型別

1.       此類別只有一種類型，即BOOLEAN類型

2.       用於儲存邏輯值(TRUE、FALSE和NULL)

3.       不能向資料庫中插入BOOLEAN資料

4.       不能將列值儲存到BOOLEAN變數中

5.       只能對BOOLEAN變數執行邏輯操作

LOB  資料類型

用於儲存大文本、映像、視訊剪輯和聲音剪輯等非結構化資料。

LOB 資料類型可儲存最大 4GB的資料。

LOB 類型包括：

6.       BLOB   將大型二進位Object Storage Service在資料庫中

7.       CLOB   將大型字元資料儲存在資料庫中

8.       NCLOB   儲存大型UNICODE字元資料

9.       BFILE     將大型二進位Object Storage Service在作業系統檔案中

屬性類型

用於引用資料庫列的資料類型，以及表示表中一行的記錄類型

屬性類型有兩種：

1.       %TYPE -  引用變數和資料庫列的資料類型

2.       %ROWTYPE -  提供表示表中一行的記錄類型

使用屬性類型的優點：

1.       不需要知道被引用的表列的具體類型

2.       如果被引用對象的資料類型發生改變，PL/SQL 變數的資料類型也隨之改變

邏輯比較

邏輯比較用於比較變數和常量的值，這些運算式稱為布林運算式

布林運算式由關係運算子與變數或常量組成

控制結構

PL/SQL 支援的流程式控制制結構：

•  條件控制

–  IF 語句

–  CASE 語句

•  迴圈控制

–  LOOP 迴圈

–  WHILE 迴圈

–  FOR 迴圈

•  順序控制

–  GOTO 語句

–  NULL 語句

•  迴圈控制用於重複執行一系列語句

•  迴圈控制語句包括：

–  LOOP、EXIT 和EXIT WHEN

•  迴圈控制的三種類型：

–  LOOP   -   無條件迴圈

–  WHILE  -  根據條件迴圈

–  FOR  -  迴圈固定的次數

      loop...exit...when...endloop迴圈控制

採用loop...exit...when...end loop迴圈控制的文法結構如下所示：

loop

         迴圈體；

         exitwhen迴圈條件;

end loop

      while...loop...end loop迴圈控制

這種迴圈控制的文法如下：

while 條件

loop

         執行語句段;

end loop;

例：

Declare counter number:=0;

Begin

loop     --[無條件]迴圈

exit when counter>10;

DBMS_OUTPUT.PUT_LINE(‘lplpl‘||counter);

counter:=counter+1;

 

end loop;

end;

……………………………………………………

Declare counter number:=0;

Begin

 while counter<9 loop    --先判斷，再執行迴圈

 DBMS_OUTPUT.PUT_LINE(‘lplpl‘||counter);

counter:=counter+1;

  endloop;

end;

 

…………………………………………………………..

Declare i number:=0;

Begin

  forx in 1..9 loop  --for迴圈：x在1-9則迴圈

   DBMS_OUTPUT.PUT_LINE(‘lplpl‘||x);

  

  endloop;

 

end;

CASE 語句用於根據單個變數或運算式與多個值進行比較

執行 CASE 語句前，先計算選取器的值

BEGIN

   CASE ‘&grade’

     WHEN ’A’ THEN DBMS_OUTPUT.PUT_LINE(’優異’);

     WHEN ’B’ THEN DBMS_OUTPUT.PUT_LINE (優秀’);

     WHEN ’C’ THEN DBMS_OUTPUT.PUT_LINE (’良好’);

     WHEN ’D’ THEN DBMS_OUTPUT.PUT_LINE (’一般’);

     WHEN ’F’ THEN DBMS_OUTPUT.PUT_LINE (’較差’);

     ELSE DBMS_OUTPUT.PUT_LINE (’沒有此成績’);

   END CASE;

END;

錯誤處理

•  在運行程式時出現的錯誤叫做異常

•  發生異常後，語句將停止執行，控制權轉移到 PL/SQL 塊的異常處理部分

•  異常有兩種類型：

–      預定義異常 -  當 PL/SQL 程式違反 Oracle 規則或超越系統限制時隱式引發

–      使用者定義異常  -  使用者可以在 PL/SQL 塊的聲明部分定義異常，自訂的異常通過 RAISE 語句顯式引發

處理預定義異常

access_into_null 在未初化對象時出現

case_not_found 在CASE語句中的選項與使用者輸入的資料不匹配時出現

collection_is_null 在給尚未初始化的表或數組賦值時出現

cursor_already_open 在使用者試圖開啟已經開啟的遊標時出現

dup_val_on_index 在使用者試圖將重複的值存在使用唯一索引的資料庫列中時出現

invalid_cursor 在執行非法遊標運算（如開啟一個尚未開啟的遊標）時出現

invalid_number 在將字串轉換為數字時出現

login_denied 在輸入的使用者名稱或密碼無效時出現

no_data_found 在表中不存在的請求的行時出現，此外，當程式引用已經刪除的元素時

storage_error 在記憶體損壞或PL/SQL耗盡記憶體時出現

too_many_rows 在執行SELECTINTO語句後返回多行時出現

value_error 在產生大小限制錯誤時出現

zero_divide 以零作除數時出現

Others針對所有異常

 

處理使用者定義異常

DECLARE

 invalidCATEGORY EXCEPTION;

 category VARCHAR2(10);

BEGIN

 category := ‘&Category‘;

  IFcategory NOT IN (‘附件‘,‘頂蓋‘,‘備件‘) THEN

   RAISE invalidCATEGORY;

 ELSE

   DBMS_OUTPUT.PUT_LINE(‘您輸入的類別是‘|| category);

  ENDIF;

EXCEPTION

 WHEN invalidCATEGORY THEN

    DBMS_OUTPUT.PUT_LINE(‘無法識別該類別‘);

END;

 

DECLARE

 rate itemfile.itemrate%TYPE;

 rate_exception EXCEPTION;

BEGIN

 SELECT NVL(itemrate,0) INTO rate FROM itemfile

 WHERE  itemcode = ‘i207‘;

  IFrate = 0 THEN

   RAISE rate_exception;

 ELSE

   DBMS_OUTPUT.PUT_LINE(‘項費率為：‘ || rate);

  ENDIF;

EXCEPTION

 WHEN rate_exception THEN

   RAISE_APPLICATION_ERROR(-20001, ‘未指定項費率‘);

END;

 

總結：

      PL/SQL 是一種可移植的高效能交易處理語言

      PL/SQL 引擎駐留在 Oracle 伺服器中

      PL/SQL 塊由聲明部分、可執行部分和異常處理部分組成

      PL/SQL 資料類型包括純量資料型別、LOB資料類型和屬性類型

      控制結構包括條件控制、迴圈控制和順序控制

      PL/SQL 支援動態 SQL

      運行時出現的錯誤叫做異常

      異常可以分為預定義異常和使用者定義的異常

Oracle資料庫--實用操作(3) PL/SQL