Oracle PL/SQL - Opening Cursor Variable for Chosen Query (Different Return Types)

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Cursor Variables

Introduction

In the following code,the stored procedure opens its cursor variable parameter for a chosen query.

The queries have the different return types. 


CREATE OR REPLACE PACKAGE admin_data AS 
  TYPE gencurtyp IS REF CURSOR; 
  PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT); 
END admin_data; 
/ 
CREATE OR REPLACE 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 departments; 
    END IF; 
  END; 
END admin_data; 
/

Related Topic