1、Oracle游標(biāo)使用方法及語(yǔ)法大全當(dāng)查詢返回結(jié)果超過(guò)一行時(shí)，就需要一個(gè)顯式游標(biāo)，此時(shí)用戶不能使用select into語(yǔ)句。PL/SQL管理隱式游標(biāo)，當(dāng)查詢開(kāi)始時(shí)隱式游標(biāo)打開(kāi)，查詢結(jié)束時(shí)隱式游標(biāo)自動(dòng)關(guān)閉。顯式游標(biāo)在PL/SQL塊的聲明部分聲明，在執(zhí)行部分或異常處理部分打開(kāi)，取出數(shù)據(jù),關(guān)閉。使用游標(biāo)語(yǔ)法： 例： SET SERVERIUTPUT ON DECLARE R_emp EMP%ROWTYPE; CURSOR c_emp IS select * from emp; BEGINOPEN c_emp;LoopFETCH c_emp into r_emp;EXIT WHEN C_EMP%NOT

2、FOUND;Dbms_output.put_line(Salary of Employee|r_emp.ename|is)End loop;Close c_emp;End;%ROWTYPE也可以用游標(biāo)名來(lái)定義，這樣的話就必須要首先聲明游標(biāo)：For record_name IN(corsor_name(parameter,parameter) |(query_difinition) loop StatementsEnd loop;下面我們用for循環(huán)重寫(xiě)上面的例子：在游標(biāo)FOR循環(huán)中使用查詢?cè)谟螛?biāo)FOR循環(huán)中可以定義查詢，由于沒(méi)有顯式聲明所以游標(biāo)沒(méi)有名字，記錄名通過(guò)游標(biāo)查詢來(lái)定義。 游標(biāo)中的子查

3、詢語(yǔ)法如下：可以看出與SQL中的子查詢有沒(méi)什么區(qū)別。游標(biāo)中的更新和刪除 在PL/SQL中依然可以使用UPDATE和DELETE語(yǔ)句更新或刪除數(shù)據(jù)行。顯示游標(biāo)只有在需要獲得多行數(shù)據(jù)的情況下使用。PL/SQL提供了僅僅使用游標(biāo)就可以執(zhí)行刪除或更新記錄的方法。UPDATE或DELETE語(yǔ)句中的WHERE CURRENT OF子串專門處理要執(zhí)行UPDATE或DELETE操作的表中取出的最近的數(shù)據(jù)。要使用這個(gè)方法，在聲明游標(biāo)時(shí)必須使用FOR UPDATE子串，當(dāng)對(duì)話使用FOR UPDATE子串打開(kāi)一個(gè)游標(biāo)時(shí)，所有返回集中的數(shù)據(jù)行都將處于行級(jí)（ROW-LEVEL)獨(dú)占式鎖定，其他對(duì)象只能查詢這些數(shù)據(jù)行，不

4、能進(jìn)行UPDATE、DELETE或SELECT.FOR UPDATE操作。 語(yǔ)法：在多表查詢中，使用OF自居來(lái)鎖定特定的表，如果忽略了OF子句，那么所有表中選擇的數(shù)據(jù)行都將被鎖定。如果這些數(shù)據(jù)行已經(jīng)被其他會(huì)話鎖定，那么正常情況下oracle將等待，直到數(shù)據(jù)行解鎖。在UPDATE和DELETE中使用WHERE CURRENT OF子串的語(yǔ)法如下： WHERE CURRENT OF cursor_name|search_condition實(shí)例: DELCARE CURSOR c1 IS SELECT empno,salary FROM emp WHERE comm IS NULLFOR UPDAT

5、E OF comm;v_comm NUMBER(10,2); BEGIN FOR r1 IN c1 LOOP IF r1.salary500 THEN v_comm:=r1.salary*0.25;ELSEIF r1.salary1000 THEN v_comm:=r1.salary*0.20; ELSEIF r1.salary3000 THEN v_comm:=r1.salary*0.15; ELSE v_comm:=r1.salary*0.12; END IF; UPDATE emp SET comm=v_comm WHERE CURRENT OF c1; END LOOP; END=or

