1、 PL/SQL 程序設計程序設計 目目 錄錄 第一章PL/SQL 程序設計簡介.4 1.2 SQL 與 PL/SQL.4 1.2.1 什么是 PL/SQL?.4 1.2.1 PL/SQL 的好處.4 1.2.2 PL/SQL 可用的 SQL 語句.5 1.3 運行 PL/SQL 程序.5 第二章 PL/SQL 塊結構和組成元素.6 2.1 PL/SQL 塊.6 2.2 PL/SQL 結構.6 2.3 標識符.6 2.4 PL/SQL 變量類型.7 2.4.1 變量類型.7 2.4.2 復合類型.9 2.4.3 使用%ROWTYPE .11 2.4.4 LOB 類型*.11 2.4.5 Bind

2、 變量.11 2.4.6 INDEX BY TABLES.12 2.4.7 數(shù)據(jù)類型的轉(zhuǎn)換*.13 2.5 運算符和表達式(數(shù)據(jù)定義).13 2.5.1 關系運算符.13 2.5.2 一般運算符.13 2.5.3 邏輯運算符.13 2.6 變量賦值.13 2.6.1 字符及數(shù)字運算特點.13 2.6.2 BOOLEAN 賦值.13 2.6.3 數(shù)據(jù)庫賦值.13 2.6.4 可轉(zhuǎn)換的類型賦值.13 2.7 變量作用范圍及可見性.13 2.8 注釋.13 2.9 簡單例子.13 2.9.1 簡單數(shù)據(jù)插入例子.13 2.9.2 簡單數(shù)據(jù)刪除例子.13 第三章 PL/SQL 流程控制語句.13 3.1

3、 條件語句.13 3.2 CASE 表達式.13 3.3 循環(huán).13 3.3 標號和 GOTO .13 3.4 NULL 語句 .13 第四章 游標的使用 .13 4.1 游標概念.13 4.1.1 處理顯式游標.13 4.1.2 處理隱式游標.13 4.1.3 游標修改和刪除操作.13 第五章 異常錯誤處理.13 5.1 異常處理概念.13 5.1.1 預定義的異常處理.13 5.1.2 非預定義的異常處理.13 5.1.3 用戶自定義的異常處理.13 5.1.4 用戶定義的異常處理.13 5.2 異常錯誤傳播.13 5.2.1 在執(zhí)行部分引發(fā)異常錯誤.13 5.2.2 在聲明部分引發(fā)異常錯

4、誤.13 5.3 異常錯誤處理編程.13 5.4 在 PL/SQL 中使用 SQLCODE, SQLERRM.13 第六章 存儲函數(shù)和過程.13 6.1 引言.13 6.2 創(chuàng)建函數(shù).13 6.3 存儲過程.13 6.3.1 創(chuàng)建過程.13 6.3.2 調(diào)用存儲過程.13 6.3.3 開發(fā)存儲過程步驟.13 6.3.4 與過程相關數(shù)據(jù)字典.13 第七章 包的創(chuàng)建和應用.13 7.1 引言.13 7.2 包的定義.13 7.3 包的開發(fā)步驟.13 7.4 包定義的說明.13 7.5 子程序重載.13 7.6 刪除過程、函數(shù)和包.13 7.7 包的管理.13 第八章 觸發(fā)器.13 8.1 觸發(fā)器類

5、型.13 8.1.1 DML 觸發(fā)器.13 8.1.2 替代觸發(fā)器.13 8.1.3 系統(tǒng)觸發(fā)器.13 8.2 創(chuàng)建觸發(fā)器.13 8.2.1 觸發(fā)器觸發(fā)次序.13 8.2.2 創(chuàng)建 DML 觸發(fā)器.13 8.2.3 創(chuàng)建替代(Instead_of)觸發(fā)器 .13 8.2.3 創(chuàng)建系統(tǒng)事件觸發(fā)器.13 8.2.4 系統(tǒng)觸發(fā)器事件屬性.13 8.2.5 使用觸發(fā)器謂詞.13 8.2.6 重新編譯觸發(fā)器.13 8.3 刪除和使能觸發(fā)器.13 8.4 觸發(fā)器和數(shù)據(jù)字典.13 8.5 數(shù)據(jù)庫觸發(fā)器的應用舉例.13 第一章第一章PL/SQL 程序設計簡介程序設計簡介 PL /SQL是一種高級數(shù)據(jù)庫程序設計

