118个真实应用场景的Oracle存储过程案例及开发指南(从入门到熟练使用)

CREATE OR REPLACE PROCEDURE P_DATA_STAT_DAY_CUS_COUNT(V_INPUT_DATE_NUM IN NUMBER) IS BEGIN --1.计算每个经理的客户日统计 DECLARE V_TOTAL_FWKH_NUM NUMBER(12); --服务客户数 V_TOTAL_BIND_NUM NUMBER(12); --服务客户认证总数 V_TOTAL_BIND_RATIO NUMBER(12); --服务客户认证率 V_TOTAL_BIND_FRIEND_NUM NUMBER(12); --服务客户添加好友数 V_TOTAL_BIND_FRIEND_RATIO NUMBER(12); --有效认证率 V_ADD_FWKH_NUM NUMBER(12); --新增服务客户数 V_YESTERDAY_ADD_FWKH_NUM NUMBER(12); --昨日新增服务客户数 V_COMPARE_FWKH_NUM NUMBER(12); --新增服务客户数环比数 V_ADD_BIND_NUM NUMBER(12); --新增服务客户认证数 V_YESTERDAY_ADD_BIND_NUM NUMBER(12); --昨日新增服务客户认证数 V_COMPARE_BIND_NUM NUMBER(12); --新增服务客户认证数环比数 V_TOTAL_DINGDING_FRIEND_NUM NUMBER(12); --累计钉钉好友数 V_TOTAL_DINGDING_BIND_NUM NUMBER(12); --累计钉钉认证数 V_TOTAL_DINGDING_BIND_RATIO NUMBER(12); --累计钉钉认证率 V_DINGDING_NOT_BIND_NUM NUMBER(12); --累计钉钉未认证数 V_ADD_DINGDING_FRIEND_NUM NUMBER(12); --新增钉钉好友数 V_YD_ADD_DINGDING_FRIEND_NUM NUMBER(12); --昨日新增钉钉好友数 V_COMPARE_ADD_DINGDING_FRIEND_NUM NUMBER(12); --新增钉钉好友数环比 V_ADD_DINGDING_BIND_FRIEND_NUM NUMBER(12); --今日新增钉钉认证且为好友数 V_YD_ADD_DINGDING_BIND_FRIEND_NUM NUMBER(12); --昨日新增钉钉认证且为好友数 V_COMPARE_DINGDING_BIND_FRIEND_NUM NUMBER(12); --今日新增钉钉认证且为好友数环比 V_DELETE_FRIEND_NUM NUMBER(12); --删除/拉黑成员的好友数 V_YD_DELETE_FRIEND_NUM NUMBER(12); --昨日删除/拉黑成员的好友数 V_COMPARE_DELETE_FRIEND_NUM NUMBER(12); --删除/拉黑成员的好友数环比 V_SOL_CUS_NUM NUMBER(12); --招揽服务客户数 V_SOL_BIND_NUM NUMBER(12); --招揽服务客户认证数 V_SOL_BIND_RATIO NUMBER(12); --招揽服务客户认证率 V_EAR_CUS_NUM NUMBER(12); --指定服务客户数 V_EAR_BIND_NUM NUMBER(12); --指定服务客户认证数 V_EAR_BIND_RATIO NUMBER(12); --指定服务客户认证率 V_POPUL_CUS_NUM NUMBER(12); --推广服务客户数 V_POPUL_BIND_NUM NUMBER(12); --推广服务客户认证数 V_POPUL_BIND_RATIO NUMBER(12); --推广服务客户认证率 BEGIN DELETE FROM T_DATA_STAT_CUS_INFO WHERE DATE_TYPE = 'DAY' AND DT = V_INPUT_DATE_NUM; FOR I IN (SELECT T.ID STAFF_ID, T.RYXM STAFF_NAME, X.BRANCH_ID, LB.NAME BRANCH_NAME, X.REGINON_ID, X.REGINON_NAME, TD.TD, TD.TDMC FROM CIC.TRYXX T LEFT JOIN (SELECT * FROM (SELECT N2.ID BRANCH_ID, N1.ID REGINON_ID, N1.NAME REGINON_NAME FROM T_CRM_LBORGANIZATION N1 LEFT JOIN T_CRM_LBORGANIZATION N2 ON N1.ID = N2.FID WHERE N1.GRADE = 1) Y GROUP BY Y.BRANCH_ID, REGINON_ID, REGINON_NAME) X ON T.ORGID = X.BRANCH_ID LEFT JOIN T_CRM_LBORGANIZATION LB ON T.ORGID = LB.ID LEFT JOIN (SELECT F.TD, F.RYXX, G.TDMC FROM T_CRM_TTDCY F, T_CRM_TTDBM G WHERE F.TD = G.ID AND F.ZT = 0 AND G.ZT = 0) TD --团队信息 ON TD.RYXX = T.ID WHERE T.ZHZT = 0 AND T.ORGID <> 1) LOOP --服务客户数 SELECT COUNT(DISTINCT(FWKHH)) INTO V_TOTAL_FWKH_NUM FROM CIC.TFWGX --服务关系表 WHERE KHJL = I.STAFF_ID AND ZT = 0 AND FWGXLX IN (3, 4, 5) AND SZRQ <= V_INPUT_DATE_NUM; --服务客户认证总数 SELECT SUM(CASE WHEN U.USR_ID IS NOT NULL THEN 1 ELSE 0 END) --已认证 INTO V_TOTAL_BIND_NUM FROM (SELECT DISTINCT (FWKHH) FROM CIC.TFWGX --服务关系表 WHERE KHJL = I.STAFF_ID AND ZT = 0 AND FWGXLX IN (3, 4, 5)) T INNER JOIN ETC.TKHXX S --客户信息表 ON T.FWKHH = S.ID INNER JOIN ETC.T_ZJZH K --专属账号表 ON S.KHH = K.KHH LEFT JOIN (SELECT USR_ID, PROP_VAL, UPD_TM FROM AC_FIC.TC_VX_USR_INF WHERE SOURCE IN (0, 3) AND PROPERTY = 'ZSZH') U --认证关系 ON K.ZSZH = U.PROP_VAL WHERE K.ZHZT = 0 AND K.ZHLB = '0' AND K.ZZHBZ = '1' AND K.XYBZ = '0' AND TO_NUMBER(TO_CHAR(U.UPD_TM, 'YYYYMMDD')) <= V_INPUT_DATE_NUM; --服务客户认证率 四舍五入取整 * 100 IF V_TOTAL_FWKH_NUM = 0 THEN V_TOTAL_BIND_RATIO := 0; END IF; IF V_TOTAL_FWKH_NUM > 0 THEN V_TOTAL_BIND_RATIO := ROUND(V_TOTAL_BIND_NUM / V_TOTAL_FWKH_NUM, 0) * 100; END IF; --服务客户添加好友数 SELECT SUM(CASE WHEN VX_RLT.STATUS IS NOT NULL AND U.USR_ID IS NOT NULL THEN 1 ELSE 0 END) --已认证已添加 INTO V_TOTAL_BIND_FRIEND_NUM FROM (SELECT DISTINCT (FWKHH) FROM CIC.TFWGX --服务关系 WHERE KHJL = I.STAFF_ID AND ZT = 0 AND FWGXLX IN (3, 4, 5)) T INNER JOIN ETC.TKHXX S --客户信息表 ON T.FWKHH = S.ID INNER JOIN ETC.T_ZJZH K --专属账号 ON S.KHH = K.KHH LEFT JOIN (SELECT USR_ID, PROP_VAL, UPD_TM FROM AC_FIC.TC_VX_USR_INF WHERE SOURCE IN (0, 3) AND PROPERTY = 'ZSZH') U --认证关系 ON K.ZSZH = U.PROP_VAL LEFT JOIN (SELECT USR_ID, STATUS, CRT_TM FROM AC_FIA.TC_VX_USR_RLT WHERE STF_ID = I.STAFF_ID) VX_RLT --钉钉添加好友表 ON U.USR_ID = VX_RLT.USR_ID WHERE K.ZHZT = 0 AND K.ZHLB = '0' AND K.ZZHBZ = '1' AND K.XYBZ = '0' AND TO_NUMBER(TO_CHAR(VX_RLT.CRT_TM, 'YYYYMMDD')) <= V_INPUT_DATE_NUM; --有效认证率：指服务客户添加好友数占服务客户认证总数百分比，计算公式：有效认证率=服务客户添加好友数/服务客户认证总数*100%，计算结果四舍五入取整 IF V_TOTAL_BIND_NUM = 0 THEN V_TOTAL_BIND_FRIEND_RATIO := 0; END IF; IF V_TOTAL_BIND_NUM > 0 THEN V_TOTAL_BIND_FRIEND_RATIO := ROUND(V_TOTAL_BIND_FRIEND_NUM / V_TOTAL_BIND_NUM, 0) * 100; END IF; --新增服务客户数 SELECT COUNT(DISTINCT(FWKHH)) INTO V_ADD_FWKH_NUM FROM CIC.TFWGX --服务关系表 WHERE KHJL = I.STAFF_ID AND ZT = 0 AND FWGXLX IN (3, 4, 5) AND SZ