6、acle 隱式游標(biāo),顯示游標(biāo),游標(biāo)循環(huán)動(dòng)態(tài)SELECT語(yǔ)句和動(dòng)態(tài)游標(biāo),異常處理,自定義異常游標(biāo)的概念: 游標(biāo)是SQL的一個(gè)內(nèi)存工作區(qū)，由系統(tǒng)或用戶以變量的形式定義。游標(biāo)的作用就是用于臨時(shí)存儲(chǔ)從數(shù)據(jù)庫(kù)中提取的數(shù)據(jù)塊。在某些情況下，需要把數(shù)據(jù)從存放在磁盤(pán)的表中調(diào)到計(jì)算機(jī)內(nèi)存中進(jìn)行處理，最后將處理結(jié)果顯示出來(lái)或最終寫(xiě)回?cái)?shù)據(jù)庫(kù)。這樣數(shù)據(jù)處理的速度才會(huì)提高，否則頻繁的磁盤(pán)數(shù)據(jù)交換會(huì)降低效率。 游標(biāo)有兩種類型：顯式游標(biāo)和隱式游標(biāo)。在前述程序中用到的SELECT.INTO.查詢語(yǔ)句，一次只能從數(shù)據(jù)庫(kù)中提取一行數(shù)據(jù)，對(duì)于這種形式的查詢和DML操作，系統(tǒng)都會(huì)使用一個(gè)隱式游標(biāo)。但是如果要提取多行數(shù)據(jù)，就要由程序員

7、定義一個(gè)顯式游標(biāo)，并通過(guò)與游標(biāo)有關(guān)的語(yǔ)句進(jìn)行處理。顯式游標(biāo)對(duì)應(yīng)一個(gè)返回結(jié)果為多行多列的SELECT語(yǔ)句。 游標(biāo)一旦打開(kāi)，數(shù)據(jù)就從數(shù)據(jù)庫(kù)中傳送到游標(biāo)變量中，然后應(yīng)用程序再?gòu)挠螛?biāo)變量中分解出需要的數(shù)據(jù)，并進(jìn)行處理。 隱式游標(biāo)：如前所述，DML操作和單行SELECT語(yǔ)句會(huì)使用隱式游標(biāo)，它們是： * 插入操作：INSERT。 * 更新操作：UPDATE。 * 刪除操作：DELETE。 * 單行查詢操作：SELECT . INTO .。 當(dāng)系統(tǒng)使用一個(gè)隱式游標(biāo)時(shí)，可以通過(guò)隱式游標(biāo)的屬性來(lái)了解操作的狀態(tài)和結(jié)果，進(jìn)而控制程序的流程。隱式游標(biāo)可以使用名字SQL來(lái)訪問(wèn)，但要注意，通過(guò)SQL游標(biāo)名總是只能訪問(wèn)前一

8、個(gè)DML操作或單行SELECT操作的游標(biāo)屬性。所以通常在剛剛執(zhí)行完操作之后，立即使用SQL游標(biāo)名來(lái)訪問(wèn)屬性。游標(biāo)的屬性有四種，如下所示。 Sql代碼：隱式游標(biāo)的屬性 返回值類型 意義 1. SQL%ROWCOUNT整型代表DML語(yǔ)句成功執(zhí)行的數(shù)據(jù)行數(shù) 2. SQL%FOUND 布爾型值為TRUE代表插入、刪除、更新或單行查詢操作成功 3. SQL%NOTFOUND布爾型與SQL%FOUND屬性返回值相反 4. SQL%ISOPEN 布爾型DML執(zhí)行過(guò)程中為真，結(jié)束后為假隱式游標(biāo)的屬性返回值類型意 義SQL%ROWCOUNT整型代表DML語(yǔ)句成功執(zhí)行的數(shù)據(jù)行數(shù)SQL%FOUND布爾型值為TRUE

9、代表插入、刪除、更新或單行查詢操作成功SQL%NOTFOUND布爾型與SQL%FOUND屬性返回值相反SQL%ISOPEN布爾型DML執(zhí)行過(guò)程中為真，結(jié)束后為假【訓(xùn)練1】使用隱式游標(biāo)的屬性，判斷對(duì)雇員工資的修改是否成功。 步驟1：輸入和運(yùn)行以下程序： Sql代碼：1. SETSERVEROUTPUTON 2. BEGIN3. UPDATEempSETsal=sal+100WHEREempno=1234; 4. IFSQL%FOUNDTHEN 5. DBMS_OUTPUT.PUT_LINE(成功修改雇員工資！); 6. COMMIT; 7. ELSE8. DBMS_OUTPUT.PUT_LINE

