程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle學習筆記十一游標，oracle學習筆記游標

Oracle學習筆記十一游標，oracle學習筆記游標

編輯：Oracle教程

Oracle學習筆記十一游標，oracle學習筆記游標

游標的簡介

游標的概念

游標是從數據表中提取出來的數據，以臨時表的形式存放在內存中，在游標中有一個數據指針，在初始狀態下指向的是首記錄，利用fetch語句可以移動該指針，從而對游標中的數據進行各種操作，然後將操作結果寫回數據表中。

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

游標的定義

游標作為一種數據類型，首先必須進行定義，其語法如下：

cursor 游標名 is select 語句;

cursor是定義游標的關鍵詞，select是建立游標的數據表查詢命令。

declare
　　cursor  c1  is 
　　　　select  ename, sal  from emp  where rownum<11;  --定義游標
　　v_ename varchar2(10);
　　v_sal number(7,2);
begin
  open  c1;  --打開游標
  fetch  c1  into  v_ename, v_sal;  --fetch游標，讀取數據
  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;

游標的類型

隱式游標

在PL/SQL中使用DML語句時自動創建隱式游標,即：所有的SQL 語句在上下文區內部都是可執行的，因此都有一個游標指向上下文區，此游標就是所謂的SQL游標(SQL cursor)，與顯式游標不同，SQL游標不被程序打開和關閉，通過檢查隱式游標的屬性可以獲得最近執行的DML 語句的信息。

隱式游標的屬性有：SQL+屬性　　1.%FOUND – SQL 語句影響了一行或多行時為 TRUE 　　2.%NOTFOUND – SQL 語句沒有影響任何行時為TRUE 　　3.%ROWCOUNT – SQL 語句影響的行數　　4.%ISOPEN - 游標是否打開，始終為FALSE

SET SERVEROUTPUT ON
BEGIN
    UPDATE toys SET toyprice=270
    WHERE toyid= 'P005';
    IF SQL%FOUND THEN    --只有在 DML 語句影響一行或多行時，才返回 True
        DBMS_OUTPUT.PUT_LINE('表已更新');
    END IF;
END;

SET SERVEROUTPUT ON
DECLARE
      v_TOYID TOYS.ID%type := '&TOYID';
      v_TOYNAME TOYS.NAME%Type := '&TOYNAME';
     BEGIN
      UPDATE TOYS SET NAME = v_TOYNAME
      WHERE toyid=v_TOYID;
      IF SQL%NOTFOUND THEN      --如果 DML 語句不影響任何行，則返回 True 
            DBMS_OUTPUT.PUT_LINE('編號未找到。');
     ELSE
        DBMS_OUTPUT.PUT_LINE('表已更新');
    END IF;
END;

SET SERVEROUTPUT ON 
BEGIN
    UPDATE vendor_master
    SET venname= 'Rob Mathew'
    WHERE vencode='V004';
    DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT);    --返回 DML 語句影響的行數
END;

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;

SELECT INTO 語句

SET SERVEROUTPUT ON
DECLARE 
    empid VARCHAR2(10);
    desig VARCHAR2(10);
BEGIN
    empid:= '&Employeeid';
    SELECT designation INTO desig 
        FROM employee WHERE empno=empid;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN    --如果沒有與SELECT INTO語句中的條件匹配的行，將引發NO_DATA_FOUND異常
            DBMS_OUTPUT.PUT_LINE('職員未找到');
END;

SET SERVEROUTPUT ON
DECLARE 
　　empid VARCHAR2(10);
BEGIN
　　SELECT empno INTO empid FROM employee;--給變量多個值
　　EXCEPTION
　　　　WHEN TOO_MANY_ROWS THEN      --如果 SELECT INTO 語句返回多個值，將引發TOO_MANY_ROWS異常
　　　　　　DBMS_OUTPUT.PUT_LINE('該查詢提取多行');
END;

顯式游標

顯式游標在 PL/SQL 塊的聲明部分定義查詢，該查詢可以返回多行。顯式游標的操作過程：

如何使用？

游標的打開操作

　　如果要使用創建好的游標，需要先打開游標，語法結構如下：

　　open 游標名;

　　打開游標的過程有以下兩個步驟：

　　　　（1）將符合條件的記錄送入內存。

　　　　（2）將指針指向第一條記錄的前面。

