用两天时间写的存储过程

声明：欢迎拍砖：

本来想用3层循环的，但是后来发现：需要遍历表TF_F_CUST_USER_地市（表名要拼接的+地市），既要用动态sql文拼接表名，又要用定义成一个游标。想了半天时间还是没实现。放弃。改用这种组合查询的方法，绕过游标。进行批量的插入。

这段sql文用的知识点包括：1、动态sql文；2、时间上减一个月；3、根据一列的值把另外一列转换成多列；4、for 循环（嵌套）；5、当然最复杂的还是业务知识（大意是根据一张参数表+一个客户用户表+一个客户表。统计每个地市各个业务下每个mdz集团客户的用户数量）

 

CREATE OR REPLACE PROCEDURE "P_CMS_MDZ_PRODUCT_USER_NUM"
(

v_remark IN VARCHAR2, --
v_resultcode OUT NUMBER,
v_resultinfo OUT VARCHAR2
)
-----------------------------------------------------------------------
-- Function: P_CMS_MDZ_PRODUCT_USER_NUM
-- Description: 统计各个地市下的每个业务用户数量

-- Author: xxxx
-- Date: 2012-03-13
-- Version: 1.0
-- Modified
-------------------------------------------------------------------------------------------------------------

IS

type v_ar_type is varray(13) of VARCHAR2(4);
eparch_ar v_ar_type:= v_ar_type('0651','0852','0953','0464','0455','0126','0407','0467','0899','0124','0478','0908','1269');

v_relation_id NUMBER;

v_metering_period NUMBER(6); --帐期

v_i number(2);

v_sql varchar2(5000);

-------------------------------------------------------------------------------------------------------------

BEGIN

--v_remark:=''; --- 无需传入的参数,但时为了配置定时任务,加的一个参数
v_resultcode:=-1;
v_resultinfo:='TradeOk';
v_i := 1;
v_relation_id:=1;

select to_char(sysdate,'yyyymm') into v_metering_period from dual;

-- 循环地市
FOR v_i IN 1..13
LOOP
BEGIN
--循环业务ID
FOR v_relation_id IN 1..18
LOOP
BEGIN
v_sql:='insert into TMP_T_SI_DKH_MDZ_TRUE_'||v_metering_period||
' (EPARCHY_CODE,CRM_CUST_ID,CUST_NAME,EPARCHY_NAME,TYPE_NAME1,TYPE_NAME2,TYPE_NAME3,TYPE_NAME4,USER_NUM,AREA_CODE,AREA_NAME,METERING_PERIOD)
values (select eparchy_code,cust_id_md crm_cust_id, cust_name,EPARCHY_NAME,
case when relation_level=1 then relation_name else '||''||' end as type_name1,
case when relation_level=2 then relation_name else '||''||' end as type_name2,
case when relation_level=3 then relation_name else '||''||' end as type_name3,
case when relation_level=4 then relation_name else '||''||' end as type_name4,
user_num,city_code area_code,AREA_NAME,
(select to_char(sysdate,'||'yyyymm'||') from dual) metering_period
from (

select result_sum.cust_id_md,result_sum.user_num,cm.cust_name,cm.eparchy_code,cm.city_code,
(SELECT AREA_NAME FROM TD_M_AREA WHERE AREA_CODE = cm.EPARCHY_CODE) EPARCHY_NAME,
(SELECT AREA_NAME FROM TD_M_AREA WHERE AREA_CODE = cm.CITY_CODE) AREA_NAME,
(select pr.relation_level from td_s_mdz_product_relation pr
where pr.relation_id='||v_relation_id||' and rownum=1) relation_level,
(select pr.relation_name from td_s_mdz_product_relation pr
where pr.relation_id='||v_relation_id||' and rownum=1) relation_name

from (SELECT CU.CUST_ID_MD cust_id_md, COUNT(USER_ID_B) user_num
FROM TF_F_CUST_USER_'||eparch_ar(v_i)||' CU, TF_F_USER_'||eparch_ar(v_i)||' USR
WHERE CU.USER_ID_B = USR.USER_ID
AND USR.PRODUCT_ID IN (SELECT DISTINCT (PRODUCT_ID)
FROM TD_S_MDZ_PRODUCT_RELATION PR
WHERE PR.RELATION_ID = '||v_relation_id||')
GROUP BY CU.CUST_ID_MD) result_sum, TF_F_CUSTOMER_0451 cm where result_sum.cust_id_md= cm.cust_id)';
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
v_resultcode := -1;
v_resultinfo := 'P_CMS_MDZ_PRODUCT_USER_NUM新增用户统计信息失败:'||SQLERRM;
END;
END LOOP;
END;
END LOOP;

---------------------------------------------------------------------------------------------------------
COMMIT;
v_resultinfo:='存储过程[P_CMS_MDZ_PRODUCT_USER_NUM]执行成功！';
v_resultcode:=0;
-- RETURN;
-------------------------------------------------------------------------------------------------------------
EXCEPTION

WHEN OTHERS THEN
v_resultcode := -1;
v_resultinfo := '存储过程[P_CMS_MDZ_PRODUCT_USER_NUM]执行失败！'||SQLERRM;
RETURN;
END;