【安博培训技术】Oracle6 游标管理20130911

Oracle6 游标管理


回顾
PL/SQL 是一种可移植的高性能事务处理语言 
PL/SQL 引擎驻留在 Oracle 服务器中
PL/SQL 块由声明部分、可执行部分和异常处理部分组成 
PL/SQL 支持的数据类型包括标量数据类型、LOB 数据类型和属性类型 
控制结构包括条件控制、循环控制和顺序控制
运行时出现的错误叫做异常
异常可以分为预定义异常和用户定义的异常


目标
掌握并使用游标管理技巧 
隐式游标
显式游标
Ref游标


游标是什么：
当sql执行查询时和dml时，oracle会为其分配上下文区域，游标是指向上下文区域的指针
为了处理sql语返回多行数据，开发人员可以使用游标


游标简介 2-1
[思路]从一个问题入手：给全班同学随机排考号，根据t_student表形成考试座次表t_examorder(f_stuid,f_order)
如何完成这样一个PL/SQL程序？我们需要这样的一个功能：逐条处理查询结果集的每条记录
初步认识什么是游标，然后逐个说明每一种游标
[说明]此处简单讲述内置包dbms_random的用法


游标简介 2-2
逐行处理查询结果，以编程的方式访问数据
游标的类型：
逐行处理查询结果，以编程的方式访问数据
游标的类型：游标类型
隐式游标 显式游标 REF游标
REF游标用于处理运行时才能确定的动态SQL查询的结果
[说明]游标也可分为两类：静态游标（隐式、显式）与REF游标（动态游标）


隐式游标 4-1
在PL/SQL中使用DML语句时自动创建隐式游标
隐式游标自动声明、打开和关闭，其名为 SQL
通过检查隐式游标的属性可以获得最近执行的DML 语句的信息
隐式游标的属性有：
%FOUND – SQL 语句影响了一行或多行时为 TRUE
%NOTFOUND – SQL 语句没有影响任何行时为TRUE
%ROWCOUNT – SQL 语句影响的行数
%ISOPEN - 游标是否打开，始终为FALSE
[说明]隐式游标：只能使用，不能定义和控制


隐式游标 4-2
BEGIN
UPDATE emp5 SET ename = '张三丰'
WHERE empno= '7369';
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('表已更新');
    dbms_output.put_line(sql%rowcount);
END IF;
     END;
只有在 DML 语句影响一行或多行时，才返回 True
[例]将编号为001的学生姓名改为'张三丰'


隐式游标 4-3
DECLARE
  v_id emp5.empno%type := '&id';
  v_name emp5.ename%Type := '&name';
     BEGIN
  UPDATE emp5 SET ename = v_name
  WHERE empno = v_id;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('编号未找到');
  ELSE
DBMS_OUTPUT.PUT_LINE('表已更新');
END IF;
     END;
如果 DML 语句不影响任何行，则返回 True 
[例]根据用户输入修改相应学生姓名


隐式游标 4-4
SQL> SET SERVEROUTPUT ON 
SQL> begin
  update t_student set f_department='CHE' where f_class=2;
  if SQL%Found then
    dbms_output.put_line('修改记录数为：'||SQL%RowCount);
  else
    dbms_output.put_line('未找到相应记录');
  end if;
end;
/
返回 DML 语句影响的行数
[演示]SQL%Found、SQL%RowCount的用法：将学生表中2班的同学系别改为化学系（‘CHE’），并显示修改记录个数
begin
  update t_student set f_department='CHE' where f_class=2;
  if SQL%Found then
    dbms_output.put_line('修改记录数为：'||SQL%RowCount);
  else
    dbms_output.put_line('未找到相应记录');
  end if;
end;
/


SELECT INTO 语句 2-1
SQL> SET SERVEROUTPUT ON
SQL> DECLARE 
v_id t_course.f_id%type;
v_name t_course.f_name%type;
     BEGIN
v_id:= '&id';
SELECT f_name INTO v_name 
FROM t_course WHERE f_id=v_id;
     EXCEPTION
WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('课程未找到');
     END;
     /
如果没有与SELECT INTO语句中的条件匹配的行，将引发NO_DATA_FOUND异常
[例]根据输入系别显示学生姓名
declare
  name t_student.f_name%type;
  department t_student.f_department%type;
begin
  department:='&department';
  select f_name into name from t_student where f_department=department;
  dbms_output.put_line('学生姓名：'||name); 
exception
  when no_data_found then
    dbms_output.put_line('未找到相关记录');
  when too_many_rows then
    dbms_output.put_line('该查询提取多行');
end;
/


SELECT INTO 语句 2-2
SQL> SET SERVEROUTPUT ON
SQL> DECLARE 
v_birth t_student.f_birth%type;
     BEGIN
SELECT f_birth INTO v_birth FROM t_student;
              DBMS_OUTPUT.PUT_LINE(v_birth);
     EXCEPTION
