调试存储过程与declare语句差异

 当应用有调用存储过程，而节点有几十个或者上百个，找错是不是一个很麻烦的事情，这个时候，我建议写到数据库中，下面是我做的一个demo.

1、建立错误日志记录表

drop table PUB_PROC_ERR_LOG purge;

create table PUB_PROC_ERR_LOG

 (

   LOG_ID      NUMBER,

   MODULE_NAME VARCHAR2(100),

   PROC_NAME   VARCHAR2(100),

  ERR_TIME    DATE,

   SQL_CODE    VARCHAR2(50),

   SQL_ERRM    VARCHAR2(100),

   ERR_CONTENT VARCHAR2(500)

 );

comment on column PUB_PROC_ERR_LOG.LOG_ID is '主键';

comment on column PUB_PROC_ERR_LOG.MODULE_NAME  is '模块名称';

comment on column PUB_PROC_ERR_LOG.PROC_NAME  is '存储过程名称';

comment on column PUB_PROC_ERR_LOG.ERR_TIME  is '报错时间';

comment on column PUB_PROC_ERR_LOG.SQL_CODE  is 'SQLCODE';

comment on column PUB_PROC_ERR_LOG.SQL_ERRM  is 'SQLERRM';

comment on column PUB_PROC_ERR_LOG.ERR_CONTENT  is '报错的具体行';

2、表主键的序列

create sequence SEQ_RECORD_PROC_ERR

 minvalue 1

 maxvalue 9999999999999999999999999999

 start with 21

 increment by 1

cache 20;

3、通用记录错误存储过程,用自治事务

CREATE OR REPLACE PROCEDURE

 record_proc_err_log(module_name varchar2,

                 proc_name   varchar2,

                 v_SQLCODE   varchar2,

                 v_SQLERRM   varchar2,

                 v_err_line  varchar2) is

   PRAGMA AUTONOMOUS_TRANSACTION;

 BEGIN

   insert into pub_proc_err_log

     (log_id,

      module_name,

      proc_name,

      err_time,

      sql_code,

      sql_errm,

      err_content)

   values

     (seq_record_proc_err.nextval,

      module_name,

      proc_name,

      sysdate,

      v_SQLCODE,

      v_SQLERRM,

      v_err_line);

   commit;

 END record_proc_err_log;

4、测试

create or replace procedure proce_test is

begin

  for rec in (SELECT T1.*

                FROM XX.V_PROD_INST_INFO T1, AUDI_SAMPLE_NM T2

               where t1.PROD_INST_ID = t2.prod_inst_id) loop

    begin

      INSERT INTO ASA_PROD_INFO_PROV_NM_BK

        (PROV_CODE,

         PROD_INST_ID,

         PROD_ID,

         EXT_PROD_ID,

         ACC_NUM,

         ACCOUNT,

         PAYMENT_MODE_CD,

         OWNER_CUST_ID,

         STATUS_CD,

         AUDI_DATE,

         AUDI_BATCH)

      values

        ('NM',

         rec.PROD_INST_ID,

         rec.PROD_ID,

         rec.EXT_PROD_ID,

         rec.ACC_NUM,

         rec.ACCOUNT,

         rec.PAYMENT_MODE_CD,

         rec.OWNER_CUST_ID,

         rec.STATUS_CD,

         sysdate,

         '2017-12');

      commit;

    　　Exception

      　　WHEN OTHERS

　　　　　　Then

        record_proc_err_log('moduleName',

                            'proce_test()',

                            SQLCODE,

                            SQLERRM,

                            substr(dbms_utility.format_error_backtrace,1,400));

    end;

 end loop;

end proce_test;

5、编译存过调试存过，输入参数，点击放大镜（开始调试器）开始debug存过

6、执行存过（如下两种方式）：

等价于如下存过/语句块，给表里面插入数据，并显示错误信息。此种方式记录的错误信息在语句执行结束后在输出窗口显示

