CREATE OR REPLACE FUNCTION getexposureinfo(contractNo varchar,expType varchar)
return varchar
is exposureValue number(24,6);--
sBusinessType varchar(20);--业务类型
dBusinessSum number(24,6);--金额
dBalance number(24,6);--余额
dBailRatio number(24,6);--保证金比例
dClaBailRatio number(24,6);--类保证金比例
dOverratio number(24,6);--溢短装比例
dParam number(24,6);--
begin
select BusinessSum ,BusinessType ,Balance ,BAILRATIO,CLABAILRATIO,RATIO into
dBusinessSum,sBusinessType,dBalance,dBailRatio,dClaBailRatio,dOverratio
from business_contract where serialno=contractNo;
if(dBusinessSum is null)then dBusinessSum := 0; end if;
if(dBalance is null)then dBalance := 0; end if;
if(dBailRatio is null)then dBailRatio := 0; end if;
if(dClaBailRatio is null)then dClaBailRatio := 0; end if;
if(dOverratio is null)then dOverratio := 0; end if;
dParam := (1 - dBailRatio/100 - dClaBailRatio/100)*( 1 + dOverratio/100);
if(expType='1')then
exposureValue := dBusinessSum*dParam; --总敞口金额
end if;
if(expType='2')then
exposureValue := dBusinessSum*dParam; --非项目贷款已用敞口金额为总敞口金额
if(sBusinessType is not null and instr(sBusinessType,'10102')=1)then --若当前业务品种是项目贷款
exposureValue := dBalance*dParam;--已用敞口金额
end if;
end if;
if(expType='3')then
exposureValue := 0;--非项目贷款可用敞口金额为0
if(sBusinessType is not null and instr(sBusinessType,'10102')=1)then --若当前业务品种是项目贷款
exposureValue := (dBusinessSum - dBalance)*dParam;--可用敞口金额
end if;
end if;
return exposureValue;
end;
<pre class="sql" name="code">CREATE OR REPLACE FUNCTION getApproveOrg(pOrgID varchar2)
return varchar2
is pApproveOrg varchar2(80);
a1 varchar2(80);
a2 varchar2(80);
pOrgLevel varchar2(20);</p><p>begin
select OrgLevel into pOrgLevel from ORG_INFO where OrgID = pOrgID;
select relativeorgid into a1 from org_info where orgid=pOrgID;
select orgid into a2 from org_info where orgid=pOrgID;</p><p> if(pOrgLevel='6' or pOrgLevel='4') then
pApproveOrg := a1;
else
pApproveOrg :=a2;
end if;
return pApproveOrg;
end;
创建视图
CREATE OR REPLACE VIEW OTHERSINGLE_APPLY AS
SELECT serialNo,getotherexposureinfo(serialNo,'1') as ExposureSum ,
getotherexposureinfo(serialNo,'2') as UsedExposureSum ,
getotherexposureinfo(serialNo,'3') as AvailExposureSum
FROM business_contract;