游標打開了要關閉：CLOSE 游標名；

游標提取數據的操作（每次只能提取一行）；

　　如果要提取游標中的數據，需要使用fetch命令，語法形式如下。

　　fetch 游標名 into 變量名1, 變量名2,……;

　　或fetch 游標名 into 記錄型變量名;

示例代碼：

set serveroutput on 
declare
     tempsal scott.emp.sal%type;    --定義cursorrecord變量是游標mycursor的記錄行變量 
    cursor mycursor is 
    select * from scott.emp 
    where sal>tempsal;     --在游標mycursor的結果中找到sal字段大於800的第一個記錄
    cursorrecord mycursor%rowtype;    
begin
     tempsal:=800;
    open mycursor; 
    fetch mycursor into cursorrecord; 
    dbms_output.put_line(cursorrecord.deptno);     --顯示deptno字段的內容 
end;

set serveroutput on 
declare     tempsal scott.emp.sal%type; 
    cursor mycursor is 
    select * from scott.emp 
    where sal>tempsal; 
    cursorrecord mycursor%rowtype; 
begin     tempsal:=800; 
    if mycursor%isopen then 
        fetch mycursor into cursorrecord; dbms_output.put_line(to_char(cursorrecord.deptno)); 
    else     dbms_output.put_line('游標沒有打開！'); 
    end if; 
end;

SET SERVER OUTPUT ON
DECLARE
　　my_toy_price toys.toyprice%TYPE;    
　　CURSOR toy_cur IS
　　　　SELECT toyprice FROM toys
　　　　　　WHERE toyprice<250;    --聲明游標
BEGIN
　　OPEN toy_cur;      --打開游標
　　LOOP
　　　　FETCH toy_cur INTO my_toy_price;    --提取行
　　　　EXIT WHEN toy_cur%NOTFOUND;
　　　　DBMS_OUTPUT.PUT_LINE('TOYPRICE=:玩具單價=：'||my_toy_price);
　　END LOOP;
　　CLOSE toy_cur;    --關閉游標
END;

帶參數的顯式游標

聲明顯式游標時可以帶參數以提高靈活性。聲明帶參數的顯式游標的語法如下：

CURSOR <cursor_name>(<param_name> <param_type>)

IS select_statement;

要注意的是：參數定義時，數據類型只能寫名字，而不能定義長度！還有，當定義了參數游標後一定要在游標子查詢的where子句中引用參數不然就沒有意義。

SET SERVEROUTPUT ON
DECLARE
　　desig    VARCHAR2(20);
　　emp_code VARCHAR2(5);
　　empnm    VARCHAR2(20);
　　CURSOR emp_cur(desigparam VARCHAR2) IS
　　　　SELECT empno, ename FROM employee
　　　　　　WHERE designation=desigparam;
BEGIN
　　desig:= '&desig';
　　OPEN emp_cur(desig);
　　LOOP
　　　　FETCH emp_cur INTO emp_code,empnm;
　　　　EXIT WHEN emp_cur%NOTFOUND;
　　　　　　DBMS_OUTPUT.PUT_LINE(emp_code||' '||empnm);
　　END LOOP;
　　CLOSE emp_cur;
END;

使用顯式游標更新行

允許使用游標刪除或更新活動集中的行。聲明游標時必須使用 SELECT … FOR UPDATE OF 列名 [nowait]語句. For update用於結果集上加鎖，nowait 不等待鎖。

　　CURSOR <cursor_name> IS

　　　　SELECT statement FOR UPDATE;

更新的語法

　　UPDATE <table_name>

　　　　SET <set_clause>

　　　　　　WHERE CURRENT OF <cursor_name>

刪除的語法

DELETE FROM <table_name>

　　WHERE CURRENT OF <cursor_name>

SET SERVEROUTPUT ONDECLARE
　　new_price NUMBER;
　　CURSOR cur_toy IS
　　　　SELECT toyprice FROM toys WHERE toyprice<100
　　　　　　FOR UPDATE OF toyprice;
BEGIN
　　OPEN cur_toy;
　　LOOP
　　　　FETCH cur_toy INTO new_price;
　　　　EXIT WHEN cur_toy%NOTFOUND;
　　　　UPDATE toys
　　　　　　SET toyprice = 1.1*new_price
　　　　　　　　WHERE CURRENT OF cur_toy;
　　END LOOP;
　　CLOSE cur_toy;
　　COMMIT;
END;

