Oracle游标

最新推荐文章于 2024-07-24 15:35:41 发布

最新推荐文章于 2024-07-24 15:35:41 发布 · 110 阅读

文章标签：

本文深入探讨了SQL游标的概念及其应用，包括静态游标、显示游标、隐式游标等，同时介绍了REF游标的特点与优势。通过示例代码，详细解释了如何使用显示游标进行数据提取、隐式游标自动处理DML语句、REF游标动态查询等技术。此外，还对比了REF游标与普通游标在性能上的提升，特别是对于大量数据操作的优化。

1.游标(cursor):用了查询数据库,获取记录集合(结果集)的指针,可以一次一行的访问指针,在每条结果集中操作。其游标分为:

a.静态游标:分为显式(explicit)游标和隐式(implicit)游标。

显示游标:注:游标关闭后将提取不到数据

定义游标—打开游标—提取数据—关闭游标(Cursor cursor_name is select_statement)。

游标属性

%ISOPEN：是否打开。

%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。

%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。

%ROWCOUNT：当前时刻已经从游标中获取的记录数量。

--在显示游标中使用FETCH...INTO语句

declare cursor emp_cursor is select ename,sal from scott.emp ;
v_ename scott.emp.ename%type;
v_sal scott.emp.sal%type;
begin
      open emp_cursor;
      loop
           Fetch emp_cursor into v_ename,v_sal;
           exit when emp_cursor%notfound;
           dbms_output.put_line(v_ename||'    '||v_sal);
      end loop;
      close emp_cursor;
end;

--在显示游标中，使用FECTH..BULK COLLECT INTO语句提取所有数据(使用批量处理时在赋值的时候是不需要初始化集合的大小(嵌套表,变长数组))

declare type ename_table_type is table of scott.emp.ename%type index by pls_integer;

ename_table ename_table_type;

cursor emp_cursor is select ename from scott.emp;
begin
       open emp_cursor;
       fetch emp_cursor bulk collect into ename_table;
       dbms_output.put_line(emp_cursor%rowcount);
       for i in 1..ename_table.count loop
           dbms_output.put_line(ename_table(i));
       end loop;
       close emp_cursor;
end;

--使用游标FOR循环

declare cursor emp_cursor is select * from scott.emp;
begin
        for emp in emp_cursor loop
            dbms_output.put_line(emp_cursor%rowcount||emp.ename);
        end loop;
end;

--在游标FOR循环中直接使用子查询

begin
        for emp in (select * from scott.emp) loop
        dbms_output.put_line(emp.ename);
        end loop;
end;

--参数游标

declare cursor emp_cursor(c_deptno number) is select * from scott.emp where deptno=c_deptno;
type emp_table_type is table of emp_cursor%rowtype index by pls_integer;
emp_table emp_table_type;
begin
     open emp_cursor(&deptno);
     fetch emp_cursor bulk collect into emp_table;
     dbms_output.put_line(emp_table.count);
     close emp_cursor;
end;

--使用游标属性(游标的当前状态,当游标指针读取超过最后一行时，游标还是取缓冲区的值，也就是最后一行的数据,但是属性都是当前值)

declare cursor ename_cursor is select ename from scott.emp;
type ename_table_type is table of scott.emp.ename%type index by pls_integer;
ename_table ename_table_type;

begin
        if not ename_cursor%isopen 
               then open ename_cursor;
        end if;
        fetch  ename_cursor into ename_table(1); 

        if ename_cursor%found then dbms_output.put_line('有数据');end if;
        if ename_cursor%notfound then dbms_output.put_line('没有取得数据');end if;
        
        if ename_cursor%isopen 
               then close ename_cursor;
        end if;
        dbms_output.put_line(ename_table.count);
end;

--基于游标定义记录变量(ROWTYPE,RECORD)

declare cursor emp_cursor is select ename,sal from scott.emp;
type emp_record is record(r_ename varchar(50),r_sal number(11,0));
type emp_table_type is table of emp_record index by pls_integer;
emp_table emp_table_type;
begin
open emp_cursor;
     fetch emp_cursor bulk collect into emp_table;
     dbms_output.put_line(emp_table.count);
     close emp_cursor;
