课外天地 李树青 → 学习天地 → 数据库系统原理课件 → [推荐]Oracle数据库PL-SQL学习资料之四——游标


  共有20106人关注过本帖树形打印复制链接

主题：[推荐]Oracle数据库PL-SQL学习资料之四——游标
帅哥哟，离线，有人找我吗？
admin
 小大  1楼 博客 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 管理员
等级：管理员 帖子：1945 积分：26736 威望：0 精华：34 注册：2003/12/30 16:34:32 		[推荐]Oracle数据库PL-SQL学习资料之四——游标  发帖心情 Post By：2008/11/21 19:11:57 [只看该作者]

6 游标
6、1 简述
为了处理数据，内存中会分配一块区域，即上下文，游标即是指向上下文区域的句柄或者指针，通过它可以控制上下文和语句处理变化。
set serveroutput on size 1000000;

DECLARE
  v_SID    stu.sid%TYPE;
  v_NAME    stu.name%TYPE;
  v_SEX     stu.sex%TYPE:='M';
  CURSOR c_Students IS
    SELECT sid, name FROM stu WHERE sex= v_SEX;
BEGIN
  OPEN c_Students;
  LOOP
    FETCH c_Students INTO v_SID, v_NAME;
    EXIT WHEN c_Students%NOTFOUND;
    DBMS_OUTPUT.PUT(v_SID);
    DBMS_OUTPUT.PUT(v_NAME);
    DBMS_OUTPUT.PUT_LINE('---------');
  END LOOP;
  CLOSE c_Students;
END;
/

注意退出语句不能放在后面，否则会多打印最后一行
set serveroutput on size 1000000;

DECLARE
  v_SID    stu.sid%TYPE;
  v_NAME    stu.name%TYPE;
  v_SEX     stu.sex%TYPE:='M';
  CURSOR c_Students IS
    SELECT sid, name FROM stu WHERE sex= v_SEX;
BEGIN
  OPEN c_Students;
  LOOP
    FETCH c_Students INTO v_SID, v_NAME;
    DBMS_OUTPUT.PUT(v_SID);
    DBMS_OUTPUT.PUT(v_NAME);
    DBMS_OUTPUT.PUT_LINE('---------');
    EXIT WHEN c_Students%NOTFOUND;
  END LOOP;
  CLOSE c_Students;
END;
/

也可以使用记录来书写
set serveroutput on size 1000000;

DECLARE
  v_STU    stu%ROWTYPE;
  v_SEX    stu.sex%TYPE:='M';
  CURSOR c_Students IS
    SELECT * FROM stu WHERE sex= v_SEX;
BEGIN
  OPEN c_Students;
  LOOP
    FETCH c_Students INTO v_STU;
    EXIT WHEN c_Students%NOTFOUND;
    DBMS_OUTPUT.PUT(v_STU.SID);
    DBMS_OUTPUT.PUT(v_STU.NAME);
    DBMS_OUTPUT.PUT(v_STU.SEX);
    DBMS_OUTPUT.PUT_LINE('---------');
  END LOOP;
  CLOSE c_Students;
END;
/

6、2 游标属性
有四个属性，分别为%FOUND、%NOTFOUND、%ISOPEN、%ROWCOUNT。
set serveroutput on size 1000000;

DECLARE
  v_STU    stu%ROWTYPE;
  CURSOR c_Students IS
    SELECT * FROM stu;
BEGIN
  OPEN c_Students;
  FETCH c_Students INTO v_STU;
  WHILE c_Students%FOUND LOOP
    DBMS_OUTPUT.PUT(c_Students%ROWCOUNT);
    DBMS_OUTPUT.PUT_LINE('---------');
    FETCH c_Students INTO v_STU;
  END LOOP;
  if c_Students%ISOPEN then
    DBMS_OUTPUT.PUT_LINE('ISOPEN');
  end if;
  CLOSE c_Students;
  if not c_Students%ISOPEN then
    DBMS_OUTPUT.PUT_LINE('ISCLOSED');
  end if;
END;
/

注意：ROWCOUNT属性为游标中记录的行号，而非表记录的行号，所以永远都是从1开始递增

6、3 参数化游标
使用参数的游标，如：
set serveroutput on size 1000000;

DECLARE
  v_STU    stu%ROWTYPE;
  p_sex stu.sex%TYPE;
  CURSOR c_Students IS  SELECT * FROM stu where sex=p_sex;
BEGIN
  p_sex:='M';
  OPEN c_Students;
  FETCH c_Students INTO v_STU;
  WHILE c_Students%FOUND LOOP
    DBMS_OUTPUT.PUT(c_Students%ROWCOUNT);
    DBMS_OUTPUT.PUT_LINE('---------');
    FETCH c_Students INTO v_STU;
  END LOOP;
END;
/

参数化游标即含有传入参数的游标，比一般的写法更为简单，如：
set serveroutput on size 1000000;

