使用plsql編程-有解無憂

第七章 使用PL/SQL編程

初識PL/SQL（Procedure Language & Structured Query Language）

PL/SQL是Oracle在標準SQL語言上的程序性擴展，允許嵌入SQL陳述句，定義變數和常量允許程序語言結構（條件分支陳述句和回圈陳述句）允許使用例外來處理Oracle錯誤可以用于創建存盤程序、觸發器和程式包等，也可以用于處理業務規則、資料庫事件或給SQL命令的執行添加程式邏輯

普通SQL語言只能實作訪問，操作資料；

PL/SQL可以實作流程控制，例外處理，創建可存盤的代碼塊；

PL/SQL塊

所有的PL/SQL程式都以塊作為基本單位

塊中包含程序化陳述句和SQL的DML陳述句，這些塊可以按順序出現，也可以相互嵌套（一個塊在另一個塊的內部）

塊的分類

1. 無名塊或匿名塊（anonymous）：動態構造，只能執行一次，可呼叫其它程式，但不能被其它程式呼叫，

2. 命名塊（named）：是帶有名稱的匿名塊，這個名稱就是標簽，

3. 子程式（subprogram）：存盤在資料庫中的存盤程序、函式等，當在資料庫上建立好后可以在其它程式中呼叫它們，

4. 觸發器（Trigger）：當資料庫發生操作時，會觸發一些事件，從而自動執行相應的程式，

5. 程式包/包（package）：存盤在資料庫中的一組子程式、變數定義，在包中的子程式可以被其它程式包或子程式呼叫，但如果宣告的是區域子程式，則只能在定義該區域子程式的塊中呼叫該區域子程式，

PL/SQL塊的結構

示例1：

DECLARE

V_NAME varchar2(20) := '郭老師';

BEGIN

DBMS_OUTPUT.put_line(V_NAME||'的第一條PL/SQL陳述句');

END;

與用戶互動輸入引數

declare

v_name varchar2(20):= '&input_name';

begin

dbms_output.put_line(v_name||'的第一條PL/SQL陳述句');

end;

示例2：

DECLARE

v_name1 varchar2(32);

v_name2 varchar2(32) := 'ABC';

v_num1 number := 13;

v_num2 number;

BEGIN

dbms_output.put_line(v_name1);

dbms_output.put_line(v_name1 || v_name2);

dbms_output.put_line(v_num1);

dbms_output.put_line(v_num1 + v_num2);

END;

注意：

1. 宣告變數時，必須要指定型別

2. 變數名需要先賦值，后使用

3. 變數名沒有默認值（表現是空，無意義）

示例3：

CONSTANT 定義常量值，定義后無法修改

DECLARE

v_number1 number := 13;

v_number2 CONSTANT number := 3.14;

BEGIN

DBMS_OUTPUT.put_line(v_number1 * v_number2);

END;

示例4：

變數與指定的列的型別一致采用%TYPE

DECLARE

v_num1 employees.salary%type := 13.234;

v_num2 employees.manager_id%type := 2;

BEGIN

DBMS_OUTPUT.put_line(v_num1 / v_num2);

END;

示例5：

DML結果裝載入PLSQL變數

declare

v_salary employees.salary%type;

begin

select salary into v_salary from employees where employee_id=198;

DBMS_OUTPUT.put_line('v_salary:'||v_salary);

end;

示例6：

%ROWTYPE表示資料型別是一行資料

DECLARE

v_emp employees%rowtype;

BEGIN

select * into v_emp from employees where employee_id = 100;

dbms_output.put_line('部門編號:'||v_emp.department_id ||

' 員工姓名:'|| v_emp.first_name);

END;

insert操作，接識訓傳值

示例6：

回傳值拼接

update操作，接識訓傳值

示例7：

delete操作，接識訓傳值

示例8：

陣列型別

題：通過用戶輸入的員工號，查詢一行記錄

例外處理

例外名稱	例外編碼	例外解釋
TIMEOUT_ON_RESOURCE	ORA-0051	發生超時
TOO_MANY_ROWS	ORA-1422	SELECT INTO命令回傳的多行
TRANSACTION_BACKED_OUT	ORA-006	由于死鎖提交被退回
VALUE_ERROR	ORA-6502	轉換或者裁剪錯誤
ZERO_DIVIDE	ORA-1476	試圖被零除
LOGIN_DENIED	ORA-1017	無效的用戶名或者口令
NO_DATA_FOUND	ORA-1403	查詢未找到資料
NOT_LOGGED_ON	ORA-1012	還未連接就試圖資料庫操作
PROGRAM_ERROR	ORA-6501	內部錯誤
ROWTYPE_MISMATCH	ORA-6504	主變數和游標的型別不兼容
STORAGE_ERROR	ORA-6500	內部錯誤
ACCESS_INTO_NULL	ORA-6530	試圖訪問一個未初始化的物件時出現
CASE_NOT_FOUND	ORA-6592	CASE陳述句中的選項與用戶輸入資料不匹配時出現
CURSOR_ALREADY_OPEN	ORA-6511	試圖打開一個已打開的游標
DUP_VAL_ON_INDEX	ORA-0001	試圖破壞一個唯一性限制
INVALID_CURSOR	ORA-1001	試圖使用一個無效的游標
INVALID_NUMBER	ORA-1722	試圖對非數字值進行數字操作