oracle數據庫中關於游標的常見用法

　　什麼是游標

游標是一種PL/SQL控制結構可以對SQL語句進行顯示控制便於對表的數據逐條進行處理

游標分類

顯示游標: Declared and named by the programmer

隱式游標: Declared for all DML and PL/SQL SELECT statements

 

游標的屬性

%FOUNDEvaluates to TRUE if the most recent SQL statement affects one   or more rows

%NOTFOUND和%FOUND相反

%ISOPEN是一個布爾值如果游標打開則為TRUE 如果游標關閉則為FALSE對於隱式游標而言SQL%ISOPEN總是FALSE這是因為隱式游標在DML語句執行時打開結束時就立即關閉

%ROWCOUNTNumber of records affected by the most recent SQL statement

 

注意dbms_outputput_line();這個是不能打印boolean型的解決方法

if b then

 dbms_outputput_line(b=true);

end if;

 

或者

declare

 b boolean;

begin

 b := true;

 dbms_outputput_line((case when b then true else false end));

end;

 

對於null課先使用NVL（）或者decode()處理

 

顯示游標:需要手動open和close

例如

DECLARE

CURSOR mycursor IS

SELECT * FROM dept;

myrecord dept%ROWTYPE;

BEGIN

OPEN mycursor;

FETCH mycursor INTO myrecord;

WHILE mycursor%FOUND LOOP

DBMS_OUTPUTPUT_LINE(myrecorddeptno|| ||myrecorddname||   ||myrecordloc);

FETCH mycursor INTO myrecord;

END LOOP;

CLOSE mycursor;

END;

注意在進行while循環前必須先有一個FETCHINTO操作否者%FOUND總是返 回false

 

帶參數的游標

DECLARE

CURSOR mycursor(num varchar) IS

SELECT * FROM DEPT WHERE deptno=num;

myrecord dept%ROWTYPE;

BEGIN

OPEN mycursor();

LOOP

FETCH mycursor INTO myrecord;

EXIT WHEN mycursor%NOTFOUND;

DBMS_OUTPUTPUT_LINE(deptNum=||myrecorddeptno|| deptName=||myrecorddname);

END LOOP;

CLOSE mycursor;

END;

 

FOR循環操作游標

使用FOR循環讀取游標時不需要顯示申明變量用於接收結果也不需要手動打開和關閉游標例如

DECLARE

CURSOR mycursor(num varchar) IS

SELECT * FROM DEPT WHERE deptno=num;

BEGIN

FOR cur IN mycursor() LOOP

DBMS_OUTPUTPUT_LINE(deptNum=||curdeptno|| deptName=||curdname);

END LOOP;

END;

 

注意PL/SQL中參數只需要給出類型不需要給出長度或精度

當直接將游標的值讀取到變量時變量的個數應與游標指向的結果集的列數相同例如結果集中有兩個列那麼使用FETCHINTO 時對應的變量個數也應該有兩個

DECLARE

d_no number;

d_name varchar();

CURSOR mycursor(num varchar) IS

SELECT deptnodname FROM DEPT WHERE deptno=num;

BEGIN

OPEN mycursor();

FETCH mycursor INTO d_nod_name;

LOOP

DBMS_OUTPUTPUT_LINE(d_no|| ||d_name);

FETCH mycursor INTO d_nod_name;

EXIT WHEN mycursor%NOTFOUND;

END LOOP;

CLOSE mycursor;

END;

/

 

%ROWCOUNT初始值為null每當使用FETCHINTO從游標中取出一條數據後      ROWCOUNT的值加並不是標識結果集的行數

例如

DECLARE

d_name varchar();

CURSOR mycursor IS

SELECT dname FROM DEPT;

BEGIN

OPEN mycursor;

LOOP

FETCH mycursor INTO d_name;

EXIT WHEN mycursor%NOTFOUND;

DBMS_OUTPUTPUT_LINE(mycursor%ROWCOUNT);

END LOOP;

CLOSE mycursor;

END;

結果集中有行記錄輸出結果為

 

可更新數據的游標

要想在使用游標的同時修改數據需要在申明游標時加上FOR UPDATE關鍵字

例如

DECLARE

d_name VARCHAR();

CURSOR mycursor IS

SELECT dname FROM dept FOR UPDATE;

BEGIN

OPEN mycursor;

LOOP

FETCH mycursor INTO d_name;

EXIT WHEN mycursor%NOTFOUND;

UPDATE dept SET dname=RTRIM(dname_t) WHERE CURRENT OF mycursor;

END LOOP;

CLOSE mycursor;

END;

CURRENT OF+游標名獲取游標當前所指向的行

RTRIM(dname_t)LTRIMRTRIM實現字符串過濾(不僅僅去除空格)

 

 

隱式游標不使用DECLARE顯示申明的游標

例如

BEGIN

FOR cur IN(SELECT dname FROM dept) LOOP

DBMS_OUTPUTPUT_LINE(curdname);

END LOOP;

END;

 

含有參數的游標

declare
 cursor cur_my (mv number) is select * from Person where no<mv;
begin
  for tem in cur_my() loop
  DBMS_OUTPUTput_line(name:||temname);
  end loop;
end;

設置引用游標
declare
   temp_row Person%rowtype;
   type my_type is ref cursor;
   cur_my my_type;
begin
   open cur_my for select * from Person ;
   loop
      fetch cur_my into temp_row;
      exit when cur_my%notfound;
      DBMS_OUTPUTput_line(name:||temp_rowname);
   end loop;
   close cur_my;
end;

for loop循環游標

DECLARE
    v_id Integer;
    v_name varchar();
    v_age Integer;
    cursor cur_mycursor is select idnameage from Users;
BEGIN
    for temp in cur_mycursor loop
       v_id :=tempid;
       v_name :=tempname;
       v_age :=tempage;
       dbms_outputput_line(id:||v_id||name:||v_name||age:||v_age);
    end loop;
    /**dbms_outputput_line(所有記錄數||cur_mycursor%rowcount||條！);*/
END;

標准化loop循環游標

DECLARE
    v_id Integer;
    v_name varchar();
    v_age Integer;
    cursor cur_mycursors is select idnameage from Users;
BEGIN
     OPEN cur_mycursors;
       dbms_outputput_line(所有記錄數||cur_mycursors%rowcount||條！);
     LOOP
       FETCH cur_mycursors INTO v_idv_namev_age;
       dbms_outputput_line(id:||v_id||name:||v_name||age:||v_age);
       IF  cur_mycursors%NOTFOUND THEN
            EXIT;
       END IF;
     END LOOP;
     dbms_outputput_line(所有記錄數||cur_mycursors%rowcount||條！);
     CLOSE cur_mycursors;
END;

From:http://tw.wingwit.com/Article/program/Oracle/201311/19107.html