oracle v￥bh,ORACLE的一些函数、存储过程范例-CSDN博客

一、函数

1. F_GETDIGISTSBYRST

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

RESULT VARCHAR2(1000);

CURSOR C_JOB

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

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

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

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

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

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"

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)

RETURN NUMBER

BEGIN

SCTX := zh_concat_im(NULL) ;

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,

P1 IN VARCHAR2)

RETURN NUMBER

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

BEGIN

RETURNVALUE := CURR_STR ;

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,

SCTX2 IN zh_concat_im)

RETURN NUMBER

BEGIN

IF(SCTX2.CURR_STR IS NOT NULL) THEN

SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;

END IF;

RETURN ODCICONST.SUCCESS;

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;