Oracle中游标示例

后4844

　　----隐式游标
declare
begin
     update emp set sal=sal+10 where empNO=73619;
     if SQL%found then
        dbms_output.put_line('表被更新');
     end if;
     if SQL%isopen then
         dbms_output.put_line('test');
     else
         dbms_output.put_line('隐式游标始终为false');
     end if;
     dbms_output.put_line(SQL%Rowcount);
 end;
　　----SQL%notfound    no_data_found  异常
 declare
   myrow emp%rowtype;
   empDept emp.deptno%type;
 begin
   empDept := '&部门编号';
   select * into myrow from emp where emp.deptno=empDept;
 exception
    when no_data_found then
        dbms_output.put_line('没有找到数据');
    when too_many_rows then
        dbms_output.put_line('返回行过多');
    when others then
        dbms_output.put_line('未知错误');
 end;
 
 
----显式游标
  declare
   myrow emp%rowtype;
   empDept emp.deptno%type;
   cursor mycur is select * from emp where deptno=20;
 begin
       open mycur;
       loop
          fetch mycur into myrow;
          exit when mycur%notfound;
          dbms_output.put_line('员工姓名'||myrow.eName||'员工薪水'|| to_char(myrow.sal+nvl(myrow.comm,0)));
       end loop;
       close mycur;
 exception
    when no_data_found then
        dbms_output.put_line('没有找到数据');
    when too_many_rows then
        dbms_output.put_line('返回行过多');
    when others then
        dbms_output.put_line('未知错误');
 end;
　　----带参数显式游标
  declare
   myrow emp%rowtype;
   empDept emp.deptno%type;
   cursor mycur(deptId emp.deptno%type) is select * from emp where deptno=deptId;
 begin
      empDept := '&部门编号';
      dbms_output.put_line('部门编号'||empDept );
       open mycur(empDept);
       loop
          fetch mycur into myrow;
          exit when mycur%notfound;
          dbms_output.put_line('员工姓名'||myrow.eName||'  员工薪水'|| to_char(myrow.sal+nvl(myrow.comm,0)));
       end loop;
       close mycur;
 exception
    when no_data_found then
        dbms_output.put_line('没有找到数据');
    when too_many_rows then
        dbms_output.put_line('返回行过多');
    when others then
        dbms_output.put_line('未知错误');
 end;
　　----通过显示游标更改数据
  declare
   myrow emp%rowtype;
   empDept emp.deptno%type;
   cursor mycur is select * from emp where deptno=20 for update;
  begin
       open mycur;
       loop
          fetch mycur into myrow;
          exit when mycur%notfound;
          delete from emp where current of mycur;
       end loop;
       close mycur;
 exception
    when no_data_found then
        dbms_output.put_line('没有找到数据');
    when too_many_rows then
        dbms_output.put_line('返回行过多');
    when others then
        dbms_output.put_line('未知错误');
 end;
 
　　----循环游标  自动打开，自动提取, 自动关闭
 declare
   cursor mycur is select emp.empno,emp.ename from emp ;
 begin
    for abc in mycur
    loop
       dbms_output.put_line(abc.empno||'   '||abc.ename);
    end loop;
 end;
 
----参数 循环游标
  declare
   deptID emp.deptno%type;
   cursor mycur(mypara emp.deptno%type) is select emp.empno,emp.ename,emp.deptno from emp where emp.deptno=mypara;
 begin
    deptID := '&部门ID';
    for abc in mycur(deptID)
    loop
       dbms_output.put_line(abc.deptno||'  '||abc.empno||'   '||abc.ename);
    end loop;
 end;
 
 
----ref cursor  强类型 (return 类型)
----弱类型  无返回类型
 declare
     type myCur_type is ref cursor;
     mycur  myCur_type;
     EmpInfo emp%rowtype;
     deptInfo dept%rowtype;
     choice varchar2(100);
     deptId emp.deptno%type;
 begin
   choice :=substr('&输入要查询的简写字母"员工:e,部门:d"',1,1);
   deptID :='&部门部号';
   dbms_output.put_line(choice);
   if (choice ='e') then
      open mycur for 'select * from emp where deptno=:1' using deptId;
   elsif (choice ='d') then
      open mycur for select * from dept;
   end if;
  
 
   case choice
      when 'e' then
         fetch mycur into EmpInfo;
         dbms_output.put_line(EmpInfo.deptNO);
          loop
             exit when mycur%notfound;
             dbms_output.put_line(EmpInfo.empno||'  '|| EmpInfo.eName);
             fetch mycur into EmpInfo;
          end loop;
      when 'd' then
          loop
              fetch mycur into deptInfo;
              exit when mycur%notfound;
             dbms_output.put_line(deptInfo.deptno||'  '||deptInfo.dname);        
          end loop;
   end case;
   close mycur;  
 end;