10、(修改雇員工資失?。?; 9. ENDIF; 10. END;SET SERVEROUTPUT ON BEGIN UPDATE emp SET sal=sal+100 WHERE empno=1234; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(成功修改雇員工資！); COMMIT; ELSEDBMS_OUTPUT.PUT_LINE(修改雇員工資失?。?; END IF; END;運(yùn)行結(jié)果為： Sql代碼：1. 修改雇員工資失??！ 2. PL/SQL過(guò)程已成功完成。修改雇員工資失?。L/SQL 過(guò)程已成功完成。步驟2：將雇員編號(hào)1234改為7788，重新執(zhí)

11、行以上程序： 運(yùn)行結(jié)果為： Sql代碼：1. 成功修改雇員工資！ 2. PL/SQL過(guò)程已成功完成。成功修改雇員工資！PL/SQL 過(guò)程已成功完成。說(shuō)明：本例中，通過(guò)SQL%FOUND屬性判斷修改是否成功，并給出相應(yīng)信息。 顯式游標(biāo)： 游標(biāo)的定義和操作 游標(biāo)的使用分成以下4個(gè)步驟。 1聲明游標(biāo) 在DECLEAR部分按以下格式聲明游標(biāo)： CURSOR 游標(biāo)名(參數(shù)1 數(shù)據(jù)類型，參數(shù)2 數(shù)據(jù)類型.) IS SELECT語(yǔ)句; 參數(shù)是可選部分，所定義的參數(shù)可以出現(xiàn)在SELECT語(yǔ)句的WHERE子句中。如果定義了參數(shù)，則必須在打開(kāi)游標(biāo)時(shí)傳遞相應(yīng)的實(shí)際參數(shù)。 SELECT語(yǔ)句是對(duì)表或視圖的查詢語(yǔ)句，甚至

12、也可以是聯(lián)合查詢?？梢詭HERE條件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT語(yǔ)句中可以使用在定義游標(biāo)之前定義的變量。 2打開(kāi)游標(biāo) 在可執(zhí)行部分，按以下格式打開(kāi)游標(biāo)： OPEN 游標(biāo)名(實(shí)際參數(shù)1，實(shí)際參數(shù)2.); 打開(kāi)游標(biāo)時(shí)，SELECT語(yǔ)句的查詢結(jié)果就被傳送到了游標(biāo)工作區(qū)。 3提取數(shù)據(jù) 在可執(zhí)行部分，按以下格式將游標(biāo)工作區(qū)中的數(shù)據(jù)取到變量中。提取操作必須在打開(kāi)游標(biāo)之后進(jìn)行。 FETCH 游標(biāo)名 INTO 變量名1，變量名2.; 或 FETCH 游標(biāo)名 INTO 記錄變量; 游標(biāo)打開(kāi)后有一個(gè)指針指向數(shù)據(jù)區(qū)，F(xiàn)ETCH語(yǔ)句一次返回指針?biāo)傅囊恍袛?shù)據(jù)，要

13、返回多行需重復(fù)執(zhí)行，可以使用循環(huán)語(yǔ)句來(lái)實(shí)現(xiàn)?？刂蒲h(huán)可以通過(guò)判斷游標(biāo)的屬性來(lái)進(jìn)行。 下面對(duì)這兩種格式進(jìn)行說(shuō)明： 第一種格式中的變量名是用來(lái)從游標(biāo)中接收數(shù)據(jù)的變量，需要事先定義。變量的個(gè)數(shù)和類型應(yīng)與SELECT語(yǔ)句中的字段變量的個(gè)數(shù)和類型一致。 第二種格式一次將一行數(shù)據(jù)取到記錄變量中，需要使用%ROWTYPE事先定義記錄變量，這種形式使用起來(lái)比較方便，不必分別定義和使用多個(gè)變量。 定義記錄變量的方法如下： 變量名 表名|游標(biāo)名%ROWTYPE； 其中的表必須存在，游標(biāo)名也必須先定義。 4關(guān)閉游標(biāo) CLOSE 游標(biāo)名; 顯式游標(biāo)打開(kāi)后，必須顯式地關(guān)閉。游標(biāo)一旦關(guān)閉，游標(biāo)占用的資源就被釋放，游標(biāo)變成

14、無(wú)效，必須重新打開(kāi)才能使用。 以下是使用顯式游標(biāo)的一個(gè)簡(jiǎn)單練習(xí)。 【訓(xùn)練1】 用游標(biāo)提取emp表中7788雇員的名稱和職務(wù)。 Sql代碼：1. SETSERVEROUTPUTON2. DECLARE 3. v_enameVARCHAR2(10); 4. v_jobVARCHAR2(10); 5. CURSORemp_cursorIS 6. SELECTename,jobFROMempWHEREempno=7788; 7. BEGIN8. OPENemp_cursor; 9. FETCHemp_cursorINTOv_ename,v_job; 10. DBMS_OUTPUT.PUT_LINE(v

15、_ename|,|v_job); 11. CLOSEemp_cursor; 12. END;SET SERVEROUTPUT ONDECLARE 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;

