【筆記】遊標變數

/*To centralize data retrieval, you can group type-compatible queries in a stored
procedure. In the example below, the packaged procedure declares a selector as one
of its formal parameters. When called, the procedure opens the cursor variable
emp_cv for the chosen query.*/

CREATE PACKAGE emp_data AS
  TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  PROCEDURE open_emp_cv(emp_cv IN OUT EmpCurTyp, choice INT);
END emp_data;

CREATE PACKAGE BODY emp_data AS
  PROCEDURE open_emp_cv(emp_cv IN OUT EmpCurTyp, choice INT) IS
  BEGIN
    IF choice = 1 THEN
      OPEN emp_cv FOR
        SELECT * FROM emp WHERE comm IS NOT NULL;
    ELSIF choice = 2 THEN
      OPEN emp_cv FOR
        SELECT * FROM emp WHERE sal > 2500;
    ELSIF choice = 3 THEN
      OPEN emp_cv FOR
        SELECT * FROM emp WHERE deptno = 20;
    END IF;
  END;
END emp_data;

/*For more flexibility, you can pass a cursor variable and a selector to a stored
procedure that executes queries with different return types. Here is an example:*/

CREATE PACKAGE admin_data AS
  TYPE GenCurTyp IS REF CURSOR;
  PROCEDURE open_cv(generic_cv IN OUT GenCurTyp, choice INT);
END admin_data;

CREATE PACKAGE BODY admin_data AS
  PROCEDURE open_cv(generic_cv IN OUT GenCurTyp, choice INT) IS
  BEGIN
    IF choice = 1 THEN
      OPEN generic_cv FOR
        SELECT * FROM emp;
    ELSIF choice = 2 THEN
      OPEN generic_cv FOR
        SELECT * FROM dept;
    ELSIF choice = 3 THEN
      OPEN generic_cv FOR
        SELECT * FROM salgrade;
    END IF;
  END;
END admin_data;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-584507/，如需轉載，請註明出處，否則將追究法律責任。