Oracle中的函数和存储过程--真实项目示例

最近，在项目中写了数据库的函数和存储过程，趁此机会，整理了一下。

用户自定义函数是存储在数据库中的代码块，可以把值返回到调用程序。函数的参数有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 numberasstandardccy_ 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)ashzamount_ 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个返回值,而存储过程是通过参数返回的,可以有多个或者没有