16、執(zhí)行結(jié)果為: Sql代碼：1. SCOTT,ANALYST 2. PL/SQL過(guò)程已成功完成。SCOTT,ANALYSTPL/SQL 過(guò)程已成功完成。說(shuō)明：該程序通過(guò)定義游標(biāo)emp_cursor，提取并顯示雇員7788的名稱和職務(wù)。 作為對(duì)以上例子的改進(jìn)，在以下訓(xùn)練中采用了記錄變量。 【訓(xùn)練2】 用游標(biāo)提取emp表中7788雇員的姓名、職務(wù)和工資。 Sql代碼：1. SETSERVEROUTPUTON2. DECLARE3. CURSORemp_cursorISSELECTename,job,salFROMempWHEREempno=7788; 4. emp_recordemp_cursor%

17、ROWTYPE; 5. BEGIN6. OPENemp_cursor; 7. FETCHemp_cursorINTOemp_record; 8. DBMS_OUTPUT.PUT_LINE(emp_record.ename|,|emp_record.job|,|emp_record.sal); 9. CLOSEemp_cursor; 10. END;SET SERVEROUTPUT ONDECLARE CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno=7788; emp_record emp_cursor%ROWTYPE

18、;BEGINOPEN emp_cursor; FETCH emp_cursor INTO emp_record; DBMS_OUTPUT.PUT_LINE(emp_record.ename|,| emp_record.job|,| emp_record.sal); CLOSE emp_cursor;END;執(zhí)行結(jié)果為: Sql代碼：1. SCOTT,ANALYST,3000 2. PL/SQL過(guò)程已成功完成。SCOTT,ANALYST,3000PL/SQL 過(guò)程已成功完成。說(shuō)明：實(shí)例中使用記錄變量來(lái)接收數(shù)據(jù)，記錄變量由游標(biāo)變量定義，需要出現(xiàn)在游標(biāo)定義之后。 注意：可通過(guò)以下形式獲得記錄變量的內(nèi)

19、容： 記錄變量名.字段名。 【訓(xùn)練3】 顯示工資最高的前3名雇員的名稱和工資。 Sql代碼：1. SETSERVEROUTPUTON2. DECLARE3. V_enameVARCHAR2(10); 4. V_salNUMBER(5); 5. CURSORemp_cursorISSELECTename,salFROMempORDERBYsalDESC; 6. BEGIN7. OPENemp_cursor; 8. FORIIN1.3LOOP 9. FETCHemp_cursorINTOv_ename,v_sal; 10. DBMS_OUTPUT.PUT_LINE(v_ename|,|v_sal)

20、; 11. ENDLOOP; 12. CLOSEemp_cursor; 13. END;SET SERVEROUTPUT ONDECLARE 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

21、; CLOSE emp_cursor; END;執(zhí)行結(jié)果為: Sql代碼：1. KING,5000 2. SCOTT,3000 3. FORD,3000 4. PL/SQL過(guò)程已成功完成。 KING,5000 SCOTT,3000 FORD,3000 PL/SQL 過(guò)程已成功完成。 說(shuō)明：該程序在游標(biāo)定義中使用了ORDER BY子句進(jìn)行排序，并使用循環(huán)語(yǔ)句來(lái)提取多行數(shù)據(jù)。 游標(biāo)循環(huán) 【訓(xùn)練1】 使用特殊的FOR循環(huán)形式顯示全部雇員的編號(hào)和名稱。 Sql代碼 1. SETSERVEROUTPUTON2. DECLARE3. CURSORemp_cursorIS 4. SELECTempno,en

22、ameFROMemp; 5. BEGIN6. FOREmp_recordINemp_cursorLOOP 7. DBMS_OUTPUT.PUT_LINE(Emp_record.empno|Emp_record.ename); 8. ENDLOOP; 9. END;SET SERVEROUTPUT ONDECLARE CURSOR emp_cursor IS SELECT empno, ename FROM emp;BEGINFOR Emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(Emp_record.empno| Emp_record.en

23、ame);END LOOP;END;執(zhí)行結(jié)果為： Sql代碼 1. 7369SMITH 2. 7499ALLEN 3. 7521WARD 4. 7566JONES 5. PL/SQL過(guò)程已成功完成。7369SMITH7499ALLEN7521WARD7566JONES PL/SQL 過(guò)程已成功完成。 說(shuō)明：可以看到該循環(huán)形式非常簡(jiǎn)單，隱含了記錄變量的定義、游標(biāo)的打開(kāi)、提取和關(guān)閉過(guò)程。Emp_record為隱含定義的記錄變量，循環(huán)的執(zhí)行次數(shù)與游標(biāo)取得的數(shù)據(jù)的行數(shù)相一致。 【訓(xùn)練2】 另一種形式的游標(biāo)循環(huán)。 Sql代碼 1. SETSERVEROUTPUTON 2. BEGIN3. FORreIN

