Oracle第12课.doc

第12章 游标游标的定义1.可以对比java中的ResultSet理解。2.分类：显式游标和隐式游标3．格式：定义?打开?提取（使用，一般与循环结合使用）?关闭二．显式游标语法：Cursor 游标名 is 查询语句;Open游标名;Fetch游标名 into 某个变量;Close游标名;例：declarecursor cur is select sname from student;v_sname student.sname%type;beginopen cur;fetch cur into v_sname;dbms_output.put_line(v_sname);close cur;end;/例：declarecursor cur is select * from student;v_stu student%rowtype;beginopen cur;fetch cur into v_stu;dbms_output.put_line(v_stu.sname||v_stu.sage||v_stu.ssex);close cur;end;/属性类型描述%ISOPENBoolean如果游标打开，则为TRUE %FOUND Boolean 一直为TRUE ，直到最近提取没有取回行记录%NOTFOUNDBoolean如果最近的提取没有返回一条记录，则为TRUE%ROWCOUNTNumber 返回到当前位置为止游标读取的记录行数。 游标与循环联合使用1.loop…end loop;例：查询员工表工资前三名的员工信息declarecursor cur is select * from (select * from employees order by salary desc) where rownum4;v_emp cur%rowtype;beginopen cur;loopfetch cur into v_emp;exit when cur%notfound; ? cur%rowcount3dbms_output.put_line(v_emp.last_name||v_emp.salary);end loop;close cur;end;/2.while;例：declarecursor cur is select * from (select * from employees order by salary desc) where rownum4;v_emp cur%rowtype;beginopen cur;fetch cur into v_emp;while cur%found loop dbms_output.put_line(v_emp.last_name||v_emp.salary); fetch cur into v_emp; 必须得有，否则死循环end loop;close cur;end;/3.for;例：declarecursor cur is select * from (select * from employees order by salary desc) where rownum4;beginfor i in cur loop dbms_output.put_line(i.last_name||i.salary);end loop;end;/隐式游标1. 隐式游标被用于描述执行的SQL命令游标具有属性，可以使用游标属性测试SQL 命令的结果SQL%ROWCOUNT 受SQL影响的行数SQL%FOUND Boolean 值，是否还有数据SQL%NOTFOUND Boolean 值，是否已无数据SQL%ISOPEN 总是为FALSE例：begindelete from temp;dbms_output.put_line(sql%rowcount);end;/例：begin update student set sage=15 where sno=123456; if sql%found then dbms_output.put_line(更新成功); else dbms_output.put_line(更新失败); end if; end; /四．带参数的游标declarecursor cur(v_sex student.ssex%type) is select sname from student where ssex=v_sex;v_sname student.sname%type;beginopen cur(男);fetch cur into v_sname;while cur%found loopdbms_outpu