ORACLE的一些函数、存储过程范例

一、函数

1. F_GETDIGISTSBYRST

CREATE OR REPLACE FUNCTION F_GETDIGISTSBYRST(RSTID IN INTEGER) RETURN VARCHAR2 IS
  RESULT VARCHAR2(1000);

  CURSOR C_JOB
  IS
  SELECT D.SLDIGISTTABLE_SELECT, D.SLDIGISTTABLE_NAME FROM SLOW_CASERST A
  LEFT JOIN SLOWSICK_CHOICE B ON B.SLOWRST_ID = A.RID
  LEFT JOIN SICK_GIST_CONNECT C ON C.SLSICK_ID = B.RID
  LEFT JOIN SLDIGISTTABLE D ON C.GIST_ID = D.RECORD_ID
  WHERE A.RID = RSTID;
  C_ROW C_JOB%ROWTYPE;
BEGIN
  FOR C_ROW IN C_JOB LOOP
    IF C_ROW.SLDIGISTTABLE_SELECT IS NOT NULL THEN RESULT := RESULT || C_ROW.SLDIGISTTABLE_SELECT || ',';
    END IF;
    END LOOP;
  RESULT := SUBSTR(RESULT, 1, LENGTH(RESULT) -1);
  RETURN(RESULT);
END;

2. F_GETDIGISTSBYRST2

CREATE OR REPLACE FUNCTION F_GETDIGISTSBYRST2(RSTID IN INTEGER) RETURN VARCHAR2 IS
  RESULT VARCHAR2(1000);

  CURSOR C_JOB
  IS
  SELECT D.SLDIGISTTABLE_SELECT, D.SLDIGISTTABLE_NAME
    FROM SLOW_CASERST A
    LEFT JOIN SLOWSICK_CHOICE B ON B.SLOWRST_ID = A.RID
    LEFT JOIN SICK_GIST_CONNECT C ON C.SLSICK_ID = B.RID
    LEFT JOIN SLDIGISTTABLE D ON C.GIST_ID = D.RECORD_ID
   WHERE D.SLDIGISTTABLE_SELECT IS NOT NULL AND
  A.RID = RSTID;
  C_ROW C_JOB%ROWTYPE;
BEGIN
  FOR C_ROW IN C_JOB LOOP
    IF RESULT IS NULL THEN RESULT:= C_ROW.SLDIGISTTABLE_SELECT ;
    ELSIF  TO_NUMBER(C_ROW.SLDIGISTTABLE_SELECT) >  TO_NUMBER(RESULT) THEN RESULT := C_ROW.SLDIGISTTABLE_SELECT ;
    END IF;
    END LOOP;
  RETURN(RESULT);
END;

3. F_GETIFDEATHTOLIVE

CREATE OR REPLACE FUNCTION F_GETIFDEATHTOLIVE(RSTID IN INTEGER,SICKID IN INTEGER) RETURN VARCHAR2 IS
  RESULT VARCHAR2(10);
  CURSOR C_LST
  IS
SELECT B.IF_DIEALIVE, B.SLOWSICK_ID FROM SLOW_CASERST A
  LEFT JOIN SLOWSICK_CHOICE B ON B.SLOWRST_ID = A.RID
  WHERE B.SLOWSICK_ID =SICKID AND A.RID = RSTID;
  C_ROW C_LST%ROWTYPE;
BEGIN
  FOR C_ROW IN C_LST LOOP
      IF  C_ROW.IF_DIEALIVE IS NOT NULL  AND C_ROW.IF_DIEALIVE=1
         THEN RESULT := '是';
          ELSIF C_ROW.IF_DIEALIVE IS NOT NULL AND C_ROW.IF_DIEALIVE=0
         THEN RESULT := '否';
         ELSIF C_ROW.IF_DIEALIVE IS  NULL
         THEN RESULT := '';
      END IF;
       END LOOP;
  RETURN(RESULT);
END ;

4.F_GETRPTINFO

CREATE OR REPLACE FUNCTION F_GETRPTINFO(VAR1 VARCHAR2, VAR2 VARCHAR2) RETURN CLOB IS
       RST CLOB;
       CURSOR RSTCUR IS
       SELECT A.EVALREP_TITLE, A.RID FROM EVALUATE_RST A
       WHERE A.EVAL_TYPE = VAR1 AND A.USRDFN01=VAR2
       ORDER BY A.START_DATE;
       ROWCUR RSTCUR%ROWTYPE;
BEGIN
     FOR ROWCUR IN RSTCUR LOOP
         RST:=RST||ROWCUR.EVALREP_TITLE||':'||TO_CHAR(ROWCUR.RID)||';';
     END LOOP;
     RETURN (RST);
END F_GETRPTINFO;

5.F_GETSEQNUMBYRST

