time_limit.sql

Oracle PLSQL for DBAs 源代码

CREATE TYPE rowset_o AS OBJECT ( col1 NUMBER );
/
CREATE TYPE rowset_t AS TABLE OF rowset_o;
/

BEGIN
  FOR counter IN 1..30 LOOP
    INSERT INTO a_table
    VALUES(counter);
  END LOOP;
END;
/

CREATE OR REPLACE FUNCTION get_a_table ( p_limit NUMBER )
                  RETURN rowset_t
                  PIPELINED AS

  /*
    || Put a time limit on the records returned by this
    || table function
  */

  CURSOR curs_get_a IS
  SELECT *
    FROM a_table;
  v_start DATE;

BEGIN
  v_start := SYSDATE;

  FOR v_a_rec IN curs_get_a LOOP
    PIPE ROW(rowset_o(v_a_rec.col1));
    IF SYSDATE - v_start >= ( p_limit * 0.000011574 ) THEN
      PIPE ROW(rowset_o(-1));
      EXIT;
    END IF;
  END LOOP;

END;
/
SELECT *
  FROM TABLE(get_a_table(1))
/