6、語言，該語言專門用于在各種環(huán)境下對ORACLE 數(shù)據(jù)庫進行訪問。由于該語言集成于數(shù)據(jù)庫服務器中，所以PL/SQL代碼可以對數(shù)據(jù)進行快 速高效的處理。除此之外，可以在ORACLE數(shù)據(jù)庫的某些客戶端工具中，使用PL/SQL語 言也是該語言的一個特點。本章的主要內(nèi)容是討論引入PL/SQL語言的必要性和該語言的主 要特點，以及了解PL/SQL語言的重要性和數(shù)據(jù)庫版本問題。還要介紹一些貫穿全書的更詳 細的高級概念，并在本章的最后就我們在本書案例中使用的數(shù)據(jù)庫表的若干約定做一說明。 本章主要重點：本章主要重點： PL/SQL 概述 PL/SQL 塊結構 PL/SQL 流程 運算符和表達式 游標 異常處理

7、數(shù)據(jù)庫存儲過程和函數(shù) 包 觸發(fā)器 1.2 SQL 與與 PL/SQL 1.2.1 什么是什么是 PL/SQL? PL/SQL 是 Procedure Language 其中 執(zhí)行部分是必須的。 PL/SQL 塊可以分為三類：塊可以分為三類： 1.無名塊：動態(tài)構造，只能執(zhí)行一次。 2.子程序：存儲在數(shù)據(jù)庫中的存儲過程、函數(shù)及包等。當在數(shù)據(jù)庫上建立好后可以在其 它程序中調(diào)用它們。 3.觸發(fā)器：當數(shù)據(jù)庫發(fā)生操作時，會觸發(fā)一些事件，從而自動執(zhí)行相應的程序。 2.2 PL/SQL 結構結構 PL/SQL 塊中可以包含子塊； 子塊可以位于 PL/SQL 中的任何部分； 子塊也即 PL/SQL 中的一條命令

8、； 2.3 標識符標識符 PL/SQL 程序設計中的標識符定義與 SQL 的標識符定義的要求相同。要求和限制有： 標識符名不能超過 30 字符； 第一個字符必須為字母； 不分大小寫； 不能用-(減號); 不能是 SQL 保留字。 提示提示: 一般不要把變量名聲明與表中字段名完全一樣一般不要把變量名聲明與表中字段名完全一樣,如果這樣可能得到不正確的結果如果這樣可能得到不正確的結果. 例如：下面的例子將會刪除所有的紀錄，而不是 KING 的記錄； DECLARE Ename varchar2(20) :=KING; BEGIN DELETE FROM emp WHERE ename=ename;

9、END; 變量命名在 PL/SQL 中有特別的講究，建議在系統(tǒng)的設計階段就要求所有編程人員共 同遵守一定的要求，使得整個系統(tǒng)的文檔在規(guī)范上達到要求。下面是建議的命名方法： 標識符命名規(guī)則例子 程序變量V_nameV_name 程序常量C_NameC_company_name 游標變量Name_cursorEmp_cursor 異常標識E_nameE_too_many 表類型Name_table_typeEmp_record_type 表Name_tableEmp 記錄類型Name_recordEmp_record SQL*Plus 替代變量P_nameP_sal 綁定變量G_nameG_yea

10、r_sal 2.4 PL/SQL 變量類型變量類型 在前面的介紹中，有系統(tǒng)的數(shù)據(jù)類型，也可以自定義數(shù)據(jù)類型。下表是 ORACLE 類 型和 PL/SQL 中的變量類型的合法使用列表： 2.4.1 變量類型變量類型 在 ORACLE8i 中可以使用的變量類型有： 類型子類說 明范 圍ORACLE 限制 CHARCharacter String Rowid Nchar 定長字符串 民族語言字符集 032767 可選,確省=1 2000 VARCHAR2Varchar, String NVARCHAR2 可變字符串 民族語言字符集 032767 4000 4000 BINARY_INT EGER 帶