CREATE OR REPLACE FUNCTION F_GETSEQNUMBYRST(RSTID IN INTEGER) RETURN VARCHAR2 IS
  RESULT VARCHAR2(10);
  CURSOR C_LST
  IS
  SELECT B.SEQ_NUM, B.SLOWSICK_ID FROM SLOW_CASERST A
  LEFT JOIN SLOWSICK_CHOICE B ON B.SLOWRST_ID = A.RID
  WHERE A.RID = RSTID;
  C_ROW C_LST%ROWTYPE;
BEGIN
  FOR C_ROW IN C_LST LOOP
      IF C_ROW.SLOWSICK_ID = 3 AND C_ROW.SEQ_NUM IS NOT NULL
         THEN RESULT := C_ROW.SEQ_NUM;
      END IF;
  END LOOP;
  RETURN(RESULT);
END F_GETSEQNUMBYRST;

6.F_GETSICNAMESBYRST

--增加慢病个案报告结果表字段：数据字段类型DATA_TYPE,数据类型为INTEGER
CREATE OR REPLACE FUNCTION F_GETSICNAMESBYRST(RSTID IN INTEGER) RETURN VARCHAR2 IS
  RESULT VARCHAR2(200);

  CURSOR C_LST
  IS
  SELECT C.SLOWSICKHISTABLE_CODE, C.SLOWSICKHISTABLE_NAME FROM SLOW_CASERST A
  LEFT JOIN SICKHIS_RST_CONNECT B ON B.RST_ID = A.RID
  LEFT JOIN SLOWSICKHISTABLE C ON C.RECORD_ID = B.SICKHIS_ID
  WHERE A.RID = RSTID;
  C_ROW C_LST%ROWTYPE;
BEGIN
  FOR C_ROW IN C_LST LOOP
      RESULT := RESULT || C_ROW.SLOWSICKHISTABLE_NAME || ',';
  END LOOP;
  RESULT := SUBSTR(RESULT,1,LENGTH(RESULT)-1);
  RETURN(RESULT);
END F_GETSICNAMESBYRST;

7. F_TO_T_IN

CREATE OR REPLACE FUNCTION F_TO_T_IN (P_IN CLOB) RETURN T_IN AS
V_RETURN T_IN DEFAULT T_IN();
 V_IN VARCHAR2(4000);
 V_COUNT NUMBER DEFAULT 0;
 BEGIN
 V_IN := REPLACE(P_IN || ',', CHR(10), '');
 WHILE(INSTR(V_IN, ',') > 0) LOOP
 V_RETURN.EXTEND;
 V_COUNT := V_COUNT + 1;
 V_RETURN(V_COUNT) := SUBSTR(V_IN, 1, INSTR(V_IN, ',') - 1);
 V_IN := SUBSTR(V_IN, INSTR(V_IN, ',') + 1);
 END LOOP;
 RETURN V_RETURN;
 END;

8.F_TO_T_VAR

CREATE OR REPLACE FUNCTION F_TO_T_VAR (P_VAR CLOB) RETURN T_VAR AS
V_RETURN T_VAR DEFAULT T_VAR();
 V_VAR VARCHAR2(32000);
 V_COUNT NUMBER DEFAULT 0;
 BEGIN
 V_VAR := REPLACE(P_VAR || ',', CHR(10), '');
 WHILE(INSTR(V_VAR, ',') > 0) LOOP
 V_RETURN.EXTEND;
 V_COUNT := V_COUNT + 1;
 V_RETURN(V_COUNT) := SUBSTR(V_VAR, 1, INSTR(V_VAR, ',') - 1);
 V_VAR := SUBSTR(V_VAR, INSTR(V_VAR, ',') + 1);
 END LOOP;
 RETURN V_RETURN;
 END;

9.F_WEEK_TO_DATE

CREATE OR REPLACE FUNCTION F_WEEK_TO_DATE(A_WEEK VARCHAR2) RETURN CHAR IS
  V_FIRST_DATE   CHAR(10);
  V_DATE_OF_WEEK NUMBER(1);

BEGIN
  SELECT TO_CHAR(TO_DATE(SUBSTR(A_WEEK, 1, 4) || '0101', 'YYYYMMDD'), 'D')
    INTO V_DATE_OF_WEEK
    FROM DUAL;
  V_DATE_OF_WEEK := V_DATE_OF_WEEK - 1;
  IF V_DATE_OF_WEEK <= 4 THEN
    SELECT TO_CHAR(TO_DATE(SUBSTR(A_WEEK, 1, 4) || '0101', 'YYYYMMDD') +
                   SUBSTR(A_WEEK, 5, 2) * 7 - 7 - V_DATE_OF_WEEK + 1,
                   'YYYY-MM-DD')
      INTO V_FIRST_DATE
      FROM DUAL;
  ELSE
    SELECT TO_CHAR(TO_DATE(SUBSTR(A_WEEK, 1, 4) || '0101', 'YYYYMMDD') +
                   SUBSTR(A_WEEK, 5, 2) * 7 - V_DATE_OF_WEEK + 1,
                   'YYYY-MM-DD')
      INTO V_FIRST_DATE
      FROM DUAL;
  END IF;

  RETURN V_FIRST_DATE;

