oracle中的游標的原理和使用詳解

oracle中的游標的原理和使用詳解

---

游標 游標的簡介：

逐行處理查詢結果，以編程的方式訪問數據

游標的類型：

1，隱式游標：在 PL/SQL 程序中執行DML SQL 語句時自動創建隱式游標，名字固定叫sql。

2，顯式游標：顯式游標用於處理返回多行的查詢。

3，REF 游標：REF 游標用於處理運行時才能確定的動態 SQL 查詢的結果

隱式游標：

---

q在PL/SQL中使用DML語句時自動創建隱式游標 q隱式游標自動聲明、打開和關閉，其名為 SQL q通過檢查隱式游標的屬性可以獲得最近執行的DML 語句的信息 q隱式游標的屬性有： q%FOUND – SQL 語句影響了一行或多行時為 TRUE q%NOTFOUND – SQL 語句沒有影響任何行時為TRUE q%ROWCOUNT – SQL 語句影響的行數 q%ISOPEN - 游標是否打開，始終為FALSE

begin
  update student s set s.sage = s.sage + 10 ;
  if sql %FOUND   then
    dbms_output.put_line('這次更新了' || sql% rowcount );
    else
      dbms_output.put_line ('一行也沒有更新' );
      end if;
      end;

 

在select中有兩個中比較常見的異常： 1. NO_DATA_FOUND 2. TOO_MANY_ROWS

SQL> declare
  2  sname1 student.sname%TYPE;
  3  begin
  4    select sname into sname1 from student;
  5    if sql%found then
  6      dbms_output.put_line(sql%rowcount);
  7    else
  8      dbms_output.put_line('沒有找到數據');
  9      end if;
 10      exception
 11        when too_many_rows then
 12          dbms_output.put_line('查找的行記錄多於1行');
 13         when no_data_found then
 14            dbms_output.put_line('未找到匹配的行');
 15       end;
 16  /
查找的行記錄多於1行
PL/SQL procedure successfully completed

SQL> 

 

顯式游標：

---

sqlserver與oracle的不同之處在於： 最後sqlserver會deallocate 丟棄游標，而oracle只有前面四步： 聲明游標、打開游標、使用游標讀取記錄、關閉游標。
顯式游標的使用：

------------------------------------無參數游標-------------------------------
declare
  sname varchar2( 20); --聲明變量
  cursor student_cursor is select sname from student ; --聲明游標
  begin
    open student_cursor;--打開游標
      fetch student_cursor into sname ;--讓游標指針往下移動
    while student_cursor%found --判斷游標指針是否指向某行記錄
      loop--遍歷
        dbms_output.put_line ('學生姓名' ||sname );
        fetch student_cursor into sname;
      end loop;
       close student_cursor;
      end;
     
------------------------------------有參數游標-------------------------------
declare
sname student.sname%type;
sno student.sno%type;
cursor student_cursor (input_sno number) is select s.sname, s.sno from student s where s.sno > input_sno; --聲明帶參數的游標
begin
  sno := &請輸入學號 ;--要求從客戶端輸入參數值，"&"相當於占位符；
  open student_cursor( sno); --打開游標，並且傳遞參數
  fetch student_cursor into sname, sno; --移動游標
  while student_cursor% found
    loop
      dbms_output.put_line ('學號為：' ||sno ||'姓名為：' ||sname );
      fetch student_cursor into sname,sno;
      end loop;
       close student_cursor;
      end;
------------------------------------循環游標-------------------------------    
-- Created on 18-1月-15 by 永文
declare
stu1 student%rowtype ;--這裡也不需要定義變量來接收fetch到的值
cursor student_cursor is select * from student ;
begin
 open student_cursor; --這裡不需要開啟游標
  for stu1 in student_cursor
    loop
      dbms_output.put_line ('學生學號：' ||stu1.sno ||'學生姓名：' ||stu1.sname );
      fetch student_cursor into stu1;--也不需要fetch了
      end loop;
    close student_cursor;  --這裡也不需要關閉游標
      end;
     
------------------------------------使用游標更新行-------------------------------   
  declare
  stu1 student%rowtype ;
  cursor student_cursor is select * from student s where s.sno in (2 ,3 ) for update;--創建更新游標
  begin
   open student_cursor;
   fetch student_cursor into stu1;--移動游標
   while student_cursor%found --遍歷游標，判斷是否指向某個值
     loop
       update student set sage = sage + 10 where current of student_cursor;--通過游標中的信息更新數據
        fetch student_cursor into stu1;--移動游標
       end loop;
       close student_cursor;
       end;

  declare
  stu1 student%rowtype ;
  cursor student_cursor is select * from student s where s.sno in (2 ,3 ) for update;--創建更新游標
  begin
   open student_cursor;
  -- fetch student_cursor into stu1;--移動游標
  -- while student_cursor%found--遍歷游標，判斷是否指向某個值
     loop
         fetch student_cursor into stu1 ;--移動游標
         exit when student_cursor %notfound ;
       update student set sage = sage + 10 where current of student_cursor;--通過游標中的信息更新數據
       end loop;
       close student_cursor;
       end;
