光标

光标使用练习

--给员工涨工资，总裁1000，经理800，其他400declare  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 = 'PRESDENT' 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;  close cemp;  dbms_output.put_line('涨工资完成');end;select * from emprollback/*1.光标属性%found %notfound%isopen 判断光标是否打开%rowcount 影响行数2.光标数限制*/declare  cursor cemp is    select empno, job from emp;  pempno emp.empno%type;  pjob   emp.job%type;begin  open cemp;  if cemp%isopen then    dbms_output.put_line('光标打开');  else    dbms_output.put_line('光标未打开');    close cemp;  end if;end;  --修改光标数的限制--alter system set open cursor=400 scope=both--scope的取值 both memory spfile（数据库重启）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;    dbms_output.put_line('rowcount'||cemp%rowcount);  end loop;  close cemp;end;   --带参数光标--查询某部门员工的姓名declare cursor cemp(dno number) is select ename from emp where deptno=dno;pename emp.ename%type;begin  open cemp(10);  loop    fetch cemp into pename;    exit when cemp%notfound;    dbms_output.put_line(pename);    end loop;close cemp;end;