数据库Function和View (用于虚拟表或虚拟字段处理)

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;