oracle存儲過程介紹解讀課件

1、Oracle存儲過程介紹*第1頁，共53頁。存儲過程介紹存儲過程概念介紹PL/SQL基礎(chǔ)游標(biāo)存儲過程的用戶接口JAVA調(diào)用數(shù)據(jù)庫存儲過程存儲過程的異常處理存儲過程的包第2頁，共53頁。存儲過程的概念（1）過程就是高級程序設(shè)計語言中的模塊的概念,將一些內(nèi)部聯(lián)系的命令組成一個個過程,通過參數(shù)在過程之間傳遞數(shù)據(jù)是模塊化設(shè)計思想的重要內(nèi)容.存儲過程（Stored Procedure）是在大型數(shù)據(jù)庫系統(tǒng)中，一組為了完成特定功能的SQL 語句集，存儲在數(shù)據(jù)庫中，經(jīng)過第一次編譯后再次調(diào)用不需要再次編譯，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。存儲過程是數(shù)據(jù)庫中的一個重要對象，

2、可以被有權(quán)用戶在任何需要的地方調(diào)用。在 oracle數(shù)據(jù)庫中，存儲過程是Oracle的一種第3頁，共53頁。存儲過程的概念（2）對象，是一種帶名的PL/SQL過程程序塊。在ORACLE中，若干個有聯(lián)系的過程可以組合在一起構(gòu)成程序包。第4頁，共53頁。存儲過程優(yōu)點（1）1）提高效率：存儲過程是預(yù)編譯過的，并且經(jīng)優(yōu)化后存儲于SQL內(nèi)存中，使用時無需再次編譯，提高了工作效率；2）減少網(wǎng)絡(luò)流量：存儲過程的代碼直接存放于數(shù)據(jù)庫中，一般由客戶端直接通過存儲過程的名字進(jìn)行調(diào)用，減少了網(wǎng)絡(luò)流量，加快了系統(tǒng)執(zhí)行速度，例如在進(jìn)行百萬以上的大批量數(shù)據(jù)查詢時，使用存儲過程分頁要比其他方式分頁快得多；3）安全性高：使用

3、存儲過程可以減少SQL注入式攻擊，提高了系統(tǒng)的安全性，執(zhí)行存儲過程的用戶要具有一定的權(quán)限才能使用存儲過程，沒有數(shù)據(jù)操作權(quán)限的用戶只能在其控制下間接地存取數(shù)據(jù);第5頁，共53頁。存儲過程優(yōu)點（2）4）重復(fù)使用。存儲過程可以重復(fù)使用，從而可以減少數(shù)據(jù)庫開發(fā)人員的工作量。5）靈活：使用存儲過程，可以實現(xiàn)存儲過程設(shè)計和編碼工作分開進(jìn)行，只要將存儲過程名、參數(shù)、及返回信息告訴編碼人員即可。第6頁，共53頁。存儲過程缺點（1）1）移植性差：使用存儲過程封裝業(yè)務(wù)邏輯將限制應(yīng)用程序的可移植性；2）維護(hù)成本高：如果更改存儲過程的參數(shù)或者其返回的數(shù)據(jù)及類型的話，需要修改應(yīng)用程序的相關(guān)代碼，比較繁瑣。第7頁，共53

4、頁。pl/sql基礎(chǔ)第8頁，共53頁。PL/SQL塊的基本結(jié)構(gòu)（1）基本的PL/SQL塊由定義部分,執(zhí)行部分,異常處理部分組成: DECLARE 定義部分 BEGIN 執(zhí)行部分 EXCEPTION 異常處理部分 END第9頁，共53頁。PL/SQL塊的基本結(jié)構(gòu)（2）定義部分: 定義在程序執(zhí)行部分使用的常量,變量,游標(biāo)和異常處理名稱可執(zhí)行部分 包括數(shù)據(jù)庫操作語句和PL/SQL塊控制語句異常處理部分 對執(zhí)行部分的所有PL/SQL語句的執(zhí)行進(jìn)行監(jiān)控,如執(zhí)行發(fā)生異常,則程序跳到該部分執(zhí)行第10頁，共53頁。定義變量（1） 在PLSQL中所使用的變量必須在變量定義部分明確定義.變量定義部分是包括在關(guān)鍵字

