oracle數(shù)據(jù)庫基礎(chǔ)6--游標(biāo)管理ppt課件

1、第六章第六章游標(biāo)管理回憶v PL/SQL 是一種可移植的高性能事務(wù)處理語言是一種可移植的高性能事務(wù)處理語言 v PL/SQL 引擎駐留在引擎駐留在 Oracle 服務(wù)器中服務(wù)器中v PL/SQL 塊由聲明部分、可執(zhí)行部分和異常處理部分組塊由聲明部分、可執(zhí)行部分和異常處理部分組成成 v PL/SQL 支持的數(shù)據(jù)類型包括標(biāo)量數(shù)據(jù)類型、支持的數(shù)據(jù)類型包括標(biāo)量數(shù)據(jù)類型、LOB 數(shù)據(jù)數(shù)據(jù)類型和屬性類型類型和屬性類型 v 控制結(jié)構(gòu)包括條件控制、循環(huán)控制和順序控制控制結(jié)構(gòu)包括條件控制、循環(huán)控制和順序控制v PL/SQL 支持動態(tài)支持動態(tài) SQLv 運行時出現(xiàn)的錯誤叫做異常運行時出現(xiàn)的錯誤叫做異常v 異?？梢?/p>

2、分為預(yù)定義異常和用戶定義的異常異常可以分為預(yù)定義異常和用戶定義的異常目的v掌握游標(biāo)管理技巧掌握游標(biāo)管理技巧 游標(biāo)簡介 2-1Oracle 服務(wù)器服務(wù)器執(zhí)行 PL/SQL 程序內(nèi)存單元保存到游標(biāo)中一次處理一行檢索行提取行游標(biāo)簡介 2-2v逐行處理查詢結(jié)果，以編程的方式訪問數(shù)據(jù)逐行處理查詢結(jié)果，以編程的方式訪問數(shù)據(jù)v游標(biāo)的類型：游標(biāo)的類型：隱式游標(biāo)REF 游標(biāo)顯式游標(biāo)在 PL/SQL 程序中執(zhí)行DML SQL 語句時自動創(chuàng)建隱式游標(biāo)。 顯式游標(biāo)用于處理返回多行的查詢。REF 游標(biāo)用于處理運行時才能確定的動態(tài) SQL 查詢的結(jié)果游標(biāo)類型隱式游標(biāo) 4-1v在在PL/SQL中使用中使用DML語句時自動創(chuàng)

3、建隱式游標(biāo)語句時自動創(chuàng)建隱式游標(biāo)v隱式游標(biāo)自動聲明、打開和關(guān)閉，其名為隱式游標(biāo)自動聲明、打開和關(guān)閉，其名為 SQLv通過檢查隱式游標(biāo)的屬性可以獲得最近執(zhí)行的通過檢查隱式游標(biāo)的屬性可以獲得最近執(zhí)行的DML 語句的信息語句的信息v隱式游標(biāo)的屬性有：隱式游標(biāo)的屬性有：v%FOUND SQL 語句影響了一行或多行時為語句影響了一行或多行時為 TRUEv%NOTFOUND SQL 語句沒有影響任何行時語句沒有影響任何行時為為TRUEv%ROWCOUNT SQL 語句影響的行數(shù)語句影響的行數(shù)v%ISOPEN - 游標(biāo)是否打開，始終為游標(biāo)是否打開，始終為FALSE隱式游標(biāo) 4-2SQL SET SERVER

4、OUTPUT ONSQL BEGINUPDATE toys SET toyprice=270WHERE toyid= P005;IF SQL%FOUND THENDBMS_OUTPUT.PUT_LINE(表已更新);END IF; END;/只有在 DML 語句影響一行或多行時，才返回 True隱式游標(biāo) 4-3SQL SET SERVEROUTPUT ONSQL DECLARE v_TOYID TOYS.ID%type := &TOYID; v_TOYNAME TOYS.NAME%Type := &TOYNAME; BEGIN UPDATE TOYS SET NAME = v_TOYNAME

5、WHERE toyid=v_TOYID; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE(編號未找到。); ELSEDBMS_OUTPUT.PUT_LINE(表已更新);END IF; END;/假如 DML 語句不影響任何行，則返回 True 隱式游標(biāo) 4-4SQL SET SERVEROUTPUT ON SQL BEGINUPDATE vendor_masterSET venname= Rob MathewWHERE vencode=V004;DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT); END; /返回 DML 語句影響的行

