游标定义，多行，一行

create or replace procedure SP_CIF_Smary_CustInfo(
o_Cur out Types.CursorType,
o_ret out integer,
o_msg out varchar2,
i_user in integer,
i_ip in varchar2,
i_CustNo in varchar2--客户号
) is
/*
**功能说明：客户基本信息

**创建日期：2015-04-09
************************************************************************
**修改记录
************************************************************************
**修改者 版本号 修改日期 说明
1.0 2015-04-09 创建
*/

begin
o_ret := -99;
o_msg := '未知错误';

open o_Cur for
select FCustNo as 客户号,
FName as 客户姓名,
case Fsex when 1 then '男' when 2 then '女' else '未说明性别' end||','||(select fn_PUB_CalAge(substr(to_char(a.fbirthday,'yyyymmdd'),0,4),FCertType,FCertNo) from dual) as "性别/年龄",
--(select fn_PUB_CalAge(substr(to_char(a.fbirthday,'yyyymmdd'),0,4),FCertType,FCertNo) from dual) as 年龄,
(select b.fname from pub_employee b where b.id = a.FManager) as 客户经理,
1 as 理财顾问,
FMobile as 服务手机,
1 as 服务EMAIL,
1 as 最近联系时间,
1 as 所属群,
1 as 客户特征
from Cif_Persinfo a where FCustNo = i_CustNo;

o_ret := 1;
o_msg := '';

exception
when others then
o_ret := -99;
o_msg := case when o_msg is null then '未知错误' else '在['||o_msg||']处发生异常' end;

end SP_CIF_Smary_CustInfo;


结果

<C0>1</C0>
<客户号>101332602197504126498</客户号>
<客户姓名>郭华鸥</客户姓名>
<性别_年龄>男,41</性别_年龄>
<客户经理>马志清</客户经理>
<理财顾问>1</理财顾问>
<服务手机>13186976222</服务手机>
<服务EMAIL>1</服务EMAIL>
<最近联系时间>1</最近联系时间>
<所属群>1</所属群>
<客户特征>1</客户特征>


create or replace procedure SP_CIF_Smary_Business(
o_Cur out Types.CursorType,
o_ret out integer,
o_msg out varchar2,
i_user in integer,
i_ip in varchar2,
i_CustNo in varchar2--客户号
) is
/*
**功能说明：业务开通情况
**创建人：
**创建日期：2015-04-10
************************************************************************
**修改记录
************************************************************************
**修改者 版本号 修改日期 说明
1.0 2015-04-10 创建
*/

begin
o_ret := -99;
o_msg := '未知错误';

open o_Cur for
select
'网上银行' as 业务,
b.khsj as 开通日期,
(select name from lborganization c where c.orgcode = b.khwdjgh) as 办理机构,
case when C3002 = 1 then '已开通' else '未开通' end as 状态
from dcuser.dc_customerdatah a
left join dcuser.PB_CSTINF_PRO b on a.fcustomer = b.zjlx||b.zjhm
where a.fcustomer = i_CustNo and a.fdate = (select max(fdate) from dcuser.dc_customerdatah)
union all
select
'手机银行' as 业务,
substr(b.khsj,0,8) as 开通日期,
(select name from lborganization c where c.orgcode = b.khwd) as 办理机构,
case when C3003 = 1 then '已开通' else '未开通' end as 状态
from dcuser.dc_customerdatah a
left join (select c.khsj,c.khwd,d.zjlx,d.zjhm from dcuser.PB_CSTINF_PRO d left join dcuser.MB_PB_CSTINF_PRO c on c.dzyxkhnm=d.wykhnm) b
on a.fcustomer = b.zjlx||b.zjhm
where a.fcustomer = i_CustNo and a.fdate = (select max(fdate) from dcuser.dc_customerdatah)
union all
select
'借记卡' as 业务,
to_char(b.kkrq,'yyyymmdd') as 开通日期,
(select name from lborganization c where c.orgcode = b.sljgh) as 办理机构,
case when C3007 = 1 then '已开通' else '未开通' end as 状态
from dcuser.dc_customerdatah a
left join dcuser.BWFMDCIM b on a.fcustomer=b.khh
where a.fcustomer = i_CustNo and a.fdate = (select max(fdate) from dcuser.dc_customerdatah);

o_ret := 1;
o_msg := '';

exception
when others then
o_ret := -99;
o_msg := case when o_msg is null then '未知错误' else '在['||o_msg||']处发生异常' end;

end SP_CIF_Smary_Business;


1 网上银行 未开通
2 手机银行 未开通
3 借记卡 已开通


方法定义

create or replace function fn_PUB_CalAge
(
i_BirthDate varchar2, --出生年份
i_CertType int := null, --证件类型
i_CertNo varchar2 := null --证件号码
) return int as
/*
**功能说明：获取年龄
**创建人：
**创建日期：2014-06-24
**************************************************************************
**修改记录
**************************************************************************
**修改者 版本号 修改日期 说明
**张华斌 2014-06-24 创建
*/
v_Age int;
v_BirthDate varchar2(4);
begin
if(i_CertType = '101') then
if(length(i_CertNo) = 15) then
v_BirthDate := '19' || substr(i_CertNo, 7, 2);
elsif(length(i_CertNo) = 18) then
v_BirthDate := substr(i_CertNo, 7, 4);
end if;
else
v_BirthDate := i_BirthDate;
end if;
if(v_BirthDate is not null) then
v_Age := to_char(sysdate, 'yyyy') - v_BirthDate + 1;
end if;
return v_Age;
end;