第11章 PL-SQL和存儲過程ppt課件

1、Database Principles 數據庫原理數據庫原理 Database Principles Database Principles 第十一章第十一章 PL/SQLPL/SQL與存儲過與存儲過程程Database Principles 擴展SQL的數據處理能力 PL Procedural Language？ Programming Language？ Oracle在1992年引入PL/SQLDatabase Principles PL/SQL 擴展了控制語句，使得SQL具有程序模塊結構語言特征，包括異常處理 PL/SQL語句與其它現代編程語言C，java的語句類似 存儲過程 用PL/S

2、QL編寫，編譯、優(yōu)化后存儲在數據庫服務器中 是數據庫中可執(zhí)行PERFORM PROCEDURE語句的對象Database Principles 第十一章第十一章 PL/SQL與存儲過程與存儲過程11.1 PL/SQL的塊結構的塊結構 11.2 變量常量的定義變量常量的定義 11.3 控制結構控制結構 11.4 存儲過程存儲過程 11.5 小結小結 Database Principles 11.1 PL/SQL的塊結構的塊結構vPL/SQL ： vSQL的擴展 v增加了過程化語句功能 v基本結構是塊 v塊之間可以互相嵌套 v每個塊完成一個邏輯操作 Database Principles PL/S

3、QL的塊結構續(xù)）的塊結構續(xù)）vPL/SOL塊的基本結構：塊的基本結構： v 1.定義部分定義部分 v DECLARE v -變量、常量、游標、異常等變量、常量、游標、異常等 v定義的變量、常量等只能在該基本塊中使用定義的變量、常量等只能在該基本塊中使用 v當基本塊執(zhí)行結束時，定義就不再存在當基本塊執(zhí)行結束時，定義就不再存在 Database Principles PL/SQL的塊結構續(xù)）的塊結構續(xù)）vPL/SOL塊的基本結構塊的基本結構(續(xù)續(xù))： v 2.執(zhí)行部分執(zhí)行部分 v BEGIN v -SQL語句、語句、PL/SQL的流程控制語句的流程控制語句 v EXCEPTION v -異常處理部

4、分異常處理部分 v END；Database Principles 第十一章第十一章 PL/SQL與存儲過程與存儲過程11.1 PL/SQL的塊結構的塊結構 11.2 變量常量的定義變量常量的定義 11.3 控制結構控制結構 11.4 存儲過程存儲過程 11.5 小結小結 Database Principles 11.2 變量常量的定義變量常量的定義1. PL/SQL中定義變量的語法形式是: 變量名 數據類型 NOT NULL:=初值表達式或 變量名 數據類型 NOT NULL初值表達式 2. 常量的定義類似于變量的定義: 常量名 數據類型 CONSTANT :=常量表達式 常量必須要給一個值

5、，并且該值在存在期間或常量的作用域內不能改變。如果試圖修改它，PL/SQL將返回一個異常。 3. 賦值語句 變量名稱:=表達式Database Principles 第十一章第十一章 PL/SQL與存儲過程與存儲過程11.1 PL/SQL的塊結構的塊結構 11.2 變量常量的定義變量常量的定義 11.3 控制結構控制結構 11.4 存儲過程存儲過程 11.5 小結小結 Database Principles 11.3 控制結構控制結構 vPL/SQL 功能：功能： v一、條件控制語句一、條件控制語句 v二、循環(huán)控制語句二、循環(huán)控制語句 v三、錯誤處理三、錯誤處理 Database Princi

6、ples 控制結構續(xù)）控制結構續(xù)）一、一、 條件控制語句條件控制語句 IF-THEN， IF-THEN-ELSE和嵌套的和嵌套的IF語句語句 1. IF condition THEN Sequence_of_statements; END IF 2. IF condition THEN Sequence_of_statements1; ELSE Sequence_of_statements2; END IF; 3. 在在THEN和和ELSE子句中還可以再包括子句中還可以再包括IF語句，即語句，即IF語句可以嵌套語句可以嵌套 Database Principles 控制結構續(xù)）控制結構續(xù)）二、循

7、環(huán)控制語句二、循環(huán)控制語句 LOOP， WHILE-LOOP和和FOR-LOOP 1.最簡單的循環(huán)語句最簡單的循環(huán)語句LOOP LOOP Sequence_of_statements; END LOOP; 多數數據庫服務器的多數數據庫服務器的PL/SQL都提供都提供EXIT、BREAK或或LEAVE等循環(huán)結束語句，保證等循環(huán)結束語句，保證LOOP語句塊能夠結束。語句塊能夠結束。Database Principles 控制結構續(xù)）控制結構續(xù)）二、循環(huán)控制語句續(xù)）二、循環(huán)控制語句續(xù)） 2. WHILE-LOOP WHILE condition LOOP Sequence_of_statements

8、; END LOOP; 每次執(zhí)行循環(huán)體語句之前，首先對條件進行求值每次執(zhí)行循環(huán)體語句之前，首先對條件進行求值 如果條件為真，則執(zhí)行循環(huán)體內的語句序列。如果條件為真，則執(zhí)行循環(huán)體內的語句序列。 如果條件為假，則跳過循環(huán)并把控制傳遞給下一個語句如果條件為假，則跳過循環(huán)并把控制傳遞給下一個語句 3. FOR-LOOP FOR count IN REVERSEbound1 bound2 LOOP Sequence_of_statements; END LOOP;Database Principles 控制結構續(xù)）控制結構續(xù)）三、錯誤處理： 如果PL/SQL在執(zhí)行時出現異常，則應該讓程序在產生異常的語句

