PL/SQL Procedure to Return Table Type

Few days ago I got a requirement in my company to provide a 'Manager-wise Infected Portfolio' which will show Managers with No of Contracts in their Portfolio, current overdue amount, outstanding exposure and last three month's exposure based of cutoff date user is providing.

Pattern look likes this

Name  No of Contracts / OverdueAmount / ThisMonthExposure /  Exposure Month3 / Exposure Month2 / Exposure Month1

Solution:

So I decided to solve this task using Database procedure which will take some inputs and return whole PL/SQL Table type containing multiple rows of the same contract but data of different Months.....I used that procedure in a form and when user press the button it inserts all rows in a database table to generate a report.

Using this operation the report generation process became faster than my older work working directly into Form application.

Any ways come to the example here are the steps to implement this kind of requirement.

Note: For some reasons i m not showing my exact code but just showing the concept of my logic that I've implemented for this Example u need traditional SCOTT schema in Oracle database.

Example:

In This Example I m going to show the PL/SQL procedure to receive input parameter DeptCode (The Department code from Employee table) and return all employees of the same department but as PL/SQL Table

--Create a Package which will contain structure of your returning table (You can do it database object types as well)

Create or replace Package MyTablePackage as

-- Create a Record type for Table

TYPE MyRecord
IS RECORD
(EmpCd number,
EmpName Varchar2(60),
DepartCode number,
Salary number );

--Declaring a Returing Table type

TYPE MyTableType IS TABLE OF MyRecord
INDEX BY BINARY_INTEGER;

-- Procedure will use that Table type as OUT parameter
PROCEDURE MyProcedure
(DeptCode number,
OutTableParam OUT MyTableType);
end MyTablePackage;


--Next is to create Package body.
Create or replace PACKAGE BODY MYTABLEPACKAGE AS
  PROCEDURE MyProcedure
  (DeptCode number, OutTableParam OUT MyTableType) AS
      v_counter number := 0;
  BEGIN
     for EmpCursor in
         (select empno, ename, sal
           from scott.emp
           where deptno = deptcode
           order by empno)
     loop
       v_counter := v_counter + 1;
       OutTableParam(v_counter).EmpCd := EmpCursor.empno;
       OutTableParam(v_counter).EmpName := EmpCursor.ename;
       OutTableParam(v_counter).DepartCode := Deptcode;
       OutTableParam(v_counter).Salary := EmpCursor.sal;
    end loop;  
  END MyProcedure;
END MYTABLEPACKAGE;



---To Verify the output

Set serveroutput on
declare
   v_counter  BINARY_INTEGER := 1;
   v_mytable  MyTablePackage.MyTableType;
begin
   --Passing Parameters to Procedure Department No 10
    MyTablePackage.MyProcedure(10, v_mytable);

 

  while v_counter <= v_mytable.count
   loop

       dbms_output.put_line(v_mytable(v_counter).EmpCd||'|'||

                                           v_mytable(v_counter).EmpName||'|'||

                                           v_mytable(v_counter).DepartCode||'|'||

                                           v_mytable(v_counter).Salary);
      v_counter := v_counter + 1;
   end loop;
end;


Output would be like this (   MyTablePackage.MyProcedure(10, v_mytable);)

Department No 10
7839|KING|10|5000
7934|MILLER|10|1300

Department No 30 (   MyTablePackage.MyProcedure(30, v_mytable);)
7499|ALLEN|30|1600
7521|WARD|30|1250
7654|MARTIN|30|1250
7698|BLAKE|30|2850
7844|TURNER|30|1500
7900|JAMES|30|950 

Feel free to comment

Cheers,
Baig