------------------------------------使用fetch ... bulk collect into-------------------------------   
declare
  cursor   my_cursor is select ename from emp where deptno= 10; --聲明游標
  type   ename_table_type is table of varchar2 (10 );--定義一種表類型，表中的屬性列為varchar2類型
  ename_table  ename_table_type;--通過上面定義的類型來定義變量
begin
  open   my_cursor; --打開游標
  fetch my_cursor bulk collect into   ename_table; --移動游標
  for   i in 1 ..ename_table.count  loop
     dbms_output.put_line(ename_table(i));
  end   loop ;
  close my_cursor;
end;


-----------------------------------顯示游標題目--------------------------------------

SQL > select * from student ;
        XH XM
---------- ----------
         1 A
         2 B
         3 C
         4 D

SQL > select * from address ;
        XH ZZ
---------- ----------
         2 鄭州
         1 開封
         3 洛陽
         4 新鄉
        
完成的任務 :給表student添加一列zz ,是varchar2 (10 )類型；
再從address中，將zz字段的數值取出來，對應的插入到
student新增的zz列中。
即：得到的結果：student表中，是：
          XH XM         ZZ
         -- ---------- ------
          1 A          開封
          2 B          鄭州
          3 C          洛陽
          4 D          新鄉


declare
stu1 student %rowtype ;
add1 address %rowtype ;
cursor student_cursor is select * from student for update;--聲明更新游標
cursor address_cursor is select * from address ;--聲明游標
begin
  open student_cursor ;--打開游標
  fetch student_cursor into stu1;--移動游標
  while student_cursor% found--判斷游標是否指向某條記錄
    loop
      open address_cursor ;--打開另外一個游標
      fetch address_cursor into add1 ;--移動游標
      while address_cursor %found--判斷游標是否指向某條記錄
        loop
          if add1.xh = stu1.xh then--判斷兩個游標所指向的記錄中xh的值是否相等
            update student s set s.zz = add1.zz where current of student_cursor;--假如相等就更新游標所指向的記錄值
            end if;
            fetch address_cursor into add1 ;--移動游標
            end loop;
            close address_cursor ;--關閉游標
            fetch student_cursor into stu1 ;--移動游標
            end loop;
            close student_cursor ;--關閉游標
            end;

 

REF游標也叫動態游標：

---

qREF 游標和游標變量用於處理運行時動態執行的 SQL 查詢 q創建游標變量需要兩個步驟： q聲明 REF 游標類型 q聲明 REF 游標類型的變量 q用於聲明 REF 游標類型的語法為：

TYPE <ref_cursor_name> IS REF CURSOR

[RETURN <return_type>];

-----------------------------------ref游標---------------------------------
declare
type ref_cursor  is ref cursor; --聲明一個ref游標類型
tab_cursor ref_cursor ;--聲明一個ref游標
sname student.xm %type ;
sno student.xh %type ;
tab_name varchar2 (20 );
begin
  tab_name := '&tab_name'; --接收客戶輸入的表明
  if tab_name = 'student' then
    open tab_cursor for select xh ,xm  from student ; --打開ref游標
    fetch tab_cursor into sno ,sname ;--移動游標
    while tab_cursor %found
      loop
        dbms_output.put_line ('學號：' ||sno ||'姓名：' ||sname );
        fetch tab_cursor into sno ,sname ;
        end loop;
        close tab_cursor ;
        else
          dbms_output.put_line ('沒有找到你想要找的表數據信息' );
          end if;
        end;

 
-----------------------------------ref游標題目---------------------------------
SQL > select * from student ;
        XH KC
---------- ----------
         1 語文
         1 數學
         1 英語
         1 歷史
         2 語文
         2 數學
         2 英語
         3 語文
         3 英語
9 rows selected

SQL >       
完成的任務 :
生成student2表 (xh number, kc  varchar2 (50 ))；
對應於每一個學生，求出他的總的選課記錄，把每個學生的選課記錄插入到student2表中。
即，student2中的結果如下：
                      XH KC
                 --- -------------------------------------------
                       1 語文數學英語歷史
                       2 語文數學英語
                       3 語文英語

create table student2 (xh number, kc varchar2 (50 ));

declare
kcs varchar2 (50 );
kc varchar2 (50 );
type ref_cursor is ref cursor; --聲明一個ref游標類型
stu_cursor ref_cursor ;--定義一個ref游標類型的變量
type tab_type is table of number; --聲明一個table類型
tab_xh tab_type ;--定義一個表類型的變量
cursor cursor_xh is select distinct( xh) from student; --聲明一個游標
begin
  open cursor_xh; --打開游標
  fetch cursor_xh bulk collect into tab_xh; --提取數據到表中
  for i in 1 .. tab_xh.count
    loop
      kcs :='' ;
      open stu_cursor for select kc from student s where s.xh = tab_xh(i ); --打開ref游標
      fetch stu_cursor into kc ; --移動游標
      while stu_cursor %found
        loop
          kcs := kc ||kcs ; --連接字符串使用||而不是+
          fetch stu_cursor into kc ; --移動游標
          end loop;
          insert into student2 (xh , kc ) values( i, kcs);
          close stu_cursor ;
      end loop;
      close cursor_xh ;
      end;