第12篇 游标.doc

第12章 游标 游标：相当于Java中的ResultSet，可以存放批量的数据。 语法： 定义游标( 打开游标 ( 提取游标中的数据 ( 关闭游标 题： declare cursor cur is select sname from student; 定义游标 v_sname student.sname%type; begin open cur; 打开游标 fetch cur into v_sname; 提取游标中的数据 dbms_output.put_line(v_sname); close cur; 关闭游标 end; / 题： declare cursor c is select * from student; v_stu student%rowtype; begin open c; fetch c into v_stu; dbms_output.put_line(v_stu.sname||v_stu.sage); close c; end; / 利用循环遍历游标 declare cursor c is select * from student; v_stu student%rowtype; begin open c; loop fetch c into v_stu; dbms_output.put_line(v_stu.sname||v_stu.sage); exit when c%notfound; end loop; close c; end; / 题：查询工资最高的前3名员工信息 Loop循环： declare cursor c is select * from (select * from employees order by salary desc) where rownum4; v_emp employees%rowtype; begin open c; loop fetch c into v_emp; exit when c%notfound; dbms_output.put_line(v_emp.first_name); end loop; close c; end; / While循环： declare cursor c is select * from (select * from employees order by salary desc) where rownum4; v_emp employees%rowtype; begin open c; fetch c into v_emp; while c%found loop dbms_output.put_line(v_emp.first_name); fetch c into v_emp; end loop; close c; end; / For循环： declare cursor c is select * from student; begin for i in c loop dbms_output.put_line(i.sname||i.sage); end loop; end; / begin for i in (select * from student) loop dbms_output.put_line(i.sname||i.sage); end loop; end; / 游标的分类 显示游标与隐式游标 显示游标 不带参数的游标：以上写的程序都是不带参数的游标 带参数的游标：定义与使用游标时，可以从外界传入参数 带参数的游标 declare cursor c(v_sex student.ssex%type) is select sname from student where ssex=v_sex; v_sname student.sname%type; begin open c(男); fetch c into v_sname; while c%f