CREATE OR REPLACE PROCEDURE DOOCC.USP_DRPT_DISPLAY_MAIN------------------------------------------------------------------------
--EXEC USP_DRPT_DISPLAY_MAIN (:P_GRP_CD, :P_KPI_CD, :P_ORG_GRP_CD, :P_ORG_DLR_CD, :P_ORG_BSC_CD, :P_USER_SEQ, :P_SVC_TYPE, :P_YYYY_MM_FROM, :P_YYYY_MM_TO, :R_RESULT1, :R_RESULT2)
--****************************
------------------------------------------------------------------------
(
P_GRP_CD IN NVARCHAR2 DEFAULT NULL
,P_KPI_CD IN NVARCHAR2 DEFAULT NULL
,P_ORG_GRP_CD IN NVARCHAR2 DEFAULT NULL --集团总部代理商代码
,P_ORG_DLR_CD IN NVARCHAR2 DEFAULT NULL --代理商代码(包含集团总部代理商)
,P_ORG_BSC_CD IN NVARCHAR2 DEFAULT NULL --办事处代码
,P_USER_SEQ IN NVARCHAR2 DEFAULT NULL --用户代码,权限Cross检查用 (可不使用)
,P_SVC_TYPE IN NVARCHAR2 DEFAULT NULL --DEFAULT '601'
,P_YYYY_MM_FROM IN NVARCHAR2 DEFAULT NULL
,P_YYYY_MM_TO IN NVARCHAR2 DEFAULT NULL
,R_RESULT1 OUT SYS_REFCURSOR
,R_RESULT2 OUT SYS_REFCURSOR
)
ASV_ORG_LEVEL NVARCHAR2(2); -- GROUP BY 基准
V_SVC_TYPE NVARCHAR2(50);
BEGIN-------------------------------------------
--DEFAULT VALUE SETTING
-------------------------------------------
V_SVC_TYPE := NVL(P_SVC_TYPE, '601');
-------------------------------------------
--定义统计的组织层级
-------------------------------------------
V_ORG_LEVEL := CASE
WHEN P_ORG_GRP_CD IS NULL AND P_ORG_DLR_CD IS NULL AND P_ORG_BSC_CD IS NULL THEN 'C' --CHINA, DICC LEVEL
WHEN P_ORG_GRP_CD IS NOT NULL OR P_ORG_GRP_CD <> '' THEN 'G' --GRUOP DEALER
WHEN P_ORG_DLR_CD IS NOT NULL OR P_ORG_DLR_CD <> '' THEN 'D' --DEALER
WHEN P_ORG_BSC_CD IS NOT NULL OR P_ORG_BSC_CD <> '' THEN 'B' --办事处
END;
-------------------------------------------
--获取要统计的组织范围
-------------------------------------------
USP_DRPT_RETURN_CHOICED_DEALER (P_ORG_GRP_CD, P_ORG_DLR_CD, P_ORG_BSC_CD, P_USER_SEQ);-------------------------------------------
--执行KPI别的Query
-------------------------------------------
IF P_GRP_CD = 'R2_P1_G1' THEN USP_DRPT_DISPLAY_R2_P1_G1 (P_GRP_CD, P_KPI_CD, V_ORG_LEVEL, V_SVC_TYPE, P_YYYY_MM_FROM, P_YYYY_MM_TO, R_RESULT1, R_RESULT2);END IF;
END;
///////////////////////////////////////////////
CREATE OR REPLACE PROCEDURE DOOCC.USP_DRPT_DISPLAY_R2_P1_G1------------------------------------------------------------------------
--EXEC USP_DRPT_DISPLAY_R2_P1_G1 ( :P_GRP_CD, :P_KPI_CD, :P_ORG_LEVEL, :P_SVC_TYPE, :P_YYYY_MM_FROM, :P_YYYY_MM_TO, :R_RESULT_UNIT, :R_RESULT_ACCUM)
------------------------------------------------------------------------
(
P_GRP_CD IN NVARCHAR2 DEFAULT NULL
,P_KPI_CD IN NVARCHAR2 DEFAULT NULL
,P_ORG_LEVEL IN NVARCHAR2 DEFAULT NULL
,P_SVC_TYPE IN NVARCHAR2 DEFAULT NULL
,P_YYYY_MM_FROM IN NVARCHAR2 DEFAULT NULL
,P_YYYY_MM_TO IN NVARCHAR2 DEFAULT NULL
,R_RESULT_UNIT OUT SYS_REFCURSOR
,R_RESULT_ACCUM OUT SYS_REFCURSOR
)
ASV_VAL_TOTAL NUMBER(20); -- 为计算比率的分母
BEGIN
----------------------------------------------------------------------------------------------
--计算计算比率用的分母
----------------------------------------------------------------------------------------------
SELECT SUM(TM.DATA1_VAL)
INTO V_VAL_TOTALFROM TB_DRPT_DATAMART TM
INNER JOIN TB_DRPT_TMP_CHOICED_ORG TG ON
TG.DEALER_CD = TM.ORG_DLR_CD
AND
TG.BSC_CD = TM.ORG_BSC_CD
WHERE
GRP_CD = P_GRP_CD
AND KPI_CD = P_KPI_CD
AND KPI_SUB1_CD = P_SVC_TYPE
AND KPI_PERIOD BETWEEN P_YYYY_MM_FROM AND P_YYYY_MM_TO;--==========================================================================================================
-- 返还时间点别件数的柱状图信息
OPEN R_RESULT_UNIT FOR
--==========================================================================================================
SELECT
TM.GRP_CD
,TM.KPI_CD
,TM.KPI_NM
,P_ORG_LEVEL AS ORG_LEVEL -- G,D,B ,FN_DRPT_RETURN_ORG_CD_BY_LEVEL (P_ORG_LEVEL, ORG_GRP_CD, ORG_DLR_CD, ORG_BSC_CD, 'SAME') AS ORG_CD ,FN_DRPT_RETURN_ORG_CD_BY_LEVEL (P_ORG_LEVEL, ORG_GRP_NM, ORG_DLR_NM, ORG_BSC_NM, 'SAME') AS ORG_NM
,TM.KPI_SUB1_CD AS SVC_TYPE_CD
,TM.KPI_SUB1_NM AS SVC_TYPE_NM -----------------------------------------------------------
,TM.KPI_SUB2_NM AS D1_NM
,TM.KPI_SUB2_CD AS D1_VAL
,TM.KPI_SUB2_UNIT AS D1_UNIT
-----------------------------------------------------------
,TM.DATA1_NM AS D2_NM
,SUM(TM.DATA1_VAL) AS D2_VAL
,TM.DATA1_UNIT AS D2_UNIT
-----------------------------------------------------------
,TM.DATA2_NM AS D3_NM
,CASE WHEN V_VAL_TOTAL = 0
THEN 0
ELSE ROUND( (SUM(TM.DATA2_VAL)*100)
/
V_VAL_TOTAL
,0)
END
AS D3_VAL
,TM.DATA2_UNIT AS D3_UNIT
-----------------------------------------------------------
, '' AS D4_NM , '' AS D4_VAL , '' AS D4_UNIT
, '' AS D5_NM , '' AS D5_VAL , '' AS D5_UNIT
, '' AS D6_NM , '' AS D6_VAL , '' AS D6_UNIT
, '' AS D7_NM , '' AS D7_VAL , '' AS D7_UNIT
, '' AS D8_NM , '' AS D8_VAL , '' AS D8_UNIT
, '' AS D9_NM , '' AS D9_VAL , '' AS D9_UNIT
, '' AS D10_NM , '' AS D10_VAL , '' AS D10_UNIT
----------------------------------------------------------- ,TM.CREATE_TIME
FROM TB_DRPT_DATAMART TM
INNER JOIN TB_DRPT_TMP_CHOICED_ORG TG ON
TG.DEALER_CD = TM.ORG_DLR_CD
AND
TG.BSC_CD = TM.ORG_BSC_CD
WHERE
GRP_CD = P_GRP_CD
AND KPI_CD = P_KPI_CD
AND KPI_SUB1_CD = P_SVC_TYPE
AND KPI_PERIOD BETWEEN P_YYYY_MM_FROM AND P_YYYY_MM_TO
GROUP BY TM.GRP_CD
,TM.KPI_CD
,TM.KPI_NM
,P_ORG_LEVEL
,FN_DRPT_RETURN_ORG_CD_BY_LEVEL (P_ORG_LEVEL, ORG_GRP_CD, ORG_DLR_CD, ORG_BSC_CD, 'SAME') ,FN_DRPT_RETURN_ORG_CD_BY_LEVEL (P_ORG_LEVEL, ORG_GRP_NM, ORG_DLR_NM, ORG_BSC_NM, 'SAME')
,TM.ORG_SVM_CD
,TM.ORG_SVM_NM ,TM.KPI_SUB1_CD
,TM.KPI_SUB1_NM
,TM.KPI_SUB2_NM
,TM.KPI_SUB2_CD
,TM.KPI_SUB2_UNIT ,TM.DATA1_NM
,TM.DATA1_UNIT ,TM.DATA2_NM
,TM.DATA2_UNIT ,TM.CREATE_TIMEORDER BY 1,2,3,4,5,6,7,8,9,10;
END;
/
这存储过程写的有问题吗,求大神,执行后数据能出来,
--EXEC USP_DRPT_DISPLAY_MAIN (:P_GRP_CD, :P_KPI_CD, :P_ORG_GRP_CD, :P_ORG_DLR_CD, :P_ORG_BSC_CD, :P_USER_SEQ, :P_SVC_TYPE, :P_YYYY_MM_FROM, :P_YYYY_MM_TO, :R_RESULT1, :R_RESULT2)
--****************************
------------------------------------------------------------------------
(
P_GRP_CD IN NVARCHAR2 DEFAULT NULL
,P_KPI_CD IN NVARCHAR2 DEFAULT NULL
,P_ORG_GRP_CD IN NVARCHAR2 DEFAULT NULL --集团总部代理商代码
,P_ORG_DLR_CD IN NVARCHAR2 DEFAULT NULL --代理商代码(包含集团总部代理商)
,P_ORG_BSC_CD IN NVARCHAR2 DEFAULT NULL --办事处代码
,P_USER_SEQ IN NVARCHAR2 DEFAULT NULL --用户代码,权限Cross检查用 (可不使用)
,P_SVC_TYPE IN NVARCHAR2 DEFAULT NULL --DEFAULT '601'
,P_YYYY_MM_FROM IN NVARCHAR2 DEFAULT NULL
,P_YYYY_MM_TO IN NVARCHAR2 DEFAULT NULL
,R_RESULT1 OUT SYS_REFCURSOR
,R_RESULT2 OUT SYS_REFCURSOR
)
ASV_ORG_LEVEL NVARCHAR2(2); -- GROUP BY 基准
V_SVC_TYPE NVARCHAR2(50);
BEGIN-------------------------------------------
--DEFAULT VALUE SETTING
-------------------------------------------
V_SVC_TYPE := NVL(P_SVC_TYPE, '601');
-------------------------------------------
--定义统计的组织层级
-------------------------------------------
V_ORG_LEVEL := CASE
WHEN P_ORG_GRP_CD IS NULL AND P_ORG_DLR_CD IS NULL AND P_ORG_BSC_CD IS NULL THEN 'C' --CHINA, DICC LEVEL
WHEN P_ORG_GRP_CD IS NOT NULL OR P_ORG_GRP_CD <> '' THEN 'G' --GRUOP DEALER
WHEN P_ORG_DLR_CD IS NOT NULL OR P_ORG_DLR_CD <> '' THEN 'D' --DEALER
WHEN P_ORG_BSC_CD IS NOT NULL OR P_ORG_BSC_CD <> '' THEN 'B' --办事处
END;
-------------------------------------------
--获取要统计的组织范围
-------------------------------------------
USP_DRPT_RETURN_CHOICED_DEALER (P_ORG_GRP_CD, P_ORG_DLR_CD, P_ORG_BSC_CD, P_USER_SEQ);-------------------------------------------
--执行KPI别的Query
-------------------------------------------
IF P_GRP_CD = 'R2_P1_G1' THEN USP_DRPT_DISPLAY_R2_P1_G1 (P_GRP_CD, P_KPI_CD, V_ORG_LEVEL, V_SVC_TYPE, P_YYYY_MM_FROM, P_YYYY_MM_TO, R_RESULT1, R_RESULT2);END IF;
END;
///////////////////////////////////////////////
CREATE OR REPLACE PROCEDURE DOOCC.USP_DRPT_DISPLAY_R2_P1_G1------------------------------------------------------------------------
--EXEC USP_DRPT_DISPLAY_R2_P1_G1 ( :P_GRP_CD, :P_KPI_CD, :P_ORG_LEVEL, :P_SVC_TYPE, :P_YYYY_MM_FROM, :P_YYYY_MM_TO, :R_RESULT_UNIT, :R_RESULT_ACCUM)
------------------------------------------------------------------------
(
P_GRP_CD IN NVARCHAR2 DEFAULT NULL
,P_KPI_CD IN NVARCHAR2 DEFAULT NULL
,P_ORG_LEVEL IN NVARCHAR2 DEFAULT NULL
,P_SVC_TYPE IN NVARCHAR2 DEFAULT NULL
,P_YYYY_MM_FROM IN NVARCHAR2 DEFAULT NULL
,P_YYYY_MM_TO IN NVARCHAR2 DEFAULT NULL
,R_RESULT_UNIT OUT SYS_REFCURSOR
,R_RESULT_ACCUM OUT SYS_REFCURSOR
)
ASV_VAL_TOTAL NUMBER(20); -- 为计算比率的分母
BEGIN
----------------------------------------------------------------------------------------------
--计算计算比率用的分母
----------------------------------------------------------------------------------------------
SELECT SUM(TM.DATA1_VAL)
INTO V_VAL_TOTALFROM TB_DRPT_DATAMART TM
INNER JOIN TB_DRPT_TMP_CHOICED_ORG TG ON
TG.DEALER_CD = TM.ORG_DLR_CD
AND
TG.BSC_CD = TM.ORG_BSC_CD
WHERE
GRP_CD = P_GRP_CD
AND KPI_CD = P_KPI_CD
AND KPI_SUB1_CD = P_SVC_TYPE
AND KPI_PERIOD BETWEEN P_YYYY_MM_FROM AND P_YYYY_MM_TO;--==========================================================================================================
-- 返还时间点别件数的柱状图信息
OPEN R_RESULT_UNIT FOR
--==========================================================================================================
SELECT
TM.GRP_CD
,TM.KPI_CD
,TM.KPI_NM
,P_ORG_LEVEL AS ORG_LEVEL -- G,D,B ,FN_DRPT_RETURN_ORG_CD_BY_LEVEL (P_ORG_LEVEL, ORG_GRP_CD, ORG_DLR_CD, ORG_BSC_CD, 'SAME') AS ORG_CD ,FN_DRPT_RETURN_ORG_CD_BY_LEVEL (P_ORG_LEVEL, ORG_GRP_NM, ORG_DLR_NM, ORG_BSC_NM, 'SAME') AS ORG_NM
,TM.KPI_SUB1_CD AS SVC_TYPE_CD
,TM.KPI_SUB1_NM AS SVC_TYPE_NM -----------------------------------------------------------
,TM.KPI_SUB2_NM AS D1_NM
,TM.KPI_SUB2_CD AS D1_VAL
,TM.KPI_SUB2_UNIT AS D1_UNIT
-----------------------------------------------------------
,TM.DATA1_NM AS D2_NM
,SUM(TM.DATA1_VAL) AS D2_VAL
,TM.DATA1_UNIT AS D2_UNIT
-----------------------------------------------------------
,TM.DATA2_NM AS D3_NM
,CASE WHEN V_VAL_TOTAL = 0
THEN 0
ELSE ROUND( (SUM(TM.DATA2_VAL)*100)
/
V_VAL_TOTAL
,0)
END
AS D3_VAL
,TM.DATA2_UNIT AS D3_UNIT
-----------------------------------------------------------
, '' AS D4_NM , '' AS D4_VAL , '' AS D4_UNIT
, '' AS D5_NM , '' AS D5_VAL , '' AS D5_UNIT
, '' AS D6_NM , '' AS D6_VAL , '' AS D6_UNIT
, '' AS D7_NM , '' AS D7_VAL , '' AS D7_UNIT
, '' AS D8_NM , '' AS D8_VAL , '' AS D8_UNIT
, '' AS D9_NM , '' AS D9_VAL , '' AS D9_UNIT
, '' AS D10_NM , '' AS D10_VAL , '' AS D10_UNIT
----------------------------------------------------------- ,TM.CREATE_TIME
FROM TB_DRPT_DATAMART TM
INNER JOIN TB_DRPT_TMP_CHOICED_ORG TG ON
TG.DEALER_CD = TM.ORG_DLR_CD
AND
TG.BSC_CD = TM.ORG_BSC_CD
WHERE
GRP_CD = P_GRP_CD
AND KPI_CD = P_KPI_CD
AND KPI_SUB1_CD = P_SVC_TYPE
AND KPI_PERIOD BETWEEN P_YYYY_MM_FROM AND P_YYYY_MM_TO
GROUP BY TM.GRP_CD
,TM.KPI_CD
,TM.KPI_NM
,P_ORG_LEVEL
,FN_DRPT_RETURN_ORG_CD_BY_LEVEL (P_ORG_LEVEL, ORG_GRP_CD, ORG_DLR_CD, ORG_BSC_CD, 'SAME') ,FN_DRPT_RETURN_ORG_CD_BY_LEVEL (P_ORG_LEVEL, ORG_GRP_NM, ORG_DLR_NM, ORG_BSC_NM, 'SAME')
,TM.ORG_SVM_CD
,TM.ORG_SVM_NM ,TM.KPI_SUB1_CD
,TM.KPI_SUB1_NM
,TM.KPI_SUB2_NM
,TM.KPI_SUB2_CD
,TM.KPI_SUB2_UNIT ,TM.DATA1_NM
,TM.DATA1_UNIT ,TM.DATA2_NM
,TM.DATA2_UNIT ,TM.CREATE_TIMEORDER BY 1,2,3,4,5,6,7,8,9,10;
END;
/
这存储过程写的有问题吗,求大神,执行后数据能出来,
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货