游标

语法

cursor 游标名 [(参数名 数据类型,参数名 数据类型,......)] is select 语句;

光标属性

%isopen　　光标是否打开，打开返回true否则返回false

%rowcount　　影响的行数，即从光标中取出的行数

%found　　光标读取的内容，读取到返回true否则返货false

%notfount　　光标读取不到内容，读取不到返回true否则返回false

游标使用步骤(c1为游标名称)

1. 打开游标：open c1;

2. 取一行游标的值：fetch c1 into pjob;

3. 关闭游标：close c1;

4. 游标结束方式：exit when c1%notfound;

5. pjob必须与表中字段类型一致：定义pjob方式，pjob emp.job%type;

范例

--给员工涨工资，总裁1000、经理800、其他400
declare 
  cursor cemp is select empno,job from emp;
  pempno emp.empno%type;
  pjob emp.job%type;
begin
 open cemp;
 loop
   fetch cemp into pempno,pjob;
   exit when cemp%notfound;
   if pjob = 'PRESIDENT' then
     update emp set sal = sal + 1000 where empno = pempno;
     elsif pjob = 'MANAGER' then
     update emp set sal = sal + 800 where empno = pempno;
     else
       update emp set sal = sal + 400 where empno = pempno;
   end if;
 end loop;
 commit;
 dbms_output.put_line('完成');
end;

 

--带参数的光标
--查询某个部门的员工姓名
declare
cursor cemp(dno number) is select ename from emp where deptno = dno;
pename emp.ename%type;
begin
  open cemp(20);
  loop
    fetch cemp into pename;
    exit when cemp%notfound;
    dbms_output.put_line(pename);
  end loop;
  end;