proc_rept_acctfee.sql

一个用在电信帐务系统中的oracle的存储过程

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

s_acctid		VARCHAR2(14);
n_predeposittypeid	NUMBER(10);
s_sourceid		VARCHAR(6);
n_thisbal		NUMBER(10);
s_areaid		VARCHAR2(8);
s_billingcyclid	VARCHAR2(8);
s_stat			CHAR(1);
s_paysn			VARCHAR2(20);
n_balance		NUMBER;
s_subareaid		VARCHAR2(8);
s_errmsg		VARCHAR2(200);
n_lastfee		NUMBER;
n_thisfee		NUMBER;

n_curcycl		NUMBER(1);
s_curbillingcycl	VARCHAR2(20);
s_prebillingcycl	VARCHAR2(20);
s_cyclid		varchar(20);
s_accttype  VARCHAR2(20);
n_cnt			NUMBER;


CURSOR cur_tab_accountpredeposit IS SELECT acctid, predeposittypeid, sourceid, thisbal, areaid
								FROM tab_accountpredeposit a;

CURSOR cur_tab_accountpredepositdtal IS SELECT acctid, predeposittypeid, paysn, balance
								FROM tab_accountpredepositdtal b
								WHERE b.predeposittypeid = n_predeposittypeid
									AND b.acctid = s_acctid
									AND (recordtype = '02' OR recordtype='04');

BEGIN
	COMMIT;
	out_n_errno := 0;
	n_cnt := 0 ;

	BEGIN
    	DELETE FROM rept_acctfee;
		EXCEPTION
		WHEN OTHERS THEN
			s_errmsg := 'DELETE表rept_acctfee错误: '  || 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;
		    return ;
	END;

	/*取当前帐务周期 */
	BEGIN
		SELECT billingcyclid INTO s_curbillingcycl FROM tab_billingcycl WHERE stat='1';
		EXCEPTION
		WHEN NO_DATA_FOUND THEN
			out_s_errmsg := '没有当前帐务周期';
			INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, out_s_errmsg);
			out_n_errno := 2;
			COMMIT;
			RETURN;

       	WHEN OTHERS THEN
			out_s_errmsg := '取tab_billingcycl表错误：' || SUBSTR(SQLERRM,1,100);
			INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, out_s_errmsg);
			out_n_errno := 2;
			COMMIT;
			RETURN;
	END;


	/*取上一帐务周期 */
	BEGIN
		SELECT max(billingcyclid) INTO s_prebillingcycl FROM tab_billingcycl WHERE billingcyclid<s_curbillingcycl;
		EXCEPTION
		WHEN NO_DATA_FOUND THEN
			out_s_errmsg := '没有上一帐务周期';
			INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, out_s_errmsg);
			out_n_errno := 2;
			COMMIT;
			RETURN;

       	WHEN OTHERS THEN
			out_s_errmsg := '取tab_billingcycl表错误：' || SUBSTR(SQLERRM,1,100);
			INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, out_s_errmsg);
			out_n_errno := 2;
			COMMIT;
			RETURN;
	END;

	n_lastfee := 0;
	n_thisfee := 0;

	FOR C1 IN cur_tab_accountpredeposit LOOP

		n_lastfee := 0;
		n_thisfee := 0;
		FOR C3 IN cur_tab_accountpredepositdtal LOOP
			s_cyclid := '';
			SELECT NVL(billingcyclid, '') INTO s_cyclid FROM tab_billdetail WHERE WRTOFFSN=C3.paysn;

			IF (s_cyclid!='') THEN
				IF (s_cyclid = s_prebillingcycl) THEN
					n_thisfee := n_thisfee + C3.balance;
				ELSIF (s_cyclid < s_prebillingcycl) THEN
					n_lastfee := n_lastfee + C3.balance;
				END IF;
			END IF;
		END LOOP;

		BEGIN
			SELECT standby2 INTO s_subareaid FROM tab_subscrb WHERE subscrbid = C1.acctid;
			EXCEPTION
			WHEN NO_DATA_FOUND THEN
               	s_subareaid :='';
               	out_s_errmsg := '取表tab_subscrb没有相应数据' || C1.acctid;
               	INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, out_s_errmsg);
           		out_n_errno := 3;
           		COMMIT;
           		RETURN;

           	WHEN OTHERS THEN
               	s_subareaid := '';
                	out_s_errmsg := '取表tab_subscrb错误 ' || C1.acctid  || SUBSTR(SQLERRM,1,100);
                	INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, out_s_errmsg);
           		out_n_errno := 3;
           		COMMIT;
           		RETURN;
		END;

		BEGIN
			SELECT accttype INTO s_accttype FROM tab_acct WHERE acctid=C1.acctid;
			EXCEPTION
			WHEN NO_DATA_FOUND THEN
				s_accttype := '';
               	out_s_errmsg := '取表tab_accttype没有相应数据' || C1.acctid;
               	INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, out_s_errmsg);
           		out_n_errno := 4;
           		COMMIT;
           		RETURN;
           	WHEN OTHERS THEN
                	s_subareaid := '';
                	out_s_errmsg := '取表tab_accttype错误 ' || C1.acctid  || SUBSTR(SQLERRM,1,100);
                	INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, out_s_errmsg);
           		out_n_errno := 4;
           		COMMIT;
           		RETURN;
		END;

		BEGIN
			INSERT INTO rept_acctfee(acctid, predeposittypeid, source_id, lastfee,
				thisfee, balance, billingcycl, areaid, subareaid, acctypeid)
			VALUES(C1.acctid, C1.predeposittypeid, C1.sourceid, n_lastfee, n_thisfee,
			C1.thisbal, s_curbillingcycl, C1.areaid, s_subareaid, s_accttype);
			EXCEPTION
			WHEN OTHERS THEN
				out_s_errmsg := 'INSERT表tab_subscrb错误: '  || SUBSTR(SQLERRM,1,100);
	       		INSERT INTO rept_log(log_time, log_remark) VALUES(SYSDATE, out_s_errmsg);
	    		out_n_errno := 5;
	    		COMMIT;
	    		RETURN;
		END;

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

	END LOOP;

	COMMIT;

END PROC_REPT_ACCTFEE;