OracleCursor详解与实例 - 数据库编程

类型变量；

exit when 游标名%notfound；

end loop；

iii、while循环

……

open 游标名

fetch 游标名into临时记录或属性类型变量；

while 游标名%foundloop

-- do something

fetch 游标名into临时记录或属性类型变量；

end loop;

……

close 游标名

5）常见显式Cursor用法：

i、使用for循环来使用cursor：

declare 
  cursor cur is select * from t_user where age = 22;
  userinfo t_user%rowtype;
begin
  for userinfo in cur loop
    exit when cur%notfound;
    dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);
  end loop;
  exception 
    when others then
      dbms_output.put_line(sqlerrm);
end;

ii、使用fetch来使用cursor： exp2

declare 
  cursor cur is select * from t_user where age = 22;
  userinfo t_user%rowtype;
begin
  open cur;
  loop
     exit when cur%notfound;
     fetch cur into userinfo;
     dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);
  end loop;
  exception
     when others then
          dbms_output.put_line(sqlerrm);
 close cur;
end;

iii、使用fetch结合while使用cursor：exp3

declare
  cursor cur is select * from t_user where age = 23;
  userinfo t_user%rowtype;
begin
  open cur;
  fetch cur into userinfo;
  if cur%isopen then
    while cur%found loop
          dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);
          fetch cur into userinfo;
    end loop;
    dbms_output.put_line('totle result : ' || cur%rowcount);
  else
    dbms_output.put_line('cursor is closed!');
  end if;  
  close cur;
  exception
     when others then
          dbms_output.put_line(sqlerrm);
 close cur;
end;

v、使用cursor实现数据的修改（带参数的cursor）、下面三种作用是一样的、只是内部实现有点区别

-- 给工作为CLERK的员工加薪

--one
declare
   cursor cur(c_job varchar2) is select * from emp1 where emp1.job=c_job for update of sal;
   ef emp1%rowtype;
   c_sal emp1.sal%type;
begin
  for ef in cur('CLERK') LOOP
    EXIT WHEN CUR%NOTFOUND;
    IF EF.SAL < 1000 THEN
      C_SAL := EF.SAL*1.2;
    ELSIF EF.SAL < 2000 THEN
      C_SAL := EF.SAL*1.5;
    ELSIF EF.SAL < 3000 THEN
      C_SAL := EF.SAL*2;
    ELSE
      C_SAL := EF.SAL*2.2;
    END IF;
    UPDATE EMP1 SET EMP1.SAL=C_SAL WHERE CURRENT OF CUR;
  END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
     dbms_output.put_line(sqlerrm);
END;
--two
declare
  cursor cur(c_job varchar2) is select * from emp1 where emp1.job=c_job for update of sal;
  EF emp1%rowtype;
  C_SAL emp1.sal%type;
begin
  open cur('CLERK');
  fetch cur into EF;
  while cur%found loop 
    EXIT WHEN CUR%NOTFOUND;
    IF EF.SAL < 1000 THEN
      C_SAL := EF.SAL*1.2;
    ELSIF EF.SAL < 2000 THEN
      C_SAL := EF.SAL*1.5;
    ELSIF EF.SAL < 3000 THEN
      C_SAL := EF.SAL*2;
    ELSE
      C_SAL := EF.SAL*2.2;
    END IF;
    update emp1 set emp1.sal=C_SAL where current of cur;
    fetch cur into EF;
  end loop;
  close cur;
end;  
--three
declare
  --define the cursor Note: the select sql is not excuted!
  cursor cur(c_job varchar2) is select * from emp1 where emp1.job=c_job for update of sal;
  ef emp1%rowtype;
  c_sal emp1.sal%type;
begin
  open cur('CLERK');
  fetch cur into ef;
  
  while cur%found loop
    exit when cur%notfound;
    case
      when ef.sal < 1000 
      then c_sal := ef.sal*1.2;
      when ef.sal < 2000 
      then c_sal := ef.sal*1.4;
      when ef.sal < 3000 
      then c_sal := ef.sal*1.6;
    end case;
    update emp1 set emp1.sal = c_sal where current of cur;
    fetch cur into ef;
  end loop;
  close cur;
end;

vi、使用cursor实现数据的删除：

--use cursor to delect date
create table emp3 as select * from emp;

--delete the date of emp3 where the job is 'CLERK';
declare
  cursor cur(c_job varchar2) is select * from emp3 where emp3.job=c_job for update;
  ef emp3%rowtype;
begin
  for ef in cur('CLERK') loop
    exit when cur%notfound;
    delete from emp3 where current of cur;
  end loop;
end;

补充：

ref cursor会在下一个笔记中出现、这里补充一个循环时使用的判断条件if的东西。注意看下面两段代码：

IF EF.SAL < 1000 THEN
      C_SAL := EF.SAL*1.2;
    ELSIF EF.SAL < 2000 THEN
      C_SAL := EF.SAL*1.5;
    ELSIF EF.SAL < 3000 THEN
      C_SAL := EF.SAL*2;
    ELSE
      C_SAL := EF.SAL*2.2;
    END IF;

IF EF.SAL < 1000 THEN
      C_SAL := EF.SAL*1.2;
    ELSE IF EF.SAL < 2000 THEN
      C_SAL := EF.SAL*1.5;
    ELSE IF EF.SAL < 3000 THEN
      C_SAL := EF.SAL*2;
    ELSE
      C_SAL := EF.SAL*2.2;
    END IF;

当我们使用多个if条件的时候写成后则就会出错、必须要写成前面的elsif来结合if多条件的情况！

OracleCursor详解与实例(二)

补充：