oracle游標(biāo)的使用.ppt

第十二章 光標(biāo)的使用,光標(biāo)的概念,Oracle 光標(biāo)是一種用于輕松的處理多行數(shù)據(jù)的機(jī)制。 當(dāng) PL/SQL 光標(biāo)查詢返回多行數(shù)據(jù)時，這些記錄組被稱為結(jié)果集。 Oracle 將這種活動集存儲在您創(chuàng)建的顯示定義的已命名的光標(biāo)中。 沒有光標(biāo)， Oracle 開發(fā)人員必須單獨(dú)地、顯式地取回并管理光標(biāo)查詢選擇的每一條記錄。,12.1 光標(biāo)的定義與使用,顯式光標(biāo)處理需四個 PL/SQL步驟: cursor 光標(biāo)名稱 is 查詢語句; open 光標(biāo)名稱; Fetch 光標(biāo)名稱 into 變量列表; Close 光標(biāo)名稱;,12.1 光標(biāo)的定義與使用,例1. declare cursor c1 is select ename, sal from emp where rownum11; v_ename varchar2(10); v_sal number(7,2); begin open c1; fetch c1 into v_ename, v_sal; while c1%found loop dbms_output.put_line(v_ename|to_char(v_sal) ); fetch c1 into v_ename, v_sal; end loop; close c1; end;,12.1 光標(biāo)的定義與使用,2.光標(biāo)屬性 %found 布爾型屬性,當(dāng)最近一次讀記錄時成功返回,則值為true %nofound 布爾型屬性,與%found相反. %isopen 布爾型屬性, 當(dāng)光標(biāo)已打開時返回 true. %rowcount 數(shù)字型屬性, 返回已從光標(biāo)中讀取得記錄數(shù).,12.1 光標(biāo)的定義與使用,3.參數(shù)化光標(biāo) 在聲明光標(biāo)時，將未確定的參數(shù)說明成變量，在使用光標(biāo)時給出光標(biāo)變量的具體值（相當(dāng)于實(shí)參）使得語句可以按照給出的條件進(jìn)行查詢。,12.1 光標(biāo)的定義與使用,例2： DECLARE Cursor c1 (view_pattern varchar2) IS Select view_name from all_views Where view_name like view_pattern|% AND rownum = 10 Order by view_name ; Vname varchar2(40); BEGIN For I1 in c1 ( DBA) loop DBMS_OUTPUT.PUT_LINE( I1.view_name ) ; END loop; END;,12.1 光標(biāo)的定義與使用,處理隱式光標(biāo) 所有的SQL 語句在上下文區(qū)內(nèi)部都是可執(zhí)行的，因此都有一個光標(biāo)指向上下文區(qū)，此光標(biāo)就是所謂的SQL光標(biāo)(SQL cursor)，與顯式光標(biāo)不同，SQL 光標(biāo)不被程序打開和關(guān)閉。,12.1 光標(biāo)的定義與使用,例3: -節(jié)自在線代碼 nomat1.sql BEGIN UPDATE rooms SET number_seats = 100 WHERE room_id = 99980; - 如果更新沒有匹配則插入一新行 IF SQL%NOTFOUND THEN INSERT INTO rooms ( room_id, number_seats ) VALUES ( 99980, 100 ) ; END IF; END;,12.1 光標(biāo)的定義與使用,例4: -節(jié)自在線代碼 nomat2.sql BEGIN UPDATE rooms SET number_seats = 100 WHERE room_id = 99980; - 如果更新沒有匹配則插入一新行 IF SQL%ROWCOUNT = 0 THEN INSERT INTO rooms ( room_id, number_seats ) VALUES ( 99980, 100 ) ; END IF; END;,12.1 光標(biāo)的定義與使用,例5: -節(jié)自在線代碼 nodata.sql DECLARE V_roomdata rooms%ROWTYPE; BEGIN Select * into v_roomdata from rooms Where room_id = -1 ; IF SQL%NOFOUND THEN INSERT INTO temp_table ( char_col ) VALUES ( Not Found ! ) ; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO temp_table ( char_col ) VALUES ( Not Found ! exception handle. ) ; END;,12.2 光標(biāo)循環(huán),在高級語言編程和 PL/SQL 編程中，經(jīng)常使用“提取循環(huán)(fetch loop)” 來實(shí)現(xiàn)逐行提取所需數(shù)據(jù)的方法。,12.2 光標(biāo)循環(huán),LOOP 和END LOOP來實(shí)現(xiàn)簡單循環(huán)。 DECLARE V_studentID students.id%TYPE; V_firstName students.first_name%TYPE; V_LastName students.last_name%TYPE; CURSOR c_HistoryStudents IS SELECT id, first_name, last_name from students WHERE major = History;,BEGIN Open c_HistoryStudents ; LOOP FETCH c_HistoryStudents INTO v_studentID, v_FirstName, V_LastName ; EXIT WHEN c_HistoryStudents%NOFOUND ; INSERT INTO registered_students(student_id, department,course ) VALUES ( v_StudentsID,HIS,301 ); INSERT INTO temp_table ( num_col, char_col ) VALUES ( v_StudentID, v_FirstName| | v_lastName ) ; END LOOP; CLOSE c_HistoryStudents; COMMIT; END;,12.2 光標(biāo)循環(huán),WHILE循環(huán) 與簡單循環(huán)不一樣的是，while 循環(huán)是先判斷后執(zhí)行。只有滿足條件才能執(zhí)行其循環(huán)體內(nèi)的語句；而簡單循環(huán)至少執(zhí)行一次。 declare v_ename emp.ename%type; v_empno emp.empno%type; v_sal emp.sal%type; v_rownum integer; cursor c_sal is select empno,ename,sal from emp where sal=3000;,begin open c_sal; v_rownum:=c_sal%rowcount; dbms_output.put_line(to_char(v_rownum); fetch c_sal INTO v_empno,v_ename,v_sal; while c_sal%found loop dbms_output.put_line(員工信息為：|to_char(v_empno)| |v_ename|的工資為：|to_char(v_sal); fetch c_sal INTO v_empno,v_ename,v_sal; /*exit when c_sal%notfound;*/ end loop;,12.2 光標(biāo)循環(huán),v_rownum:=c_sal%rowcount; dbms_output.put_line(to_char(v_rownum); close c_sal; end; 最主要的作用： 在前臺的開發(fā)環(huán)境下，利用游標(biāo)變量的值進(jìn)行數(shù)據(jù)處理。,12.2 光標(biāo)循環(huán),12.2 光標(biāo)循環(huán),12.2 光標(biāo)循環(huán),12.2.3 光標(biāo) FOR 循環(huán) DECLARE CURSOR c_HistoryStudents IS SELECT id, first_name,last_name FROM students WHERE major = History; BEGIN,FOR v_StudentData IN c_HistoryStudents LOOP INSERT INTO registered_students(student_id, department,course ) VALUES ( v_StudentsID,HIS,301 ); INSERT INTO temp_table ( num_col, char_col ) VALUES ( v_StudentID, v_FirstName| | v_lastName ) ; - 循環(huán)前隱含檢查 %NOFOUND END LOOP; - 自動關(guān)閉光標(biāo) COMMIT; END;,12.2 光標(biāo)循環(huán),關(guān)于NO_DATA_FOUND和%NOTFOUND SELECT . . . INTO 語句觸發(fā) NO_DATA_FOUND； 當(dāng)一個顯式光標(biāo)的 where 子句未找到時 觸發(fā) %NOFOUND； 當(dāng)UPDATE或DELETE 語句的where 子句未找到時 觸發(fā) SQL%NOTFOUND； 在光標(biāo)的提取(Fetch)循環(huán)中要用 %NOTFOUND 或%FOUND 來確定循環(huán)的退出條件，不要用 NO_DATA_FOUND。,12.3 光標(biāo)變量,前面所給出光標(biāo)都是顯式的光標(biāo)，而且是靜態(tài)的光標(biāo)。 Oracle從PL/SQL Release2.2版以后，聲明的光標(biāo)可以不是靜態(tài)的，光標(biāo)變量就是允許開發(fā)人員編寫的程序在運(yùn)行時與不同的語句相關(guān)聯(lián)。 光標(biāo)變量在運(yùn)行可以取不同的值。以達(dá)到靈活的目的。,12.3 光標(biāo)變量,聲明光標(biāo)變量 TYPE type_name IS REF CURSOR RETURN return_type; 為光標(biāo)變量分配存儲空間 EXEC SQL ALLCATE :variable 打開光標(biāo)變量 OPEN cursor_variable FOR select_statements; 關(guān)閉光標(biāo)變量 CLOSE cursor_variable;,實(shí)例見教材p118,學(xué)生訓(xùn)練案例,編寫完成如下功能的pl/sql程序（要求用游標(biāo)完成）。 1、按部門編號從小到大的順序輸出雇員名字、工資以及工資與平均工資的差。 2、已知每個部門有一個經(jīng)理，編寫程序，統(tǒng)計輸出部門名稱、部門總?cè)藬?shù)、總工資和部門經(jīng)理。 3、為所有雇員增加工資，工資在1000以內(nèi)的增加30%，工資在10002000之間的增加20%，2000以上的增加10%。,學(xué)生訓(xùn)練案例,4、用游標(biāo)for循環(huán)完成如下功能的pl/sql程序： CLERK和manager是emp表中job列的2個屬性值，求出職員為CLERK和manager的平均工資，并輸出每個職員的工資及與其所屬job的平均工資的差值。 注意： 游標(biāo)for循環(huán)示顯示游標(biāo)的一種快捷使用方式，它使用for循環(huán)依次讀取結(jié)果集中的行數(shù)據(jù)，當(dāng)form循環(huán)開始時，游標(biāo)自動打開（不需要open），每循環(huán)一次系統(tǒng)自動讀取游標(biāo)當(dāng)前行的數(shù)據(jù)（不需要fetch)，當(dāng)退出for循環(huán)時，游標(biāo)被自動關(guān)閉（不需要使用close）。使用游標(biāo)for循環(huán)的時候不能使用open語句，fetch語句和close語句，否則會產(chǎn)生錯誤。,學(xué)生訓(xùn)練案例,例如： DECALRE CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR2) IS SELECT ename,salary FROM emp WHERE deptno=p_dept ORDER BY ename v_tot_salary EMP.SALARY%TYPE;,學(xué)生訓(xùn)練案例,BEGIN FOR r_dept IN c_dept LOOP DBMS_OUTPUT.PUT_LINE(Department:| r_d