创建并调用PL / SQL函数 [英] Creating and calling PL/SQL functions
本文介绍了创建并调用PL / SQL函数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我是PL / SQL的新手。它一直很好,直到现在是。
我查询这个查询是否正常。
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;
我现在想创建一个包含上述代码的函数,所以我可以使用单个SQL或PL / SQL查询。
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.
推荐答案
你可以使它成为一个管道功能:
首先你需要一个表类型:
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)
这篇关于创建并调用PL / SQL函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文