循環游標

循環游標用於簡化游標處理代碼，當用戶需要從游標中提取所有記錄時使用，不需要顯示打開和關閉游標。循環游標的語法如下：

FOR <record_index> IN <cursor_name>
LOOP
    <executable statements>
END LOOP;

注意： <record_index> 名字可以不需要定義，直接使用，因為是Oracle隱含定義的變量名

SET SERVER OUTPUT ON
DECLARE 
　　CURSOR mytoy_cur IS
　　　　SELECT toyid, toyname, toyprice
　　　　　　FROM toys;
BEGIN
　　FOR toy_rec IN mytoy_cur
　　LOOP
　　　　DBMS_OUTPUT.PUT_LINE('玩具編號：'||' ' ||toy_rec.toyid||' '            
            ||'玩具名稱：'||' '||toy_rec.toyname||' '
            ||'玩具單價：'||' '||toy_rec.toyprice);
　　END LOOP;
END;

REF 游標和游標變量

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

Type newer_cur is ref cursor; --定義了一個newer_cur的游標類型（弱類型）
Type newer_cur is ref cursor return emp%rowtype; --強游標類型，表示這個游標的結果返回的一定是empty類型

　　2.聲明 REF 游標類型的變量

Mycur newer_cur;  --定義變量mycur，它是引用游標類型

用於聲明 REF 游標類型的語法為：

TYPE <ref_cursor_name> IS REF CURSOR [RETURN <return_type>];

打開游標變量的語法如下：

OPEN cursor_name FOR select_statement;

聲明強類型的 REF 游標

TYPE my_curtype IS REF CURSOR
  RETURN stud_det%ROWTYPE;
order_cur my_curtype;

聲明弱類型的 REF 游標

TYPE my_ctype IS REF CURSOR;
stud_cur my_ctype;

DECLARE 
　　TYPE toys_curtype IS REF CURSOR
　　　　RETURN toys%ROWTYPE;
　　toys_curvar toys_curtype;
　　toys_rec toys%ROWTYPE;
BEGIN
　　OPEN toys_curvar FOR
　　　　SELECT * FROM toys;
　　FETCH toys_curvar INTO toys_rec;
　　...
　　CLOSE toys_curvar;
END;

游標變量的優點和限制

游標變量的功能強大，可以簡化數據處理。游標變量的優點有：　　1.可從不同的 SELECT 語句中提取結果集　　2.可以作為過程的參數進行傳遞　　3.可以引用游標的所有屬性　　4.可以進行賦值運算使用游標變量的限制：　　1.不能在程序包中聲明游標變量　　2.FOR UPDATE子句不能與游標變量一起使用　　3.不能使用比較運算符可以使用游標變量執行動態構造的 SQL 語句。打開執行動態 SQL 的游標變量的語如下：

OPEN cursor_name FOR dynamic_sqlstring [USING bind_argument_list];

DECLARE
　　r_emp emp%ROWTYPE;
　　TYPE c_type IS REF CURSOR;
　　cur c_type;
　　p_salary NUMBER;
BEGIN
　　p_salary := 2500;
　　OPEN cur FOR 'select * from emp where sal>:1 order by sal desc' USING p_salary;
　　DBMS_OUTPUT.PUT_LINE('薪水大於'|| p_salary ||'的員工有：');
　　LOOP
　　　　FETCH cur INTO r_emp;
　　EXIT WHEN cur%NOTFOUND;
　　　　DBMS_OUTPUT.PUT_LINE('編號：'|| r_emp.empno
　　　　　　|| ' 姓名：' || r_emp.ename|| ' 薪水：' || r_emp.sal );
　　END LOOP;
　　CLOSE cur; 
END;

其他筆記

顯示游標

--更新員工的獎金，如果成功了，則提示成功的信息，失敗了則提示失敗的信息。

begin
    update emp set comm = 1 where empno = 8499; --DML語句
    if SQL%found then
        dbms_output.put_line('數據已經成功更新了');
    else
        dbms_output.put_line('數據更新失敗');
    end if;
end;

--記錄用戶登錄情況的信息（更新用戶的登錄信息）

Begin
    Update login set ltime = sysdate where name = 'zs';
    If sql%notfound then
        Insert into login values('zs', sysdate);
    End if;