24、(SELECTenameFROMEMP)LOOP 4. DBMS_OUTPUT.PUT_LINE(re.ename) 5. ENDLOOP; 6. END;SET SERVEROUTPUT ON BEGIN FOR re IN (SELECT ename FROM EMP) LOOP DBMS_OUTPUT.PUT_LINE(re.ename) END LOOP;END;執(zhí)行結(jié)果為： Sql代碼 1. SMITH 2. ALLEN 3. WARD 4. JONESSMITHALLENWARDJONES 說(shuō)明：該種形式更為簡(jiǎn)單，省略了游標(biāo)的定義，游標(biāo)的SELECT查詢語(yǔ)句在循環(huán)中直接出現(xiàn)。 顯式

25、游標(biāo)屬性 雖然可以使用前面的形式獲得游標(biāo)數(shù)據(jù)，但是在游標(biāo)定義以后使用它的一些屬性來(lái)進(jìn)行結(jié)構(gòu)控制是一種更為靈活的方法。顯式游標(biāo)的屬性如下所示。 Sql代碼：游標(biāo)的屬性返回值類型意義 1. %ROWCOUNT整型獲得FETCH語(yǔ)句返回的數(shù)據(jù)行數(shù) 2. %FOUND布爾型最近的FETCH語(yǔ)句返回一行數(shù)據(jù)則為真，否則為假 3. %NOTFOUND布爾型與%FOUND屬性返回值相反 4. %ISOPEN布爾型游標(biāo)已經(jīng)打開(kāi)時(shí)值為真，否則為假游標(biāo)的屬性返回值類型意 義%ROWCOUNT整型獲得FETCH語(yǔ)句返回的數(shù)據(jù)行數(shù)%FOUND布爾型最近的FETCH語(yǔ)句返回一行數(shù)據(jù)則為真，否則為假%NOTFOUND布爾

26、型與%FOUND屬性返回值相反%ISOPEN布爾型游標(biāo)已經(jīng)打開(kāi)時(shí)值為真，否則為假可按照以下形式取得游標(biāo)的屬性： 游標(biāo)名%屬性 要判斷游標(biāo)emp_cursor是否處于打開(kāi)狀態(tài)，可以使用屬性emp_cursor%ISOPEN。如果游標(biāo)已經(jīng)打開(kāi)，則返回值為“真”，否則為“假”。具體可參照以下的訓(xùn)練。 【訓(xùn)練1】 使用游標(biāo)的屬性練習(xí)。 Sql代碼 1. SETSERVEROUTPUTON2. DECLARE3. V_enameVARCHAR2(10); 4. CURSORemp_cursorIS 5. SELECTenameFROMemp; 6. BEGIN7. OPENemp_cursor; 8.

27、IFemp_cursor%ISOPENTHEN9. LOOP 10. FETCHemp_cursorINTOv_ename; 11. EXITWHENemp_cursor%NOTFOUND; 12. DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)|-|v_ename); 13. ENDLOOP; 14. ELSE15. DBMS_OUTPUT.PUT_LINE(用戶信息：游標(biāo)沒(méi)有打開(kāi)！); 16. ENDIF; 17. CLOSEemp_cursor; 18. END;SET SERVEROUTPUT ONDECLARE V_ename VA

28、RCHAR2(10); CURSOR emp_cursor IS SELECT ename FROM emp;BEGIN OPEN emp_cursor; IF emp_cursor%ISOPEN THENLOOP 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(用戶信息：游標(biāo)沒(méi)有打開(kāi)！); END IF; CLOSE emp

29、_cursor;END;執(zhí)行結(jié)果為： Sql代碼 1. 1-SMITH 2. 2-ALLEN 3. 3-WARD 4. PL/SQL過(guò)程已成功完成。1-SMITH2-ALLEN3-WARD PL/SQL 過(guò)程已成功完成。 說(shuō)明：本例使用emp_cursor%ISOPEN判斷游標(biāo)是否打開(kāi)；使用emp_cursor%ROWCOUNT獲得到目前為止FETCH語(yǔ)句返回的數(shù)據(jù)行數(shù)并輸出；使用循環(huán)來(lái)獲取數(shù)據(jù)，在循環(huán)體中使用FETCH語(yǔ)句；使用emp_cursor%NOTFOUND判斷FETCH語(yǔ)句是否成功執(zhí)行，當(dāng)FETCH語(yǔ)句失敗時(shí)說(shuō)明數(shù)據(jù)已經(jīng)取完，退出循環(huán)。 【練習(xí)1】去掉OPEN emp_cursor