END;

10.STR2NUMLIST

CREATE OR REPLACE FUNCTION STR2NUMLIST( P_STRING IN VARCHAR2 ) RETURN
           NUMTABLETYPE
           AS
           V_STR LONG DEFAULT P_STRING || ',';
           V_N NUMBER;
           V_DATA NUMTABLETYPE := NUMTABLETYPE();
           BEGIN
           LOOP
           V_N := TO_NUMBER(INSTR( V_STR, ',' ));
          EXIT WHEN (NVL(V_N,0) = 0);
          V_DATA.EXTEND;
          V_DATA( V_DATA.COUNT ) := LTRIM(RTRIM(SUBSTR(V_STR,1,V_N-1)));
          V_STR := SUBSTR( V_STR, V_N+1 );
          END LOOP;
          RETURN V_DATA;
          END;

11. wm_concat

CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING ZH_CONCAT_IM;

二、存储过程

1. PKG_BHKANADBTM2

CREATE OR REPLACE PROCEDURE PKG_BHKANADBTM2 AS
  CRPTTEMP VARCHAR(15)  ;
   ERRERSQL VARCHAR(2000);
BEGIN
  DECLARE L_COUNT NUMBER;  INNUM NUMBER;
  --申报和体检都有数据
  CURSOR CURHARMBHK IS SELECT  REPT.YEAR_EMP_COUNT,REPT.PRODUCE_EMP_COUNT,REPT.HARM_EMP_COUNT, BHK.* FROM (
   ( SELECT  T.CRPT_CODE,TO_CHAR(T.RPT_DATE,'YYYY') RPTYEAR,T.YEAR_EMP_COUNT,T.PRODUCE_EMP_COUNT,T.HARM_EMP_COUNT
    FROM TD_HARMITEM_REPORT T
    WHERE T.RID IN (SELECT RID FROM(SELECT T.CRPT_CODE,TO_CHAR(T.RPT_DATE,'YYYY'),MAX(T.RID) RID FROM TD_HARMITEM_REPORT T
    INNER JOIN KHD_USER KHDUSER ON T.CRPT_CODE = KHDUSER.CRPT_CODE
    GROUP BY T.CRPT_CODE,TO_CHAR(T.RPT_DATE,'YYYY')
    ORDER BY T.CRPT_CODE,TO_CHAR(T.RPT_DATE,'YYYY') DESC)
    )) REPT INNER JOIN--申报
    (SELECT T.CRPT_CODE,TO_CHAR(T.BHK_DATE,'YYYY') BHKYEAR,COUNT(T.RID) TC,COUNT(DISTINCT W.ZWTJ_CODE) WC,
    COUNT(DISTINCT G.ZWTJ_CODE) GC FROM TD_ZWTJ_BHK T
    INNER JOIN TD_TJ_BADRSN B ON B.ZWTJ_CODE=T.UPDATE_CODE
    LEFT JOIN TD_WRKTABU_CONN W ON W.ZWTJ_CODE=T.UPDATE_CODE
    LEFT JOIN TD_TARGETDIS_CONN G ON G.ZWTJ_CODE=T.UPDATE_CODE
    INNER JOIN KHD_USER KHDUSER ON T.CRPT_CODE = KHDUSER.CRPT_CODE
    GROUP BY T.CRPT_CODE,TO_CHAR(T.BHK_DATE,'YYYY')
    ORDER BY T.CRPT_CODE, TO_CHAR(T.BHK_DATE,'YYYY') DESC ) BHK
    ON REPT.RPTYEAR=BHK.BHKYEAR AND REPT.CRPT_CODE=BHK.CRPT_CODE); --体检
  --体检数据
  CURSOR CURBHK IS  SELECT
   CASE WHEN  REPT.YEAR_EMP_COUNT  IS NULL THEN 0 END AS REPT_YEAREMPCOUNT, --年末职工数
   CASE WHEN  REPT.PRODUCE_EMP_COUNT IS NULL THEN 0 END AS REPT_PRODUCEEMPCOUNT, --生产工人数
   CASE WHEN  REPT.HARM_EMP_COUNT IS NULL THEN 0 END AS REPT_HARMEMPCOUNT, --接害人数
   BHK.* FROM ((
     SELECT  T.CRPT_CODE,TO_CHAR(T.RPT_DATE,'YYYY') RPTYEAR,T.YEAR_EMP_COUNT,T.PRODUCE_EMP_COUNT,T.HARM_EMP_COUNT
    FROM TD_HARMITEM_REPORT T WHERE
    T.RID IN (
    SELECT RID FROM(
    SELECT T.CRPT_CODE,TO_CHAR(T.RPT_DATE,'YYYY'),MAX(T.RID) RID FROM TD_HARMITEM_REPORT T
    INNER JOIN KHD_USER KHDUSER ON T.CRPT_CODE = KHDUSER.CRPT_CODE
    GROUP BY T.CRPT_CODE,TO_CHAR(T.RPT_DATE,'YYYY')
    ORDER BY T.CRPT_CODE,TO_CHAR(T.RPT_DATE,'YYYY') DESC)) ) REPT--申报
    RIGHT JOIN   (
     SELECT T.CRPT_CODE,TO_CHAR(T.BHK_DATE,'YYYY') BHKYEAR,COUNT(T.RID) TC,COUNT(DISTINCT W.ZWTJ_CODE) WC,
    COUNT(DISTINCT G.ZWTJ_CODE) GC FROM TD_ZWTJ_BHK T
    INNER JOIN TD_TJ_BADRSN B ON B.ZWTJ_CODE=T.UPDATE_CODE
    LEFT JOIN TD_WRKTABU_CONN W ON W.ZWTJ_CODE=T.UPDATE_CODE
    LEFT JOIN TD_TARGETDIS_CONN G ON G.ZWTJ_CODE=T.UPDATE_CODE

    INNER JOIN KHD_USER KHDUSER ON T.CRPT_CODE = KHDUSER.CRPT_CODE
    --WHERE T.CRPT_CODE='320207000263'
    GROUP BY T.CRPT_CODE,TO_CHAR(T.BHK_DATE,'YYYY')
    ORDER BY T.CRPT_CODE, TO_CHAR(T.BHK_DATE,'YYYY') DESC ) BHK--体检
    ON REPT.RPTYEAR=BHK.BHKYEAR AND REPT.CRPT_CODE=BHK.CRPT_CODE
    ) WHERE RPTYEAR IS   NULL;

  --申报数据
  CURSOR CURHARM IS
   SELECT REPT.*,
   CASE WHEN  BHK.TC  IS NULL THEN 0 END AS BHKTC,
   CASE WHEN  BHK.WC  IS NULL THEN 0 END AS BHKWC ,
   CASE WHEN  BHK.GC  IS NULL THEN 0 END AS BHKGC
   FROM ((SELECT  T.CRPT_CODE,TO_CHAR(T.RPT_DATE,'YYYY') RPTYEAR,T.YEAR_EMP_COUNT,T.PRODUCE_EMP_COUNT,T.HARM_EMP_COUNT
    FROM TD_HARMITEM_REPORT T WHERE --T.CRPT_CODE='320207000263' AND
    T.RID IN (
    SELECT RID FROM(
    SELECT T.CRPT_CODE,TO_CHAR(T.RPT_DATE,'YYYY'),MAX(T.RID) RID FROM TD_HARMITEM_REPORT T
     INNER JOIN KHD_USER KHDUSER ON T.CRPT_CODE = KHDUSER.CRPT_CODE
    GROUP BY T.CRPT_CODE,TO_CHAR(T.RPT_DATE,'YYYY')
    ORDER BY T.CRPT_CODE,TO_CHAR(T.RPT_DATE,'YYYY') DESC))) REPT --申报
    LEFT  JOIN (
     SELECT T.CRPT_CODE,TO_CHAR(T.BHK_DATE,'YYYY') BHKYEAR,COUNT(T.RID) TC,COUNT(DISTINCT W.ZWTJ_CODE) WC,
    COUNT(DISTINCT G.ZWTJ_CODE) GC FROM TD_ZWTJ_BHK T
    INNER JOIN TD_TJ_BADRSN B ON B.ZWTJ_CODE=T.UPDATE_CODE
    LEFT JOIN TD_WRKTABU_CONN W ON W.ZWTJ_CODE=T.UPDATE_CODE
    LEFT JOIN TD_TARGETDIS_CONN G ON G.ZWTJ_CODE=T.UPDATE_CODE
     INNER JOIN KHD_USER KHDUSER ON T.CRPT_CODE = KHDUSER.CRPT_CODE
    --WHERE T.CRPT_CODE='320207000263'
    GROUP BY T.CRPT_CODE,TO_CHAR(T.BHK_DATE,'YYYY')
    ORDER BY T.CRPT_CODE, TO_CHAR(T.BHK_DATE,'YYYY') DESC ) BHK--体检
    ON REPT.RPTYEAR=BHK.BHKYEAR AND REPT.CRPT_CODE=BHK.CRPT_CODE
    ) WHERE BHKYEAR IS   NULL;

  BEGIN
     SELECT MAX(RID) INTO INNUM FROM TD_BHKANALY_MAIN;
     L_COUNT:=1;
     IF INNUM>=1 THEN
      L_COUNT:=INNUM+1;
     END IF;
     DBMS_OUTPUT.PUT_LINE(L_COUNT);

     FOR RECB IN CURHARMBHK LOOP  --循环插入(申报和体检都有数据)
       BEGIN
          INSERT INTO TD_BHKANALY_MAIN VALUES(L_COUNT,RECB.CRPT_CODE,NULL,RECB.BHKYEAR,1);
          INSERT INTO TD_BHKANALY_SUB(MAIN_ID,YEAR_EMP_COUNT,PRODUCE_EMP_COUNT,HARM_EMP_COUNT,BHK_COUNT,DISQUA_COUNT,DISQUA_RATE,WRKTABU_COUNT,TARGETDIS_COUNT)
          VALUES (L_COUNT,RECB.YEAR_EMP_COUNT,RECB.PRODUCE_EMP_COUNT,RECB.HARM_EMP_COUNT,RECB.TC,(RECB.WC+RECB.GC),(RECB.WC+RECB.GC)/RECB.TC,RECB.WC,RECB.GC );
       END;

       L_COUNT:=L_COUNT+1;
       CRPTTEMP := RECB.CRPT_CODE;
       COMMIT;
      END LOOP;

      FOR RECB IN CURBHK LOOP --循环插入(体检数据)
       BEGIN
          INSERT INTO TD_BHKANALY_MAIN VALUES(L_COUNT,RECB.CRPT_CODE,NULL,RECB.BHKYEAR,1);
          INSERT INTO TD_BHKANALY_SUB(MAIN_ID,YEAR_EMP_COUNT,PRODUCE_EMP_COUNT,HARM_EMP_COUNT,BHK_COUNT,DISQUA_COUNT,DISQUA_RATE,WRKTABU_COUNT,TARGETDIS_COUNT)
          VALUES (L_COUNT,0,0,0,RECB.TC,(RECB.WC+RECB.GC),(RECB.WC+RECB.GC)/RECB.TC,RECB.WC,RECB.GC );
           L_COUNT:=L_COUNT+1;
           CRPTTEMP := RECB.CRPT_CODE;
           COMMIT;
         END;
      END LOOP;


      FOR RECB IN CURHARM LOOP --循环插入(申报数据)
       BEGIN
          INSERT INTO TD_BHKANALY_MAIN VALUES(L_COUNT,RECB.CRPT_CODE,NULL,RECB.RPTYEAR,1);
          INSERT INTO TD_BHKANALY_SUB(MAIN_ID,YEAR_EMP_COUNT,PRODUCE_EMP_COUNT,HARM_EMP_COUNT,BHK_COUNT,DISQUA_COUNT,DISQUA_RATE,WRKTABU_COUNT,TARGETDIS_COUNT)
          VALUES (L_COUNT, RECB.YEAR_EMP_COUNT,RECB.PRODUCE_EMP_COUNT,RECB.HARM_EMP_COUNT,0,0,0,0,0 );
           L_COUNT:=L_COUNT+1;
           CRPTTEMP := RECB.CRPT_CODE;
          COMMIT;
         END;
      END LOOP;
  END;

  EXCEPTION
     WHEN OTHERS THEN
     BEGIN
       --DBMS_OUTPUT.PUT_LINE(SQLERRM);
       --ROLLBACK;
       ROLLBACK;
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
       ERRERSQL :=  SQLERRM;
       INSERT INTO KHD_TIMEORLOG VALUES    (KHDSYSLOG_SEQUENCE.NEXTVAL,CRPTTEMP,'职业健康监护分析',
        'PKG_BHKANADBTM2' ,  NULL,  ERRERSQL  , SYSDATE);
       COMMIT;
     END;
