Collection Varray TABLE() in a SELECT statement

 

create object types, nested tables or VARRAY collections, and then use TABLE() in a SELECT statement

-- Object type representing an employee

CREATE OR REPLACE TYPE emp_obj AS OBJECT (

    emp_id   NUMBER,

    emp_name VARCHAR2(50)

);

/

-- Nested table of emp_obj

CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_obj;

/

-- Table storing departments with employees as a collection

CREATE TABLE dept_with_emps (

    dept_id   NUMBER,

    dept_name VARCHAR2(50),

    employees emp_tab

) NESTED TABLE employees STORE AS employees_nt;

/

INSERT INTO dept_with_emps VALUES (

    10,

    'HR',

    emp_tab(

        emp_obj(101, 'Alice'),

        emp_obj(102, 'Bob')

    )

);

INSERT INTO dept_with_emps VALUES (

    20,

    'IT',

    emp_tab(

        emp_obj(201, 'Charlie'),

        emp_obj(202, 'David'),

        emp_obj(203, 'Eve')

    )

);

COMMIT;

/

-- Select all employees from the nested collection

SELECT d.dept_id,

       d.dept_name,

       e.emp_id,

       e.emp_name

FROM dept_with_emps d,

     TABLE(d.employees) e;

/

---**************** Object Type **********----

DROP TYPE emp_tab FORCE;

DROP TYPE emp_obj FORCE;

-- Step 1: Create an Object Type

CREATE OR REPLACE TYPE emp_obj AS OBJECT (

    emp_id   NUMBER,

    emp_name VARCHAR2(50)

);

/

-- Step 2: Create a Nested Table Type of emp_obj

CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_obj;

/

-- Step 3: Use the collection in SQL with TABLE() function

SELECT *

FROM TABLE(

         emp_tab(

            emp_obj(101, 'Alice'),

            emp_obj(102, 'Bob'),

            emp_obj(103, 'Charlie')

         )

     );

/

DECLARE

    v_emps emp_tab := emp_tab(

                        emp_obj(201, 'David'),

                        emp_obj(202, 'Eve')

                      );

BEGIN

    FOR rec IN (SELECT * FROM TABLE(v_emps)) LOOP

        DBMS_OUTPUT.PUT_LINE('ID: ' || rec.emp_id || ', Name: ' || rec.emp_name);

    END LOOP;

END;

/