30、;語(yǔ)句，重新執(zhí)行以上程序。 游標(biāo)參數(shù)的傳遞 【訓(xùn)練1】 帶參數(shù)的游標(biāo)。 Sql代碼 1. SETSERVEROUTPUTON2. DECLARE3. V_empnoNUMBER(5); 4. V_enameVARCHAR2(10); 5. CURSORemp_cursor(p_deptnoNUMBER,p_jobVARCHAR2)IS6. SELECTempno,enameFROMemp 7. WHEREdeptno=p_deptnoANDjob=p_job; 8. BEGIN9. OPENemp_cursor(10,CLERK); 10. LOOP 11. FETCHemp_cursorIN

31、TOv_empno,v_ename; 12. EXITWHENemp_cursor%NOTFOUND; 13. DBMS_OUTPUT.PUT_LINE(v_empno|,|v_ename); 14. ENDLOOP; 15. END;SET SERVEROUTPUT ONDECLARE 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 =

32、 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;執(zhí)行結(jié)果為： Sql代碼 1. 7934,MILLER 2. PL/SQL過(guò)程已成功完成。7934,MILLERPL/SQL 過(guò)程已成功完成。說(shuō)明：游標(biāo)emp_cursor定義了兩個(gè)參數(shù)：p_deptno代表部門編號(hào)，p_job代表職務(wù)。語(yǔ)句OPEN emp_

33、cursor(10, CLERK)傳遞了兩個(gè)參數(shù)值給游標(biāo)，即部門為10、職務(wù)為CLERK，所以游標(biāo)查詢的內(nèi)容是部門10的職務(wù)為CLERK的雇員。循環(huán)部分用于顯示查詢的內(nèi)容。 【練習(xí)1】修改Open語(yǔ)句的參數(shù)：部門號(hào)為20、職務(wù)為ANALYST，并重新執(zhí)行。 也可以通過(guò)變量向游標(biāo)傳遞參數(shù)，但變量需要先于游標(biāo)定義，并在游標(biāo)打開(kāi)之前賦值。對(duì)以上例子重新改動(dòng)如下： 【訓(xùn)練2】 通過(guò)變量傳遞參數(shù)給游標(biāo)。 Sql代碼 1. SETSERVEROUTPUTON2. DECLARE3. v_empnoNUMBER(5); 4. v_enameVARCHAR2(10); 5. v_deptnoNUMBER(5)

34、; 6. v_jobVARCHAR2(10); 7. CURSORemp_cursorIS8. SELECTempno,enameFROMemp 9. WHEREdeptno=v_deptnoANDjob=v_job; 10. BEGIN11. v_deptno:=10; 12. v_job:=CLERK; 13. OPENemp_cursor; 14. LOOP 15. FETCHemp_cursorINTOv_empno,v_ename; 16. EXITWHENemp_cursor%NOTFOUND; 17. DBMS_OUTPUT.PUT_LINE(v_empno|,|v_ename)

35、; 18. ENDLOOP; 19. END;SET SERVEROUTPUT ONDECLARE 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_e

36、mpno,v_ename; EXIT WHEN emp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_empno|,|v_ename); END LOOP;END;執(zhí)行結(jié)果為： Sql代碼：1. 7934,MILLER 2. PL/SQL過(guò)程已成功完成。7934,MILLERPL/SQL 過(guò)程已成功完成。說(shuō)明：該程序與前一程序?qū)崿F(xiàn)相同的功能。 動(dòng)態(tài)SELECT語(yǔ)句和動(dòng)態(tài)游標(biāo)的用法： Oracle支持動(dòng)態(tài)SELECT語(yǔ)句和動(dòng)態(tài)游標(biāo)，動(dòng)態(tài)的方法大大擴(kuò)展了程序設(shè)計(jì)的能力。 對(duì)于查詢結(jié)果為一行的SELECT語(yǔ)句，可以用動(dòng)態(tài)生成查詢語(yǔ)句字符串的方法，在程序執(zhí)行階段臨時(shí)地生