6、數(shù)SELECT INTO 語句 2-1SQL SET SERVEROUTPUT ONSQL DECLARE empid VARCHAR2(10);desig VARCHAR2(10); BEGINempid:= &Employeeid;SELECT designation INTO desig FROM employee WHERE empno=empid; EXCEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(職員未找到); END; /如果沒有與SELECT INTO語句中的條件匹配的行，將引發(fā)NO_DATA_FOUND異常SELECT

7、 INTO 語句 2-2SQL SET SERVEROUTPUT ONSQL DECLARE empid VARCHAR2(10); BEGINSELECT empno INTO empid FROM employee; EXCEPTIONWHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(該查詢提取多行); END; /假如 SELECT INTO 語句返回多個值，將引發(fā)TOO_MANY_ROWS異常顯式游標(biāo) 2-1v 顯式游標(biāo)在 PL/SQL 塊的聲明部分定義查詢，該查詢可以返回多行v 顯式游標(biāo)的操作過程： 數(shù)據(jù)庫打開游標(biāo)30George344Roge

8、r245James1Stud_mrksStud_namestud_no提取行變量 關(guān)閉游標(biāo) 顯式游標(biāo) 2-2SQLSET SERVER OUTPUT ONSQLDECLARE my_toy_price toys.toyprice%TYPE; CURSOR toy_cur IS SELECT toyprice FROM toys WHERE toyprice250; BEGIN OPEN toy_cur; LOOP FETCH toy_cur INTO my_toy_price; EXIT WHEN toy_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE (TOYPRICE

9、=:玩具單價=：|my_toy_price); END LOOP; CLOSE toy_cur; END;聲明游標(biāo)打開游標(biāo)提取行關(guān)閉游標(biāo)帶參數(shù)的顯式游標(biāo)v聲明顯式游標(biāo)時可以帶參數(shù)以提高靈活性聲明顯式游標(biāo)時可以帶參數(shù)以提高靈活性v聲明帶參數(shù)的顯式游標(biāo)的語法如下：聲明帶參數(shù)的顯式游標(biāo)的語法如下：vCURSOR ( )v IS select_statement;SQL SET SERVEROUTPUT ONSQL DECLAREdesig VARCHAR2(20);emp_code VARCHAR2(5);empnm VARCHAR2(20);CURSOR emp_cur(desigparam VA

10、RCHAR2) IS SELECT empno, ename FROM employee WHERE designation=desig; BEGINdesig:= &desig;OPEN emp_cur(desig);LOOPFETCH emp_cur INTO emp_code,empnm;EXIT WHEN emp_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE(emp_code| |empnm); END LOOP;CLOSE emp_cur; END;使用顯式游標(biāo)更新行 2-1v允許使用游標(biāo)刪除或更新活動集中的行允許使用游標(biāo)刪除或更新活動集中的行v聲明游標(biāo)時必須使

11、用聲明游標(biāo)時必須使用 SELECT FOR UPDATE語句語句vv CURSOR ISv SELECT statement FOR UPDATE;vUPDATE vSET vWHERE CURRENT OF 更新的語法DELETE FROM WHERE CURRENT OF 刪除的語法 使用顯式游標(biāo)更新行 2-2SQL SET SERVEROUTPUT ONSQL DECLARE new_price NUMBER; CURSOR cur_toy IS SELECT toyprice FROM toys WHERE toyprice100 FOR UPDATE OF toyprice;BEGI

12、N OPEN cur_toy; LOOP FETCH cur_toy INTO new_price; EXIT WHEN cur_toy%NOTFOUND; UPDATE toys SET toyprice = 1.1*new_price WHERE CURRENT OF cur_toy; END LOOP; CLOSE cur_toy; COMMIT;END;循環(huán)游標(biāo) 2-1v循環(huán)游標(biāo)用于簡化游標(biāo)處理代碼循環(huán)游標(biāo)用于簡化游標(biāo)處理代碼v當(dāng)用戶需要從游標(biāo)中提取所有記錄時使用當(dāng)用戶需要從游標(biāo)中提取所有記錄時使用v循環(huán)游標(biāo)的語法如下：循環(huán)游標(biāo)的語法如下：vFOR IN LOOPEND LOOP;循環(huán)

