三）遊標1.遊標的基本原理對資料結果進行逐條處理。執行效率低、佔用資源。2.遊標屬性%FOUND%ISOPEN是否開啟%NOTFOUND%ROWCOUNT遊標每抽取一行增加1，記錄遊標抽取過的行數。參數化遊標1）DECLARECURSOR cur_para<id varchar2> ISSELECT books_name FROM books WHERE books_id=id;t_name books.books_name%TYPE;BEGINOPEN cur_para<'0001'>;LOOPFETCH cur_para INTO t_name;EXIT WHEN cur_para%NOTFOUND;DBMS_OUTPUT.PUT_LINE<t_name>;END LOOP;CLOSE cur_para;END;/2）ACCEPT my_tid prompt 'Please input the tid:'DECLARE CURSOR teacher_cur(CURSOR_id NUMBER) IS SELECT TNAME,TITLE,SEX FROM TEACHERS WHERE TID=CURRSOR_id;BEGIN OPEN teacher_cur(my_tid); LOOP FETCH teacher_cur INTO teacher_name,teacher_title,teacher_sex; EXIT WHEN teacher_cur%NOTFOUND; ... END LOOP; CLOSE teacher_cur;END;3.顯式遊標使用過程:1)聲明CURSOR<遊標名> IS SELECT<語句>;2)開啟OPEN <遊標名>3)提取FETCH <遊標名> INTO <變數列表>;FETCH <遊標名> INTO PL/SQL記錄;4)判斷遊標是否為空白，空轉入第5）非空轉入3）5)關閉CLOSE <遊標名>4.隱式遊標1）SELECT 只能返回一條記錄BEGIN SELECT TID,TNAME,TITLE,SEX INTO teacher_id,teacher_name,teacher_title,teacher_sex FROM TEACHERS WHERE TID=113;END;2）SELECT可以返回多條記錄BEGINFOR cur IN(SELECT name FROM deptment) LOOPDBMS_OUTPUT.PUT_LINE<cur.name>;END LOOP;END;/5.遊標變數1)聲名TYPE <類型名> IS REF CURSORRETURN <傳回型別>;2)開啟遊標變數OPEN <遊標變數> FOR <SELECT語句>3)關閉遊標變數CLOSE6.使用執行個體1)DECLARECURSOR mycur ISSELECT * FROM books;myrecord books%ROWTYPE;BEGINOPEN mycur;FETCH mycur INTO myrecord;WHILE mycur%FOUND LOOPDBMS_OUTPUT.PUT_LINE(myrecord.books_id||','||myrecord.books_name);FETCH mycur INTO myrecord;END LOOP;CLOSE mycur;END;2)通過遊標修改資料DECLARECURSOR cur ISSELECT name FROM deptment FOR UPDATE;text varchar2<10>;BEGINOPEN cur;FETCH cur INTO text;WHILE cur%FOUND LOOPUPDATE deptment SET name=name||'_t' WHERE CURRENT OF cur;FETCH cur INTO text;END LOOP;CLOSE cur;END;/
