bidir.pkg

OReilly Oracle PL SQL Programming第4版源代码

/* Formatted on 2001/12/27 18:53 (Formatter Plus v4.5.2) */
CREATE OR REPLACE PACKAGE bidir
IS
   /* Returns data by primary key */
   FUNCTION rowforid (id_in IN employee.employee_id%TYPE)
      RETURN employee%ROWTYPE;

   FUNCTION firstrow
      RETURN PLS_INTEGER;

   FUNCTION lastrow
      RETURN PLS_INTEGER;

   FUNCTION ROWCOUNT
      RETURN PLS_INTEGER;

   FUNCTION end_of_data
      RETURN BOOLEAN;

   /* Iterate through rows in the result set */
   PROCEDURE setrow (nth IN PLS_INTEGER);

   FUNCTION currrow
      RETURN employee%ROWTYPE;

   PROCEDURE nextrow;

   PROCEDURE prevrow;
END;
/
CREATE OR REPLACE PACKAGE BODY bidir
IS
   g_currrow   PLS_INTEGER := NULL;

   TYPE employee_tt IS TABLE OF employee%ROWTYPE
      INDEX BY BINARY_INTEGER;

   employees   employee_tt;

   FUNCTION rowforid (id_in IN employee.employee_id%TYPE)
      RETURN employee%ROWTYPE
   IS
   BEGIN
      RETURN employees (id_in);
   END;

   FUNCTION ROWCOUNT
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN employees.COUNT;
   END;

   FUNCTION firstrow
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN employees.FIRST;
   END;

   FUNCTION lastrow
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN employees.LAST;
   END;

   FUNCTION end_of_data
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN g_currrow IS NULL;
   END;

   PROCEDURE setrow (nth IN PLS_INTEGER)
   IS
   BEGIN
      g_currrow := GREATEST (LEAST (nth, lastrow), firstrow);
   END;

   FUNCTION currrow
      RETURN employee%ROWTYPE
   IS
   BEGIN
      RETURN employees (g_currrow);
   END;

   PROCEDURE nextrow
   IS
   BEGIN
      g_currrow := employees.NEXT (g_currrow);
   END;

   PROCEDURE prevrow
   IS
   BEGIN
      g_currrow := employees.PRIOR (g_currrow);
   END;

BEGIN
   FOR rec IN  (SELECT *
                  FROM employee)
   LOOP
      employees (rec.employee_id) := rec;
   END LOOP;

   g_currrow := firstrow;
END;
/

/* Example of usage */
DECLARE
   l_employee   employee%ROWTYPE;
BEGIN
   LOOP
      EXIT WHEN bidir.end_of_data;
      l_employee := bidir.currrow;
      DBMS_OUTPUT.put_line (l_employee.last_name);
      bidir.nextrow;
   END LOOP;

   bidir.setrow (bidir.lastrow);

   LOOP
      EXIT WHEN bidir.end_of_data;
      l_employee := bidir.currrow;
      DBMS_OUTPUT.put_line (l_employee.last_name);
      bidir.prevrow;
   END LOOP;
END;



/*======================================================================
| Supplement to the third edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2002 O'Reilly &
| Associates, Inc. To submit corrections or find more code samples visit
| http://www.oreilly.com/catalog/oraclep3/
*/