end;

begin
     for emp_record in (select * from scott.emp) loop
     dbms_output.put_line(emp_record.ename|| '   '||emp_record.sal);
     end loop;
end;

--使用游标更新数据

declare cursor emp_cursor is select ename,sal from scott.emp for update;
c_emp emp_cursor%rowtype;
begin
        open emp_cursor;
        loop

               fetch emp_cursor into c_emp;
               exit when emp_cursor%notfound;
               if emp_cursor%found then dbms_output.put_line('有数据');end if;
               if emp_cursor%notfound then dbms_output.put_line('无数据');end if;
               if c_emp.sal<2000 then 
                  update scott.emp set sal=sal+100 where current of emp_cursor;
                  end if;
             
        end loop;
        close emp_cursor;
end;

备注:如果不加where current of secondcursor会修改或删除所有行

--使用OF子句在特定表上加行共享锁

declare cursor emp_cursor is select e.deptno,e.ename,e.sal,d.dname from scott.emp e,scott.dept d where e.deptno=d.deptno for update of e.deptno(锁住了这个列的这一行被锁住了);
c_emp emp_cursor%rowtype;
begin
        open emp_cursor;
        loop

               fetch emp_cursor into c_emp;
               exit when emp_cursor%notfound;
               if c_emp.deptno=10 then 
                  update scott.emp set sal=sal+100 where current of emp_cursor;
                  
                  end if;
             dbms_output.put_line('雇员名：'||c_emp.ename||'  工资：'||c_emp.sal||'  部门：'||c_emp.dname);
        end loop;
        dbms_output.put_line(sql%rowcount);
        close emp_cursor;
end;

--使用NOWAIT子句

declare cursor emp_cursor is select * from scott.emp for update nowait(wait 5);
c_emp emp_cursor%rowtype;
begin
        open emp_cursor;
        loop

               fetch emp_cursor into c_emp;
               exit when emp_cursor%notfound;
                if c_emp.sal<2000 then 
                  update scott.emp set sal=sal+100 where current of emp_cursor;
                  end if;
             
        end loop;
        dbms_output.put_line(sql%rowcount);
        close emp_cursor;
end;

隐式游标:对任何的DML语句都有,默认的名字为sql。

begin
   update testcursor set testid = testid + '001' where testid='10001';
   if sql%found then
   dbms_output.put_line('表已经更新');
    elsif sql%notfound then
    dbms_output.put_line('没有找到数据');
    end if;
   end;

显式和隐式游标的区别：

尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。

b.REF游标:也称之为游标变量,一种引用类型,类似于指针。

--在定义REF CURSOR类型是不指定RETURN子句

declare type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
type emp_table_type is table of scott.emp%rowtype index by pls_integer;
emp_tyoe scott.emp%rowtype;
emp_table emp_table_type;
begin
      open emp_cursor for select * from scott.emp;(可以open多次)
      loop
           fetch emp_cursor into emp_tyoe;
           dbms_output.put_line(emp_tyoe.ename);
           exit when emp_cursor%notfound;
      end loop;
      close emp_cursor;
end;

--在定义REF CURSOR类型是指定RETURN子句(一般好)

declare type emp_record_type is record(name varchar2(50),salary number(6,2));

type emp_cursor_type is ref cursor return emp_record_type;
emp_record emp_record_type;
emp_cursor emp_cursor_type;
begin
           open emp_cursor for select ename,sal from scott.emp;
           loop
                fetch emp_cursor into emp_record;
                dbms_output.put_line(emp_record.name||'   '||emp_record.salary);
                exit when emp_cursor%notfound;
           end loop;
           close emp_cursor;
end;

--使用CURSOR表达式

