Live ORACLE

CREATE OR REPLACE PACKAGE emp_data

AS

  TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

 

  PROCEDURE open_emp_cv(emp_cv  IN OUT EMPCURTYP,choice  INT);

END emp_data;

/


CREATE OR REPLACE 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   employees

        WHERE  commission_pct IS NOT NULL;

    ELSIF choice = 2 THEN

      OPEN emp_cv FOR

        SELECT *

        FROM   employees

        WHERE  salary > 2500;

    ELSIF choice = 3 THEN

      OPEN emp_cv FOR

        SELECT *

        FROM   employees

        WHERE  department_id = 100;

    END IF;

  END;

END emp_data;

/

Stored Procedure to Open Ref Cursors with Different Queries