最近,在项目中写了数据库的函数和存储过程,趁此机会,整理了一下。
用户自定义函数是存储在数据库中的代码块,可以把值返回到调用程序。函数的参数有3种类型:
(1)in参数类型:表示输入给函数的参数,该参数只能用于传值,不能被赋值。
(2)out参数类型:表示参数在函数中被赋值,可以传给函数调用程序,该参数只能用于赋值,不能用于传值。
(3)in out参数类型:表示参数既可以传值,也可以被赋值。
语法如下: create or replace function function_name
(
argu1 [mode1] datatype1,
argu2 [mode2] datatype2, ........
)
return datatype
is
begin
end;
create or replace function ns_ffcm_getExchangeAmount(ccy varchar2, --交易币种
sellccy varchar2, --卖出币种
buyccy varchar2, --买入币种
amount number, --预约金额
rate number) --汇率
return number
as
standardccy_ varchar2(5);
unit_ integer;
exchangeamount_ number(15,2);
digist_ integer:=2;
begin
--取兑换金额
for rec in (select u.cid, u.exchangeccy1, u.exchangeccy2, u.standardccy, u.unit
from ffcm_currencyunit u
where 1=1
and ((u.exchangeccy1=ns_ffcm_getExchangeAmount.sellccy and u.exchangeccy2=ns_ffcm_getExchangeAmount.buyccy)
or (u.exchangeccy1=ns_ffcm_getExchangeAmount.buyccy and u.exchangeccy2=ns_ffcm_getExchangeAmount.sellccy))
order by u.cid desc) loop
standardccy_:=rec.standardccy;
unit_:=rec.unit;
end loop;
if ((ns_ffcm_getExchangeAmount.ccy=ns_ffcm_getExchangeAmount.sellccy and ns_ffcm_getExchangeAmount.buyccy='JPY')
or (ns_ffcm_getExchangeAmount.ccy=ns_ffcm_getExchangeAmount.buyccy and ns_ffcm_getExchangeAmount.sellccy='JPY')) then
digist_:=0;
end if;
/*
1)基准币种=交易币种
兑换金额=预约金额*汇率/基准币种单位
2)基准币种!=交易币种
兑换金额=预约金额/汇率*基准币种单位
*/
if ns_ffcm_getExchangeAmount.standardccy_ is not null and ns_ffcm_getExchangeAmount.unit_ is not null then
if ns_ffcm_getExchangeAmount.ccy=ns_ffcm_getExchangeAmount.standardccy_ then
exchangeamount_:=round(ns_ffcm_getExchangeAmount.amount*ns_ffcm_getExchangeAmount.rate/ns_ffcm_getExchangeAmount.unit_, digist_);
elsif ns_ffcm_getExchangeAmount.ccy!=ns_ffcm_getExchangeAmount.standardccy_ then
exchangeamount_:=round(ns_ffcm_getExchangeAmount.amount/ns_ffcm_getExchangeAmount.rate*ns_ffcm_getExchangeAmount.unit_, digist_);
end if;
end if;
if ns_ffcm_getExchangeAmount.sellccy=ns_ffcm_getExchangeAmount.buyccy then
exchangeamount_:=ns_ffcm_getExchangeAmount.amount;
end if;
return exchangeamount_;
end;
存储过程:是一个可以用编程的方式来操作SQL的集合。
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END 存储过程名字
项目示例:create or replace procedure ns_ffcm_caldeliverypl(dtid in number)
as
hzamount_ number(15,2):=0; --合同折合金额
middlerate_ number(15,6):=0; --外币中间价
yamount_ number(15,2):=0; --月末金额
yzamount_ number(15,2):=0; --月末折合金额
ccymiddlerate_ number(15,6):=0; --交易币种中间价
pl_ number(15,6):=0; --外币损益价
rmbpl_ number(15,6):=0; --人民币损益价
begin
for rec in (select d.dtid, m.ccy, m.sellccy, m.buyccy, d.amount, d.rate, d.sellamount, d.buyamount, d.deliverydate
from ffcm_appointmentDeliveryTx d inner join ffcm_deliverymatch m on d.dmid=m.dmid
where 1=1
and d.dtid=ns_ffcm_caldeliverypl.dtid
order by d.dtid) loop
middlerate_:=ns_ffcm_getmiddlerate(rec.sellccy, rec.buyccy, rec.deliverydate);
if rec.ccy=rec.sellccy then
ccymiddlerate_:=ns_ffcm_getmiddlerate(rec.buyccy, 'CNY', rec.deliverydate);
hzamount_:=nvl(rec.buyamount, 0);
yzamount_:=ns_ffcm_getexchangeamount(rec.sellccy, rec.sellccy, rec.buyccy, nvl(rec.sellamount, 0), middlerate_);
pl_:=nvl(hzamount_-yzamount_, 0);
rmbpl_:=ns_ffcm_getexchangeamount(rec.buyccy, rec.buyccy, 'CNY', pl_, ccymiddlerate_);
else
ccymiddlerate_:=ns_ffcm_getmiddlerate(rec.sellccy, 'CNY', rec.deliverydate);
hzamount_:=nvl(rec.sellamount, 0);
yzamount_:=ns_ffcm_getexchangeamount(rec.buyccy, rec.buyccy, rec.sellccy, nvl(rec.buyamount, 0), middlerate_);
pl_:=nvl(yzamount_-hzamount_, 0);
rmbpl_:=ns_ffcm_getexchangeamount(rec.sellccy, rec.sellccy, 'CNY', pl_, ccymiddlerate_);
end if;
update ffcm_appointmentDeliveryTx d
set d.middlerate=middlerate_,
d.ccymiddlerate=ccymiddlerate_,
d.pl=pl_,
d.rmbpl=rmbpl_
where 1=1
and d.dtid=rec.dtid;
end loop;
end;
两者对比:
1、执行方式
执行存储过程:exec 过程名(参数);
执行函数:select 函数名(参数) from dual;函数可以随时在语句中执行。
2、返回的值
返回值的区别,函数有1个返回值,而存储过程是通过参数返回的,可以有多个或者没有