Oracle----PL/SQL
PL/SQL 是過程語言(Procedural Language)與結構化查詢語言(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 將大型二進制對象存儲在數據庫中
7. CLOB 將大型字符數據存儲在數據庫中
8. NCLOB 存儲大型UNICODE字符數據
9. BFILE 將大型二進制對象存儲在操作系統文件中
屬性類型
用於引用數據庫列的數據類型，以及表示表中一行的記錄類型
屬性類型有兩種：
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
運行時出現的錯誤叫做異常
異常可以分為預定義異常和用戶定義的異常