5、DECLARE和BEGIN之間的部分,每條語句后用(;)結(jié)束.定義格式: 變量標(biāo)示符 CONSTANT 數(shù)據(jù)類型 NOT NULL :=缺省值或PLSQL表達(dá)式; 變量標(biāo)示符命名規(guī)則應(yīng)遵循SQL實體命名規(guī)則第11頁，共53頁。定義變量（2）數(shù)據(jù)類型簡單數(shù)據(jù)類型(標(biāo)量數(shù)據(jù)類型): NUMBER(m,n) 數(shù)字類型 m為總長度,n為小數(shù)長度 CHAR(m) 字符型 m為變量長度 VARCHAR2(m) 可變長字符型 m為最大長度 DATE 日期型 LONG 長型 BOONEAN 布爾型 值為TRUE FALSE NULL第12頁，共53頁。賦值變量（1）變量賦值時需使用PLSQL變量賦值操作符(:

6、=) 常量賦值: 變量名 := 常量 變量賦值: 變量名 := 同類型變量 表達(dá)式賦值: 變量名:=表達(dá)式或函數(shù)第13頁，共53頁。PLSQL中使用的SQL語句在PL/SQL塊中,所有對數(shù)據(jù)庫的訪問和操作還是要經(jīng)由SQL語言進(jìn)行,在PL/SQL塊中可以使用數(shù)據(jù)查詢語言,數(shù)據(jù)操縱語言和數(shù)據(jù)控制語言,但不能使用數(shù)據(jù)定義語言具體地說可以使用select,insert,update,delete,commit, rollback,但不能使用create,alter,drop, grant,revoke.第14頁，共53頁。 PL/SQL流程控制PL/SQL具有與高級語言類似的流程控制語句.PL/SQL

7、主要控制語句有: 條件控制語句 循環(huán)控制語句 跳轉(zhuǎn)控制語句第15頁，共53頁。條件控制語句(1)IF_THEN語句語法: IF 條件 THEN 語句;END IF;條件可為IS NULL或NOT IS NULL以及AND, OR, NOT,邏輯運算符第16頁，共53頁。條件控制語句(2)IF_THEN_ELSE語句語法: IF 條件 THEN 語句;ELSE語句;END IF;條件可為IS NULL或NOT IS NULL以及AND, OR, NOT,邏輯運算符第17頁，共53頁。條件控制語句(3)IF_THAN_ELSIF語句:語法:IF 條件 THEN語句;ELSIF 條件 THEN語句;

8、ELSIF 條件 THEN 語句;ELSE 語句;END IF第18頁，共53頁。循環(huán)控制語句(1)LOOP循環(huán):語法:LOOP語句;EXIT WHEN 條件; END LOOP第19頁，共53頁。循環(huán)控制語句(2)for循環(huán):語法:FOR 計數(shù)器 IN REVERSE 下界上界 LOOP 語句; END LOOP第20頁，共53頁。循環(huán)控制語句(3)WHILE循環(huán):語法:WHILE 條件 LOOP 語句; END LOOP第21頁，共53頁。跳轉(zhuǎn)控制語句語法: 標(biāo)號 其他語句； GOTO 標(biāo)號;說明:（1）跳轉(zhuǎn)語句可在同一塊語句間跳轉(zhuǎn)（2）跳轉(zhuǎn)語句可從子塊跳轉(zhuǎn)倒父塊中，但不能從父塊跳轉(zhuǎn)到子塊

9、中（3）跳轉(zhuǎn)語句不能在IF語句體外跳到IF體內(nèi)（4）跳轉(zhuǎn)語句不能從循環(huán)體外跳到循環(huán)體內(nèi)第22頁，共53頁。游標(biāo)（cursor）第23頁，共53頁。為什么要使用游標(biāo)（1）SQL語言與主語言具有不同數(shù)據(jù)處理方式SQL語言是面向集合的，一條SQL語句原則上可以產(chǎn)生或處理多條記錄主語言是面向記錄的，一組主變量一次只能存放一條記錄第24頁，共53頁。為什么要使用游標(biāo)（2）僅使用主變量并不能完全滿足SQL語句向應(yīng)用程序輸出數(shù)據(jù)的要求引入游標(biāo)的概念，用來協(xié)調(diào)這兩種不同的處理方式 第25頁，共53頁。游標(biāo)游標(biāo)是系統(tǒng)為用戶開設(shè)的一個數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果每個游標(biāo)區(qū)都有一個名字用戶可以用SQL語句逐

