Oracle

游标

1.隐式游标
数据库中定义好的游标，在执行sql语句时自动打开。
%fount 执行语句后有数据返回
%notfount 执行语句后没有数据返回
%rowcount 执行完sql语句后受影响的数据行数
%isopen 是否打开游标 默认false
除rowcount外，其余用于if判断

declare
begin
        update emp set emp.ename='SOPHIE' where emp.empno=7369;
        dbms_output.put_line(SQL%rowcount);
end;
----------------------------------分割
select * from emp;
declare ename emp.ename%type;
begin
       select e.ename into ename from emp e where e.empno=7499;
       --insert into emp values(7999,'LEO','CLERK',7902,to_date('2019-09-01','yyyy-mm-dd'),4000,200,20);
       --dbms_output.put_line(SQL%found);
       --%found只能用于判断
       if SQL%found then
       dbms_output.put_line(ename);
       --dbms_output.put_line(SQL%isopen);
       end if;
end;

2.显式游标(自定义游标)

declare 
cursor myc is select * from emp;
myr emp%rowtype;
begin
       open myc;
       loop
       exit when myc%notfound;
       fetch myc into myr;
       dbms_output.put_line(myr.ename);
       end loop;
end;

declare 
cursor mydno is select * from dept;
mydno2 dept%rowtype;
cursor myen(dno number) is select * from emp where emp.deptno=dno;
myen2 emp%rowtype;
begin
      open mydno;     
      loop
      exit when mydno%notfound;
      fetch mydno into mydno2;
      
             dbms_output.put_line(mydno2.dname);
             dbms_output.put_line(mydno2.deptno);
             
             open myen(mydno2.deptno);
             loop
             exit when myen%notfound;
             fetch myen into myen2;
             dbms_output.put_line(myen2.ename);
             end loop;
             
             close myen;
      end loop;
end;
declare
cursor mydr is select * from dept;
cursor myer(dno number) is select * from emp where emp.deptno=dno;
begin
       for i in mydr loop
       dbms_output.put_line(i.dname);
       for j in myer(i.deptno)loop
       dbms_output.put_line(j.ename);
       end loop;
       end loop;       
end;
select * from dept;
select * from emp;

3.ref 游标(引用类型游标)

declare 
type myref is ref cursor;
myr myref;
myrr emp%rowtype;
eid number;
begin
eid:=&请输入;
    open myr for 'select * from emp where emp.empno=:1'using eid;
    loop
    fetch myr into myrr;
    exit when myr%notfound;
    dbms_output.put_line(myrr.ename);
    end loop;
end;