开发者

Oracle中游标Cursor的用法详解

目录
  • 一、使用游标
    • 1.定义游标
    • 2.打开游标
    • 3.提取数据
    • 4.关闭游标
    • 5.游标属性
    • 6.参数游标
  • 二、for循环遍历,实现遍历游标最高效方式。
    • 三、使用游标更新或删除数据
      • 四、通过bulk collect减少loop处理的开销
        • 五、使用游标变量
          • 1.游标变量使用步骤
            • 1.1定义ref cursor类型和游标变量
            • 1.2打开游标
            • 1.3提取游标数据
            • 1.4关闭游标变量
          • 2.游标变量使用示例

          一、使用游标

          对于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)S编程客栈ELECT. . . 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
              www.devze.com for emp_record in (select ename,sal from emp) loop
                  dbms_output.put_line(emp_record.ename);
              end loop;
          end;

          开发者_mssql2008、使用游标更新或删除数据

          要通过游标更新或删除数据,在定义游标时必须要带有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 COLLhttp://www.devze.comECT 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 http://www.devze.comis 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 wh编程客栈en emp_cursor%notfound;
               dbms_output.put_line('第'||emp_curosr%rowcount||'个雇员: '||emp_record.ename);
              end loop;
              close emp_cursor;
           end;

          到此这篇关于Oracle中游标Cursor用法的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持我们。

          0

          上一篇:

          下一篇:

          精彩评论

          暂无评论...
          验证码 换一张
          取 消

          最新数据库

          数据库排行榜