10、一從游標(biāo)中獲取記錄，并賦給主變量，交由主語言進(jìn)一步處理第26頁，共53頁。定義游標(biāo)語法: CURSOR 游標(biāo)名稱 ISSELECT 語句;定義游標(biāo)應(yīng)寫在PL/SQL語句的DECLARE變量定義部分定義游標(biāo)時SELECT語句中不可有INTO子語句在SELECT語句中使用的變量必須在定義游標(biāo)前定義第27頁，共53頁。打開游標(biāo)語法: OPEN 游標(biāo)名;在BEGIN語句之后,可以打開游標(biāo),在打開游標(biāo)之前,必須對游標(biāo)所涉及到的變量賦值第28頁，共53頁。利用游標(biāo)提取數(shù)據(jù)語法: FETCH 游標(biāo)名 INTO 變量1,變量2,.;游標(biāo)每次只能取到一條數(shù)據(jù),同時游標(biāo)指針下移,等待取下一條數(shù)據(jù).該條語句變量列表

11、應(yīng)與定義游標(biāo)時的參數(shù)列表一致第29頁，共53頁。關(guān)閉游標(biāo)語法: CLOSE 游標(biāo)名;關(guān)閉游標(biāo),釋放資源,游標(biāo)關(guān)閉后不能再提取數(shù)據(jù).第30頁，共53頁。存儲過程的用戶接口創(chuàng)建存儲過程執(zhí)行存儲過程刪除存儲過程第31頁，共53頁。創(chuàng)建存儲過程創(chuàng)建存儲過程語法：CREATE Procedure 過程名（參數(shù)1，參數(shù)2，.） AS/IS；過程名：數(shù)據(jù)庫服務(wù)器合法的對象標(biāo)識參數(shù)列表：用名字來標(biāo)識調(diào)用時給出的參數(shù)值，必須指定值的數(shù)據(jù)類型。參數(shù)也可以定義輸入?yún)?shù)、輸出參數(shù)或輸入/輸出參數(shù)。默認(rèn)為輸入?yún)?shù)。過程體：是一個。包括聲明部分和可執(zhí)行語句部分 ;不用 declare 語句 第32頁，共53頁。創(chuàng)建存儲過

12、程(2)例子：例1 利用存儲過程來實現(xiàn)下面的應(yīng)用: 從一個賬戶轉(zhuǎn)指定數(shù)額的款項到另一個賬戶中。 CREATE PROCEDURE TRANSFER(inAccount INT， outAccount INT， amount FLOAT) AS totalDeposit FLOAT; BEGIN /* 檢查轉(zhuǎn)出賬戶的余額 */ SELECT total INTO totalDeposit FROM ACCOUNT WHERE ACCOUNTNUM=outAccount; IF totalDeposit IS NULL THEN /* 賬戶不存在或賬戶中沒有存款 */ ROLLBACK; RETU

13、RN; END IF; 第33頁，共53頁。創(chuàng)建存儲過程(3) IF totalDeposit amount THEN /* 賬戶賬戶存款不足 */ ROLLBACK; RETURN;END IF; UPDATE account SET total=total-amount WHERE ACCOUNTNUM=outAccount; /* 修改轉(zhuǎn)出賬戶，減去轉(zhuǎn)出額 */ UPDATE account SET total=total + amount WHERE ACCOUNTNUM=inAccount; /* 修改轉(zhuǎn)入賬戶，增加轉(zhuǎn)出額 */COMMIT; /* 提交轉(zhuǎn)賬事務(wù) */END;第34頁

