nested.sql

Oracle PLSQL for DBAs 源代码

CREATE TABLE main_table
(col1 NUMBER);
CREATE TABLE or_table_one
(col11 NUMBER);
CREATE TABLE or_table_two
(col21 NUMBER);
CREATE TABLE or_table_three
(col31 NUMBER);

CREATE OR REPLACE TYPE number_t AS TABLE OF NUMBER
/

CREATE OR REPLACE FUNCTION nested RETURN number_t AS

  /*
    ||
    || Demonstration of nested cursors. Initial query includes 3
    || nested cursors that are executed in succession until an
    || associated record is found.
    ||
  */

  -- get a main table record
  CURSOR curs_get_mt IS
  SELECT mt.col1,
         CURSOR ( SELECT 1
                    FROM or_table_one
                   WHERE col11 = mt.col1 ),
         CURSOR ( SELECT 1
                    FROM or_table_two
                   WHERE col21 = mt.col1 ),
         CURSOR ( SELECT 1
                    FROM or_table_three
                   WHERE col31 = mt.col1 )
    FROM main_table mt
   WHERE col1 = 1;

  v_col1 NUMBER;
  cursor_one SYS_REFCURSOR;
  cursor_two SYS_REFCURSOR;
  cursor_three SYS_REFCURSOR;
  v_dummy NUMBER;
  v_ret_val number_t := number_t( );

BEGIN

  -- get the main record
  OPEN curs_get_mt;
  FETCH curs_get_mt INTO v_col1, cursor_one,cursor_two,cursor_three;

  -- now work through the otehr tables looking for an
  -- associated record
  IF curs_get_mt%FOUND THEN
    -- look in the first OR table
    FETCH cursor_one INTO v_dummy;
    IF cursor_one%FOUND THEN
      v_ret_val.EXTEND;
      v_ret_val(v_ret_val.LAST) := v_col1;
    ELSE
      -- look in the seconds OR table
      FETCH cursor_two INTO v_dummy;
      IF cursor_two%FOUND THEN
        v_ret_val.EXTEND;
        v_ret_val(v_ret_val.LAST) := v_col1;
      ELSE
         -- look in the third OR table
        FETCH cursor_three INTO v_dummy;
        IF cursor_two%FOUND THEN
          v_ret_val.EXTEND;
          v_ret_val(v_ret_val.LAST) := v_col1;
        END IF;
      END IF;
    END IF;
  END IF;

  -- close any open cursors
  IF cursor_one%ISOPEN THEN
    CLOSE cursor_one;
  END IF;
  IF cursor_two%ISOPEN THEN
    CLOSE cursor_two;
  END IF;
  IF cursor_three%ISOPEN THEN
    CLOSE cursor_three;
  END IF;
  CLOSE curs_get_mt;
  RETURN(v_ret_val);
END;
/