set serveroutput on
declare
--定义光标
cursor cemp is select ename,sal from emp;
pename emp.ename%type;
psal emp.sal%type;
begin
open cemp;
loop
fetch cemp into pename,psal;
exit when %notfound
dbms_output.put_line(pename||" "||psal);
end loop;
close cemp;
end ;
set serveroutput on
declare
--定义光标 给哪些员工涨工资
cursor cemp is select empno,empjob from emp;
pempno emp.empno%type;
pjob emp.empjob%type;
begin
open cemp;
loop
fetch cemp into pempno,pjob;
exit when cemp%notfound
if pjob = 'p1' then update emp set sal=sal+1000 where empno = pempno;
elsif pjob= 'p2 ' then update emp set sal=sal+500 where empno = pempno;
else then update emp set sal=sal+100 where empno = pempno;
end if ;
end loop;
close cemp;
commit ;
end ;
alter system set open_cursors = 400 scope = both ;
scope 的取值:both memory spfile
set serveroutput on
declare
cursor cemp(dno number ) is select enmame 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;
end ;