WHEN TOO_MANY_ROWS THEN
 DBMS_OUTPUT.PUT_LINE('该查询提取多行');
     END;
     /
如果 SELECT INTO 语句返回多个值，将引发TOO_MANY_ROWS异常


显式游标 2-1
显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行
显式游标的操作过程： 
打开游标 提取行(变量) 关闭游标 
[思路]隐式游标功能有限，显式游标可提供更为强大的功能


显示游标的属性
curosrname%isopen     用于确定游标打开，返回boolean
curosrname %FOUND 用于确定是否从结果集中提取到了数据，返回boolean
curosrname %NOTFOUND
curosrname %ROWCOUNT 返回行数


显式游标 2-2
declare
   v_emp emp%rowtype;
   cursor cursor_emp is select * from emp; --声明游标--
begin
   open cursor_emp;--打开游标--
       loop 
         fetch cursor_emp into v_emp;--提取行--
        exit when cursor_emp%notfound;
         dbms_output.put_line(v_emp.ename);
       end loop; 
   close cursor_emp;--关闭游标--
end;
/
先看书上的语法：定义游标、打开游标、获取记录、关闭游标（说明：获取记录后游标自动下移一个）
再看例子，如何使用
做以下例子，练习显式游标的使用
[例]显示系别为’CS’的学生姓名
set serveroutput on
declare
  name t_student.f_name%type;
  Cursor cur_stu Is select f_name from t_student where f_department='CS';
begin
  Open cur_stu;
  Loop
    Fetch cur_stu Into name;
    Exit When cur_stu%NotFound;
    dbms_output.put_line('学生姓名：'||name); 
  End Loop;
  Close cur_stu;
end;
/


使用批量fetch
declare 
cursor emp_cursor is select ename from emp where deptno=10;
  type ename_table is table of varchar2(10);
  v_ename ename_table;
begin
  open emp_cursor;
      fetch emp_cursor bulk collect into v_ename;
        for i in 1..v_ename.count loop
        dbms_output.put_line(v_ename(i));
       end loop;
  close emp_cursor; 
end;


带参数的显式游标
declare
  v_emp emp%rowtype;
  cursor emp_cur(v_deptno emp.deptno%type,v_empno emp.empno%type)
        is select * from emp where deptno=v_deptno and v_empno=v_empno;
begin
  open emp_cur(&deptno,&empno);
       loop
         fetch emp_cur into v_emp;
         exit when emp_cur%notfound;
         dbms_output.put_line(v_emp.ename);
         dbms_output.put_line(v_emp.sal);       
       end loop;
  close emp_cur;
end;
[注]该处参数未使用，我的例子也是如此，修改下
[例]根据输入系别显示学生姓名、出生日期
set serveroutput on
declare
  name t_student.f_name%type;
  birth t_student.f_birth%type;
  department t_student.f_department%type;
  Cursor cur_stu Is select f_name,f_birth from t_student where f_department= department;
begin
  department:='&department';
  Open cur_stu;
  Loop
    Fetch cur_stu Into name,birth;
    Exit When cur_stu%NotFound;
    dbms_output.put_line('学生姓名：'||name||'  出生日期：'||birth); 
  End Loop;
  Close cur_stu;
end;
/


使用显式游标更新行 2-1
允许使用游标删除或更新活动集中的行
声明游标时必须使用 SELECT … FOR UPDATE语句

    CURSOR <cursor_name> IS
 SELECT statement FOR UPDATE;


UPDATE <table_name>
SET <set_clause>
WHERE CURRENT OF <cursor_name>


DELETE FROM <table_name>
WHERE CURRENT OF <cursor_name>
[说明]对于写操作，也可能出现逐条修改的情况
使用时仅需在条件中加上where Current Of cursor_name（修改当前行）


给工资低于2000元的员工加薪100
declare
    cursor emp_cur is select ename,sal from emp2 for update;
    v_ename emp.ename%type;
    v_sal emp.sal%type;
begin
    open emp_cur;
         loop
           fetch emp_cur into v_ename,v_sal;
           exit when emp_cur%notfound;
           if v_sal<2000 then 
             update emp2 set sal=sal+100 where current of emp_cur;
           end if;
         end loop;
    close emp_cur;
end;
[说明]在直接使用DML语句可以实现的情况下，减少对游标的使用，效率比较低


循环游标 2-1
循环游标用于简化游标处理代码
当用户需要从游标中提取所有记录时使用
循环游标的语法如下：
FOR <record_index> IN <cursor_name>
LOOP
<executable statements>
END LOOP;
[思路]也许我们感到上面循环使用游标的方式不够方便，可使用循环游标
[说明]注意循环游标必须提取游标中的所有记录


