Creating and calling PL/SQL functions

Question

I'm new to PL/SQL. It's been on fine until now that is. I've the query this query which works fine.

declare
rec employees_practice%rowtype;
sam taxObligations%rowtype;
socialsecurity number;
rentallowance number;
transportation number;
taxableincome number;
incometaxliability number;
netpay number;
total number;
totaldeductions number;

    begin
    for rec in (select * from employees_practice)
    loop
    socialsecurity:=(5.5/100)*(rec.salary);
    rentallowance:=(20/100)*(rec.salary);
    if(rec.Category='S') 
    then transportation:= 150; 
    else transportation:=100;
    end if;

taxableincome:=rec.Salary-socialsecurity+rentallowance+transportation;

for sam in (select * from taxObligations) 
loop
if(taxableincome between sam.Minincome and sam.Maxincome)
then incometaxliability:= sam.MinimumBracketTax + (taxableincome-sam.Minincome)*(sam.TaxBracketRate/100);
else incometaxliability:=null;
end if;
end loop;
netpay:= taxableincome-incometaxliability;
total:= rec.Salary + rentallowance + transportation;

totaldeductions:=socialsecurity + incometaxliability;

-- Here, I used DBMS.... to give an output in different format.

end loop; 
end;

I now want to create a function that encompasses the above code so I can call it with a single SQL or PL/SQL Query. It's been a headache for me.

score 1 · Answer 1 · answered Jul 02 '12 at 10:41

You can make it a pipelined function:

first you'll need a table type:

create or replace type result_tab as table of varchar2(32767);

Then, make your code a pipelined function:

create or replace function your_func return result_tab PIPELINED is

rec employees_practice%rowtype;
sam taxObligations%rowtype;
socialsecurity number;
rentallowance number;
transportation number;
taxableincome number;
incometaxliability number;
netpay number;
total number;
totaldeductions number;

    begin
    for rec in (select * from employees_practice)
    loop
    socialsecurity:=(5.5/100)*(rec.salary);
    rentallowance:=(20/100)*(rec.salary);
    if(rec.Category='S') 
    then transportation:= 150; 
    else transportation:=100;
    end if;

taxableincome:=rec.Salary-socialsecurity+rentallowance+transportation;

for sam in (select * from taxObligations) 
loop
if(taxableincome between sam.Minincome and sam.Maxincome)
then incometaxliability:= sam.MinimumBracketTax + (taxableincome-sam.Minincome)*(sam.TaxBracketRate/100);
else incometaxliability:=null;
end if;
end loop;
netpay:= taxableincome-incometaxliability;
total:= rec.Salary + rentallowance + transportation;

totaldeductions:=socialsecurity + incometaxliability;

-- Here, I used PIPE ROW() to give an output in different format.
pipe row('what ever you had in your dbms_output command');

end loop; 

return;

end your_func;

Now, you can call / query it like this:

select * from table(your_func)

I tried it and got this error. Error at line 0: PL/SQL: Compilation unit analysis terminated — Kofi Addo, Jul 02 '12 at 11:09

score 0 · Answer 2 · answered Jul 02 '12 at 10:23

If everything is working now, try doing it all in a stored FUNCTION with a CURSOR as IN parameter to return a NUMBER. Learn about variable types and cursors. The Oracle docs should be enough for you to do it yourself.

After that, if you encounter problems with your code post it here and you certainly will be helped.

Creating and calling PL/SQL functions

2 Answers2

Linked