14、，共53頁。執(zhí)行存儲過程執(zhí)行存儲過程語法：CALL/PERFORM Procedure 過程名(參數(shù)1，參數(shù)2，.)；在PL/SQL中，數(shù)據(jù)庫服務(wù)器支持在過程體中調(diào)用其他存儲過程使用CALL或者PERFORM等方式激活存儲過程的執(zhí)行。調(diào)用時”()”是不可少的，無論是有參數(shù)還是無參數(shù)。第35頁，共53頁。執(zhí)行存儲過程（2）例子：例2從賬戶01003815868轉(zhuǎn)一萬元到01003813828賬戶中。 CALL Procedure TRANSFER(01003813828，01003815868，10000)； 第36頁，共53頁。刪除存儲過程刪除存儲過程語法：DROP PROCEDURE 過程名

15、；第37頁，共53頁。JAVA調(diào)用數(shù)據(jù)庫存儲過程前面我們已經(jīng)講述了有關(guān)oracle數(shù)據(jù)庫的存儲過程的相關(guān)知識，下面我將根據(jù)上面存儲過程的實例來舉出JAVA對oracle存儲過程的調(diào)用第38頁，共53頁。僅有返回值的過程：public static void main(String args) Connection conn=BBConnection.getConnection();String sql=call stu_proc2(?);try CallableStatement statement=conn.prepareCall(sql);statement.registerOutPara

16、meter(1,Types.VARCHAR);statement.execute();String pname=statement.getString(1);System.out.println(pname); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();JAVA調(diào)用實例第39頁，共53頁。既有輸入?yún)?shù)又有輸出參數(shù)的過程public static void main(String args) Connection conn=BBConnection.getConnection();Str

17、ing sql=call stu_proc3(?,?);try CallableStatement statement=conn.prepareCall(sql);statement.setInt(1, 1);statement.registerOutParameter(2,Types.VARCHAR);statement.execute();String pname=statement.getString(2);System.out.println(pname); catch (SQLException e) / TODO Auto-generated catch blocke.printS

18、tackTrace();JAVA調(diào)用實例第40頁，共53頁。下面將舉出無out參數(shù)的調(diào)用實例這種參數(shù)不適于用在查詢語句上，因為查詢語句需要有返回值才能被JAVA調(diào)用返回到 OUT 參數(shù)中的值可能會是JDBC NULL。當(dāng)出現(xiàn)這種情形時，將對 JDBC NULL 值進(jìn)行轉(zhuǎn)換以使 getXXX 方法所返回的值為 null、0 或 false，這取決于getXXX 方法類型。對于 ResultSet 對象，要知道0或false是否源于JDBCNULL的唯一方法，是用方法wasNull進(jìn)行檢測。如果 getXXX 方法讀取的最后一個值是 JDBC NULL，則該方法返回 true，否則返回 flase

