ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【笔记】forall and nested table and cursor

【笔记】forall and nested table and cursor

原创 Linux操作系统 作者:yellowlee 时间:2009-03-31 10:04:06 0 删除 编辑

CREATE TYPE Coords AS OBJECT (x NUMBER, y NUMBER);
CREATE TABLE test_grid (num NUMBER, loc Coords);
INSERT INTO test_grid VALUES(10, Coords(1,2));
INSERT INTO test_grid VALUES(20, Coords(3,4));


DECLARE
TYPE CoordsTab IS TABLE OF Coords;
pairs CoordsTab;
BEGIN
SELECT loc BULK COLLECT INTO pairs FROM test_grid;
-- now pairs contains (1,2) and (3,4)
END;

DECLARE
  TYPE NameList IS TABLE OF emp.ename%TYPE;
  TYPE SalList IS TABLE OF emp.sal%TYPE;
  CURSOR c1 IS
    SELECT ename, sal FROM emp WHERE sal > 1000;
  names NameList;
  sals  SalList;
BEGIN
  OPEN c1;
  FETCH c1 BULK COLLECT
    INTO names, sals;
END;

DECLARE
  TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
  dept_recs DeptRecTab;
  CURSOR c1 IS
    SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
  OPEN c1;
  FETCH c1 BULK COLLECT
    INTO dept_recs;
END;


forall和bulk conect 结合

FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE empno = depts(j)
RETURNING empno BULK COLLECT INTO enums;

错误注意:

All targets in a BULK COLLECT INTO clause must be collections, as the
following example shows:

DECLARE
  TYPE NameList IS TABLE OF emp.ename%TYPE;
  names  NameList;
  salary emp.sal%TYPE;
BEGIN
  SELECT ename, sal BULK COLLECT
    INTO names, salary -- illegal target
    FROM emp
   WHERE ROWNUM < 50;
END;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-582299/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-12-27

  • 博文量
    316
  • 访问量
    658297