11、符號整數(shù),為整數(shù)計算優(yōu) 化性能 NUMBER(p, s) Dec Double precision Integer Int Numeric Real 小數(shù), NUMBER 的子類型 高精度實數(shù) 整數(shù), NUMBER 的子類型 整數(shù), NUMBER 的子類型 與 NUMBER 等價 與 NUMBER 等價 整數(shù), 比 integer 小 Small int LONG變長字符串 0-32,767 字節(jié) DATE日期型 公元前 4712 年 1 月 1 日至公元后 4712 年 12 月 31 日 BOOLEAN布爾型TRUE, FALSE,NULL 不使用 ROWID存放數(shù)據(jù)庫行號 UROWID通

12、用行標識符，字符類型 例 1.插入一條記錄并顯示； DECLARE Row_id UROWID; info VARCHAR2(40); BEGIN INSERT INTO dept VALUES (90, SERVICE, BEIJING) RETURNING rowid, dname|:|to_char(deptno)|:|loc INTO row_id, info; DBMS_OUTPUT.PUT_LINE(ROWID:|row_id); DBMS_OUTPUT.PUT_LINE(info); END; 其中：RETURNING 子句用于檢索 INSERT 語句中所影響的數(shù)據(jù)行數(shù)，當 IN

13、SERT 語句使 用 VALUES 子句插入數(shù)據(jù)時，RETURNING 字句還可將列表達式、ROWID 和 REF 值返 回到輸出變量中。在使用 RETURNING 子句是應注意以下幾點限制： 1不能并行 DML 語句和遠程對象一起使用； 2不能檢索 LONG 類型信息； 3當通過視圖向基表中插入數(shù)據(jù)時，只能與單基表視圖一起使用。 例例 2. 修改一條記錄并顯示 DECLARE Row_id UROWID; info VARCHAR2(40); BEGIN UPDATE dept SET deptno=80 WHERE DNAME=SERVICE RETURNING rowid, dname|

14、:|to_char(deptno)|:|loc INTO row_id, info; DBMS_OUTPUT.PUT_LINE(ROWID:|row_id); DBMS_OUTPUT.PUT_LINE(info); END; 其中：RETURNING 子句用于檢索被修改行信息：當 UPDATE 語句修改單行數(shù)據(jù)時， RETURNING 子句可以檢索被修改行的 ROWID 和 REF 值，以及行中被修改列的列表達 式，并可將他們存儲到 PL/SQL 變量或復合變量中；當 UPDATE 語句修改多行數(shù)據(jù)時， RETURNING 子句可以將被修改行的 ROWID 和 REF 值，以及列表達式值返回到

15、復合變 量數(shù)組中。在 UPDATE 中使用 RETURNING 子句的限制與 INSERT 語句中對 RETURNING 子句的限制相同。 例例 3. 刪除一條記錄并顯示 DECLARE Row_id UROWID; info VARCHAR2(40); BEGIN DELETE dept WHERE DNAME=SERVICE RETURNING rowid, dname|:|to_char(deptno)|:|loc INTO row_id, info; DBMS_OUTPUT.PUT_LINE(ROWID:|row_id); DBMS_OUTPUT.PUT_LINE(info); END

16、; 其中：RETURNING 子句用于檢索被修改行信息：當 UPDATE 語句修改單行數(shù)據(jù)時， RETURNING 子句可以檢索被修改行的 ROWID 和 REF 值，以及行中被修改列的列表達 式，并可將他們存儲到 PL/SQL 變量或復合變量中；當 UPDATE 語句修改多行數(shù)據(jù)時， RETURNING 子句可以將被修改行的 ROWID 和 REF 值，以及列表達式值返回到復合變 量數(shù)組中。在 UPDATE 中使用 RETURNING 子句的限制與 INSERT 語句中對 RETURNING 子句的限制相同。 2.4.2 復合類型復合類型 ORACLE 在 PL/SQL 中除了提供象前面介紹

17、的各種類型外,還提供一種稱為復合類型 的類型-記錄和表. 記錄類型記錄類型 記錄類型是把邏輯相關的數(shù)據(jù)作為一個單元存儲起來，它必須包括至少一個標量型或 RECORD 數(shù)據(jù)類型的成員，稱作 PL/SQL RECORD 的域(FIELD)，其作用是存放互不相 同但邏輯相關的信息。 定義記錄類型語法如下定義記錄類型語法如下: TYPE record_type IS RECORD( Field1 type1 NOT NULL := exp1 , Field2 type2 NOT NULL := exp2 , . . . . . . Fieldn typen NOT NULL := ex

18、pn ) ; 例例 4 ： DECLARE TYPE test_rec IS RECORD( Code VARCHAR2(10), Name VARCHAR2(30) NOT NULL :=a book); V_book test_rec; BEGIN V_book.code :=123; V_ :=C+ Programming; DBMS_OUTPUT.PUT_LINE(v_book.code|v_); END; 可以用 SELECT 語句對記錄變量進行賦值,只要保證記錄字段與查詢結果列表中的字 段相配即可。 使用使用%TYPE 定義一個變