END PKG_BHKANADBTM2;

2.PKG_BHKANATIME

CREATE OR REPLACE PROCEDURE PKG_BHKANATIME IS
   CRPTTEMP VARCHAR(15)  ;
   ERRERSQL VARCHAR(2000);
BEGIN
  DECLARE L_COUNT NUMBER;

  --L_T TD_ZWTJ_BHK%ROWTYPE;

  CURSOR CUR_T IS
    SELECT T.CRPT_CODE,T.BHK_DATE FROM TD_ZWTJ_BHK T
    INNER JOIN TD_TJ_BADRSN B ON B.ZWTJ_CODE=T.UPDATE_CODE
    INNER JOIN XT_NOSAFE_VINDI V ON V.FACTOR_CODE=B.BADRSN_CODE
     INNER JOIN KHD_USER KHDUSER ON T.CRPT_CODE = KHDUSER.CRPT_CODE
    GROUP BY T.CRPT_CODE,T.BHK_DATE;
  BEGIN
    DELETE FROM TD_BHKANALY_SUB;
    DELETE FROM TD_BHKANALY_MAIN;
     --OPEN CUR_T;
     --WHILE CUR_T%FOUND LOOP
     L_COUNT:=1;
     FOR REC IN CUR_T LOOP
       --DBMS_OUTPUT.PUT_LINE(REC.BHK_DATE);
       CRPTTEMP := REC.CRPT_CODE;
      INSERT INTO TD_BHKANALY_MAIN VALUES(L_COUNT,REC.CRPT_CODE,TO_DATE(TO_CHAR(REC.BHK_DATE,'YYYY-MM-DD'),'YYYY-MM-DD'),NULL,0);
      INSERT INTO TD_BHKANALY_SUB(MAIN_ID,TYPE_CODE,TYPE_NAME,BHK_COUNT,DISQUA_COUNT,DISQUA_RATE,WRKTABU_COUNT,TARGETDIS_COUNT) (SELECT L_COUNT,B.BADRSN_CODE,V.FACTOR_NAME,COUNT(T.RID),(COUNT(DISTINCT W.ZWTJ_CODE)+COUNT(DISTINCT G.ZWTJ_CODE)),CASE WHEN COUNT(T.RID)=0 THEN 0 ELSE (COUNT(DISTINCT W.ZWTJ_CODE)+COUNT(DISTINCT G.ZWTJ_CODE))/COUNT(T.RID) END,COUNT(DISTINCT W.ZWTJ_CODE),COUNT(DISTINCT G.ZWTJ_CODE) FROM TD_ZWTJ_BHK T INNER JOIN TD_TJ_BADRSN B ON B.ZWTJ_CODE=T.UPDATE_CODE AND T.CRPT_CODE=REC.CRPT_CODE AND TO_CHAR(T.BHK_DATE,'YYYY-MM-DD')=TO_CHAR(REC.BHK_DATE,'YYYY-MM-DD') INNER JOIN XT_NOSAFE_VINDI V ON V.FACTOR_CODE=B.BADRSN_CODE LEFT JOIN TD_WRKTABU_CONN W ON W.ZWTJ_CODE=T.UPDATE_CODE LEFT JOIN TD_TARGETDIS_CONN G ON G.ZWTJ_CODE=T.UPDATE_CODE GROUP BY B.BADRSN_CODE,V.FACTOR_NAME);
      L_COUNT:=L_COUNT+1;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(L_COUNT);
      COMMIT;
  END;

  EXCEPTION
     WHEN OTHERS THEN
     BEGIN
       ROLLBACK;
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
       ERRERSQL :=  SQLERRM;
       INSERT INTO KHD_TIMEORLOG VALUES    (KHDSYSLOG_SEQUENCE.NEXTVAL,CRPTTEMP,'职业健康监护分析',
        'PKG_BHKANATIME' ,  NULL,  ERRERSQL  , SYSDATE);
       COMMIT;
     END;
