Tuesday, May 13, 2008

Function to return Nested table

Function to return Nested table

CREATE OR REPLACE FUNCTION func_get_emp

RETURN emp_nest_tab

AS

p_sal emp.sal%TYPE;

old_dept emp.deptno%TYPE := 0;

new_dept emp.deptno%TYPE;

iteration NUMBER (2) := 0;

flag NUMBER := 0;

emp_nest emp_nest_tab;

new_sal NUMBER := 0;

CURSOR cur_emp

IS

SELECT empno

FROM emp

ORDER BY deptno;

empn emp.empno%TYPE;

--emp_ret emp_nest_tab;

BEGIN

OPEN cur_emp;

LOOP

FETCH cur_emp INTO empn;

EXIT WHEN cur_emp%NOTFOUND;

--------------procedure----------------

SELECT sal

INTO p_sal

FROM emp

WHERE empno = empn;

SELECT deptno

INTO new_dept

FROM emp

WHERE empno = empn;

----------- Code to initialize emp_nest-----------

IF flag = 0

THEN

emp_nest := emp_nest_tab ();

flag := 1;

END IF;

----------- Code to initialize emp_nest-----------

emp_nest.EXTEND;

IF old_dept = new_dept

THEN

new_sal := new_sal + p_sal;

emp_nest (emp_nest.COUNT) :=

emp_tab_type (new_dept, p_sal, new_sal, iteration);

iteration := iteration + 1;

ELSE

new_sal := p_sal;

emp_nest (emp_nest.COUNT) :=

emp_tab_type (new_dept, p_sal, new_sal, iteration);

old_dept := new_dept;

iteration := iteration + 1;

END IF;

--------------procedure----------------

END LOOP;

CLOSE cur_emp;

/*FOR cur IN (SELECT *

FROM TABLE (CAST (emp_nest AS emp_nest_tab)))

LOOP

DBMS_OUTPUT.put_line ( CHR (9)

|| cur.ini

|| CHR (9)

|| cur.deptno

|| CHR (9)

|| cur.sal

|| CHR (10)

);

END LOOP;*/

RETURN emp_nest;

END func_get_emp;

/

SELECT *

FROM TABLE (CAST (func_get_emp AS emp_nest_tab))

DEPTNO

SAL

TOT_SAL

INI

10

2,450.00

2,450.00

0

10

5,000.00

7,450.00

1

10

1,300.00

8,750.00

2

20

800.00

800.00

3

20

1,100.00

1,900.00

4

20

3,000.00

4,900.00

5

20

3,000.00

7,900.00

6

20

2,975.00

10,875.00

7

30

1,600.00

1,600.00

8

30

2,850.00

4,450.00

9

30

1,250.00

5,700.00

10

30

950.00

6,650.00

11

30

1,500.00

8,150.00

12

30

1,250.00

9,400.00

13