19、量，其數(shù)據(jù)類型與已經(jīng)定義的某個數(shù)據(jù)變量的類型相同，或者與數(shù)據(jù)庫表的某 個列的數(shù)據(jù)類型相同，這時可以使用%TYPE。 使用%TYPE 特性的優(yōu)點在于： 所引用的數(shù)據(jù)庫列的數(shù)據(jù)類型可以不必知道； 所引用的數(shù)據(jù)庫列的數(shù)據(jù)類型可以實時改變。 例例 5： DECLARE - 用 %TYPE 類型定義與表相配的字段 TYPE t_Record IS RECORD( T_no emp.empno%TYPE, T_name emp.ename%TYPE, T_sal emp.sal%TYPE ); - 聲明接收數(shù)據(jù)的變量 v_emp t_Record; BEGIN SELECT empno, ename, s

20、al INTO v_emp FROM emp WHERE empno=7788; DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_emp.t_no)|v_emp.t_name|TO_CHAR(v_emp.t_sal); END; 例例 6： DECLARE v_empno emp.empno%TYPE := Type r_record is record ( v_name emp.ename%TYPE, v_sal emp.sal%TYPE, v_date emp.hiredate%TYPE); Rec r_record; BEGIN SELECT ename, sal, hi

21、redate INTO Rec FROM emp WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE(Rec.v_name|-|Rec.v_sal|-|Rec.v_date); END; 2.4.3 使用使用%ROWTYPE PL/SQL 提供%ROWTYPE 操作符, 返回一個記錄類型, 其數(shù)據(jù)類型和數(shù)據(jù)庫表的數(shù) 據(jù)結構相一致。 使用%ROWTYPE 特性的優(yōu)點在于： 所引用的數(shù)據(jù)庫中列的個數(shù)和數(shù)據(jù)類型可以不必知道； 所引用的數(shù)據(jù)庫中列的個數(shù)和數(shù)據(jù)類型可以實時改變。 例例 7： DECLARE v_empno emp.empno%TYPE := rec emp

22、%ROWTYPE; BEGIN SELECT * INTO rec FROM emp WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE(姓名:|rec.ename|工資:|rec.sal|工作時間:|rec.hiredate); END; 2.4.4 LOB 類型類型* ORACLE 提供了 LOB (Large OBject)類型，用于存儲大的數(shù)據(jù)對象的類型。ORACLE 目前主要支持 BFILE, BLOB, CLOB 及 NCLOB 類型。 BFILE (Movie) 存放大的二進制數(shù)據(jù)對象，這些數(shù)據(jù)文件不放在數(shù)據(jù)庫里，而是放在操作系統(tǒng)的某個 目錄里，數(shù)

23、據(jù)庫的表里只存放文件的目錄。 BLOB(Photo) 存儲大的二進制數(shù)據(jù)類型。變量存儲大的二進制對象的位置。大二進制對象的大小 =4GB。 CLOB(Book) 存儲大的字符數(shù)據(jù)類型。每個變量存儲大字符對象的位置，該位置指到大字符數(shù)據(jù)塊。 大字符對象的大小=4GB。 NCLOB 存儲大的NCHAR字符數(shù)據(jù)類型。每個變量存儲大字符對象的位置，該位置指到大字 符數(shù)據(jù)塊。大字符對象的大小關系號 .范圍運算符 |字符連接符 2.5.3 邏輯運算符邏輯運算符 運算符意義 IS NULL是空值 BETWEEN介于兩者之間 IN在一列值中間 AND邏輯與 OR邏輯或 NOT取返,如 IS NOT NULL, NOT IN 2.6 變量賦值變量賦值 在 PL/SQL 編程中，變量賦值是一個值得注意的地方，它的語法如下： variable := expression ; variable 是一個 PL/SQL