END PKG_BHKANATIME;

3.RPT_FX_DEAL

CREATE OR REPLACE PROCEDURE RPT_FX_DEAL IS
BEGIN
--已经上报的月份，以及报表主表ID
DECLARE RPTSUB_ID INTEGER;
  CURSOR DATACUR IS
         SELECT A3.YEAR_, A3.MONTH_, LISTAGG(A3.REPSUB_ID, ',')
         WITHIN GROUP (ORDER BY A3.YEAR_, A3.MONTH_) ALL_FORMS
         FROM (
         SELECT A2.REPSUB_ID, SUM(A2.YEAR_) AS YEAR_, SUM(A2.MONTH_) AS MONTH_ FROM
         (
         SELECT A1.REPSUB_ID, DECODE(A1.HZID_DATA,7, A1.DESCRIP) AS YEAR_,
         DECODE(A1.HZID_DATA,8, A1.DESCRIP) AS MONTH_
         FROM BX_SELRST_BE A1 WHERE A1.DESMTD_ID = 78
         ) A2 GROUP BY A2.REPSUB_ID ORDER BY YEAR_,MONTH_
         ) A3 GROUP BY A3.YEAR_, A3.MONTH_;
  BEGIN
      FOR DATACUR_ROW IN DATACUR LOOP
          RPT_FX_DEALDETAIL(DATACUR_ROW.YEAR_,DATACUR_ROW.MONTH_,DATACUR_ROW.ALL_FORMS);
      END LOOP;
      COMMIT;
  END;
  EXCEPTION
      WHEN OTHERS THEN
      BEGIN
      ROLLBACK;
      END;
