資料庫複習10——PL/SQL，資料庫複習plsql

資料庫複習10——PL/SQL，資料庫複習plsql
資料庫複習CH10 PL/SQL10.1 PL/SQL簡介

PL/SQL是Oracle對SQL的過程化的擴充，PL/SQL可以實現SQL相關的過程化程式，並且能夠以預存程序和函數的方式讓一段SQL商務邏輯駐留在SQL伺服器中，以便減少客戶機計算任務並減少網路I/O

10.2 PL/SQL編程基礎（1）簡介

PL/SQL編程架構為：

DECLARE     <Variable List>BEGIN    <Extented SQL Execution>EXCEPTION    <Exception Handler>END

若是預存程序或函數，首部換成Create Procedure/Function ... IS <Variable List>（見後例）

先看一個完整的PL/SQL程式例子：

DECLARE    name varchar2(20);BEGIN    Select sname Into name From Student Where s#='001';     DBMS_OUTPUT.PUT_LINE('學號001的學生姓名是:' || sname)EXCEPTION    When NO_DATA_FOUND Then        DBMS_OUTPUT.PUT_LINE('學號為001的學生不存在');    When others Then        DBMS_OUTPUT.PUT_LINE('發生了其它錯誤');END;

其中：

• name varchar2(20)聲明一個類型為varchar2長度為20的變數name
• select S into V是擴充的select語句，把表的欄位S賦值給變數V
• DBMS_OUTPUT.PUT_LINE()是PL/SQL標準輸出語句
• when <Exception> then <Extented SQL Execution>是PL/SQL異常處理的分支判斷

（2）變數聲明

變數聲明分成普通聲明、表欄位型別宣告和記錄類型聲明

普通聲明name varchar2(20);等價於表欄位型別宣告name Student.sname%TYPE，後者利用了某表的某欄位的類型來聲明變數的類型

記錄類型相當於結構體，提高程式可讀性，如定義stu記錄類型：

TYPE stu IS RECORD (    S# varchar2(10),    name varchar2(20),    age number);val stu;

引用時用點運算：val.name

（3）分支與迴圈

1.if分支

If <Condition> Then    <Statement>Elseif <Condition> Then    <Statement>Else    <Statement>End If;

注意=是相等，:=是賦值

2.while迴圈

While <Condition> Loop    <Statement>End LOOP;

3.for迴圈

For <Loop Variable> In [Reverse] <begin>..<end> Loop    <Statement>End LOOP;

4.loop迴圈

無條件的Loop，必須在迴圈體內部加入退出語句Exit;或Exit When <Condition>才能退出該迴圈

（4）異常處理

標準格式：

Exception    When <exception_name_1> Or <exception_name_2> Then        <Statement>    ...    When Others Then        <Statement>

系統定義的exception_name包括：

• NO_DATA_FOUND：select into語句未找到匹配元組
• TOO_MANY_ROWS：select into返回多行資料
• VALUE_ERROR：賦值錯誤（類型不符、長度過長）
• ZERO_DIVIDE：除零
• TIMEOUT_ON_RESOURCE：等待資源逾時

可以通過Raise <exception>產生一個自訂的異常，然後在Exception段捕捉它；也可以raise_application_error產生一個程式錯誤，由外部捕捉

10.3 遊標

PL/SQL每次只能處理單個元組，為了使其和SQL**多元組處理的特性保持一致，引入遊標**

遊標是客戶機上用來存放SQL語句返回中間結果的一塊記憶體，目的是為了協調PL/SQL和SQL間資料處理數目的矛盾

（1）聲明與開啟關閉

PL/SQL中遊標只能儲存select語句的中間結果，其聲明如下：

Declare    Cursor <cursor_name> IS <Select Statement>;

遊標聲明時不會立即執行，需要顯式調用open語句（對應關閉遊標的close語句）：

Begin    Open <cursor_name>;

（2）迴圈讀取

遊標中有一下幾個參數用於輔助控制遊標讀取資料：

• %FOUND：布爾，當前Fetch返回一行時為真
• %NOTFOUND：布爾，當前Fetch沒有返回一行時為真
• %ISOPEN：布爾，遊標已開啟為真
• %ROWCOUNT：數值，返回已從遊標中取出的元組數目

那麼利用Fetch into語句（返回一條元組）有一下兩種方式完成遊標資料訪問：

1.While迴圈版本Begin    Open <cursor_name>;    Fetch <cursor_name> into <record_type_variable>;    While <cursor_name>%FOUND Loop        DBMS_OUTPUT.PUT_LINE(...);        Fetch <cursor_name> into <record_type_variable>;    End Loop;    Close <cursor_name>;    ...2.For迴圈版本Begin    For <Loop_variable> in <cursor_name> Loop        DBMS_OUTPUT.PUT_LINE(...);    End Loop;    ...

其中有：

• Fetch into語句返回一條元組，cursor自動指向下一個元組
• For in可以簡潔的遍曆遊標內資料，迴圈開始前自動開啟遊標讀取資料，迴圈結束自動關閉遊標，並且自動為<Loop_variable>聲明記錄類型存放

（3）帶參數的遊標

遊標可以添加參數，如可以在參數中限定where子句的條件：

Cursor cs_s(val Number(3)) IS Select * from stu where age = val;

10.4 預存程序和函數

一般匿名的PL/SQL程式每次運行時都需要編譯，而命名PL/SQL程式如預存程序、函數和觸發器等則是編譯好駐留在資料庫中，可以隨時被SQL或其他PL/SQL程式調用

過程和匿名的PL/SQL程式只是聲明格式上些許不同：

Create [or Replace] Procedure <procedure_name> (    <param_name> In|Out|In Out <type> [:= <initial_value>],    ...)AS|IS    <Variable Declaration>Begin    <PL/SQL statement>Exception    <Exception Handler>End;)

其中有：

• In參數不能修改，Out參數只能賦值
• 參數不能指定長度，但可以用%TYPE
• 參數傳遞預設按位置順序傳遞，也可以顯式地用<param_name> => <param_value>按名稱亂序傳遞
• 函數是有傳回型別的預存程序，在AS前加上傳回型別聲明Return <return_type>然後再函數體中用return <return_value>返回對應類型的值，在其他的PL/SQL中按函數名傳參調用，並用:=賦值給其他變數

10.5 觸發器

觸發器是一類綁定在表上，由特定DML語句（Update、Insert、Delete）觸發自動執行的一段PL/SQL**預存程序**，一個表能有多個觸發器但一個觸發器只能綁定在一個表上

觸發器的概念在先前的SQL複習中已經詳細講解，這裡列出PL/SQL中觸發器聲明規則：

Create [Or Replace] Trigger <trigger_name>    // 聲明觸發動作與觸發時間    Before|After Delect|Insert|Update [Of <attribute_name>]    // Or 串連多個觸發動作時間聲明    {Or Before|After Delect|Insert|Update [Of <attribute_name>]}*On <table_name>    // 不寫表示語句觸發，寫For Each Row表示行觸發    [For Each Row]Declare...

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。