2019-05-13

游標（cursor）能夠根據(jù)查詢條件從數(shù)據(jù)表中提取一組記錄，將其作為一個臨時表置于數(shù)據(jù)緩沖區(qū)中，利用指針逐行對記錄數(shù)據(jù)進行操作。

隱式游標

在執(zhí)行SQL語句時，Oracle會自動創(chuàng)建隱式游標，該游標是內(nèi)存中處理該語句的數(shù)據(jù)緩沖區(qū)，存儲了執(zhí)行SQL語句的結(jié)果。通過隱式游標屬性可獲知SQL語句的執(zhí)行狀態(tài)信息。

? %found：布爾型屬性，如果sql語句至少影響到一行數(shù)據(jù)，值為true，否則為false。
? %notfound：布爾型屬性，與%found相反。
? %rowcount：數(shù)字型屬性，返回受sql影響的行數(shù)。
? %isopen：布爾型屬性，當游標已經(jīng)打開時返回true，游標關閉時則為false。

顯式游標

用戶可以顯式定義游標。使用顯式游標處理數(shù)據(jù)要4個步驟：定義游標、打開游標、提取游標數(shù)據(jù)和關閉游標。

1.定義游標

游標由游標名稱和游標對應的select結(jié)果集組成。定義游標應該放在pl/sql程序塊的聲明部分。

語法格式：cursor 游標名稱(參數(shù)) is 查詢語句

2.打開游標

打開游標時，游標會將符合條件的記錄送入數(shù)據(jù)緩沖區(qū)，并將指針指向第一條記錄。

語法格式：open 游標名稱(參數(shù));

3.提取游標數(shù)據(jù)

將游標中的當前行數(shù)據(jù)賦給指定的變量或記錄變量。

語法格式：fetch 游標名稱 into 變量名;

4.關閉游標

游標一旦使用完畢，就應將其關閉，釋放與游標相關聯(lián)的資源。

語法格式：close 游標名稱;

示例：編寫游標，統(tǒng)計雇員表中指定部門的工資在2000以下、2000-5000、以及5000以上的職工人數(shù)

declare
     cursor emp_cursor( var_deptno in number) --定義游標
     is select * from scott.emp where deptno= var_deptno ;
     var_emp scott.emp%rowtype;
     var_deptno scott.emp.deptno%type;
     num1 int default 0;
     num2 int default 0;
     num3 int default 0;   
begin
var_deptno:=&var_deptno;
open emp_cursor(var_deptno); --打開游標
fetch emp_cursor into var_emp; --提取游標數(shù)據(jù)
     while emp_cursor%found loop  --判斷是否有數(shù)據(jù)
         if(var_emp.sal<2000) then
            num1:=num1+1;
         elsif(var_emp.sal<=5000) then
            num2:=num2+1;
         else
            num3:=num3+1;
         end if;
         fetch emp_cursor into var_emp;  --提取游標數(shù)據(jù)
     end loop;
     close emp_cursor;  --使用完游標后必須顯式關閉
     dbms_output.put_line('小于2000人數(shù):' || num1);
     dbms_output.put_line('2000-5000人數(shù):' || num2);
     dbms_output.put_line('大于5000人數(shù):' || num3);
end;

游標FOR循環(huán)

PL/SQL提供了游標for循環(huán)語句，是遍歷顯式游標的一種快捷方式。

特點：
? 當for循環(huán)開始時，游標會自動打開（不需要使用open方法）。
? 每循環(huán)一次系統(tǒng)自動讀取游標當前行的數(shù)據(jù)（不需要使用fetch）。
? 當退出for循環(huán)時，游標被自動關閉（不需要使用close）。

for 變量 in 游標名稱 loop 
          語句塊 ; 
end loop ;

declare
    cursor emp_cursor( var_deptno in number) --定義游標
    is select * from emp where deptno= var_deptno ;
    var_deptno scott.emp.deptno%type;
    num1 int default 0;
    num2 int default 0;
    num3 int default 0;   
begin
    var_deptno:=&var_deptno;
    for var_emp in emp_cursor(var_deptno) loop
         if(var_emp.sal<2000) then
            num1:=num1+1;
         elsif(var_emp.sal<=5000) then
            num2:=num2+1;
         else
            num3:=num3+1;
         end if;
     end loop;
     dbms_output.put_line('小于2000人數(shù):' || num1);
     dbms_output.put_line('2000-5000人數(shù):' || num2);
     dbms_output.put_line('大于5000人數(shù):' || num3);
end;

使用游標更新記錄

cursor 游標名稱 is 查詢語句 for update;

示例：更新雇員工資 規(guī)則：1000以內(nèi)增加300;2000以內(nèi)增加200;其它增加100。

declare
     cursor emp_cursor is select * from scott.emp for update;
begin
    for var_emp in emp_cursor loop
        if var_emp.sal <= 1000 then
             update scott.emp set sal = var_emp.sal+300 
            where current of emp_cursor;//游標的當前行
        elsif var_emp.sal<=2000 then
             update scott.emp set sal = var_emp.sal+200 
            where current of emp_cursor;
        else
             update scott.emp set sal = var_emp.sal+100 
            where current of emp_cursor;
        end if; 
    end loop;
    commit;
end;