19、 JAVA調(diào)用實例第41頁，共53頁。僅有參數(shù)的過程：public static void main(String args) Connection conn=BBConnection.getConnection();String sql=call stu_proc1(?);try CallableStatement statement=conn.prepareCall(sql);statement.setInt(1, 1);statement.execute();System.out.println(statement.execute(); catch (SQLException e) /

20、TODO Auto-generated catch blocke.printStackTrace();finallytry conn.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();JAVA調(diào)用實例第42頁，共53頁。無參數(shù)過程：public static void main(String args) Connection conn=BBConnection.getConnection();String sql=call stu_proc();try CallableSta

21、tement statement=conn.prepareCall(sql);statement.execute();System.out.println(statement.execute(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();finallytry conn.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();JAVA調(diào)用實例第43頁，共53頁。由于orac

22、le存儲過程沒有返回值，它的所有返回值都是通過out參數(shù)來替代的，列表同樣也不例外，但由于是集合，所以不能用一般的參數(shù)，必須要用pagkage了.所以要分兩部分， 1. 建一個程序包。如下： CREATE OR REPLACE PACKAGE MYPACKAGE AS TYPE MY_CURSOR IS REF CURSOR; end MYPACKAGE; 2. 建立存儲過程，如下： CREATE OR REPLACE PROCEDURE PRO_3(p_CURSOR out MYPACKAGE.MY_CURSOR) IS BEGIN OPEN p_CURSOR FOR SELECT * FR

23、OM DBOEMP; END PRO_3; 可以看到，它是把游標(biāo)（可以理解為一個指針），作為一個out 參數(shù)來返回值的?？梢杂胹ys_refcursor替換MYPACKAGE.MY_CURSOR ；但sys_refcursor是oracle9i以后系統(tǒng)定義的一個refcursor,主要用在過程中返回結(jié)果集。JAVA調(diào)用實例(返回結(jié)果集-1)第44頁，共53頁。Java代碼：public static void main(String args) String driver = oracle.jdbc.driver.OracleDriver; String url = jdbc:oracle:t

24、hin::1521:orcl; String user = admin; String pwd = password; Connection conn = null; CallableStatement cs = null; ResultSet rs = null; try Class.forName(driver); conn = DriverManager.getConnection(url, user, pwd); cs = conn.prepareCall( call DBO.PRO_3(?) ); cs.registerOutParameter(1, oracle.

25、jdbc.OracleTypes.CURSOR); cs.execute(); rs = (ResultSet) cs.getObject(1); while (rs.next() System.out.println(t + rs.getString(1) + t + rs.getString(2) + t); catch (SQLException e) e.printStackTrace();JAVA調(diào)用實例(返回結(jié)果集-2)第45頁，共53頁。 catch (Exception e) e.printStackTrace(); finally try if (rs != null) rs

26、.close(); if (cs != null) cs.close(); if (conn != null) conn.close(); catch (SQLException e) JAVA調(diào)用實例(返回結(jié)果集-3)第46頁，共53頁。存儲過程的異常處理為了提高存儲過程的健壯性，避免運行錯誤，當(dāng)建立存儲過程時應(yīng)包含異常處理部分。異常(EXCEPTION)是一種PL/SQL標(biāo)識符，包括預(yù)定義異常、非預(yù)定義異常和自定義異常；預(yù)定義異常是指由PL/SQL提供的系統(tǒng)異常；非預(yù)定義異常用于處理與預(yù)定義異常無關(guān)的Oracle錯誤(如完整性約束等)；自定義異常用于處理與Oracle錯誤的其他異常情況。R

27、AISE_APPLICATION_ERROR用于自定義錯誤消息，并且消息號必須在2000020999之間第47頁，共53頁。Oracle數(shù)據(jù)庫中提供了一些異常處理的方法，下面通過一個實例來說明create or replace procedure stu_proc6(pno in student.sno%type,pname out student.sname%type) is begin select sname into pname from student where sno=pno; EXCEPTION when NO_DATA_FOUND then RAISE_APPLICATION

28、_ERROR(-20011,ERROR：不存在！);end;存儲過程的異常處理實例第48頁，共53頁。 命名的系統(tǒng)異常 產(chǎn)生原因 ACCESS_INTO_NULL 未定義對象 CASE_NOT_FOUND CASE 中若未包含相應(yīng)的 WHEN ，并且沒有設(shè)置 COLLECTION_IS_NULL 集合元素未初始化 CURSER_ALREADY_OPEN 游標(biāo)已經(jīng)打開 DUP_VAL_ON_INDEX 唯一索引對應(yīng)的列上有重復(fù)的值 INVALID_CURSOR 在不合法的游標(biāo)上進(jìn)行操作 INVALID_NUMBER內(nèi)嵌的 SQL 語句不能將字符轉(zhuǎn)換為數(shù)字 NO_DATA_FOUND 使用 select into 未返回行，或應(yīng)用索引表未初始化的 TOO_MANY_ROWS 執(zhí)行 select into 時，結(jié)果集超過一行 ZERO_DIVIDE 除數(shù)為 0 SUBSCRIPT_BEYOND_COUNT 元素下標(biāo)超過嵌套表或 VARRAY 的最大值 SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或 VARRAY 時，將下標(biāo)指定為負(fù)數(shù) VALUE_ERROR 賦值時，變量長度不足以容納實際數(shù)據(jù) LOGIN_DENIED PL/SQL 應(yīng)用程序連接到