37、成并執(zhí)行，語(yǔ)法是： execute immediate 查詢語(yǔ)句字符串 into 變量1,變量2.; 以下是一個(gè)動(dòng)態(tài)生成SELECT語(yǔ)句的例子。 【訓(xùn)練1】 動(dòng)態(tài)SELECT查詢。 Sql代碼：1. SETSERVEROUTPUTON 2. DECLARE 3. strvarchar2(100); 4. v_enamevarchar2(10); 5. begin6. str:=selectenamefromscott.empwhereempno=7788; 7. executeimmediatestrintov_ename; 8. dbms_output.put_line(v_ename);

38、9. END;SET SERVEROUTPUT ON DECLARE str varchar2(100);v_ename varchar2(10);beginstr:=select ename from scott.emp where empno=7788;execute immediate str into v_ename; dbms_output.put_line(v_ename);END; 執(zhí)行結(jié)果為： Sql代碼：1. SCOTT 2. PL/SQL過(guò)程已成功完成。SCOTTPL/SQL 過(guò)程已成功完成。說(shuō)明：SELECT.INTO.語(yǔ)句存放在STR字符串中，通過(guò)EXECUTE語(yǔ)句執(zhí)行

39、。 在變量聲明部分定義的游標(biāo)是靜態(tài)的，不能在程序運(yùn)行過(guò)程中修改。雖然可以通過(guò)參數(shù)傳遞來(lái)取得不同的數(shù)據(jù)，但還是有很大的局限性。通過(guò)采用動(dòng)態(tài)游標(biāo)，可以在程序運(yùn)行階段隨時(shí)生成一個(gè)查詢語(yǔ)句作為游標(biāo)。要使用動(dòng)態(tài)游標(biāo)需要先定義一個(gè)游標(biāo)類型，然后聲明一個(gè)游標(biāo)變量，游標(biāo)對(duì)應(yīng)的查詢語(yǔ)句可以在程序的執(zhí)行過(guò)程中動(dòng)態(tài)地說(shuō)明。 定義游標(biāo)類型的語(yǔ)句如下： TYPE 游標(biāo)類型名 REF CURSOR; 聲明游標(biāo)變量的語(yǔ)句如下： 游標(biāo)變量名 游標(biāo)類型名; 在可執(zhí)行部分可以如下形式打開(kāi)一個(gè)動(dòng)態(tài)游標(biāo)： OPEN 游標(biāo)變量名 FOR 查詢語(yǔ)句字符串; 【訓(xùn)練2】 按名字中包含的字母順序分組顯示雇員信息。 輸入并運(yùn)行以下程序： Sq

40、l代碼：1. declare 2. typecur_typeisrefcursor; 3. curcur_type; 4. recscott.emp%rowtype; 5. strvarchar2(50); 6. letterchar:=A; 7. begin8. loop 9. str:=selectenamefromempwhereenamelike%|letter|%; 10. opencurforstr; 11. dbms_output.put_line(包含字母|letter|的名字：); 12. loop 13. fetchcurintorec.ename; 14. exitwhe

41、ncur%notfound; 15. dbms_output.put_line(rec.ename); 16. endloop; 17. exitwhenletter=Z; 18. letter:=chr(ascii(letter)+1); 19. endloop; 20. end;declare type cur_type is ref cursor; cur cur_type; rec scott.emp%rowtype; str varchar2(50); letter char:= A;begin loop str:= select ename from emp where ename

42、 like %|letter|%; open cur for str; dbms_output.put_line(包含字母|letter|的名字：); loop fetch cur into rec.ename; exit when cur%notfound; dbms_output.put_line(rec.ename);end loop; exit when letter=Z; letter:=chr(ascii(letter)+1); end loop;end;運(yùn)行結(jié)果為： Sql代碼：1. 包含字母A的名字： 2. ALLEN 3. WARD 4. MARTIN 5. BLAKE 6.

43、 CLARK 7. ADAMS 8. JAMES 9. 包含字母B的名字： 10. BLAKE 11. 包含字母C的名字： 12. CLARK 13. SCOTT包含字母A的名字：ALLENWARDMARTINBLAKECLARKADAMSJAMES包含字母B的名字：BLAKE包含字母C的名字：CLARKSCOTT說(shuō)明：使用了二重循環(huán)，在外循環(huán)體中，動(dòng)態(tài)生成游標(biāo)的SELECT語(yǔ)句，然后打開(kāi)。通過(guò)語(yǔ)句letter:=chr(ascii(letter)+1)可獲得字母表中的下一個(gè)字母。 異常處理 錯(cuò)誤處理 錯(cuò)誤處理部分位于程序的可執(zhí)行部分之后，是由WHEN語(yǔ)句引導(dǎo)的多個(gè)分支構(gòu)成的。錯(cuò)誤處理的語(yǔ)法如