END RPT_FX_DEAL;

 
 
 

4.RPT_FX_DEALDETAIL

CREATE OR REPLACE PROCEDURE RPT_FX_DEALDETAIL(YEAR_ IN VARCHAR2,
       MONTH_ IN VARCHAR2,ALL_FORMS IN VARCHAR2) IS
BEGIN
--已经上报的月份，以及报表主表ID
DECLARE RPTSUB_ID INTEGER;
  CURSOR DATACUR IS
         SELECT C2.ROW_NUM AS ROW_NUM, SUM(C2.USRDFN1) USRDFN1, SUM(C2.USRDFN2) USRDFN2, SUM(C2.USRDFN3) USRDFN3,
         SUM(C2.USRDFN4) USRDFN4,SUM(C2.USRDFN5) USRDFN5,SUM(C2.USRDFN6) USRDFN6,SUM(C2.USRDFN7) USRDFN7,
         SUM(C2.USRDFN8) USRDFN8,SUM(C2.USRDFN9) USRDFN9,SUM(C2.USRDFN10) USRDFN10,SUM(C2.USRDFN11) USRDFN11,
         SUM(C2.USRDFN12) USRDFN12,SUM(C2.USRDFN13) USRDFN13,SUM(C2.USRDFN14) USRDFN14,SUM(C2.USRDFN15) USRDFN15,
         SUM(C2.USRDFN16) USRDFN16
         FROM (
         SELECT DECODE(C1.ROW_NUM,3,5,4,3,5,4,6,6,7,4,8,4,9,3,10,3,11,4,12,5,13,6,14,7,15,8,16,9,17,10,18,11,19,12,20,13,C1.ROW_NUM) AS ROW_NUM,
         C1.USRDFN1,C1.USRDFN2,C1.USRDFN3,C1.USRDFN4,C1.USRDFN5,C1.USRDFN6,C1.USRDFN7,
         C1.USRDFN8,C1.USRDFN9,C1.USRDFN10,C1.USRDFN11,C1.USRDFN12,C1.USRDFN13,C1.USRDFN14,
         C1.USRDFN15,C1.USRDFN16
         FROM BX_RPT_RST C1 WHERE C1.REPSUB_ID IN (SELECT * FROM THE (SELECT CAST(STR2NUMLIST(ALL_FORMS) AS NUMTABLETYPE ) FROM DUAL)) AND C1.ROW_NUM <> 19
         ) C2 GROUP BY C2.ROW_NUM
         UNION
         SELECT 12 AS ROW_NUM, SUM(C2.USRDFN1) USRDFN1, SUM(C2.USRDFN2) USRDFN2, SUM(C2.USRDFN3) USRDFN3,
         SUM(C2.USRDFN4) USRDFN4,SUM(C2.USRDFN5) USRDFN5,SUM(C2.USRDFN6) USRDFN6,SUM(C2.USRDFN7) USRDFN7,
         SUM(C2.USRDFN8) USRDFN8,SUM(C2.USRDFN9) USRDFN9,SUM(C2.USRDFN10) USRDFN10,
         SUM(C2.USRDFN11) USRDFN11,SUM(C2.USRDFN12) USRDFN12,SUM(C2.USRDFN13) USRDFN13,SUM(C2.USRDFN14) USRDFN14,
         SUM(C2.USRDFN15) USRDFN15,SUM(C2.USRDFN16) USRDFN16
         FROM BX_RPT_RST C2 WHERE C2.REPSUB_ID IN (SELECT * FROM THE (SELECT CAST(STR2NUMLIST(ALL_FORMS) AS NUMTABLETYPE ) FROM DUAL)) AND C2.ROW_NUM < 19;

   BEGIN
      SELECT BX_MTD.NEXTVAL INTO RPTSUB_ID FROM DUAL;
      INSERT INTO BX_RPTSUB(RID,RPTMAIN_ID,TABHEAD_NAME,FILLUNIT_CODE,
             FILLUNIT_NAME,FILLER,FILL_DATE,STATE_MARK,UPDATE_TIME,IF_GATHER,USRDFN3)
             VALUES(RPTSUB_ID,178,'无锡市腹泻病门诊报表new','32020001',
             '无锡市疾病预防控制中心','施超',TO_DATE(YEAR_||'-'||MONTH_||'-01','YYYY-MM-DD')+31,'3',SYSDATE,0,'semi');
      INSERT INTO BX_SELRST_BE(RID,REPSUB_ID,DESMTD_ID,HZID_DATA,DESCRIP)
             VALUES(BX_MTD.NEXTVAL,RPTSUB_ID,84,7,YEAR_);
      INSERT INTO BX_SELRST_BE(RID,REPSUB_ID,DESMTD_ID,HZID_DATA,DESCRIP)
             VALUES(BX_MTD.NEXTVAL,RPTSUB_ID,84,8,MONTH_);


      FOR DATACUR_ROW IN DATACUR LOOP

          INSERT INTO BX_RPT_RST(RID,REPMAIN_ID,REPSUB_ID,ROW_NUM,USRDFN1,USRDFN2,
                 USRDFN3,USRDFN4,USRDFN5,USRDFN6,USRDFN7,USRDFN8,USRDFN9,USRDFN10,
                 USRDFN11,USRDFN12,USRDFN13,USRDFN14,USRDFN15,USRDFN16,USRDFN60,UPDATE_TIME)
                 VALUES(BX_MTD.NEXTVAL,178,RPTSUB_ID,DATACUR_ROW.ROW_NUM,DATACUR_ROW.USRDFN1,
                 DATACUR_ROW.USRDFN2,DATACUR_ROW.USRDFN3,DATACUR_ROW.USRDFN4,DATACUR_ROW.USRDFN5,
                 DATACUR_ROW.USRDFN6,DATACUR_ROW.USRDFN7,DATACUR_ROW.USRDFN8,DATACUR_ROW.USRDFN9,
                 DATACUR_ROW.USRDFN10,DATACUR_ROW.USRDFN11,DATACUR_ROW.USRDFN12,DATACUR_ROW.USRDFN13,
                 DATACUR_ROW.USRDFN14,DATACUR_ROW.USRDFN15,DATACUR_ROW.USRDFN16,'semi',SYSDATE);

      END LOOP;
      COMMIT;

  END;
  EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      ROLLBACK;
