演示隐式游标,系统自动声明,自动打开,自动使用并且自动关闭

begin
     update emp set sal = 1000;
     dbms_output.put_line('影响的行数:' || sql%rowcount);
end;
 
rollback;
/*游标的使用方法:
第一步:声明游标
第二步:打开游标
第三步:使用游标进行循环操作
第四步:关闭游标*/

普通游标,游标本身就是一个变量

declare     
     --下面的这行代码声明了一个游标
     cursor mycur is select * from emp where deptno = 20;
     emprow emp%rowtype;
begin
     open mycur; --打开游标
     loop
         fetch mycur into emprow; --把游标所指的纪录放到变量中
         exit  when (mycur%notfound); --当游标没有指向行时退出循环
         dbms_output.put_line('名字:' || emprow.ename || '薪水:' || emprow.sal);
     end loop;
     close mycur;  --关闭游标
end;

简单游标,列操作

declare
       empname emp.ename%type;
       empsal emp.sal%type;
       cursor mycur is select ename,sal from emp where deptno = 30;
begin
     open mycur;
     loop
         fetch mycur into empname,empsal;
         exit when mycur%notfound;
         dbms_output.put_line('姓名:' || empname || '工资' || empsal);
     end loop;
end;

简单游标,列操作

declare
  cursor c 
  is
  select * from dept;
  vDept_row_record c%rowtype;
begin
  open c;
  fetch c into vDept_row_record;
  dbms_output.put_line(vDept_row_record.dname);
  close c;
end;

when循环游标

declare
  cursor c 
  is
  select * from dept;
  vDept_row_record c%rowtype;
begin
  open c;
  loop
       fetch c into vDept_row_record;
       exit when(c%notfound);
       dbms_output.put_line(vDept_row_record.dname);
  end loop;
  close c;
end;

while循环游标

declare
  cursor c
  is
  select * from dept;
  vDept_row_record c%rowtype;
begin
  open c;
  fetch c into vDept_row_record;
  while (c%found) loop
    dbms_output.put_line(vDept_row_record.dname);
    fetch c into vDept_row_record;
  end loop;
  close c;
end;

for循环游标

declare
  cursor c
  is
  select * from dept;
  vDept_row_record c%rowtype;
begin
  for vDept_row_record in c loop
    dbms_output.put_line(vDept_row_record.dname);
  end loop;
end;

带参游标

declare
  cursor c(sSal emp.sal%type, sEmpno emp.empno%type)
  is
  select * from emp where sal >= sSal and empno > sEmpno;
begin
  for record_data in c(2500, 6666) loop
      dbms_output.put_line(record_data.ename);
  end loop;
end;

update游标

declare
  cursor c(sSal emp2.sal%type)
  is
  select * from emp2 where sal >= sSal for update;
begin
  for record_data in c(2500) loop
    if (record_data.sal < 3000) then
      update emp2 set sal = sal + 3 where current of c;
      dbms_output.put_line(record_data.ename);
    elsif (record_data.sal = 5000) then
      update emp2 set sal = sal - 3 where current of c;
      dbms_output.put_line(record_data.ename);
    end if;
  end loop;
end;
引用游标不能使用循环游标的语法
引用游标不能进行删除和修改
引用游标是一个数据类型,使用该类型必须声明变量

弱类型引用游标,就是不指定游标将要提取的数据行的类型

declare
       type my_cur_type is ref cursor;
       mycur my_cur_type;--声明变量
       which varchar2(10);
       deptrow dept%rowtype;
       emprow emp%rowtype;
begin
     which := '&请选择dept还是emp';
     if (which = 'dept') then
        open mycur for select * from dept;
        loop
            fetch mycur into deptrow;
            exit when (mycur%notfound);
            dbms_output.put_line(deptrow.deptno || '  ' || deptrow.dname);
        end loop;
     elsif (which = 'emp') then
        open mycur for select * from emp;
        loop
            fetch mycur into emprow;
            exit when (mycur%notfound);
            dbms_output.put_line(emprow.empno || '  ' || emprow.ename);
        end loop;
     end if;
     close mycur;
end;

强类型引用游标,就是指定游标将要提取的数据行的类型 ,只能是record或%rowtype类型

比如:return number是错的,return emp.ename%type也是错的

declare
       type mycurtype is ref cursor return emp%rowtype;
       mycur mycurtype;--声明变量
       emprow emp%rowtype;
begin
        open mycur for select * from emp;
        loop
            fetch mycur into emprow;
            exit when mycur%notfound;
            dbms_output.put_line(emprow.empno || '  ' || emprow.ename);
        end loop;
        close mycur;
end;
最后修改:2022 年 01 月 18 日
如果觉得我的文章对你有用,请随意赞赏