/*
|| Cursor Variables As Function Arguments - Listing 1
||
|| Contains practical examples of using stongly-type and weakly-typed
|| cursor variables in PL/SQL, especially with table functions
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various Oracle features and 
|| should be carefully proofread before executing against any existing 
|| Oracle database to insure that no potential damage can occur.
||
*/
 
-----
-- Listing 1.1: REFerence CURsors Declared in Packages
-----
CREATE OR REPLACE PACKAGE hr.pkg_ref_cursors
/*
|| Function: hr.pkg_ref_cursors
||
|| Description: Provides example of REFerence CURsors
||
*/
IS
    -- Strongly-typed reference cursor based on RECORD
    TYPE code_table_rt IS RECORD (
        code        NUMBER(10),
        description VARCHAR2(255));
        
    TYPE code_table_rc
        IS REF CURSOR
        RETURN code_table_rt;

    -- Strongly-typed reference cursor based on table (via %ROWTYPE)
    TYPE divisions_rc
        IS REF CURSOR
        RETURN hr.divisions%ROWTYPE;

    -- Weakly-typed reference cursor
    TYPE cost_centers_rc
        IS REF CURSOR;

END;
/
 
-----
-- Listing 1.2: Using REFerence CURsors As Arguments
-----
CREATE OR REPLACE TYPE division_salary_ot IS OBJECT (
     division_id    NUMBER(5),
     max_salary     NUMBER(15,2)
);

CREATE OR REPLACE TYPE division_salary_t AS TABLE OF division_salary_ot;

CREATE OR REPLACE FUNCTION sf_get_max_division_salaries(
    arc_divisions   IN	hr.pkg_ref_cursors.divisions_rc
   ,arc_salaries	IN	SYS_REFCURSOR
)
RETURN division_salary_t PIPELINED
IS
/*
|| Function: sf_get_max_division_salaries
||
|| Description: Returns a PL/SQL collection containing the Division ID and
|| corresponding maximum salary per Division
||
*/
    
    TYPE salary_rt IS RECORD (
        id      NUMBER(6),
        salary  NUMBER(8,2)
    );

    salaries_rec salary_rt;

    division_rec hr.DIVISIONS%ROWTYPE;

    TYPE division_tbl 
        IS TABLE OF hr.divisions%ROWTYPE;

    division_t   division_tbl;
    
    l_division_name VARCHAR2(32) := NULL;

BEGIN
   
    -- Open the incoming Divisions REFerence CURsor
    LOOP
        FETCH arc_divisions INTO division_rec;
        EXIT WHEN arc_divisions%NOTFOUND;
            division_t(division_rec.DIVISION_ID).description := division_rec.description;            dbms_output.put_line('Division: ' || division_rec.description);
    END LOOP;

    -- Open the incoming Salaries REFerence CURsor
    LOOP
        FETCH arc_salaries INTO salaries_rec;
        EXIT WHEN arc_salaries%NOTFOUND;
            l_division_name := division_t(salaries_rec.id).description; 
            PIPE ROW (division_salary_ot(l_division_name, salaries_rec.salary));
    END LOOP;

    RETURN;

EXCEPTION
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('Fatal error encountered!');
        RETURN;
       
END sf_get_max_division_salaries;
/
 
-----
-- Listing 1.3: Calling a Table Function Using REFerence CURsors As Arguments
-----
SELECT *
    FROM 
    TABLE(
        CAST(
            sf_get_max_division_salaries(
                 CURSOR(SELECT * FROM hr.divisions),
                 CURSOR(SELECT D.division_id, MAX(E.salary) 
                          FROM hr.employees E, hr.departments D
                         WHERE E.department_id = D.department_id
                         GROUP BY D.division_id))
            AS division_salary_t
            )
         ); 
 
-----
-- Listing 1.4: Standardized Code Table Generator Table Function
-----
CREATE OR REPLACE TYPE code_table_ot IS OBJECT (
     code           NUMBER(10),
     description    VARCHAR2(255)
);

CREATE OR REPLACE TYPE code_table_t AS TABLE OF code_table_ot;

CREATE OR REPLACE FUNCTION sf_gather_code_table_entries(
    arc_code_table  IN	hr.pkg_ref_cursors.code_table_rc
)
RETURN code_table_t PIPELINED
/*
|| Function: sf_gather_code_table_entries
||
|| Description: Using the code and description entries supplied in the
|| input reference cursor, this function returns a PL/SQL collection
|| containing standardized code table entries.  
||
*/
IS
    -- Code table record type for reading from the REFerence CURsor
    code_table_rec hr.pkg_ref_cursors.code_table_rt;

BEGIN
    -- Open the incoming REFerence CURsor and then return a list of
    -- standard code table entries
    LOOP
        FETCH arc_code_table INTO code_table_rec;
        EXIT WHEN arc_code_table%NOTFOUND;
            PIPE ROW (code_table_ot(code_table_rec.code, code_table_rec.description));
    END LOOP;

    RETURN;

EXCEPTION
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('Fatal error encountered!');
        RETURN;
       
END sf_gather_code_table_entries;
/
 
-----
-- Listing 1.5: Return standard code tables using a table function
-----
-- Return a code table containing all Division entries, sorted
-- in no particular order
SELECT *
    FROM 
    TABLE(CAST(sf_gather_code_table_entries(
        CURSOR(SELECT division_id, description FROM hr.divisions)
        ) AS code_table_t));

-- Return a code table containing all Department entries, sorted
-- in ascending Department Name order
SELECT *
    FROM 
    TABLE(CAST(sf_gather_code_table_entries(
        CURSOR(
            SELECT department_id, department_NAME 
              FROM hr.departments
             ORDER BY department_name)
        ) AS code_table_t));

-- Return a code table containing Employee IDs and Names for any 
-- Employee whose name starts with a "C", sorted in descending Employee 
-- Last Name sequence 
SELECT *
    FROM 
    TABLE(CAST(sf_gather_code_table_entries(
        CURSOR(
            SELECT employee_id, last_name || ', ' || first_name 
              FROM hr.employees
              WHERE last_name LIKE 'C%'
             ORDER BY last_name DESC)
        ) AS code_table_t));