END RPT_FX_DEALDETAIL;
 

三、 类型

1. NUMTABLETYPE

CREATE OR REPLACE TYPE "NUMTABLETYPE"                                          AS TABLE OF NUMBER

2. T_IN

CREATE OR REPLACE TYPE "T_IN"                                          IS TABLE OF NUMBER

3. T_VAR

CREATE OR REPLACE TYPE "T_VAR"                                          IS TABLE OF VARCHAR2(4000)

4.ZH_CONCAT_IM

CREATE OR REPLACE TYPE "ZH_CONCAT_IM"                                          AUTHID CURRENT_USER AS OBJECT

(
  CURR_STR VARCHAR2(32767),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
               P1 IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
                                 RETURNVALUE OUT VARCHAR2,
                                 FLAGS IN NUMBER)
                     RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
                    SCTX2 IN  zh_concat_im) RETURN NUMBER
)

四、类型body

1.ZH_CONCAT_IM

CREATE OR REPLACE TYPE BODY "ZH_CONCAT_IM"
IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)
  RETURN NUMBER
  IS
  BEGIN
    SCTX := zh_concat_im(NULL) ;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
          P1 IN VARCHAR2)
  RETURN NUMBER
  IS
  BEGIN
    IF(CURR_STR IS NOT NULL) THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
                                 RETURNVALUE OUT VARCHAR2,
                                 FLAGS IN NUMBER)
    RETURN NUMBER
  IS
  BEGIN
    RETURNVALUE := CURR_STR ;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
                                   SCTX2 IN zh_concat_im)
  RETURN NUMBER
  IS
  BEGIN
    IF(SCTX2.CURR_STR IS NOT NULL) THEN
      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
