游标

一、显示游标
1.1、游标属性
%ISOPEN用于确定游标是否打开，如果打开返回TRUE，否则FLASE。
%FOUND用于检查是否从结果集中提取到了数据，如果取到数据返回TRUE，否则FALSE。
%NOTFOUND用于检查是否从结果集中提取到了数据，如果取到数据返回FALSE，否则TRUE。
%ROWCOUNT该属性用于返回当前行为止已经提取到的实际行数。
2、显示使用游标
2、1在显示游标中使用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
          Fetchemp_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;
2.2、在显示游标中，使用FECTH..BULK COLLECT INTO语句提取所有数据
declare type ename_table_type is table of scott.emp.ename%typeindex 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 COLLECTinto 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;
2.3、在显示游标中，使用FECTH..BULK COLLECT INTO...LIMIT语句提取部分数据
declare type ename_table_type is table of scott.emp.ename%typeindex by pls_integer;

ename_table ename_table_type;

cursor emp_cursor is select ename from scott.emp;

v_rows int:=5;
v_count int:=0;
begin
      open emp_cursor;
      loop
          fetch emp_cursor bulk collect into ename_table LIMITv_rows;
          dbms_output.put_line(emp_cursor%rowcount);
          for i in 1..(emp_cursor%rowcount-v_count) loop
              dbms_output.put_line(ename_table(i));
          end loop;
          dbms_output.put_line('----------------');
          v_count:=emp_cursor%rowcount;
          exit when emp_cursor%NOTFOUND;
       end loop;
      close emp_cursor;
end;
2.4、使用游标属性
declare cursor ename_cursor is select ename from scott.emp;
type ename_table_type is table of scott.emp.ename%type index bypls_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('有数据');endif;
       if ename_cursor%notfound then dbms_output.put_line('没有取得数据');endif;
       if ename_cursor%isopen
              then close ename_cursor;
       end if;
       dbms_output.put_line(ename_table.count);
end;
2.5、基于游标定义记录变量
declare cursor emp_cursor is select ename,sal from scott.emp;
type emp_record is record(r_ename varchar(50),r_salnumber(11,0));
type emp_table_type is table of emp_record index bypls_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;

二、参数游标

当定义参数游标时，需要指定参数名及其数据类型。只能指定数据类型而不能指定长度，定义参数游标时，一定要在游标子查询的WHERE子句中引用该参数，否则失去了定义游标参数的意义。
declare cursor emp_cursor(c_deptnonumber) is select * from scott.emp wheredeptno=c_deptno;
type emp_table_type is table of emp_cursor%rowtype index bypls_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;

三、使用游标更新或删除数据

语法：
CURSOR cursor_name(parameter_name datatype)
      IS select_statment FOR UPDATE [OF column_reference][NOWAIT]
FORUPDATE子句用于在游标结果集数据上加行共享锁，OF子句可以确定哪些表要加锁，如果没有OF子句，则会在SELECT语句所应用的全部表上加锁。NOWAIT子句用于指定不等待锁。
为了更新或删除当前游标行数据，必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句

UPDATE table_name SET column=.. WHERE CURRENT OFcursor_name
    DELETEtable_name WHERE CURRENT OF cursor_name

3.1、使用游标更新数据
declare cursor emp_cursor is select ename,sal from scott.empFORUPDATE;
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('无数据');endif;
              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;
3.2、使用游标删除数据
declare cursor emp_cursor is select * from scott.emp FORUPDATE;
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
                 delete scott.emp where CURRENTOF emp_cursor;
                 end if;
       end loop;
       dbms_output.put_line(sql%rowcount);
       close emp_cursor;
end;
3.3、使用OF子句在特定表上加行共享锁
declare cursor emp_cursor is select e.deptno,e.ename,e.sal,d.dnamefrom scott.emp e,scott.dept d where e.deptno=d.deptno FOR UPDATEOFe.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;

3.4、使用NOWAIT子句
declare cursor emp_cursor is select * from scott.emp FOR UPDATENOWAIT;
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;

四、游标FOR循环

4.1、使用游标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;
4.2、在游标FOR循环中直接使用子查询
当不需要游标属性时可以直接使用子查询
begin
       for emp in (select * from scott.emp) loop
       dbms_output.put_line(emp.ename);
       end loop;
end;

五、使用游标变量
定义：
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
ref_type ref_type_name;
打开：
OPEN cursor_varible FOR select_statement;
提取：
FETCH cursor_varible into varible1,varible2....;
FETCH cursor_varible BULK COLLECT into collect1,collect2....[LIMITrows];
关闭：CLOSEcursor_varible;

5.1、在定义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 bypls_integer;
emp_tyoe scott.emp%rowtype;
emp_table emp_table_type;
begin
     open emp_cursor for select * from scott.emp;
     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;
5.2、在定义REF CURSOR类型是指定RETURN子句
declare type emp_record_type is record(name varchar2(50),salarynumber(6,2));

type emp_cursor_type is ref cursor RETURNemp_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;
当定义REF CURSOR类型时指定了RETURN子句，所以游标的查询语句返回的结果必须与记录类型匹配

六、使用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(selecte.ename,e.sal from scott.emp e where d.deptno=e.deptno) fromscott.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;