【筆記】forall and nested table and cursor

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/，如需轉載，請註明出處，否則將追究法律責任。