END;

五、触发器

CREATE OR REPLACE TRIGGER T_MANAGE_OFFICEMARK_I
  AFTER INSERT ON XT_PERSONNEL
  FOR EACH ROW
DECLARE

BEGIN
  UPDATE XT_OFFICE SET OFFICEMARK=OFFICEMARK+1 WHERE RID=:NEW.OFFICE_RID;
END T_MANAGE_OFFICEMARK_I;



CREATE OR REPLACE TRIGGER T_MANAGE_OFFICEMARK_U
  AFTER UPDATE ON XT_PERSONNEL
  FOR EACH ROW
DECLARE

BEGIN
  IF :NEW.OFFICE_RID<>:OLD.OFFICE_RID THEN
    UPDATE XT_OFFICE SET OFFICEMARK=OFFICEMARK-1 WHERE RID=:OLD.OFFICE_RID;
    UPDATE XT_OFFICE SET OFFICEMARK=OFFICEMARK+1 WHERE RID=:NEW.OFFICE_RID;
  END IF;
END T_MANAGE_OFFICEMARK_U;


CREATE OR REPLACE TRIGGER T_MANAGE_OFFICEMARK_D
  AFTER DELETE ON XT_PERSONNEL
  FOR EACH ROW
DECLARE

BEGIN
  UPDATE XT_OFFICE SET OFFICEMARK=OFFICEMARK-1 WHERE RID=:OLD.OFFICE_RID;
END T_MANAGE_OFFICEMARK_D;