create or replace procedure proce_test is

  --is下面为变量声明区域

  iStep   number;

  iCount  number;

begin

  --变量初始化区域

  iStep  := 0;

  iCount := 0;

   <<outer_loop>>

  for rec in (SELECT T1.*

                FROM XX.V_PROD_INST_INFO T1, AUDI_SAMPLE_NM T2

               where t1.PROD_INST_ID = t2.prod_inst_id) loop

    begin

      INSERT INTO ASA_PROD_INFO_PROV_NM_BK

        (PROV_CODE,

         PROD_INST_ID,

         PROD_ID,

         EXT_PROD_ID,

         ACC_NUM,

         ACCOUNT,

         PAYMENT_MODE_CD,

         OWNER_CUST_ID,

         STATUS_CD,

         AUDI_DATE,

         AUDI_BATCH)

      values

        ('NM',

         rec.PROD_INST_ID,

         rec.PROD_ID,

         rec.EXT_PROD_ID,

         rec.ACC_NUM,

         rec.ACCOUNT,

         rec.PAYMENT_MODE_CD,

         rec.OWNER_CUST_ID,

         rec.STATUS_CD,

         sysdate,

         '2017-12');

      commit;

    　　Exception

      　　WHEN OTHERS

　　　　　　Then

        record_proc_err_log('moduleName',

                            'proce_test()',

                            SQLCODE,

                            SQLERRM,

                            substr(dbms_utility.format_error_backtrace,1,400));

		end;

		exit outer_loop;

    iStep  := iStep + 1;

    iCount := iCount + 1;

    if iStep = 2000 then

      iStep := 0;

      insert into CS_COUNT_LOG values ('CP', sysdate, iCount);

      commit;

    end if;

  end loop;

  insert into CS_COUNT_LOG values ('CP', sysdate, iCount);

  commit;

end;

end proce_test;

declare

  iStep   number;

  iCount  number;

  sErrstr varchar2(1024);

begin

  iStep  := 0;

  iCount := 0; 

  <<outer_loop>>

  for rec in (SELECT T1.*

                FROM DC_ALL_NM.V_PROD_INST_INFO T1, AUDI_SAMPLE_NM T2

               where t1.PROD_INST_ID = t2.prod_inst_id) loop 

    begin   

      INSERT INTO ASA_PROD_INFO_PROV_NM_BK

        (PROV_CODE,

         PROD_INST_ID,

         PROD_ID,

         EXT_PROD_ID,

         ACC_NUM,

         ACCOUNT,

         PAYMENT_MODE_CD,

         OWNER_CUST_ID,

         STATUS_CD,

         AUDI_DATE,

         AUDI_BATCH)

      values

        ('NM',

         rec.PROD_INST_ID,

         rec.PROD_ID,

         rec.EXT_PROD_ID,

         rec.ACC_NUM,

         rec.ACCOUNT,

         rec.PAYMENT_MODE_CD,

         rec.OWNER_CUST_ID,

         rec.STATUS_CD,

         sysdate,

         '2017-12');

    exception

      when others then

        sErrstr := '写表 xxxxxx 出错SQL:' || rec.PROD_INST_ID ||

                   rec.PAYMENT_MODE_CD || rec.PROD_ID || rec.EXT_PROD_ID ||

                   rec.OWNER_CUST_ID || ',SQL??:' || SQLCODE || ',' ||

                   Sqlerrm || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;       

        exit outer_loop;

        return;

    end; 

    iStep  := iStep + 1;

    iCount := iCount + 1;

    if iStep = 2000 then

      iStep := 0;

      insert into CS_COUNT_LOG values ('CP', sysdate, iCount);

      commit;

    end if;

  end loop; 

  dbms_output.put_line(sErrstr); 

  insert into CS_COUNT_LOG values ('CP', sysdate, iCount);

  commit;

end;

/

巴特西

调试存储过程与declare语句差异

最新文章

热门文章