?
六、游标变量
??? 简言之，其一是一个游标，其次则是一个变量，因此称之为游标变量，可以用来存储不同的游标
??? 对于游标变量的使用，在打开游标变量时指定其对应的select语句
??? 1.游标变量的使用步骤
??????? a.定义REF CURSOR 类型和游标变量
??????????? TYPE ref_type_name IS REF CURSOR [RETURN return_type];?? --必须先定义REF CURSOR类型
??????????? cursor_variable ref_type_name;?????????????????????????? --接下来再定义游标变量
??????????
??????????? ref_type_name:?? 指定自定义的类型名
??????????? RETURN:????????? 指定REF CURSOR返回结果的数据类型
??????????? cursor_variable: 定义游标变量的名字
??????????? 注：若指定RETURN子句，其数据类型必须是记录类型，此外，不能在包内定义游标变量
??????
??????? b.打开游标
??????????? 在打开游标时必须指定其对应的select语句，一旦打开游标变量则对应的select结果集将存放到游标变量中
??????????? OPEN cursor_variable FOR select_statement;
??????????
??????? c.提取数据
??????????? 提取数据与普通的显示游标提取数据的方法类似
??????????? FETCH cursor_variable INTO variable1,...variable2 ;?? --提取单行数据，需要配合循环语句来使用
??????????? FETCH cursor_variable BULK COLLECT INTO collect1,collect2,...[LIMIT rows];??? --提取多行数据，collect为集合变量
??????????
??????? d.关闭游标变量
?????????? ?CLOSE cursor_vairable;
??????????
??? 2.游标变量使用的例子
??????? --例.根据部门名称显示该部门的所有雇员(定义REF CURSOR时不指定RETURN子句)
??????????? scott@ORCL> get /u01/bk/scripts/emp_cur12.sql
????????????? 1? DECLARE
????????????? 2????? type emp_cur_type is ref cursor;??? --定义游标类型为ref cursor
????????????? 3????? emp_cur emp_cur_type;?????????????? --定义游标变量为emp_cur
????????????? 4????? emp_record emp%rowtype;???????????? --定义游标变量记录类型为emp_record
????????????? 5????? v_deptno emp.deptno%type;
????????????? 6? BEGIN
????????????? 8????? open emp_cur for select * from emp where deptno=v_deptno;
????????????? 9????? dbms_output.put_line('No??? Name');
???????????? 10????? loop
?????? ??????11????????? fetch emp_cur into emp_record;
???????????? 12????????? exit when emp_cur%notfound;
???????????? 13????????? dbms_output.put_line(emp_cur%rowcount||'??? '||emp_record.ename);
???????????? 14????? end loop;
???????????? 15????? close emp_cur;
???????????? 16* END;
???????????? 17? /
??????????? Enter value for inputno: 10
??????????? old?? 7:???? v_deptno:=&inputno;
??????????? new?? 7:???? v_deptno:=10;
??????????? No??? Name
??????????? 1??? CLARK
??????????? 2??? KING
??????????? 3??? MILLER
?
??????? --例:根据部门名称显示该部门的所有雇员名字及薪水(定义REF CURSOR时指定RETURN子句)
??????????? scott@ORCL> get /u01/bk/scripts/emp_cur13.sql
????????????? 1? DECLARE
????????????? 2????? type emp_record_type is record(name varchar2(10),salary number(6,2));? --定义PL/SQL记录变量类型
????????????? 3????? type emp_cur_type is ref cursor return emp_record_type;? --定义游标类型为ref cursor，且具有返回类型
????????????? 4????? emp_cur emp_cur_type;??????????????????????????????????? --定义游标变量为emp_cur
????????????? 5????? emp_record emp_record_type;????????????????????????????? --定义类型为emp_record_type记录变量emp_record
????????????? 6????? v_deptno emp.deptno%type;
????????????? 7? BEGIN
????????????? 8? ????v_deptno:=&inputno;
????????????? 9????? open emp_cur for select ename,sal from emp where deptno=v_deptno;
???????????? 10????? dbms_output.put_line('Name??? Salary');
???????????? 11????? loop
???????????? 12????????? fetch emp_cur into emp_record;
? ???????????13????????? exit when emp_cur%notfound;
???????????? 14????????? dbms_output.put_line(emp_record.name||'?