DECLARE
  v_STU    stu%ROWTYPE;
  CURSOR c_Students(p_sex stu.sex%TYPE) IS  SELECT * FROM stu where sex=p_sex;--声明
BEGIN
  OPEN c_Students('M');--调用
  FETCH c_Students INTO v_STU;
  WHILE c_Students%FOUND LOOP
    DBMS_OUTPUT.PUT(c_Students%ROWCOUNT);
    DBMS_OUTPUT.PUT_LINE('---------');
    FETCH c_Students INTO v_STU;
  END LOOP;
END;
/

6、4 隐式游标
当PL/SQL引擎处理SQL语句（包含insert，update，delete，select into）的时候，隐含的打开隐式SQL游标，处理完后，自动关闭游标。隐式游标没有相应的OPEN、FETCH和CLOSE等语句。但是四个常用属性可以使用，如
begin
  update stu set sex='F' where name='Alice';
  if SQL%NOTFOUND then
    insert into stu values (100,'Alice','F');
   else
    DBMS_OUTPUT.PUT_LINE('---------');
  end if;
end;
/

可以观察如下：select * from stu;

下面的效果和上述程序一样
begin
  update stu set sex='F' where name='Alice';
  if SQL%ROWCOUNT=0 then
    insert into stu values (100,'Alice','F');
  end if;
end;
/

注意SQL%ROWCOUNT和select into一齐使用没有意义，因为如果select into没有匹配到任何数列，它会产生NO_DATA_FOUND异常（其他DML语句不会产生这个异常，而会设置%NOTFOUND和%FOUND），所以不会继续检查SQL%ROWCOUNT语句，如：
set serveroutput on size 1000000;

DECLARE
  v_STU stu%ROWTYPE;
BEGIN
  SELECT *
    INTO v_STU
    FROM stu
    WHERE sid = 199;

  -- The following statement will never be executed, since
  -- control passes immediately to the exception handler.
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('SQL%NOTFOUND is true!');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND raised!');
END;
/

6、5 游标的循环
6、5、1 简单的LOOP循环（注意退出循环的语句紧跟在FECTH后面）
set serveroutput on size 1000000;

DECLARE
  v_STU    stu%ROWTYPE;
  CURSOR c_Students IS
    SELECT * FROM stu;
BEGIN
  OPEN c_Students;
  LOOP
    FETCH c_Students INTO v_STU;
    EXIT WHEN c_Students%NOTFOUND;
    DBMS_OUTPUT.PUT(v_STU.name);
    DBMS_OUTPUT.PUT_LINE('---------');
  END LOOP;
  CLOSE c_Students;
END;
/

6、5、2 WHILE循环
此时需要两句fetch，如：
set serveroutput on size 1000000;

DECLARE
  v_STU    stu%ROWTYPE;
  CURSOR c_Students IS
    SELECT * FROM stu;
BEGIN
  OPEN c_Students;
  FETCH c_Students INTO v_STU;
  WHILE c_Students%FOUND LOOP
    DBMS_OUTPUT.PUT(v_STU.name);
    DBMS_OUTPUT.PUT_LINE('---------');
    FETCH c_Students INTO v_STU;
  END LOOP;
END;
/

6、5、3 FOR循环
注意：
无需声明v_STU，FOR语句隐式声明
无需OPEN游标，FOR语句隐式打开，并且利用%FOUND判断循环
set serveroutput on size 1000000;

DECLARE
  CURSOR c_Students IS
    SELECT * FROM stu;
BEGIN
  FOR v_STU IN c_Students LOOP
    DBMS_OUTPUT.PUT(v_STU.name);
    DBMS_OUTPUT.PUT_LINE('---------');
  END LOOP;
END;
/

6、5、4 隐式FOR循环
游标也被隐式声明，没有名称，如：
set serveroutput on size 1000000;
BEGIN
  FOR v_STU IN (SELECT * FROM stu) LOOP
    DBMS_OUTPUT.PUT(v_STU.name);
    DBMS_OUTPUT.PUT_LINE('---------');
  END LOOP;
END;
/

6、6 可更新的游标
在定义游标时使用for update即可具有对表的更新能力，既可以指定可以更新的字段，也可以不指定字段以默认全部字段可以更新
具体更新的方法是使用update或者delete语句，对当前游标记录进行更新，语法是where current of，如：
set serveroutput on size 1000000;

DECLARE
  CURSOR c_Students IS
    SELECT * FROM stu for update of name;
BEGIN
  FOR v_STU IN c_Students LOOP
    update stu set name =name || 's' where current of c_Students;
    DBMS_OUTPUT.PUT(v_STU.name);
    DBMS_OUTPUT.PUT_LINE('---------');
  END LOOP;
END;
/

可以观察：
select * from stu;

回复数据为：
update stu set name=substr(name,1,length(name)-1);

