runcate gl_vouchermaxno;--删除最大号表数据(不写回滚段) truncate gl_vouchernosuppl;--删除补号表数据(不写回滚段) delete from gl_vouchermaxno;--删除最大号表数据 delete from gl_vouchernosuppl;--删除补号表数据 --注:如果确定要删除数据的话,删除大量数据用truncate速度很快,但有个缺点是不写回滚段,不能回滚,慎重选择。用delete的方式删除数据,写回滚段,大量数据删除的时候速度很慢。 /*以下语句根据凭证表数据重新插入凭证最大号*/ insert into gl_vouchermaxno (select 0, max(no), a.period, a.pk_glorgbook, max(a.pk_voucher), a.pk_vouchertype, max(ts), a.year from gl_voucher a where a.dr = 0 and (a.year || a.period > (select s.settledyear || s.settledperiod from gl_syssettled s where s.pk_glorgbook = a.pk_glorgbook) or (not exists (select s.settledyear || s.settledperiod from gl_syssettled s where s.pk_glorgbook = a.pk_glorgbook and s.settledyear is not null and s.settledperiod is not null))) group by a.pk_glorgbook, a.year, a.period, a.pk_vouchertype); /*创建一个序列,插补号表数据时用*/ create sequence sttt start with 100000000000000; /*以下语句根据最大号表和凭证表数据查出空号,并将其插入到补号表*/ DECLARE v_orgbook VARCHAR2(20); v_year char(4); v_period char(2); v_vouchertype char(20); CURSOR v_cursor IS SELECT pk_glorgbook,year,period,pk_vouchertype FROM gl_vouchermaxno; v_row v_cursor%ROWTYPE; BEGIN OPEN v_cursor; Loop FETCH v_cursor INTO v_row; v_orgbook := v_row.pk_glorgbook; v_year := v_row.year; v_period :=v_row.period; v_vouchertype := v_row.pk_vouchertype; INSERT INTO gl_vouchernosuppl SELECT 2, b.NO, (SELECT pk_vouchermaxno FROM gl_vouchermaxno WHERE pk_glorgbook = v_orgbook AND YEAR = v_year AND period = v_period AND pk_vouchertype = v_vouchertype), substr(b.pk_glorgbook,16,20) || sttt.NEXTVAL, ts FROM (SELECT a.n AS NO, ts, voucher.pk_voucher,a.pk_glorgbook, nosuppl.pk_vouchermaxno FROM (SELECT ROWNUM AS n, ts AS ts, v_orgbook as pk_glorgbook FROM gl_voucher WHERE ROWNUM <= (SELECT maxno FROM gl_vouchermaxno WHERE pk_glorgbook = v_orgbook AND YEAR = v_year AND period = v_period AND pk_vouchertype = v_vouchertype) and gl_voucher.dr=0) a LEFT OUTER JOIN (SELECT pk_voucher, NO FROM gl_voucher WHERE gl_voucher.pk_glorgbook = v_orgbook AND gl_voucher.YEAR = v_year AND gl_voucher.period = v_period AND gl_voucher.pk_vouchertype = v_vouchertype AND gl_voucher.dr = 0) voucher ON voucher.NO = a.n LEFT OUTER JOIN (SELECT NO, pk_vouchermaxno FROM gl_vouchernosuppl WHERE gl_vouchernosuppl.pk_vouchermaxno = (SELECT pk_vouchermaxno FROM gl_vouchermaxno WHERE pk_glorgbook = v_orgbook AND YEAR = v_year AND period = v_period AND pk_vouchertype = v_vouchertype)) nosuppl ON a.n = nosuppl.NO ) b WHERE b.pk_voucher IS NULL AND pk_vouchermaxno IS NULL; EXIT WHEN v_cursor%NOTFOUND; end Loop; close v_cursor; end; /*删除序列*/ drop sequence sttt
g_vouchernosuppl 表几千万条数据的问题
最新推荐文章于 2022-05-16 19:56:31 发布