declare type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
cursor dept_cursor(c_deptno number) is select d.dname,cursor(select e.ename,e.sal from scott.emp e where d.deptno=e.deptno) from scott.dept d where d.deptno=c_deptno;
type myemp_record_type is record(r_ename  varchar2(50),r_sal number(11));
myemp_record myemp_record_type;
v_dname varchar2(50);
begin
     open dept_cursor(&no);
     loop
          fetch dept_cursor into v_dname,emp_cursor;
          exit when dept_cursor%notfound;
          dbms_output.put_line('部门名：'||v_dname);
          loop
               fetch emp_cursor into myemp_record;
               exit when emp_cursor%notfound;
               dbms_output.put_line(' 姓名：'||myemp_record.r_ename||' 工资：'||myemp_record.r_sal);
          end loop;
     end loop;
     close dept_cursor;
     
end;

REF动态游标，在运行的时候才能确定游标使用的查询。可以分为：

createorreplaceprocedureTESTis sqlstrvarchar2(500);
typeRefCurisrefcursor; c1 refcur; begin sqlstr :='select * from tab';
openc1forsqlstr; closec1; end;

用REF CURSOR实现BULK功能

1.可以加速INSERT, UPDATE, DELETE语句的执行，也就是用FORALL语句来替代循环语句。

2.加速SELECT，用BULK COLLECT INTO来替代INTO。

SQL> create table tab2 as select empno ID, ename NAME, sal SALARY from emp where 1=2;

create or replace procedure REF_BULK is

type empcurtyp is ref cursor;

type idlist is table of emp.empno%type;

type namelist is table of emp.ename%type;

type sallist is table of emp.sal%type;

emp_cv empcurtyp;

ids idlist;

names namelist;

sals sallist;

row_cnt number;

begin

open emp_cv for select empno, ename, sal from emp;

fetch emp_cv BULK COLLECTINTO ids, names, sals;

--将字段成批放入变量中，此时变量是一个集合

close emp_cv;

for i in ids.first .. ids.last loop

dbms_output.put_line(' || ids(i) || ' || names(i) ||' salary=' || sals(i));

end loop;

FORALL i IN ids.first .. ids.last

insert into tab2 values (ids(i), names(i), sals(i));

commit;

select count(*) into row_cnt from tab2;

dbms_output.put_line('-----------------------------------');

dbms_output.put_line('The row number of tab2 is ' || row_cnt);

end REF_BULK;

3、cursor和ref cursor的区别

从技术底层看，两者是相同的。普通plsql cursor在定义时是“静态”的。而

Ref cursors可以动态打开。

例如下面例子：

Declare

type rc is ref cursor;

cursor c is select * from dual;

l_cursor rc;

begin

if ( to_char(sysdate,'dd') = 30 ) then

open l_cursor for 'select * from emp';

elsif ( to_char(sysdate,'dd') = 29 ) then

open l_cursor for select * from dept;

else

open l_cursor for select * from dual;

end if;

open c;

end;

rc根据逻辑动态打开；而游标c定义好了只有就无法修改了。

ref cursor可以返回给客户端，cursor则不行。

cursor可以是全局的global，ref cursor则必须定义在过程或函数中。

ref cursor可以在子程序间传递，cursor则不行。

cursor中定义的静态sql比ref cursor效率高，所以ref cursor通常用在：向客户端返回结果集。

函数和过程中返回游标

create or replace function selectallDept
return sys_refcursor
as
type ref_cur_type is ref cursor return scott.dept%rowtype;
ref_cur ref_cur_type;
begin
open ref_cur for select * from scott.dept;
return ref_cur;
end;

declare
type ref_cur_type is ref cursor return scott.dept%rowtype;
ref_cur ref_cur_type;

type dept_table_type is table of scott.dept%rowtype
index by binary_integer;
dept_table dept_table_type;
coun number := 1;
begin
ref_cur:=selectalldept();
for i in 1..100 loop
fetch ref_cur into dept_table(i);
end loop;
end;

create or replace procedure emp_dept
2 (pi_deptno IN emp.deptno%TYPE,
3 po_result OUT SYS_REFCURSOR)
4 IS
5 begin
6 OPEN po_result FOR
7 SELECT * FROM emp WHERE deptno=pi_deptno;
8 end ;