注意：
1）由于一般游标是不对对表记录进行锁定，所以其他会话可以在该游标处理期间改变数据，但是可更新游标会对表记录进行锁定，所以如果其他会话也在表记录上设置了锁，该会话会无限期的等待下去，可以使用nowait或者wait n（n为秒数）来设置等待情况。

2）可更新游标的循环中不应该使用commit，否则一旦提交会导致释放会话所拥有的锁，由于for update需要锁，所以游标无效。如果不是可更新游标，没有这种问题，但是既便如此，也不应该在游标循环中使用commit。
如果必须要在循环中使用commit提交，可以考虑下面的方式，如：
set serveroutput on size 1000000;

DECLARE
  CURSOR c_Students IS
    SELECT * FROM stu;
BEGIN
  FOR v_STU IN c_Students LOOP
    update stu set name =name || 's' where sid=v_STU.sid;
    DBMS_OUTPUT.PUT(v_STU.name);
    DBMS_OUTPUT.PUT_LINE('---------');
    commit;
  END LOOP;
END;
/

如果没有主键，可以使用rowid伪列，如：
set serveroutput on size 1000000;

DECLARE
  CURSOR c_Students IS
    SELECT rowid,name FROM stu;
BEGIN
  FOR v_STU IN c_Students LOOP
    update stu set name =name || 's' where rowid=v_stu.rowid;
    DBMS_OUTPUT.PUT(v_stu.name);
    DBMS_OUTPUT.PUT_LINE('---------');
    commit;
  END LOOP;
END;
/

6、7 游标变量
类似于游标指针，可以在运行期间动态确定查询语句。

下面的例子需要一个表，创建方法为：
create table stuBak as select * from stu;

相应的程序为：
set serveroutput on size 1000000;

declare
  /* Define the cursor variable type */
  TYPE t_stu IS REF CURSOR;
  p_Table VARCHAR2(10);
  /* and the variable itself. */
  v_CursorVar t_stu;

  /* Variables to hold the output. */
  v_sid  stu.sid%TYPE;
  v_name      stu.name%TYPE;
  v_sex      stu.sex%TYPE;
BEGIN
  p_Table:='stuBak';
  -- Based on the input parameter, open the cursor variable.
  IF p_Table = 'stu' THEN
    OPEN v_CursorVar FOR
      SELECT * FROM stu;
  ELSIF p_table = 'stuBak' THEN
    OPEN v_CursorVar FOR
      SELECT * FROM stuBak;
  ELSE
    /* Wrong value passed as input - raise an error */
    RAISE_APPLICATION_ERROR(-20000,
      'Input must be ''stu'' or ''stuBak''');
  END IF;

  /* Fetch loop.  Note the EXIT WHEN clause after the FETCH. */
  LOOP
    IF p_Table = 'stu' THEN
      FETCH v_CursorVar INTO
        v_sid, v_name,v_sex;
      EXIT WHEN v_CursorVar%NOTFOUND;
      DBMS_OUTPUT.PUT(v_sid);
      DBMS_OUTPUT.PUT(v_name);
      DBMS_OUTPUT.PUT_LINE(v_sex);
      DBMS_OUTPUT.PUT_LINE('---------');
    ELSE
      FETCH v_CursorVar INTO
        v_sid, v_name,v_sex;
      EXIT WHEN v_CursorVar%NOTFOUND;
      DBMS_OUTPUT.PUT(v_sid);
      DBMS_OUTPUT.PUT(v_name);
      DBMS_OUTPUT.PUT_LINE(v_sex);
      DBMS_OUTPUT.PUT_LINE('+++++++');
    END IF;
  END LOOP;

  /* Close the cursor. */
  CLOSE v_CursorVar;

  COMMIT;
END ShowCursorVariable;
/

说明：
1）声明游标变量，如：TYPE t_stu IS REF CURSOR;
此时为无约束游标变量，没有return语句，可以为任何查询打开
也可以准确描述返回类型，此时必须返回特定记录格式的结果，如：
TYPE t_stu IS REF CURSOR RETURN stu%rowtype;

也可以写成：
  TYPE t_record is RECORD
  (
     sid stu.sid%type,
     name stu.name%type,
     sex stu.sex%type
  );
  
  TYPE t_stu IS REF CURSOR RETURN t_record;

也可以写成：
  TYPE t_record is RECORD
  (
     sid stu.sid%type,
     name stu.name%type,
     sex stu.sex%type
  );
  
  v_record t_record;  
  TYPE t_stu IS REF CURSOR RETURN v_record%type;

2）游标变量都是位于服务器端，但是由于游标变量需要运行时分配查询内存，所以相应的被指向的内存区域既可以在客户端分配，也可以在服务器端分配。

[此贴子已经被作者于2010-12-11 20:02:20编辑过]
 回到顶部
RSS2.0 | Xhtml无图版 | Xslt无图版 Copyright ©2002 - 2016 课外天地.Net   Powered By Dvbbs Version 8.3.0
页面执行时间 0.04688 秒, 6 次数据查询