13、游標(biāo) 2-2SQL SET SERVER OUTPUT ONSQL DECLARE CURSOR mytoy_cur IS SELECT toyid, toyname, toyprice FROM toys; BEGIN FOR toy_rec IN mytoy_cur LOOP DBMS_OUTPUT.PUT_LINE( 玩具編號：| |toy_rec.toyid| |玩具名稱：| |toy_rec.toyname| |玩具單價：| |toy_rec.toyprice); END LOOP; END;REF 游標(biāo)和游標(biāo)變量 3-1vREF 游標(biāo)和游標(biāo)變量用于處理運行時動態(tài)執(zhí)行的游標(biāo)和游標(biāo)變量

14、用于處理運行時動態(tài)執(zhí)行的 SQL 查詢查詢v創(chuàng)建游標(biāo)變量需要兩個步驟：創(chuàng)建游標(biāo)變量需要兩個步驟：v聲明聲明 REF 游標(biāo)類型游標(biāo)類型v聲明聲明 REF 游標(biāo)類型的變量游標(biāo)類型的變量v用于聲明用于聲明 REF 游標(biāo)類型的語法為：游標(biāo)類型的語法為：vTYPE IS REF CURSORvRETURN ;REF 游標(biāo)和游標(biāo)變量 3-2TYPE my_curtype IS REF CURSOR RETURN stud_det%ROWTYPE;order_cur my_curtype; 聲明強(qiáng)類型的 REF 游標(biāo)v打開游標(biāo)變量的語法如下：打開游標(biāo)變量的語法如下：v OPEN cursor_name FO

15、R select_statement;TYPE my_ctype IS REF CURSOR;stud_cur my_ctype;聲明弱類型的 REF 游標(biāo)REF 游標(biāo)和游標(biāo)變量 3-3SQL DECLARE TYPE toys_curtype IS REF CURSOR RETURN toys%ROWTYPE; toys_curvar toys_curtype; toys_rec toys%ROWTYPE; BEGIN OPEN toys_curvar FOR SELECT * FROM toys; FETCH toys_curvar INTO toys_rec; . CLOSE toys_

16、curvar; END;聲明REF游標(biāo)類型聲明游標(biāo)變量游標(biāo)變量的優(yōu)點和限制v游標(biāo)變量的功能強(qiáng)大，可以簡化數(shù)據(jù)處理。游標(biāo)變量的功能強(qiáng)大，可以簡化數(shù)據(jù)處理。v游標(biāo)變量的優(yōu)點有：游標(biāo)變量的優(yōu)點有：v可從不同的可從不同的 SELECT 語句中提取結(jié)果集語句中提取結(jié)果集v可以作為過程的參數(shù)進(jìn)行傳遞可以作為過程的參數(shù)進(jìn)行傳遞v可以引用游標(biāo)的所有屬性可以引用游標(biāo)的所有屬性v可以進(jìn)行賦值運算可以進(jìn)行賦值運算v使用游標(biāo)變量的限制：使用游標(biāo)變量的限制：v不能在程序包中聲明游標(biāo)變量不能在程序包中聲明游標(biāo)變量vFOR UPDATE子句不能與游標(biāo)變量一起使用子句不能與游標(biāo)變量一起使用v不能使用比較運算符不能使用比較運算

17、符使用游標(biāo)變量執(zhí)行動態(tài) SQLv可以使用游標(biāo)變量執(zhí)行動態(tài)構(gòu)造的可以使用游標(biāo)變量執(zhí)行動態(tài)構(gòu)造的 SQL 語句語句v打開執(zhí)行動態(tài)打開執(zhí)行動態(tài) SQL 的游標(biāo)變量的語如下：的游標(biāo)變量的語如下：v OPEN cursor_name FOR dynamic_sqlstringv USING bind_argument_list;DECLARE r_emp emp%ROWTYPE; TYPE c_type IS REF CURSOR; cur c_type; p_salary NUMBER;BEGIN p_salary := 2500; OPEN cur FOR select * from emp where sal:1 order by sal desc USING p_salary; DBMS_OUTPUT.PUT_LINE(薪水大于| p_salary |的員工有：); LOOP FETCH cur INTO r_emp; EXIT WHEN cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(編號：| r_emp.empno | 姓名： | r_emp.ename| 薪水： | r_emp.sal ); END LOOP; CLOSE