循环游标 2-2
SQL> SET SERVER OUTPUT ON
SQL> declare
  stu t_student%RowType;
  Cursor cur_stu Is select f_id,f_name,f_birth from t_student where f_department='CS';
begin
  For stu In cur_stu
  Loop
    dbms_output.put_line('学号：'||stu.f_id||'  学生姓名：'||stu.f_name||'  出生日期：'||stu.f_birth); 
  End Loop;
end;
/
[例]显示系别为’CS’的学生信息（学号、姓名、出生日期）
set serveroutput on
declare
  stu t_student%RowType;
  Cursor cur_stu Is select f_id,f_name,f_birth from t_student where f_department='CS';
begin
  For stu In cur_stu
  Loop
    dbms_output.put_line('学号：'||stu.f_id||'  学生姓名：'||stu.f_name||'  出生日期：'||stu.f_birth); 
  End Loop;
end;
/


REF 游标和游标变量 3-1
REF 游标和游标变量用于处理运行时动态执行的 SQL 查询
创建游标变量需要两个步骤：
声明 REF 游标类型
声明 REF 游标类型的变量
用于声明 REF 游标类型的语法为：


TYPE <ref_cursor_name> IS REF CURSOR
[RETURN <return_type>];
[思路]定义游标所需的查询语句在定义时无法确定，先声名一个REF游标类型，然后声名相应的游标变量，
在程序体中，打开游标时动态指定相应查询语句
[说明]使用动态游标必须分为两个步骤：声明ref游标类型、声明ref游标类型的变量


REF 游标和游标变量 3-2
打开游标变量的语法如下：
OPEN cursor_name FOR select_statement;
声明强类型的 REF 游标
TYPE my_curtype IS REF CURSOR
  RETURN stud_det%ROWTYPE;
  order_cur my_curtype; 
声明弱类型的 REF 游标
TYPE my_ctype IS REF CURSOR;
stud_cur my_ctype;
[说明]包含Return的为强类型REF游标，反之为弱类型REF游标


REF 游标和游标变量 3-3
SQL> DECLARE 
  TYPE toys_curtype IS REF CURSOR
          RETURN toys%ROWTYPE;--声明REF游标类型--
  toys_curvar toys_curtype;--声明游标变量--
  toys_rec toys%ROWTYPE;
     BEGIN
  OPEN toys_curvar FOR
   SELECT * FROM toys;
  FETCH toys_curvar INTO toys_rec;
       ...
  CLOSE toys_curvar;
     END;
[例]根据用户输入，查询并显示学生或课程信息（’S’：学生信息，’C’：课程信息）
set serveroutput on
Accept tab Prompt '您想查看什么信息?学生信息(S)或课程信息(C)：';
declare
  id varchar2(10);
  name varchar2(50);
  Type cur_rec Is Ref Cursor;
  cur_info cur_rec;
  selection char(1) := Upper(SubStr('&tab',1,1));
begin
  if (selection = 'S') then
    Open cur_info For select f_id,f_name from t_student;
    dbms_output.put_line('===学生信息===');
  elsif selection = 'C' then
    Open cur_info For select f_id,f_name from t_course;
    dbms_output.put_line('===课程信息===');
  else
    dbms_output.put_line('请正确输入信息');
    return;
  end if;
  Fetch cur_info Into id,name;
  while cur_info%Found loop
    dbms_output.put_line('#'||id||'：  '||name);
    Fetch cur_info Into id,name;
  end loop;
  Close cur_info;
end;
/


游标变量的优点和限制
游标变量的功能强大，可以简化数据处理。
游标变量的优点有：
可从不同的 SELECT 语句中提取结果集
可以作为过程的参数进行传递
可以引用游标的所有属性
可以进行赋值运算
使用游标变量的限制：
不能在程序包中声明游标变量
FOR UPDATE子句不能与游标变量一起使用
不能使用比较运算符


使用游标变量执行动态 SQL
DECLARE
  r_emp emp%ROWTYPE;
  TYPE c_type IS REF CURSOR;
  cur c_type;
  p_salary NUMBER;
BEGIN
  p_salary := 2500;
  OPEN cur FOR 'select * from emp where sal>:1
                order by sal desc'
  USING p_salary;
  DBMS_OUTPUT.PUT_LINE('薪水大于'|| p_salary ||'的员工有：');
  LOOP
    FETCH cur INTO r_emp;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('编号：'|| r_emp.empno
      || ' 姓名：' || r_emp.ename|| ' 薪水：' || r_emp.sal );
  END LOOP;
  CLOSE cur; 
END;


总结
游标用于处理查询结果集中的数据
游标类型有：隐式游标、显式游标和 REF 游标
隐式游标由 PL/SQL 自动定义、打开和关闭
显式游标用于处理返回多行的查询
显式游标可以删除和更新活动集中的行
要处理结果集中所有记录时，可使用循环游标
在声明 REF 游标时，不需要将 SELECT 语句与 其关联