游标
游标的作用：一般用来遍历结果集。
declare
--定义游标
cursorcis
select*fromemp;
--定义游标类型变量
v_temp c%rowtype;
begin
--打开游标
openc;
--拿出一个值赋值给v_temp
fetchcintov_temp;
dbms_output.put_line(v_temp.ename);
closec;
end;
------------------
declare
cursorcis
select*fromemp;
v_emp c%rowtype;
begin
openc;
loop
fetchcintov_emp;
--c%notfound没有找到记录
exitwhen(c%notfound);
dbms_output.put_line(v_emp.ename);
endloop;
closec;
end;
----------------------
declare
cursorcis
select*fromemp;
v_emp c%rowtype;
begin
openc;
fetchcintov_emp;
while(c%found)loop
dbms_output.put_line(v_emp.ename);
fetchcintov_emp;
endloop;
closec;
end;
-----------------
declare
cursorcis
select*fromemp;
begin
forv_empincloop
dbms_output.put_line(v_emp.ename);
endloop;
end;
游标的作用：一般用来遍历结果集。
带参数的游标
declare
cursorc (v_deptnoemp.deptno%type, v_job emp.job%type)
is
selectename, salfromempwheredeptno = v_deptnoandjob = v_job;
begin
forv_tempinc(30,'CLERK')loop
dbms_output.put_line(v_temp.ename);
endloop;
end;
可更新的游标
declare
cursorc
is
select*fromemp2forupdate;
begin
forv_tempincloop
if(v_temp.sal <2000)then
updateemp2setsal = sal *2wherecurrentofc;
elsif(v_temp.sal =5000)then
deletefromemp2wherecurrentofc;
endif;
endloop;
commit;
end;
示例：
--1、通过使用游标来显示dept表中的部门名称，及其相应的员工列表(提示：定义两个游标，可以使用双重循环)。
declare
num dept.deptno%type:=#--定义num变量
cursor dept_cursor is select dname from dept where deptno=num;
--定义带参数的游标，参数的类型由type前dept.deptno的类型决定
cursor emp_cursor (v_deptno dept.deptno%type)
is
select * from emp where deptno = v_deptno;
begin
for v_temp in dept_cursor loop
dbms_output.put_line('部门名称:'||' '||v_temp.dname);
end loop;
for v_temp in emp_cursor(num) loop
dbms_output.put_line(v_temp.empno||' '||v_temp.ename||' '||v_temp.job||' '
||v_temp.mgr||' '||v_temp.hiredate||' '||v_temp.sal||' '||v_temp.comm||' '||v_temp.deptno);
end loop;
end;
/
--2、emp表中对所有雇员按他们基本薪水的10%给他们加薪，如果所增加后的薪水大于5000，则取消加薪。
declare
cursor emp_cursor
is
select * from emp;
begin
for v_temp in emp_cursor loop
if(v_temp.sal*1.1<5000)then
dbms_output.put_line(v_temp.empno||' '||v_temp.ename||' '||v_temp.job||' '
||v_temp.mgr||' '||v_temp.hiredate||' '||v_temp.sal*1.1||' '||v_temp.comm||' '||v_temp.deptno);
else
dbms_output.put_line(v_temp.empno||' '||v_temp.ename||' '||v_temp.job||' '
||v_temp.mgr||' '||v_temp.hiredate||' '||v_temp.sal||' '||v_temp.comm||' '||v_temp.deptno);
end if;
end loop;
end;
/