CREATE OR REPLACE PROCEDURE P_ZYBBCX_ceshi(CITY_ID IN INTEGER,COUNTY_ID IN INTEGER,START_TIME IN VARCHAR2,END_TIME IN VARCHAR2,TASH_ID IN NUMBER,FLAG OUT VARCHAR2) IS
begin
declare
BOARD_NAMENO clob:='';
CUR_RESULT sys_refcursor;
begin
IF CITY_ID IS NOT NULL AND COUNTY_ID IS NOT NULL AND START_TIME IS NOT NULL AND END_TIME IS NOT NULL
THEN
INSERT INTO Temporary_Board_Diff(int_id,CITY_NAME,COUNTRY_NAME,COLLECT_NUM,STOCK_NUM,DIFFERENT_RECORD_NUM,TASK_ID)
SELECT
M_SCHEME_INFO_seq.nextval,
B.ZH_LABEL,
C.ZH_LABEL,
SUM(CASE WHEN A.IS_CAIJI='是' OR A.IS_CAIJI='0' THEN 1 ELSE 0 END),
SUM(CASE WHEN A.IS_CAIJI='否' OR A.IS_CAIJI='0' THEN 1 ELSE 0 END),
SUM(CASE WHEN A.IS_CAIJI='是' OR A.IS_CAIJI='否' THEN 1 ELSE 0 END),
TASH_ID
FROM HBRMW7.RMS_ZP_CJBDJG A
LEFT JOIN HBRMW7.RMS_CITY B ON A.CITY_ID=B.INT_ID AND B.STATEFLAG=0
LEFT JOIN HBRMW7.RMS_COUNTY C ON A.COUNTY_ID=C.INT_ID AND C.STATEFLAG=0
WHERE A.CITY_ID=CITY_ID AND A.COUNTY_ID=COUNTY_ID GROUP BY B.ZH_LABEL,C.ZH_LABEL;
COMMIT;
FOR CUR_RESULT IN(
SELECT A.BOARD_NO FROM HBRMW7.RMS_ZP_CJBDJG A,TEMPORARY_BOARD_DIFF B,HBRMW7.RMS_BOARDPROCESS C,HBRMW7.RMS_CITY D,HBRMW7.RMS_COUNTY E WHERE A.CITY_ID=D.INT_ID AND A.COUNTY_ID=E.INT_ID AND D.ZH_LABEL=B.CITY_NAME AND E.ZH_LABEL=B.COUNTRY_NAME AND
(A.IS_CAIJI='是' OR A.IS_CAIJI='否') AND B.TASK_ID=TASH_ID)
LOOP BOARD_NAMENO:=BOARD_NAMENO||','||CUR_RESULT.BOARD_NO;
END LOOP;
UPDATE temporary_board_diff SET BOARD_NOS=BOARD_NAMENO WHERE TASK_ID=TASH_ID ;
COMMIT;
IF BOARD_NAMENO IS NOT NULL
THEN
SELECT 'TRUE' INTO FLAG FROM DUAL;
ELSE
SELECT 'FALSE' INTO FLAG FROM DUAL; END IF;
ELSE SELECT 'FALSE' INTO FLAG FROM DUAL;
end if;
end;
END P_ZYBBCX_ceshi;
begin
declare
BOARD_NAMENO clob:='';
CUR_RESULT sys_refcursor;
begin
IF CITY_ID IS NOT NULL AND COUNTY_ID IS NOT NULL AND START_TIME IS NOT NULL AND END_TIME IS NOT NULL
THEN
INSERT INTO Temporary_Board_Diff(int_id,CITY_NAME,COUNTRY_NAME,COLLECT_NUM,STOCK_NUM,DIFFERENT_RECORD_NUM,TASK_ID)
SELECT
M_SCHEME_INFO_seq.nextval,
B.ZH_LABEL,
C.ZH_LABEL,
SUM(CASE WHEN A.IS_CAIJI='是' OR A.IS_CAIJI='0' THEN 1 ELSE 0 END),
SUM(CASE WHEN A.IS_CAIJI='否' OR A.IS_CAIJI='0' THEN 1 ELSE 0 END),
SUM(CASE WHEN A.IS_CAIJI='是' OR A.IS_CAIJI='否' THEN 1 ELSE 0 END),
TASH_ID
FROM HBRMW7.RMS_ZP_CJBDJG A
LEFT JOIN HBRMW7.RMS_CITY B ON A.CITY_ID=B.INT_ID AND B.STATEFLAG=0
LEFT JOIN HBRMW7.RMS_COUNTY C ON A.COUNTY_ID=C.INT_ID AND C.STATEFLAG=0
WHERE A.CITY_ID=CITY_ID AND A.COUNTY_ID=COUNTY_ID GROUP BY B.ZH_LABEL,C.ZH_LABEL;
COMMIT;
FOR CUR_RESULT IN(
SELECT A.BOARD_NO FROM HBRMW7.RMS_ZP_CJBDJG A,TEMPORARY_BOARD_DIFF B,HBRMW7.RMS_BOARDPROCESS C,HBRMW7.RMS_CITY D,HBRMW7.RMS_COUNTY E WHERE A.CITY_ID=D.INT_ID AND A.COUNTY_ID=E.INT_ID AND D.ZH_LABEL=B.CITY_NAME AND E.ZH_LABEL=B.COUNTRY_NAME AND
(A.IS_CAIJI='是' OR A.IS_CAIJI='否') AND B.TASK_ID=TASH_ID)
LOOP BOARD_NAMENO:=BOARD_NAMENO||','||CUR_RESULT.BOARD_NO;
END LOOP;
UPDATE temporary_board_diff SET BOARD_NOS=BOARD_NAMENO WHERE TASK_ID=TASH_ID ;
COMMIT;
IF BOARD_NAMENO IS NOT NULL
THEN
SELECT 'TRUE' INTO FLAG FROM DUAL;
ELSE
SELECT 'FALSE' INTO FLAG FROM DUAL; END IF;
ELSE SELECT 'FALSE' INTO FLAG FROM DUAL;
end if;
end;
END P_ZYBBCX_ceshi;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货