End;

--在PL/SQL中顯示所有工資大於2000的員工信息

Declare
    --定義一個游標，裡面保存的是工資大於2000的數據
    Cursor mycursor is select * from emp where sal > 2000;
    mydata emp%rowtype;
Begin
    Open mycursor;   --打開游標
    Fetch mycursor into mydata;  --提取一行數據
    Dbms_output.put_line(mydata.empno || ' ' || mydata.ename);
    Close mycursor;  --關閉游標
End;

--循環輸出

Declare
    --定義一個游標，裡面保存的是工資大於2000的數據
    Cursor mycursor is select * from emp where sal > 2000;
    mydata emp%rowtype;
Begin
    Open mycursor;   --打開游標
    loop
        Fetch mycursor into mydata;  --提取一行數據
        If mycursor%notfound then
            Exit;
        End if;
        Dbms_output.put_line(mydata.empno || ' ' || mydata.ename || ' ' || mydate.sal);
    End loop;
    Close mycursor;  --關閉游標
End;

--使用for循環來操作

Declare
    --定義一個游標，裡面保存的是工資大於2000的數據
    Cursor mycursor is select * from emp where sal > 2000;
    mydata emp%rowtype;
Begin
    For myname in mycursor loop  --無需打開關閉和，表示提取一行到myname裡面
        Dbms_ouptup.put_line(myname.empno || ' ' || myname.ename);
    End loop;
End;

--使用游標2:列數和順序要一致

Declare
    Cursor mycur is select ename, sal from emp;
    Var_row emp%rowtype;
    Var_name emp.ename%type;
    Var_sal emp.sal%type;
Begin
    Open mycur;
    Loop
        --fetch mycur into var_row; --錯誤 PLS-00394: 在FETCH語句的INTO列表中值數量出現錯誤
        Fetch mycur into var_name, var_sal; -- 使用變量來接收結果
        Exit when mycur%notfound;
        Dbms_output.put_line(var_name || ' ' || var_sal);
    End loop;
    Close mycur;
End;

--參數游標：在定義游標的時候通過定義參數提高靈活性。

--指定工資參數的游標:

Declare
    Cursor cur_sal(vsal number) is select * from emp where sal > vsal;
Begin
    For row in cur_sal(2000) loop
        Dbms_output.put_line(row.ename || ' ' || row.sal);
    End loop;
End;

--可更新游標

--更新
Declare
    Cursor cur is select * from emp where sal > 2500 for update;
Begin
    For row in cur loop
        Dbms_output.put_line(row.ename || ' ' || row.sal ||  ' ' || row.comm);
        --update emp set comm = 9 where empno = row.empno;
        Update emp set comm = 9 where current of cur;
        --delete emp where current of cur;  --刪除游標數據
    End loop;
End;

REF游標

--使用

Declare
    Type newer_cur is ref cursor;
    Var_row emp%rowtype;
    Var_cur newer_cur;
Begin
    Open var_cur for select * from emp where sal > 2500;
    Loop
        Fetch var_cur into var_row;
        Exit when var_cur%notfound;
        Dbms_output.put_line(var_row.empno || ' ' || var_row.ename || ' ' || var_row.sal);
    End loop;
End;

--根據員工所在的部門信息來查詢數據，如果員工是10部門則查詢前5個員工，如果是20部門則查詢6-10號，如果是30部門則查詢10號以後的員工

Declare
    Type mycur is ref cursor;
    Cur mycur;
    Var_dept int;
    Var_n varchar2(20);
    Var_sal int;
    Var_rn int;
Begin
    Var_dept := &n;
    If var_dept = 10 then
        Open cur for select * from (select rownum rn, d.* from (select ename,sal from emp order by sal desc) d ) where rn <=5;
    Else if var_dept = 20 then
        Open cur for select * from (select rownum rn, d.* from (select ename,sal from emp order by sal desc) d ) where rn > 5 and rn <=10;
    Else
        Open cur for select * from (select rownum rn, d.* from (select ename,sal from emp order by sal desc) d ) where rn > 10;
    End if;
    End if;
    
    Loop
        Fetch cur into var_rn, var_n, var_sal;
        Exit when cur%notfound;
        Dbms_output.put_line(var_n || ' ' || var_sal || ' ' || var_rn);
    End loop;
End;