CREATE OR REPLACE Procedure P1_report_card(i_date IN DATE,
o_return OUT NUMBER,
o_msg OUT VARCHAR2)
/*
功能名称:卡挡卡种合并
执行周期:每日
数据来源:dcc_loan_cardinfo,v_card_type
目标表:m1_report_card
参数列表:
--------------------------------------------------------------------
参数 IN/OUT 类型 说明
--------------------------------------------------------------------
i_date IN DATE 跑批日期
o_return OUT NUMBER 0 成功
-1 失败
o_msg OUT VARCHAR2 异常信息 版本历史:
--------------------------------------------------------------------
作者 日期 版本号 说明
--------------------------------------------------------------------
yanhan 2012-01-09 1.0 初始 */
Is
v_step NUMBER(3) := 0;
Begin v_step := 10;
execute immediate 'truncate table m1_report_card';
v_step := 20;
insert into m1_report_card
select F_CARD_P_ORG1(bankno) p_code, --父机构
F_CARD_ORG1(bankno) org_no, --子机构
m2.card_no CM_CARD_NMBR, --卡号
m2.card_status CM_STATUS, --卡状态
m2.z_cust_no CM_CUSTOMER_NMBR, --客户号
m2.card_face CM_CARD_TYPE, --卡面
m2.ckind CM_TYPE, --卡类
ty.typeid, --卡大类编号
ty.typename, --卡大类名称
ty.pivotflag,--是否重点产品
ty.quotiety, --折算率
m2.card_blank_code CM_BLOCK_CODE, --卡封锁码
m2.crdg1 CM_CRLIMIT_PERM, --信用额度
m2.opcr_date CM_DTE_OPENED, --开卡日期(帐户)
m2.card_cls_dte CARD_CLOSE_DATE, --销户日期(客户)
m2.cust_crt_flg CUST_CREATE_FLG, --开户标志(客户)
m2.cust_cls_flg CUST_CLOSE_FLG, --销户标志(客户)
m2.yx_oper_no YX_OPER_NO, --营销人员代码
m2.yx_deptno YX_DEPTNO, --营销机构代码
m2.main_flag, --主附卡标志
m2.crd_cls_flg, --销卡标志
m2.bankno, --开户行
m2.origin_code --涞源码
from dcc_loan_cardinfo m2
left join v_card_type ty on m2.ckind = ty.cardkind
and m2.card_face = ty.cardface
WHERE substr(m2.bankno,1,3)='420'
and substr(m2.origin_code,1,2)<>'ZS'
and m2.main_flag='P';
o_return := 0;
o_msg := '执行P1_report_card成功';
--COMMIT; EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
o_return := -1;
o_msg := 'Error: Step=' || v_step || ', SQLERR= ' ||
to_char(SQLCODE) || ', ' ||
substr(SQLERRM(SQLCODE), 1, 1900);
End; 用PLSQL测试这个存储过程需要15000+秒,但将SQL语句拿出来在PLSQL中执行只要1300+秒,郁闷中求解
o_return OUT NUMBER,
o_msg OUT VARCHAR2)
/*
功能名称:卡挡卡种合并
执行周期:每日
数据来源:dcc_loan_cardinfo,v_card_type
目标表:m1_report_card
参数列表:
--------------------------------------------------------------------
参数 IN/OUT 类型 说明
--------------------------------------------------------------------
i_date IN DATE 跑批日期
o_return OUT NUMBER 0 成功
-1 失败
o_msg OUT VARCHAR2 异常信息 版本历史:
--------------------------------------------------------------------
作者 日期 版本号 说明
--------------------------------------------------------------------
yanhan 2012-01-09 1.0 初始 */
Is
v_step NUMBER(3) := 0;
Begin v_step := 10;
execute immediate 'truncate table m1_report_card';
v_step := 20;
insert into m1_report_card
select F_CARD_P_ORG1(bankno) p_code, --父机构
F_CARD_ORG1(bankno) org_no, --子机构
m2.card_no CM_CARD_NMBR, --卡号
m2.card_status CM_STATUS, --卡状态
m2.z_cust_no CM_CUSTOMER_NMBR, --客户号
m2.card_face CM_CARD_TYPE, --卡面
m2.ckind CM_TYPE, --卡类
ty.typeid, --卡大类编号
ty.typename, --卡大类名称
ty.pivotflag,--是否重点产品
ty.quotiety, --折算率
m2.card_blank_code CM_BLOCK_CODE, --卡封锁码
m2.crdg1 CM_CRLIMIT_PERM, --信用额度
m2.opcr_date CM_DTE_OPENED, --开卡日期(帐户)
m2.card_cls_dte CARD_CLOSE_DATE, --销户日期(客户)
m2.cust_crt_flg CUST_CREATE_FLG, --开户标志(客户)
m2.cust_cls_flg CUST_CLOSE_FLG, --销户标志(客户)
m2.yx_oper_no YX_OPER_NO, --营销人员代码
m2.yx_deptno YX_DEPTNO, --营销机构代码
m2.main_flag, --主附卡标志
m2.crd_cls_flg, --销卡标志
m2.bankno, --开户行
m2.origin_code --涞源码
from dcc_loan_cardinfo m2
left join v_card_type ty on m2.ckind = ty.cardkind
and m2.card_face = ty.cardface
WHERE substr(m2.bankno,1,3)='420'
and substr(m2.origin_code,1,2)<>'ZS'
and m2.main_flag='P';
o_return := 0;
o_msg := '执行P1_report_card成功';
--COMMIT; EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
o_return := -1;
o_msg := 'Error: Step=' || v_step || ', SQLERR= ' ||
to_char(SQLCODE) || ', ' ||
substr(SQLERRM(SQLCODE), 1, 1900);
End; 用PLSQL测试这个存储过程需要15000+秒,但将SQL语句拿出来在PLSQL中执行只要1300+秒,郁闷中求解
2、TABLE DCCMISDB.DCC_LOAN_CARDINFO 这张表其实也不大,大概是421M,按理说全表扫执行效率也不算太低。已建立substr(m2.bankno,1,3)和substr(m2.origin_code,1,2)函数索引。