44、下： EXCEPTION WHEN 錯(cuò)誤1OR 錯(cuò)誤2 THEN 語(yǔ)句序列1； WHEN 錯(cuò)誤3OR 錯(cuò)誤4 THEN 語(yǔ)句序列2； WHEN OTHERS 語(yǔ)句序列n； END; 其中： 錯(cuò)誤是在標(biāo)準(zhǔn)包中由系統(tǒng)預(yù)定義的標(biāo)準(zhǔn)錯(cuò)誤，或是由用戶在程序的說(shuō)明部分自定義的錯(cuò)誤，參見(jiàn)下一節(jié)系統(tǒng)預(yù)定義的錯(cuò)誤類型。 語(yǔ)句序列就是不同分支的錯(cuò)誤處理部分。 凡是出現(xiàn)在WHEN后面的錯(cuò)誤都是可以捕捉到的錯(cuò)誤，其他未被捕捉到的錯(cuò)誤，將在WHEN OTHERS部分進(jìn)行統(tǒng)一處理，OTHENS必須是EXCEPTION部分的最后一個(gè)錯(cuò)誤處理分支。如要在該分支中進(jìn)一步判斷錯(cuò)誤種類，可以通過(guò)使用預(yù)定義函數(shù)SQLCODE( )和

45、SQLERRM( )來(lái)獲得系統(tǒng)錯(cuò)誤號(hào)和錯(cuò)誤信息。 如果在程序的子塊中發(fā)生了錯(cuò)誤，但子塊沒(méi)有錯(cuò)誤處理部分，則錯(cuò)誤會(huì)傳遞到主程序中。 下面是由于查詢編號(hào)錯(cuò)誤而引起系統(tǒng)預(yù)定義異常的例子。 【訓(xùn)練1】 查詢編號(hào)為1234的雇員名字。 Sql代碼 1. SETSERVEROUTPUTON2. DECLARE3. v_nameVARCHAR2(10); 4. BEGIN5. SELECTename 6. INTOv_name 7. FROMemp 8. WHEREempno=1234; 9. DBMS_OUTPUT.PUT_LINE(該雇員名字為：|v_name); 10. EXCEPTION 11. W

46、HENNO_DATA_FOUNDTHEN12. DBMS_OUTPUT.PUT_LINE(編號(hào)錯(cuò)誤，沒(méi)有找到相應(yīng)雇員！); 13. WHENOTHERSTHEN14. DBMS_OUTPUT.PUT_LINE(發(fā)生其他錯(cuò)誤！); 15. END;SET SERVEROUTPUT ONDECLAREv_name VARCHAR2(10);BEGIN SELECTename INTOv_name FROMemp WHEREempno = 1234;DBMS_OUTPUT.PUT_LINE(該雇員名字為：| v_name);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS

47、_OUTPUT.PUT_LINE(編號(hào)錯(cuò)誤，沒(méi)有找到相應(yīng)雇員！); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(發(fā)生其他錯(cuò)誤！);END;執(zhí)行結(jié)果為： Sql代碼 1. 編號(hào)錯(cuò)誤，沒(méi)有找到相應(yīng)雇員！ 2. PL/SQL過(guò)程已成功完成。編號(hào)錯(cuò)誤，沒(méi)有找到相應(yīng)雇員！PL/SQL 過(guò)程已成功完成。說(shuō)明：在以上查詢中，因?yàn)榫幪?hào)為1234的雇員不存在，所以將發(fā)生類型為“NO_DATA_ FOUND”的異常。“NO_DATA_FOUND”是系統(tǒng)預(yù)定義的錯(cuò)誤類型，EXCEPTION部分下的WHEN語(yǔ)句將捕捉到該異常，并執(zhí)行相應(yīng)代碼部分。在本例中，輸出用戶自定義的錯(cuò)誤信息“編號(hào)錯(cuò)誤，沒(méi)有找到相應(yīng)雇員!”。如果發(fā)生其他類型的錯(cuò)誤，將執(zhí)行OTHERS條件下的代碼部分，顯示“發(fā)生其他錯(cuò)誤!”。 【訓(xùn)練2】 由程序代碼顯示系統(tǒng)錯(cuò)誤。 Sql代碼 1. SETSERVEROUTPUTON2. DECLARE3. v_tempNUMBER(5):=1; 4. BEGIN5. v_temp:=v_temp/0; 6. EXCEPTION 7. WHENOTHERSTHEN8. DBMS_OUTPUT.PUT_LINE(發(fā)生系統(tǒng)錯(cuò)誤！); 9. DBMS_OUTPUT.PUT_LI