proc_rept_qfzl.sql

用户电信帐务系统的存储过程

CREATE OR REPLACE PROCEDURE PROC_REPT_QFZL(out_n_errno OUT NUMBER,
											out_s_errmsg OUT VARCHAR2)
IS

s_subscrbid		VARCHAR2(14);
s_areaid		VARCHAR2(8);
s_billingcyclid	VARCHAR2(8);
n_ownfee		NUMBER;
s_subareaid		VARCHAR2(8);
s_errmsg		VARCHAR2(200);
n_cnt			NUMBER;

CURSOR cur_tab_billdetail IS SELECT subscrbid, areaid, billingcyclid, fee FROM tab_billdetail a;

BEGIN
	COMMIT;
	out_n_errno := 0;
	n_cnt := 0;
	BEGIN
    	DELETE FROM rept_qfzl;
		EXCEPTION
		WHEN OTHERS THEN
			s_errmsg := 'DELETE表rept_qfzl错误: '  || SUBSTR(SQLERRM,1,100);
		    INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, s_errmsg);
		    out_n_errno := 1;
		    out_s_errmsg := s_errmsg;
		    COMMIT;
		    return ;
	END;

	FOR C1 IN cur_tab_billdetail LOOP
	BEGIN
		BEGIN
			SELECT standby2 INTO s_subareaid FROM tab_subscrb WHERE subscrbid = C1.subscrbid;
			EXCEPTION
			WHEN NO_DATA_FOUND THEN
                 s_subareaid :='';
                 s_errmsg := '取表tab_subscrb没有相应数据' || C1.subscrbid;
                 INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, s_errmsg);
                 out_n_errno := 3;
                 out_s_errmsg := s_errmsg;
                 COMMIT;
                 return ;
                 
            WHEN OTHERS THEN
                 s_subareaid := '';
                 s_errmsg := '取表tab_subscrb错误 ' || C1.subscrbid  || SUBSTR(SQLERRM,1,100);
                 INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, s_errmsg);
                 out_n_errno := 3;
                 out_s_errmsg := s_errmsg;
                 COMMIT;
		END;

		BEGIN
			INSERT INTO rept_qfzl(subscrbid, areaid, billingcycl, ownfee, subareaid) VALUES(C1.subscrbid, C1.areaid, C1.billingcyclid, C1.fee, s_subareaid);
			EXCEPTION
			WHEN OTHERS THEN
				s_errmsg := 'INSERT表rept_qfzl错误: '  || SUBSTR(SQLERRM,1,100);
		       	INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, s_errmsg);
		       	out_n_errno := 2;
		    	out_s_errmsg := s_errmsg;
		    	COMMIT;
		    	return ;
		END;

		n_cnt := n_cnt+1;
		IF (n_cnt MOD 5000) = 0 THEN
			COMMIT;
    	END IF;

	END;
	END LOOP;

	COMMIT;

END PROC_REPT_QFZL;