How to write store procedure with object type as out parameter in Pl/SQL with multiple joins

Question

I am trying to write a store procedure with below sample use case and tables,

Employee
Emp_id Emp_Name 1 Jhon 2 Mark 3 Marry

Department
Emp_Id Dept_Id 1 A 2 B 3 C 1 B 2 D

Assets Emp_Id Asset_Name 1 AA 1 BB 2 CC 2 DD 4 EE 4 FF

Relationship

One employee can be added to more than one department. e.g Emp 1 added to A and B Department.

One Employee Can have more than one Assets e.g Emp 1 owing Assets AA and BB.

No Foreign key constraint between Employee And Assets. So Assets can have EmpId which is not available in Employee Table. e.g Emp 4

Desired output

EmployeeInfo Emd_Id Emp_Name Array of Dept_Id[] Array of Assets[]

Desired Result for employee Id 1

Emd_Id :1 Emp_Name :Jhon Array of Dept_Id[] :[A,B] Array of Assets[] :[AA,BB]

Desired Result for employee Id 4

Emd_Id :4 Emp_Name :null -- As no entry in Employee table. Array of Dept_Id[] :null Array of Assets[] :[EE,FF]

So want write a store procedure for this.Please suggest solution for this. Either this can be achieved with multiple cursor or object type out variable?

Stored Procedure I tried as below,

CREATE OR REPLACE PROCEDURE PRC_TEST( employeeInfo OUT SYS_REFCURSOR) IS BEGIN OPEN employeeInfo FOR SELECT e.EMP_ID ,e.EMP_NAME, d.DEPT_ID, a.ASSET_NAME FROM EMPLOYEE e, DEPARTMENT d, ASSETS a WHERE e.EMP_ID = d.EMP_ID AND e.EMP_ID = a.EMP_ID; END; Thanks in advance

Answer 1

It is best practice to create a package definition and body. Your package definition will be something like below:

CREATE OR REPLACE PACKAGE EmployeeInfo AS

TYPE departament_type IS TABLE OF VARCHAR2(100);
TYPE assets_type      IS TABLE OF VARCHAR2(100);

PROCEDURE get_employee_info ( Emp_Id_col      IN OUT NUMBER
                             ,Emp_Name_col       OUT VARCHAR2
                             ,departament_tbl    OUT DEPARTAMENT_TYPE
                             ,assets_tbl         OUT ASSETS_TYPE);

END EmployeeInfo;

Then your package body will match your definition and it is where the procedure is going to be implemented:

CREATE OR REPLACE PACKAGE BODY EmployeeInfo AS

PROCEDURE get_employee_info ( Emp_Id_col      IN OUT NUMBER
                             ,Emp_Name_col       OUT VARCHAR2
                             ,departament_tbl    OUT DEPARTAMENT_TYPE
                             ,assets_tbl         OUT ASSETS_TYPE)
IS 
BEGIN

BEGIN
  SELECT Emp_Name
  INTO   Emp_Name_col
  FROM   Employee
  WHERE  Emp_Id = Emp_Id_col;
EXCEPTION
  WHEN NO_DATA_FOUND THEN  
    RETURN;
END;  

departament_tbl := DEPARTAMENT_TYPE();

FOR dep_rec IN (SELECT *
                FROM   Department
                WHERE  Emp_id = Emp_Id_col) LOOP 
  departament_tbl.extend;
  departament_tbl(departament_tbl.COUNT) := dep_rec.Dep_Id;
END LOOP;

assets_tbl := ASSETS_TYPE();

FOR asset_rec IN (SELECT *
                  FROM   Assets
                  WHERE  Emp_Id = Emp_Id_col) LOOP 
  assets_tbl.extend;
  assets_tbl(assets_tbl.COUNT) := asset_rec.Asset_Name;
END LOOP;

END get_employee_info;

END EmployeeInfo;

Now here there is a very simple test script for your stored procedure:

DECLARE
  Emp_Id_col            NUMBER(10);
  Emp_Name_col          Employee.Emp_Name%TYPE;
  departament_tbl       EMPLOYEEINFO.DEPARTAMENT_TYPE;
  assets_tbl            EMPLOYEEINFO.ASSETS_TYPE;
BEGIN
  Emp_Id_col := 1;
  EMPLOYEEINFO.GET_EMPLOYEE_INFO(Emp_Id_col
                                ,Emp_Name_col
                                ,departament_tbl
                                ,assets_tbl);

  DBMS_OUTPUT.PUT_LINE(Emp_Name_col          || ' - ' ||
                       departament_tbl.COUNT || ' - ' || 
                       assets_tbl.COUNT);

END;

Answer 2

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Employee ( Emp_id, Emp_Name ) AS
SELECT 1, 'Jhon' FROM DUAL UNION ALL
SELECT 2, 'Mark' FROM DUAL UNION ALL
SELECT 3, 'Marry' FROM DUAL
/

CREATE TABLE Department ( Emp_Id, Dept_Id ) AS
SELECT 1, 'A' FROM DUAL UNION ALL
SELECT 2, 'B' FROM DUAL UNION ALL
SELECT 3, 'C' FROM DUAL UNION ALL
SELECT 1, 'B' FROM DUAL UNION ALL
SELECT 2, 'D' FROM DUAL
/

CREATE TABLE Assets ( Emp_Id, Asset_Name ) AS
SELECT 1, 'AA' FROM DUAL UNION ALL
SELECT 1, 'BB' FROM DUAL UNION ALL
SELECT 2, 'CC' FROM DUAL UNION ALL
SELECT 2, 'DD' FROM DUAL UNION ALL
SELECT 4, 'EE' FROM DUAL UNION ALL
SELECT 4, 'FF' FROM DUAL
/

CREATE TYPE StringLIst IS TABLE OF VARCHAR2(20)
/

CREATE TYPE Emp_Dept_Assets_Obj AS OBJECT(
  Emp_id   INTEGER,
  Emp_Name VARCHAR2(50),
  Depts    StringList,
  Assets   StringList
)
/

CREATE FUNCTION get_Details(
  i_emp_id  IN  Employee.EMP_ID%TYPE
) RETURN Emp_Dept_Assets_Obj
IS
  o_details Emp_Dept_Assets_Obj;
BEGIN
  o_details := Emp_Dept_Assets_Obj( i_emp_id, NULL, NULL, NULL );

  BEGIN
    SELECT Emp_Name
    INTO   o_details.Emp_Name
    FROM   Employee
    WHERE  emp_id = i_emp_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;

  SELECT dept_id
  BULK COLLECT INTO o_details.Depts
  FROM   Department
  WHERE  emp_id = i_emp_id;

  SELECT asset_name
  BULK COLLECT INTO o_details.Assets
  FROM   Assets
  WHERE  emp_id = i_emp_id;

  RETURN o_details;
END;
/

Query 1:

SELECT d.details.emp_id,
       d.details.emp_name,
       d.details.depts,
       d.details.assets
FROM   (
  SELECT get_Details( LEVEL ) AS details
  FROM   DUAL
  CONNECT BY LEVEL <= 4
) d

Results:

| DETAILS.EMP_ID | DETAILS.EMP_NAME | DETAILS.DEPTS | DETAILS.ASSETS |
|----------------|------------------|---------------|----------------|
|              1 |             Jhon |           A,B |          AA,BB |
|              2 |             Mark |           B,D |          CC,DD |
|              3 |            Marry |             C |                |
|              4 |           (null) |               |          EE,FF |