9、處停下來，根據異常的類型去執(zhí)行異常處理語句 SQL標準對數據庫服務器提供什么樣的異常處理做出了建議，要求PL/SQL管理器提供完善的異常處理機制 Database Principles 第十一章第十一章 PL/SQL與存儲過程與存儲過程11.1 PL/SQL的塊結構的塊結構 11.2 變量常量的定義變量常量的定義 11.3 控制結構控制結構 11.4 存儲過程存儲過程 11.5 小結小結 Database Principles 11.4 存儲過程存儲過程vPL/SQL塊類型： v命名塊：編譯后保存在數據庫中，可以被反復調用，運行速度較快。存儲過程和函數是命名塊 v匿名塊：每次執(zhí)行時都要進行編譯

10、，它不能被存儲到數據庫中，也不能在其他的PL/SQL塊中調用 Database Principles 存儲過程續(xù)）存儲過程續(xù)）一、 存儲過程的優(yōu)點 二、 存儲過程的用戶接口 三、 游標 Database Principles 存儲過程續(xù)）存儲過程續(xù)）v存儲過程：由PL/SQL語句書寫的過程，經編譯和優(yōu)化后存儲在數據庫服務器中，使用時只要調用即可。 v一、存儲過程的優(yōu)點： v1. 運行效率高 v2. 降低了客戶機和服務器之間的通信量v 3. 方便實施企業(yè)規(guī)則Database Principles 存儲過程續(xù)）存儲過程續(xù)）二、 存儲過程的用戶接口： 1. 創(chuàng)建存儲過程 2. 執(zhí)行存儲過程 3. 刪

11、除存儲過程 Database Principles 二、二、 存儲過程的用戶接口存儲過程的用戶接口1. 創(chuàng)建存儲過程：創(chuàng)建存儲過程： CREATE Procedure 過程名（參數過程名（參數1，參數，參數2，.） AS ； 過程名：數據庫服務器合法的對象標識過程名：數據庫服務器合法的對象標識 參數列表：用名字來標識調用時給出的參數值，必參數列表：用名字來標識調用時給出的參數值，必須指定值的數據類型。參數也可以定義輸入參數、須指定值的數據類型。參數也可以定義輸入參數、輸出參數或輸入輸出參數或輸入/輸出參數。默認為輸入參數。輸出參數。默認為輸入參數。 過程體：是一個過程體：是一個。包括聲明部分和

12、可。包括聲明部分和可執(zhí)行語句部分執(zhí)行語句部分 Database Principles 存儲過程的用戶接口續(xù)）存儲過程的用戶接口續(xù)）例11 利用存儲過程來實現下面的應用: 從一個賬戶轉指定數額的款項到另一個賬戶中。 CREATE PROCEDURE TRANSFER(inAccount INT， outAccount INT， amount FLOAT) AS DECLARE totalDeposit FLOAT; BEGIN /* 檢查轉出賬戶的余額 */ SELECT total INTO totalDeposit FROM ACCOUNT WHERE ACCOUNTNUM=outAccou

13、nt; IF totalDeposit IS NULL THEN /* 賬戶不存在或賬戶中沒有存款 */ ROLLBACK; RETURN; END IF; Database Principles 存儲過程的用戶接口續(xù)）存儲過程的用戶接口續(xù)） IF totalDeposit amount THEN /* 賬戶賬戶存款不足 */ ROLLBACK; RETURN; END IF; UPDATE account SET total=total-amount WHERE ACCOUNTNUM=outAccount; /* 修改轉出賬戶，減去轉出額 */ UPDATE account SET tota

14、l=total + amount WHERE ACCOUNTNUM=inAccount; /* 修改轉入賬戶，增加轉出額 */ COMMIT; /* 提交轉賬事務 */ END; Database Principles 存儲過程的用戶接口續(xù)）存儲過程的用戶接口續(xù)）v重命名存儲過程 v ALTER Procedure 過程名1 RENAME TO 過程名2;Database Principles 存儲過程的用戶接口續(xù)）存儲過程的用戶接口續(xù)）2. 執(zhí)行存儲過程：執(zhí)行存儲過程： CALL/PERFORM Procedure 過程名過程名(參數參數1，參，參數數2，.)； 使用使用CALL或者或者PE

15、RFORM等方式激活存儲過程的執(zhí)行。等方式激活存儲過程的執(zhí)行。 在在PL/SQL中，數據庫服務器支持在過程體中調用其他中，數據庫服務器支持在過程體中調用其他存儲過程存儲過程 例例12從賬戶從賬戶01003815868轉一萬元到轉一萬元到01003813828賬戶中。賬戶中。 CALL Procedure TRANSFER(01003813828，01003815868，10000)； Database Principles 存儲過程的用戶接口續(xù)）存儲過程的用戶接口續(xù)）3. 刪除存儲過程 DROP PROCEDURE 過程名（）；Database Principles 三、游三、游 標標 v 在PL/SQL中，如果SELECT語句只返回一條記錄，可以將該結果存放到變量中。 v 當查詢返回多條記錄時，就要使用游標對結果集進行處理 v 一個游標與一個SQL語句相關聯。