• Oracle中游標Cursor的用法詳解

     更新時間:2022年05月06日 08:50:29   作者:springsnow  
    本文詳細講解了Oracle中游標Cursor的用法,文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下

    一、使用游標

    對于DML語句和單行select into ,oracle自動分配隱形游標。處理select返回多行語句,可以使用顯式游標。

    使用顯示游標處理多行數據,也可使用SELECT..BULK COLLECT INTO 語句處理多行數據.

    1.定義游標

    cursor cursor_name is select_statement;

    2.打開游標

    執行對應的SELECT語句并將SELECT語句的結果暫時存放到結果集中.

    open cursor_name;

    3.提取數據

    打開游標后,SELECT語句的結果被臨時存放到游標結果集中,使用FETCH語句只能提取一行數據

    通過使用FETCH..BULK COLLECT INTO語句每次可以提取多行數據

    fetch cursor_name into variable1,varibale2,...;
    
    fetch cursor_name bulk collect into collect1,collect2,...[limit rows];

    (1)游標中使用fetch..into語句:只能處理一行數據,除非用循環語句

    declare
              v_bookname varchar2(100);
              cursor c_book(i_id number) is select bookname from book where id = i_id;
        begin
            Open c_book(10);--打開游標
            Loop
                Fetch c_book into v_bookname; --提取游標
                exit when c_book%notfound;
                update book set price = '33' where bookname = v_bookname;
            End Loop;
            Close c_book;--關閉游標
        end;

    declare
              v_bookname varchar2(100);
              cursor c_book(i_id number) is select bookname from book where id = i_id;
    begin
              Open c_book(10);
              Fetch c_book into v_bookname;--預先Fetch一次
              While c_book%found Loop
                  update book set price = '33' where bookname = v_bookname;
                   Fetch c_book into v_bookname;
              End Loop;
             Close c_book;
    end;

    (3)基于游標定義記錄變量

    declare
        cursor emp_cursor is select ename,sal from emp;
        emp_record emp_cursor%rowtype;
      begin
        open emp_cursor;
        loop
         fetch emp_cursor into emp_record;
         exit when emp_cursor%notfound;
         dbms_output.put_line('雇員名:'||emp_record.ename||',雇員工資:'||emp_record.sal);
        end loop;
     end;

    4.關閉游標

    close cursor_name;

    5.游標屬性

    用于返回顯示游標的執行信息,包括%isopen,%found,%notfound,%rowcount

    • %isopen:確定游標是否打開
    • %found:檢查是否從結果集中提取到了數據
    • %notfound:與%found行為相反。
    • %rowcount:返回當前行為止已經提取到的實際行數

    no_data_found和%notfound的用法是有區別的,小結如下1)SELECT. . . INTO 語句觸發 no_data_found;
    2)當一個顯式光標(靜態和動態)的 where 子句未找到時觸發 %notfound;
    3)當UPDATE或DELETE語句的where 子句未找到時觸發 sql%notfound;
    4)在光標的提取(Fetch)循環中要用 %notfound 或%found 來確定循環的退出條件,不要用no_data_found。

    6.參數游標

    注意:定義參數游標時,游標參數只能指定數據類型,而不能指定長度。

    declare
        cursor emp_cursor(no number) is select ename from emp where deptno=no;
        v_ename emp.ename%type;
      begin
        open emp_cursor(10);
        loop
         fetch emp_cursor into v_ename;
         exit when emp_cursor%notfound;
         dbms_output.put_line(v_ename);
        end loop;
        close emp_cursor;
      end;

    二、for循環遍歷,實現遍歷游標最高效方式。

    使用FOR循環時,ORACLE會隱含的打開游標,提取游標數據并關閉游標。

    每循環一次提取一次數據,在提取了所有數據后,自動退出循環并隱含的關閉游標。

    1.使用游標FOR循環

    --不需要聲明v_bookname,Open和Close游標和fetch操作(不用打開游標和關閉游標,實現遍歷游標最高效方式)
    declare
     cursor c_book(i_id number) is select bookname from book where id = i_id;
    begin
       for cur in c_book(10) loop --循環變量cur不需要聲明
         update book set price = '53' where bookname = cur.bookname;
       end loop;
    end;

    2.在游標FOR循環中直接使用子查詢

    begin
         for emp_record in (select ename,sal from emp) loop
            dbms_output.put_line(emp_record.ename);
        end loop;
    end;

    三、使用游標更新或刪除數據

    要通過游標更新或刪除數據,在定義游標時必須要帶有FOR UPDATE子句

    cursor cursor_name(parameter_name datetype) is select_statement for update [of column_reference] [nowait];
    • for update子句:用于在游標結果集數據上家行共享鎖,防止其他用戶在相應行執行DML操作
    • of子句:確定哪些表要加鎖,沒有OF子句,則在所引用的全部表上加鎖
    • nowait子句:用于指定不等待鎖
    • 必須在UPDATE后DELETE語句中引用WHERE CURRENT OF子句
      update table_name set column=.. where current of cursor_name;
      delete table_name where current of cursor_name;
    declare
        cursor emp_cursor is select ename,sal from emp for update;
        v_ename emp.ename%type;
        v_sal emp.sal%tyep;
      begin
        open emp_cursor;
        loop
         fetch emp_cursor into v_ename,v_oldsal;
         exit when emp_cursor%notfound;
         if v_oldsal<2000 then
            update emp set sal=sal+100 where current of emp_cursor;--delete from emp where current of emp_cursor;
         end if;
       end loop;
       close emp_cursor;
     end;

    四、通過bulk collect減少loop處理的開銷

    將查詢結果一次性加載到集合中,而不是一條一條的加載。

    (1)在顯示游標中,使用FETCH..BALK COLLECT INTO語句提取所有數據

    declare
       cursor emp_cursor is select ename from emp where deptno=10;
        type ename_table_type is table of varchar2(10);
        ename_table ename_table_type;
      begin
        open emp_cursor;
        fetch emp_cursor bulk collect into ename_table;
        for i in 1..ename_table.count loop
           dbms_output.put_line(ename_table(i));
        end loop;
        close emp_cursor;
      end;

    (2)游標中使用FETCH..BULK COLLECT INTO ..LIMIT語句提取部分數據

    declare
        type name_array_type is varray(5) of varchar2(10);
        name_array name_array_type;
        cursor emp_cursor is select ename from emp;
        rows int:=5;
        v_count int:=0;
      begin
        open emp_cursor;
        loop
         fetch emp_cursor bulk collect into name_array limit rows;
         dbms_output.pur('雇員名');
         for i in 1..(emp_currsor%rowcount-v_count) loop
           dbms_output.put(name_array(i)||' ');
         end loop;
         dbms_output.new_line;
        v_count:=emp_cursor%rowcount;
        exit when emp_cursor%notfound;
        end loop;
        close emp_cursor;
      end;

    五、使用游標變量

    PL/SQL的游標變量中存放著指向內存地址的指針.

    1.游標變量使用步驟

    包括定義游標變量,打開游標,提取游標數據,關閉游標等四個階段

    1.1定義ref cursor類型和游標變量

    type ref_type_name is ref cursor [return return_type];
    
    cursor_varibale ref_type_name;

    當指定RETURN子句時,其數據類型必須是記錄類型,不能在包內定義游標變量

    1.2打開游標

    open cursor_variable for select_statement;

    1.3提取游標數據

    fetch cursor_varibale into variable1,variable2,...;
    
    fetch cursor_varibale bulk collect into collect1,collect2,...[limit rows]

    1.4關閉游標變量

    close cursor_varibale;

    2.游標變量使用示例

    1、在定義FEF CURSOR類型時不指定RETURN子句

    在打開游標時可以指定任何的SELECT語句

    declare
        type emp_cursor_type is ref cursor;
        emp_cursor emp_cursor_type;
        emp_record emp%rowtype;
      begin
        open emp_cursor for select * from emp where deptno=10;
        loop
         fetch emp_cursor into emp_record;
         exit when emp_cursor%notfound;
         dbms_output.put_line('第'||emp_curosr%rowcount||'個雇員: '||emp_record.ename);
        end loop;
        close emp_cursor;
      end;

    2、在定義REF CURSOR類型時指定RETURN子句

    在打開游標時SELECT語句的返回結果必須與RETURN子句所指定的記錄類型相匹配.

    declare
        type emp_record_type is record(name varchar2(10),salary number(6,2));
        type emp_cursor_type is ref cursor return emp_record_type;
        emp_cursor emp_cursor_type;
        emp_record emp_record_type;
      begin
        open emp_cursor for select ename,sal from emp where deptno=20;
        loop
         fetch emp_cursor into emp_record;
         exit when emp_cursor%notfound;
         dbms_output.put_line('第'||emp_curosr%rowcount||'個雇員: '||emp_record.ename);
        end loop;
        close emp_cursor;
     end;

    到此這篇關于Oracle中游標Cursor用法的文章就介紹到這了。希望對大家的學習有所幫助,也希望大家多多支持腳本之家。

    相關文章

    最新評論

    美丽人妻被按摩中出中文字幕