游標和異常處理 oracle.doc

游標和異常處理游標和異常處理 游標的概念游標的概念 游標是 SQL 的一個內(nèi)存工作區(qū) 由系統(tǒng)或用戶以變量的形式定義 游標的作用就是用于臨時存儲從數(shù)據(jù)庫中提取的數(shù)據(jù)塊 在某些情況下 需要把數(shù)據(jù)從存放在磁盤的表中調(diào)到計算機內(nèi)存中進行處理 最后將處理 結(jié)果顯示出來或最終寫回數(shù)據(jù)庫 這樣數(shù)據(jù)處理的速度才會提高 否則頻繁的磁盤數(shù)據(jù)交 換會降低效率 游標有兩種類型 顯式游標和隱式游標 在前述程序中用到的 SELECT INTO 查詢語句 一次只能從數(shù)據(jù)庫中提取一行數(shù)據(jù) 對于 這種形式的查詢和 DML 操作 系統(tǒng)都會使用一個隱式游標 但是如果要提取多行數(shù)據(jù) 就 要由程序員定義一個顯式游標 并通過與游標有關的語句進行處理 顯式游標對應一個返回結(jié)果為多行多列的 SELECT 語句 游標一旦打開 數(shù)據(jù)就從數(shù)據(jù)庫中傳送到游標變量中 然后應用程序再從游標變量中分解 出需要的數(shù)據(jù) 并進行處理 隱式游標隱式游標 如前所述 DML 操作和單行 SELECT 語句會使用隱式游標 它們是 插入操作 INSERT 更新操作 UPDATE 刪除操作 DELETE 單行查詢操作 SELECT INTO 當系統(tǒng)使用一個隱式游標時 可以通過隱式游標的屬性來了解操作的狀態(tài)和結(jié)果 進而控 制程序的流程 隱式游標可以使用名字 SQL 來訪問 但要注意 通過 SQL 游標名總是只能 訪問前一個 DML 操作或單行 SELECT 操作的游標屬性 所以通常在剛剛執(zhí)行完操作之后 立即使用 SQL 游標名來訪問屬性 游標的屬性有四種 如下表所示 隱式游標的屬性 返回值類型 意 義 SQL ROWCOUNT 整型 代表 DML 語句成功執(zhí)行的數(shù)據(jù)行數(shù) SQL FOUND 布爾型 值為 TRUE 代表插入 刪除 更新或單行查詢操作 成功 SQL NOTFOUND 布爾型 與 SQL FOUND 屬性返回值相反 SQL ISOPEN 布爾型 DML 執(zhí)行過程中為真 結(jié)束后為假 范例 使用隱式游標的屬性 判斷對雇員工資的修改是否成功 范例 使用隱式游標的屬性 判斷對雇員工資的修改是否成功 SET SERVEROUTPUT ON BEGIN UPDATE emp SET sal sal 100 WHERE empno 1234 IF SQL FOUND THEN DBMS OUTPUT PUT LINE 成功修改雇員工資 成功修改雇員工資 COMMIT ELSE DBMS OUTPUT PUT LINE 修改雇員工資失敗 修改雇員工資失敗 END IF END 說明 本例中 通過說明 本例中 通過 SQL FOUND 屬性判斷修改是否成功 并給出相應信息 屬性判斷修改是否成功 并給出相應信息 顯式游標顯式游標 游標的定義和操作游標的定義和操作 游標的使用分成以下 4 個步驟 1 聲明游標 在 DECLEAR 部分按以下格式聲明游標 CURSOR 游標名 參數(shù) 1 數(shù)據(jù)類型 參數(shù) 2 數(shù)據(jù)類型 IS SELECT 語句 參數(shù)是可選部分 所定義的參數(shù)可以出現(xiàn)在 SELECT 語句的 WHERE 子句中 如果定義了參數(shù) 則必須在打開游標時傳遞相應的實際參數(shù) SELECT 語句是對表或視圖的查詢語句 甚至也可以是聯(lián)合查詢 可以帶 WHERE 條件 ORDER BY 或 GROUP BY 等子句 但不能使用 INTO 子句 在 SELECT 語句中可以使用在 定義游標之前定義的變量 2 打開游標 在可執(zhí)行部分 按以下格式打開游標 OPEN 游標名 實際參數(shù) 1 實際參數(shù) 2 打開游標時 SELECT 語句的查詢結(jié)果就被傳送到了游標工作區(qū) 3 提取數(shù)據(jù) 在可執(zhí)行部分 按以下格式將游標工作區(qū)中的數(shù)據(jù)取到變量中 提取操作必 須在打開游標之后進行 FETCH 游標名 INTO 變量名 1 變量名 2 或 FETCH 游標名 INTO 記錄變量 游標打開后有一個指針指向數(shù)據(jù)區(qū) FETCH 語句一次返回指針所指的一行數(shù) 據(jù) 要返回多行需重復執(zhí)行 可以使用循環(huán)語句來實現(xiàn) 控制循環(huán)可以通過判斷游標 的屬性來進行 下面對這兩種格式進行說明 第一種格式中的變量名是用來從游標中接收數(shù)據(jù)的變量 需要事先定義 變 量的個數(shù)和類型應與 SELECT 語句中的字段變量的個數(shù)和類型一致 第二種格式一次將一行數(shù)據(jù)取到記錄變量中 需要使用 ROWTYPE 事先定義 記錄變量 這種形式使用起來比較方便 不必分別定義和使用多個變量 定義記錄變量的方法如下 變量名 表名 游標名 ROWTYPE 其中的表必須存在 游標名也必須先定義 4 關閉游標 CLOSE 游標名 顯式游標打開后 必須顯式地關閉 游標一旦關閉 游標占用的資源就被釋 放 游標變成無效 必須重新打開才能使用 范例 以下是使用顯式游標的一個簡單練習 用游標提取 emp 表中 7788 雇員的名稱和職 務 yb1 sql SET SERVEROUTPUT ON DECLARE v ename VARCHAR2 10 v job VARCHAR2 10 CURSOR emp cursor IS SELECT ename job FROM emp WHERE empno 7788 BEGIN OPEN emp cursor FETCH emp cursor INTO v ename v job DBMS OUTPUT PUT LINE v ename v job CLOSE emp cursor END 說明 該程序通過定義游標 emp cursor 提取并顯示雇員 7788 的名稱和職務 作 為對以上例子的改進 在以下訓練中采用了記錄變量 范例 用游標提取 emp 表中 7788 雇員的姓名 職務和工資 yb2 sql SET SERVEROUTPUT ON DECLARE CURSOR emp cursor IS SELECT ename job sal FROM emp WHERE empno 7788 emp record emp cursor ROWTYPE BEGIN OPEN emp cursor FETCH emp cursor INTO emp record DBMS OUTPUT PUT LINE emp record ename emp record job to char emp record sal CLOSE emp cursor END 說明 實例中使用記錄變量來接收數(shù)據(jù) 記錄變量由游標變量定義 需要出現(xiàn)在游標定義 之后 注意 可通過以下形式獲得記錄變量的內(nèi)容 記錄變量名 字段名 范例 顯示工資最高的前 3 名雇員的名稱和工資 yb3 sql SET SERVEROUTPUT ON DECLARE v ename VARCHAR2 10 v sal NUMBER 5 CURSOR emp cursor IS SELECT ename sal FROM emp ORDER BY sal DESC BEGIN OPEN emp cursor FOR I IN 1 3 LOOP FETCH emp cursor INTO v ename v sal DBMS OUTPUT PUT LINE v ename v sal END LOOP CLOSE emp cursor END 說明 該程序在游標定義中使用了 ORDER BY 子句進行排序 并使用循環(huán)語句來提取多行 數(shù)據(jù) 游標循環(huán)游標循環(huán) 范例 使用特殊的 FOR 循環(huán)形式顯示全部雇員的編號和名稱 yb4 sql SET SERVEROUTPUT ON DECLARE CURSOR emp cursor IS SELECT empno ename FROM emp BEGIN FOR emp record IN emp cursor LOOP DBMS OUTPUT PUT LINE Emp record empno Emp record ename END LOOP END 說明 可以看到該循環(huán)形式非常簡單 隱含了記錄變量的定義 游標的打開 提取和關閉 過程 Emp record 為隱含定義的記錄變量 循環(huán)的執(zhí)行次數(shù)與游標取得的數(shù)據(jù)的行數(shù)相一 致 范例 另一種形式的游標循環(huán) yb5 sql SET SERVEROUTPUT ON BEGIN FOR re IN SELECT ename FROM EMP LOOP DBMS OUTPUT PUT LINE re ename END LOOP END 說明 該種形式更為簡單 省略了游標的定義 游標的 SELECT 查詢語句在循環(huán)中直接出現(xiàn) 顯式游標屬性顯式游標屬性 雖然可以使用前面的形式獲得游標數(shù)據(jù) 但是在游標定義以后使用它的一些屬性來進 行結(jié)構(gòu)控制是一種更為靈活的方法 顯式游標的屬性如下表所示 游標的屬性 返回值類型 意 義 ROWCOUNT 整型 獲得 FETCH 語句返回的數(shù)據(jù)行數(shù) FOUND 布爾型 最近的 FETCH 語句返回一行數(shù)據(jù)則為真 否則為假 NOTFOUND 布爾型 與 FOUND 屬性返回值相反 ISOPEN 布爾型 游標已經(jīng)打開時值為真 否則為假 可按照以下形式取得游標的屬性 游標名 屬性 要判斷游標 emp cursor 是否處于打開狀態(tài) 可以使用屬性 emp cursor ISOPEN 如果游標已經(jīng)打開 則返回值為 真 否則為 假 具體可參照以下的訓練 范例 使用游標的屬性練習 yb6 sql SET SERVEROUTPUT ON DECLARE v ename VARCHAR2 10 CURSOR emp cursor IS SELECT ename FROM emp BEGIN OPEN emp cursor IF emp cursor ISOPEN THEN LOOP FETCH emp cursor INTO v ename EXIT WHEN emp cursor NOTFOUND DBMS OUTPUT PUT LINE to char emp cursor ROWCOUNT v ename END LOOP ELSE DBMS OUTPUT PUT LINE 用戶信息 游標沒有打開 END IF CLOSE emp cursor END 說明 本例使用 emp cursor ISOPEN 判斷游標是否打開 使用 emp cursor ROWCOUNT 獲 得到目前為止 FETCH 語句返回的數(shù)據(jù)行數(shù)并輸出 使用循環(huán)來獲取數(shù)據(jù) 在循環(huán)體中使用 FETCH 語句 使用 emp cursor NOTFOUND 判斷 FETCH 語句是否成功執(zhí)行 當 FETCH 語句 失敗時說明數(shù)據(jù)已經(jīng)取完 退出循環(huán) 練習 去掉 OPEN emp cursor 語句 重新執(zhí)行以上程序 游標參數(shù)的傳遞游標參數(shù)的傳遞 了解了解 范例 帶參數(shù)的游標 yb7 sql SET SERVEROUTPUT ON DECLARE v empno NUMBER 5 v ename VARCHAR2 10 CURSOR emp cursor p deptno NUMBER p job VARCHAR2 IS SELECTempno ename FROM emp WHEREdeptno p deptno AND job p job BEGIN OPEN emp cursor 10 CLERK LOOP FETCH emp cursor INTO v empno v ename EXIT WHEN emp cursor NOTFOUND DBMS OUTPUT PUT LINE v empno v ename END LOOP END 說明 游標 emp cursor 定義了兩個參數(shù) p deptno 代表部門編號 p job 代表職務 語句 OPEN emp cursor 10 CLERK 傳遞了兩個參數(shù)值給游標 即部門為 10 職務為 CLERK 所以 游標查詢的內(nèi)容是部門 10 的職務為 CLERK 的雇員 循環(huán)部分用于顯示查詢的內(nèi)容 練習 修改 Open 語句的參數(shù) 部門號為 20 職務為 ANALYST 并重新執(zhí)行 也可以通過變量向游標傳遞參數(shù) 但變量需要先于游標定義 并在游標打開之前賦值 對 以上例子重新改動如下 范例 通過變量傳遞參數(shù)給游標 yb8 sql SET SERVEROUTPUT ON DECLARE v empno NUMBER 5 v ename VARCHAR2 10 v deptno NUMBER 5 v job VARCHAR2 10 CURSOR emp cursor IS SELECT empno ename FROM emp WHEREdeptno v deptno AND job v job BEGIN v deptno 10 v job CLERK OPEN emp cursor LOOP FETCH emp cursor INTO v empno v ename EXIT WHEN emp cursor NOTFOUND DBMS OUTPUT PUT LINE v empno v ename END LOOP END 說明 該程序與前一程序?qū)崿F(xiàn)相同的功能 利用游標刪除和修改數(shù)據(jù)的時候要注意 UPDATE 表名 SET WHERE CURRENT OF 游標名 DELETE 表名 WHERE CURRENT OF 游標名 范例 定義游標 emp cur 通過使用游標 根據(jù)職務調(diào)整雇員的工資 yb9 sql SET SERVEROUTPUT ON DECLARE v job emp job TYPE CURSOR emp cur IS SELECT job FROM emp FOR UPDATE BEGIN OPEN emp cur LOOP FETCH emp cur INTO v job EXIT WHEN emp cur NOTFOUND CASE WHEN v job CLERK THEN update emp set sal sal 50 where current of emp cur WHEN v job SALESMAN OR v job ANALYST THEN update emp set sal sal 40 where current of emp cur ELSE update emp set sal sal 10 where current of emp cur END CASE END LOOP COMMIT END 范例 用游標 For 循環(huán) 實現(xiàn)打印某一職務 輸入一職務 的雇員的雇員編號和雇員姓名 yb10 sql 可參考 yb4 sql DECLARE v job emp job TYPE CURSOR emp cursor IS SELECT empno ename FROM emp WHERE job v job BEGIN v job FOR emp record IN emp cursor LOOP DBMS OUTPUT PUT LINE Emp record empno Emp record ename END LOOP END 異常處理異常處理 錯誤處理錯誤處理 錯誤處理部分位于程序的可執(zhí)行部分之后 是由 WHEN 語句引導的多個分支構(gòu)成 的 錯誤處理的語法如下 EXCEPTION WHEN 錯誤 1 OR 錯誤 2 THEN 語句序列 1 WHEN 錯誤 3 OR 錯誤 4 THEN 語句序列 2 WHEN OTHERS 語句序列 n END 其中 錯誤是在標準包中由系統(tǒng)預定義的標準錯誤 或是由用戶在程序的說明部分自定 義的錯誤 參見下一節(jié)系統(tǒng)預定義的錯誤類型 語句序列就是不同分支的錯誤處理部分 凡是出現(xiàn)在 WHEN 后面的錯誤都是可以捕捉到的錯誤 其他未被捕捉到的錯誤 將在 WHEN OTHERS 部分進行統(tǒng)一處理 OTHERS 必須是 EXCEPTION 部分的最后一個錯誤處理分 支 如要在該分支中進一步判斷錯誤種類 可以通過使用預定義函數(shù) SQLCODE 和 SQLERRM 來獲得系統(tǒng)錯誤號和錯誤信息 如果在程序的子塊中發(fā)生了錯誤 但子塊沒有錯誤處理部分 則錯誤會傳遞到主 程序中 下面是由于查詢編號錯誤而引起系統(tǒng)預定義異常的例子 范例 查詢編號為 1234 的雇員名字 SET SERVEROUTPUT ON DECLARE v name VARCHAR2 10 BEGIN SELECTename INTOv name FROMemp WHEREempno 1234 DBMS OUTPUT PUT LINE 該雇員名字為 v name EXCEPTION WHEN NO DATA FOUND THEN DBMS OUTPUT PUT LINE 編號錯誤 沒有找到相應雇員 WHEN OTHERS THEN DBMS OUTPUT PUT LINE 發(fā)生其他錯誤 END 說明 在以上查詢中 因為編號為 1234 的雇員不存在 所以將發(fā)生類型為 NO DATA FOUND 的異常 NO DATA FOUND 是系統(tǒng)預定義的錯誤類型 EXCEPTION 部分下的 WHEN 語句將捕捉到該異常 并執(zhí)行相應代碼部分 在本例中 輸出用戶自定義的錯誤信 息 編號錯誤 沒有找到相應雇員 如果發(fā)生其他類型的錯誤 將執(zhí)行 OTHERS 條件下的 代碼部分 顯示 發(fā)生其他錯誤 范例 由程序代碼顯示系統(tǒng)錯誤 SET SERVEROUTPUT ON DECLARE v temp NUMBER 5 1 BEGIN v temp v temp 0 EXCEPTION WHEN OTHERS THEN DBMS OUTPUT PUT LINE 發(fā)生系統(tǒng)錯誤 DBMS OUTPUT PUT LINE 錯誤代碼 SQLCODE DBMS OUTPUT PUT LINE 錯誤信息 SQLERRM END 說明 程序運行中發(fā)生除零錯誤 由 WHEN OTHERS 捕捉到 執(zhí)行用戶自己的輸出語句顯 示錯誤信息 然后正常結(jié)束 在錯誤處理部分使用了預定義函數(shù) SQLCODE 和 SQLERRM 來進一步獲得錯誤的代碼和種類信息 預定義錯誤 了解 預定義錯誤 了解 Oracle 的系統(tǒng)錯誤很多 但只有一部分常見錯誤在標準包中予以定義 定義的錯誤可以在 EXCEPTION 部分通過標準的錯誤名來進行判斷 并進行異常處理 常見的系統(tǒng)預定義異常 如下表所示 錯 誤 名 稱 錯誤代碼 錯 誤 含 義 CURSOR ALREADY OPEN ORA 06511 試圖打開已經(jīng)打開的游標 INV ALID CURSOR ORA 01001 試圖使用沒有打開的游標 DUP V AL ON INDEX ORA 00001 保存重復值到惟一索引約束的列中 ZERO DIVIDE ORA 01476 發(fā)生除數(shù)為零的除法錯誤 INV ALID NUMBER ORA 01722 試圖對無效字符進行數(shù)值轉(zhuǎn)換 ROWTYPE MISMATCH ORA 06504 主變量和游標的類型不兼容 V ALUE ERROR ORA 06502 轉(zhuǎn)換 截斷或算術(shù)運算發(fā)生錯誤 TOO MANY ROWS ORA 01422 SELECT INTO 語句返回多于一行的數(shù)據(jù) NO DATA FOUND ORA 01403 SELECT INTO 語句沒有數(shù)據(jù)返回 TIMEOUT ON RESOURCE ORA 00051 等待資源時發(fā)生超時錯誤 TRANSACTION BACKED OUT ORA 00060 由于死鎖 提交失敗 STORAGE ERROR ORA 06500 發(fā)生內(nèi)存錯誤 PROGRAM ERROR ORA 06501 發(fā)生 PL SQL 內(nèi)部錯誤 NOT LOGGED ON ORA 01012 試圖操作未連接的數(shù)據(jù)庫 LOGIN DENIED ORA 01017 在連接時提供了無效用戶名或口令 比如 如果程序向表的主鍵列插入重復值 則將發(fā)生 DUP VAL ON INDEX 錯誤 如果一個系統(tǒng)錯誤沒有在標準包中定義 則需要在說明部分定義 語法如下 錯誤名 EXCEPTION 定義后使用 PRAGMA EXCEPTION INIT 來將一個定義的錯誤同一個特別的 Oracle 錯 誤代碼相關聯(lián) 就可以同系統(tǒng)預定義的錯誤一樣使用了 語法如下 PRAGMA EXCEPTION INIT 錯誤名 錯誤代碼 范例 定義新的系統(tǒng)錯誤類型 SET SERVEROUTPUT ON DECLARE V ENAME VARCHAR2 10 NULL INSERT ERROR EXCEPTION PRAGMA EXCEPTION INIT NULL INSERT ERROR 1400 BEGIN INSERT INTO EMP EMPNO VALUES NULL EXCEPTION WHEN NULL INSERT ERROR THEN DBMS OUTPUT PUT LINE 無法插入 NULL 值 WHEN OTHERS THEN DBMS OUTPUT PUT LINE 發(fā)生其他系統(tǒng)錯誤 END 執(zhí)行結(jié)果為 無法插入 NULL 值 PL SQL 過程已成功完成 說明 NULL INSERT ERROR 是自定義異常 同系統(tǒng)錯誤 1400 相關聯(lián) 自定義異常自定義異常 程序設計者可以利用引發(fā)異常的機制來進行程序設計 自己定義異常類型 可以 在聲明部分定義新的異常類型 定義的語法是 錯誤名 EXCEPTION 用戶定義的錯誤不能由系統(tǒng)來觸發(fā) 必須由程序顯式地觸發(fā) 觸發(fā)的語法是 RAISE 錯誤名 RAISE 也可以用來引發(fā)模擬系統(tǒng)錯誤 比如 RAISE ZERO DIVIDE 將引發(fā)模擬的除零錯誤 使用 RAISE APPLICATION ERROR 函數(shù)也可以引發(fā)異常 該函數(shù)要傳遞兩個參數(shù) 第一個是用戶自定義的錯誤編號 第二個參數(shù)是用戶自定義的錯誤信息 使用該函數(shù)引發(fā) 的異常的編號應該在 20 000 和 20 999 之間選擇 自定義異常處理錯誤的方式同前 范例 插入新雇員 限定插入雇員的編號在 7000 8000 之間 SET SERVEROUTPUT ON DECLARE new no NUMBER 10 new excp1 EXCEPTION new excp2 EXCEPTION BEGIN new no 6789 INSERT INTOemp empno ename VALUES new no 小鄭 IF new no8000 THEN RAISE new excp2 END IF COMMIT EXCEPTION WHEN new excp1 THEN ROLLBACK DBMS OUTPUT PUT LINE 雇員編號小于 7000 的下限 WHEN new excp2 THEN ROLLBACK DBMS OUTPUT PUT LINE 雇員編號超過 8000 的上限 END 執(zhí)行結(jié)果為 雇員編號小于 7000 的下限 PL SQL 過程已成功完成 說明 在此例中 自定義了兩個異常 new excp1 和 new excp2 分別代表編號小于 7000 和編號大于 8000 的錯誤 在程序中通過判斷編號大小 產(chǎn)生對應的異常 并在異常處理部 分回退插入操作 然后顯示相應的錯誤信息 范例 使用 RAISE APPLICATION ERROR 函數(shù)引發(fā)系統(tǒng)異常 SET SERVEROUTPUT ON DECLARE New no NUMBER 10 BEGIN New no 6789 INSERT INTO emp empno ename VALUES new no JAMES IF new no8000 THEN ROLLBACK RAISE APPLICATION ERROR 20002 編號大于 8000 的下限 END IF END 執(zhí)行結(jié)果為 DECLARE ERROR 位于第 1 行 ORA 20001 編號小于 7000 的下限 ORA 06512 在 line 9 說明 在本訓練中 使用 RAISE APPLICATION ERROR 引發(fā)自定義異常 并以系統(tǒng) 錯誤的方式進行顯示 錯誤編號為 20001 和 20002 注意 同上一個訓練比較 此種方法不需要事先定義異常 可直接引發(fā) 可以參考下面的程序片斷將出錯信息記錄到表中 其中 errors 為記錄錯誤信息的表 SQLCODE 為發(fā)生異常的錯誤編號 SQLERRM 為發(fā)生異常的錯誤信息 DECLARE v error code NUMBER v error message VARCHAR2 255 BEGIN EXCEPTION WHEN OTHERS THEN v error code SQLCODE v error message SQLERRM INSERT INTO errors VALUES v error code v error message END 練習 修改雇員的工資 通過引發(fā)異?？刂菩薷姆秶?600 6000 之間 存儲過程 函數(shù)和包存儲過程 函數(shù)和包 認識存儲過程和函數(shù)認識存儲過程和函數(shù) 存儲過程和函數(shù)也是一種 PL SQL 塊 是存入數(shù)據(jù)庫的 PL SQL 塊 但存儲過程和函數(shù)不同 于已經(jīng)介紹過的 PL SQL 程序 我們通常把 PL SQL 程序稱為無名塊 而存儲過程和函數(shù)是 以命名的方式存儲于數(shù)據(jù)庫中的 和 PL SQL 程序相比 存儲過程有很多優(yōu)點 具體歸納如 下 存儲過程和函數(shù)以命名的數(shù)據(jù)庫對象形式存儲于數(shù)據(jù)庫當中 存儲在數(shù)據(jù)庫中的優(yōu)點 是很明顯的 因為代碼不保存在本地 用戶可以在任何客戶機上登錄到數(shù)據(jù)庫 并調(diào) 用或修改代碼 存儲過程和函數(shù)可由數(shù)據(jù)庫提供安全保證 要想使用存儲過程和函數(shù) 需要有存儲過 程和函數(shù)的所有者的授權(quán) 只有被授權(quán)的用戶或創(chuàng)建者本身才能執(zhí)行存儲過程或調(diào)用 函數(shù) 存儲過程和函數(shù)的信息是寫入數(shù)據(jù)字典的 所以存儲過程可以看作是一個公用模塊 用戶編寫的 PL SQL 程序或其他存儲過程都可以調(diào)用它 但存儲過程和函數(shù)不能調(diào)用 PL SQL 程序 一個重復使用的功能 可以設計成為存儲過程 比如 顯示一張工資統(tǒng) 計表 可以設計成為存儲過程 一個經(jīng)常調(diào)用的計算 可以設計成為存儲函數(shù) 根據(jù) 雇員編號返回雇員的姓名 可以設計成存儲函數(shù) 像其他高級語言的過程和函數(shù)一樣 可以傳遞參數(shù)給存儲過程或函數(shù) 參數(shù)的傳遞也 有多種方式 存儲過程可以有返回值 也可以沒有返回值 存儲過程的返回值必須通 過參數(shù)帶回 函數(shù)有一定的數(shù)據(jù)類型 像其他的標準函數(shù)一樣 我們可以通過對函數(shù) 名的調(diào)用返回函數(shù)值 存儲過程和函數(shù)需要進行編譯 以排除語法錯誤 只有編譯通過才能調(diào)用 創(chuàng)建和刪除存儲過程創(chuàng)建和刪除存儲過程 創(chuàng)建存儲過程 需要有 CREATE PROCEDURE 或 CREATE ANY PROCEDURE 的系統(tǒng)權(quán) 限 該權(quán)限可由系統(tǒng)管理員授予 創(chuàng)建一個存儲過程的基本語句如下 CREATE OR REPLACE PROCEDURE 存儲過程名 參數(shù) IN OUT IN OUT 數(shù)據(jù)類型 AS IS 說明部分 BEGIN 可執(zhí)行部分 EXCEPTION 錯誤處理部分 END 過程名 其中 可選關鍵字 OR REPLACE 表示如果存儲過程已經(jīng)存在 則用新的存儲過程覆蓋 通常用于存儲過程的重建 參數(shù)部分用于定義多個參數(shù) 如果沒有參數(shù) 就可以省略 參數(shù)有三種形式 IN OUT 和 IN OUT 如果沒有指明參數(shù)的形式 則默認為 IN 關鍵字 AS 也可以寫成 IS 后跟過程的說明部分 可以在此定義過程的局部變量 編寫存儲過程可以使用任何文本編輯器或直接在 SQL Plus 環(huán)境下進行 編寫好的 存儲過程必須要在 SQL Plus 環(huán)境下進行編譯 生成編譯代碼 原代碼和編譯代碼在編譯過 程中都會被存入數(shù)據(jù)庫 編譯成功的存儲過程就可以在 Oracle 環(huán)境下進行調(diào)用了 一個存儲過程在不需要時可以刪除 刪除存儲過程的人是過程的創(chuàng)建者或者擁有 DROP ANY PROCEDURE 系統(tǒng)權(quán)限的人 刪除存儲過程的語法如下 DROP PROCEDURE 存儲過程名 如果要重新編譯一個存儲過程 則只能是過程的創(chuàng)建者或者擁有 ALTER ANY PROCEDURE 系統(tǒng)權(quán)限的人 語法如下 ALTER PROCEDURE 存儲過程名 COMPILE 執(zhí)行 或調(diào)用 存儲過程的人是過程的創(chuàng)建者或是擁有 EXECUTE ANY PROCEDURE 系統(tǒng)權(quán)限的 人或是被擁有者授予 EXECUTE 權(quán)限的人 執(zhí)行的方法如下 方法 1 EXECUTE 模式名 存儲過程名 參數(shù) 方法 2 BEGIN 模式名 存儲過程名 參數(shù) END 傳遞的參數(shù)必須與定義的參數(shù)類型 個數(shù)和順序一致 如果參數(shù)定義了默認值 則調(diào)用時可 以省略參數(shù) 參數(shù)可以是變量 常量或表達式 用法參見下一節(jié) 如果是調(diào)用本賬戶下的存儲過程 則模式名可以省略 要調(diào)用其他賬戶編寫的存 儲過程 則模式名必須要添加 以下是一個生成和調(diào)用簡單存儲過程的訓練 注意要事先授予創(chuàng)建存儲過程的權(quán) 限 范例 創(chuàng)建一個顯示雇員總?cè)藬?shù)的存儲過程 步驟 1 登錄 SCOTT 賬戶 步驟 2 在 SQL Plus 輸入?yún)^(qū)中 輸入以下存儲過程 CREATE OR REPLACE PROCEDURE EMP COUNT AS V TOTAL NUMBER 10 BEGIN SELECT COUNT INTO V TOTAL FROM EMP DBMS OUTPUT PUT LINE 雇員總?cè)藬?shù)為 V TOTAL END 步驟 3 執(zhí)行是進行編譯 如果存在錯誤 就會顯示 警告 創(chuàng)建的過程帶有編譯錯誤 如果存在錯誤 對腳本進行修改 直到?jīng)]有錯誤產(chǎn)生 如果要想查看編譯的錯誤 SHOW ERRORS PROCEDURE emp count 如果編譯結(jié)果正確 將顯示 過程已創(chuàng)建 步驟 4 調(diào)用存儲過程 在輸入?yún)^(qū)中輸入以下語句并執(zhí)行 EXECUTE EMP COUNT 顯示結(jié)果為 雇員總?cè)藬?shù)為 14 PL SQL 過程已成功完成 說明 在該訓練中 V TOTAL 變量是存儲過程定義的局部變量 用于接收查詢到的雇員總 人數(shù) 注意 在 SQL Plus 中輸入存儲過程 按 執(zhí)行 按鈕是進行編譯 不是執(zhí)行存儲過程 如果在存儲過程中引用了其他用戶的對象 比如表 則必須有其他用戶授予的對 象訪問權(quán)限 一個存儲過程一旦編譯成功 就可以由其他用戶或程序來引用 但存儲過程 或函數(shù)的所有者必須授予其他用戶執(zhí)行該過程的權(quán)限 存儲過程沒有參數(shù) 在調(diào)用時 直接寫過程名即可 注意 用 EXECUTE 調(diào)用存儲過程只有兩種情況 一種是存儲過程不帶參數(shù) 一種是存儲過程不帶輸出參數(shù) 帶輸入?yún)?shù)不是變量帶入是直接帶入 范例 在 PL SQL 程序中調(diào)用存儲過程 步驟 1 登錄 SCOTT 賬戶 步驟 2 授權(quán) STUDENT 賬戶使用該存儲過程 即在 SQL Plus 輸入?yún)^(qū)中 輸入以下 的命令 GRANT EXECUTE ON EMP COUNT TO STUDENT 授權(quán)成功 步驟 3 登錄 STUDENT 賬戶 在 SQL Plus 輸入?yún)^(qū)中輸入以下程序 SET SERVEROUTPUT ON BEGIN SCOTT EMP COUNT END 步驟 4 執(zhí)行以上程序 結(jié)果為 雇員總?cè)藬?shù)為 14 PL SQL 過程已成功完成 說明 在本例中 存儲過程是由 SCOTT 賬戶創(chuàng)建的 STUDEN 賬戶獲得 SCOTT 賬戶的授權(quán) 后 才能調(diào)用該存儲過程 注意 在程序中調(diào)用存儲過程 使用了第二種語法 范例 編寫顯示雇員信息的存儲過程 EMP LIST 并引用 EMP COUNT 存儲過程 步驟 1 在 SQL Plus 輸入?yún)^(qū)中輸入并編譯以下存儲過程 CREATE OR REPLACE PROCEDURE EMP LIST AS CURSOR emp cursor IS SELECT empno ename FROM emp BEGIN FOR Emp record IN emp cursor LOOP DBMS OUTPUT PUT LINE Emp record empno Emp record ename END LOOP EMP COUNT END 執(zhí)行結(jié)果 過程已創(chuàng)建 步驟 2 調(diào)用存儲過程 在輸入?yún)^(qū)中輸入以下語句并執(zhí)行 EXECUTE EMP LIST 說明 以上的 EMP LIST 存儲過程中定義并使用了游標 用來循環(huán)顯示所有雇員的信息 然 后調(diào)用已經(jīng)成功編譯的存儲過程 EMP COUNT 用來附加顯示雇員總?cè)藬?shù) 通過 EXECUTE 命令來執(zhí)行 EMP LIST 存儲過程 練習 編寫顯示部門信息的存儲過程 DEPT LIST 要求統(tǒng)計出部門個數(shù) 參數(shù)傳遞參數(shù)傳遞 參數(shù)的作用是向存儲過程傳遞數(shù)據(jù) 或從存儲過程獲得返回結(jié)果 正確的使用參 數(shù)可以大大增加存儲過程的靈活性和通用性 參數(shù)的類型有三種 如表 8 1 所示 參數(shù)類型 說 明 IN 定義一個輸入?yún)?shù)變量 用于傳遞參數(shù)給存儲過程 OUT 定義一個輸出參數(shù)變量 用于從存儲過程獲取數(shù)據(jù) IN OUT 定義一個輸入 輸出參數(shù)變量 兼有以上兩者的功能 參數(shù)的定義形式和作用如下 參數(shù)名 IN 數(shù)據(jù)類型 DEFAULT 值 定義一個輸入?yún)?shù)變量 用于傳遞參數(shù)給存儲過程 在調(diào)用存儲過程時 主程序 的實際參數(shù)可以是常量 有值變量或表達式等 DEFAULT 關鍵字為可選項 用來設定參數(shù) 的默認值 如果在調(diào)用存儲過程時不指明參數(shù) 則參數(shù)變量取默認值 在存儲過程中 輸 入變量接收主程序傳遞的值 但不能對其進行賦值 參數(shù)名 OUT 數(shù)據(jù)類型 定義一個輸出參數(shù)變量 用于從存儲過程獲取數(shù)據(jù) 即變量從存儲過程中返回值 給主程序 在調(diào)用存儲過程時 主程序的實際參數(shù)只能是一個變量 而不能是常量或表達式 在存儲過程中 參數(shù)變量只能被賦值而不能將其用于賦值 在存儲過程中必須給輸出變量 至少賦值一次 參數(shù)名 IN OUT 數(shù)據(jù)類型 DEFAULT 值 定義一個輸入 輸出參數(shù)變量 兼有以上兩者的功能 在調(diào)用存儲過程時 主程 序的實際參數(shù)只能是一個變量 而不能是常量或表達式 DEFAULT 關鍵字為可選項 用來 設定參數(shù)的默認值 在存儲過程中 變量接收主程序傳遞的值 同時可以參加賦值運算 也可以對其進行賦值 在存儲過程中必須給變量至少賦值一次 注意 如果省略 IN OUT 或 IN OUT 則默認模式是 IN 參數(shù)的定義只能給出類型 無需定義長度 范例 編寫給雇員增加工資的存儲過程 CHANGE SALARY 通過 IN 類型的參數(shù)傳遞要增加 工資的雇員編號和增加的工資額 步驟 1 登錄 SCOTT 賬戶 步驟 2 在 SQL Plus 輸入?yún)^(qū)中輸入以下存儲過程并執(zhí)行 CREATE OR REPLACE PROCEDURE CHANGE SALARY P EMPNO IN NUMBER DEFAULT 7788 P RAISE NUMBER DEFAULT 10 AS V ENAME VARCHAR2 10 V SAL NUMBER 5 BEGIN SELECT ENAME SAL INTO V ENAME V SAL FROM EMP WHERE EMPNO P EMPNO UPDATE EMP SET SAL SAL P RAISE WHERE EMPNO P EMPNO DBMS OUTPUT PUT LINE 雇員 V ENAME 的工資被改為 TO CHAR V SAL P RAISE COMMIT EXCEPTION WHEN OTHERS THEN DBMS OUTPUT PUT LINE 發(fā)生錯誤 修改失敗 ROLLBACK END 執(zhí)行結(jié)果為 過程已創(chuàng)建 步驟 3 調(diào)用存儲過程 在輸入?yún)^(qū)中輸入以下語句并執(zhí)行 EXECUTE CHANGE SALARY 7788 80 顯示結(jié)果為 雇員 SCOTT 的工資被改為 3080 說明 從執(zhí)行結(jié)果可以看到 雇員 SCOTT 的工資已由原來的 3000 改為 3080 參數(shù)的值由調(diào)用者傳遞 傳遞的參數(shù)的個數(shù) 類型和順序應該和定義的一致 如果順序不 一致 可以采用以下調(diào)用方法 如上例 執(zhí)行語句可以改為 EXECUTE CHANGE SALARY P RAISE 80 P EMPNO 7788 可以看出傳遞參數(shù)的順序發(fā)生了變化 并且明確指出了參數(shù)名和要傳遞的值 運算符左側(cè)是參數(shù)名 右側(cè)是參數(shù)表達式 這種賦值方法的意義較清楚 練習 創(chuàng)建插入雇員的存儲過程 INSERT EMP 并將雇員編號等作為參數(shù) 在設計存儲過程的時候 也可以為參數(shù)設定默認值 這樣調(diào)用者就可以不傳遞或 少傳遞參數(shù)了 練習 調(diào)用存儲過程 CHANGE SALARY 不傳遞參數(shù) 使用默認參數(shù)值 在 SQL Plus 輸入?yún)^(qū)中輸入以下命令并執(zhí)行 EXECUTE CHANGE SALARY 顯示結(jié)果為 雇員 SCOTT 的工資被改為 3090 說明 在存儲過程的調(diào)用中沒有傳遞參數(shù) 而是采用了默認值 7788 和 10 即默認雇員號 為 7788 增加的工資為 10 范例 使用 OUT 類型的參數(shù)返回存儲過程的結(jié)果 步驟 1 登錄 SCOTT 賬戶 步驟 2 在 SQL Plus 輸入?yún)^(qū)中輸入并編譯以下存儲過程 CREATE OR REPLACE PROCEDURE EMP COUNT P TOTAL OUT NUMBER AS BEGIN SELECT COUNT INTO P TOTAL FROM EMP END 執(zhí)行結(jié)果為 過程已創(chuàng)建 步驟 3 輸入以下程序并執(zhí)行 DECLARE V EMPCOUNT NUMBER BEGIN EMP COUNT V EMPCOUNT DBMS OUTPUT PUT LINE 雇員總?cè)藬?shù)為 V EMPCOUNT END 顯示結(jié)果為 雇員總?cè)藬?shù)為 14 PL SQL 過程已成功完成 說明 在存儲過程中定義了 OUT 類型的參數(shù) P TOTAL 在主程序調(diào)用該存儲過程時 傳遞 了參數(shù) V EMPCOUNT 在存儲過程中的 SELECT INTO 語句中對 P TOTAL 進行賦值 賦值 結(jié)果由 V EMPCOUNT 變量帶回給主程序并顯示 以上程序要覆蓋同名的 EMP COUNT 存儲過程 如果不使用 OR REPLACE 選項 就會出現(xiàn)以 下錯誤 ERROR 位于第 1 行 ORA 00955 名稱已由現(xiàn)有對象使用 練習 創(chuàng)建存儲過程 使用 OUT 類型參數(shù)獲得雇員經(jīng)理名 創(chuàng)建和刪除存儲函數(shù)創(chuàng)建和刪除存儲函數(shù) 創(chuàng)建函數(shù) 需要有 CREATE PROCEDURE 或 CREATE ANY PROCEDURE 的系統(tǒng)權(quán)限 該權(quán)限可由系統(tǒng)管理員授予 創(chuàng)建存儲函數(shù)的語法和創(chuàng)建存儲過程的類似 即 CREATE OR REPLACE FUNCTION 函數(shù)名 參數(shù) IN 數(shù)據(jù)類型 RETURN 數(shù)據(jù)類型 AS IS 說明部分 BEGIN 可執(zhí)行部分 RETURN 表達式 EXCEPTION 錯誤處理部分 END 函數(shù)名 其中 參數(shù)是可選的 但只能是 IN 類型 IN 關鍵字可以省略 在定義部分的 RETURN 數(shù)據(jù)類型 用來表示函數(shù)的數(shù)據(jù)類型 也就是返回值的類 型 此部分不可省略 在可執(zhí)行部分的 RETURN 表達式 用來生成函數(shù)的返回值 其表達式的類型應該和定義部 分說明的函數(shù)返回值的數(shù)據(jù)類型一致 在函數(shù)的執(zhí)行部分可以有多個 RETURN 語句 但只 有一個 RETURN 語句會被執(zhí)行 一旦執(zhí)行了 RETURN 語句 則函數(shù)結(jié)束并返回調(diào)用環(huán)境 一個存儲函數(shù)在不需要時可以刪除 但刪除的人應是函數(shù)的創(chuàng)建者或者是擁有 DROP ANY PROCEDURE 系統(tǒng)權(quán)限的人 其語法如下 DROP FUNCTION 函數(shù)名 重新編譯一個存儲函數(shù)時 編譯的人應是函數(shù)的創(chuàng)建者或者擁有 ALTER ANY PROCEDURE 系 統(tǒng)權(quán)限的人 重新編譯一個存儲函數(shù)的語法如下 ALTER PROCEDURE 函數(shù)名 COMPILE 函數(shù)的調(diào)用者應是函數(shù)的創(chuàng)建者或擁有 EXECUTE ANY PROCEDURE 系統(tǒng)權(quán)限的人 或是被函數(shù)的擁有者授予了函數(shù)執(zhí)行權(quán)限的賬戶 函數(shù)的引用和存儲過程不同 函數(shù)要出 現(xiàn)在程序體中 可以參加表達式的運算或單獨出現(xiàn)在表達式中 其形式如下 變量名 函數(shù)名 范例 創(chuàng)建一個通過雇員編號返回雇員名稱的函數(shù) GET EMP NAME 步驟 1 登錄 SCOTT 賬戶 步驟 2 在 SQL Plus 輸入?yún)^(qū)中輸入以下存儲函數(shù)并編譯 CREATE OR REPLACE FUNCTION GET EMP NAME P EMPNO NUMBER DEFAULT 7788 RETURN VARCHAR2 AS V ENAME VARCHAR2 10 BEGIN SELECT ENAME INTO V ENAME FROM EMP WHERE EMPNO P EMPNO RETURN V ENAME EXCEPTION WHEN NO DATA FOUND THEN DBMS OUTPUT PUT LINE 沒有該編號雇員 RETURN NULL WHEN TOO MANY ROWS THEN DBMS OUTPUT PUT LINE 有重復雇員編號 RETURN NULL WHEN OTHERS THEN DBMS OUTPUT PUT LINE 發(fā)生其他錯誤 RETURN NULL END 步驟 3 調(diào)用該存儲函數(shù) 輸入并執(zhí)行以下程序 BEGIN DBMS OUTPUT PUT LINE 雇員 7369 的名稱是 GET EMP NAME 7369 DBMS OUTPUT PUT LINE 雇員 7839 的名稱是 GET EMP NAME 7839 END 顯示結(jié)果為 雇員 7369 的名稱是 SMITH 雇員 7839 的名稱是 KING PL SQL 過程已成功完成 說明 函數(shù)的調(diào)用直接出現(xiàn)在程序的 DBMS OUTPUT PUT LINE 語句中 作為字符串表達式 的一部分 如果輸入了錯誤的雇員編號 就會在函數(shù)的錯誤處理部分輸出錯誤信息 試修 改雇員編號 重新