【干货攻略】DM存储过程打印错误代码信息和存储过程SQL耗时长的定位方法

达梦E学 2021-12-23

1791

-----正文-----

本文就DM存储过程问题的排查方法进行简单介绍，主要针对打印错误代码信息和存储过程SQL耗时长的定位方法进行测试验证。

本章内容已在如下环境上测试：

操作系统：银河麒麟V10

数据库：DM8

相关关键字：DM8，存储过程

一、存储过程打印错误代码

在DM数据库存储过程的调试执行过程中，会遇到异常错误无法准确定位的问题，这就造成无法及时准确地对错误进行修改。有没有方法来快速来定位错误的原因呢？答案是肯定的，本文通过实例进行测试，供大家参考使用。

1.1、创建字段类型为数据类型的表

CREATE TABLE "TEST"."DMTEST01"  ("ID" INT) ;

1.2、创建异常记录日志表

CREATE TABLE "TEST"."T_PROC_ERRMSG"
        (
                "SQLCODE"    VARCHAR(50) ,
                "SQLERRM"    VARCHAR(200),
                "EXECDATE"   DATETIME(6)
        );

1.3、存储过程应用示例

create or replace
procedure test.pr_error_code_test
        (
                TEST in varchar2)
                     is
         declare  begin    
        insert into  TEST.DMTEST01
                (id
                ) values
                (TEST
                );
        
         commit;
        --异常错误记录处理
 exception   
when others then
            
        insert into TEST.T_PROC_ERRMSG values
                (   sqlcode, sqlerrm, SYSDATE
                );
        
             print sqlcode;
               print  sqlerrm;
             commit;
                return;
         end;
 /

1.4、执行存储过程（将参数设置为字符串）

call "TEST"."PR_ERROR_CODE_TEST"('a');

1.5、查看异常记录信息

select * from   "TEST"."T_PROC_ERRMSG";

通过以上方法可以定位到存储过程的错误信息。

二、存储过程SQL耗时长的处理方法

存储过程的调试一直很令人头疼，因为里面的动态 SQL 和程序逻辑让人很难上手，所以我们建议他们将 EXECUTE IMMEDIATE 做一次分装，即把执行 SQL 的函数进行封装，函数里记录上我们需要的信息。

2.1、创建日志记录信息表

CREATE TABLE TEST.SQL_TRACE  
        (
                 LOG_NO    INT   IDENTITY(1, 1),
                 USER_NAME VARCHAR             ,
                 BEGIN_TIME DATE               ,
                 END_TIME DATE                 ,
                 SQL_STR   VARCHAR             ,
                 ROW_COUNT INT                 ,
                 SESSION_ID BIGINT             ,
                 IP_ADDRESS VARCHAR            ,
                 CALL_STACK VARCHAR            ,
                 TIMES      INT  
        );

2.2、创建存储过程方法

CREATE OR REPLACE
PROCEDURE EXECSQL
        (
                I_SQLSTRING IN   VARCHAR2)
AS
        V_ROWNUMID   INTEGER;
        V_TIME DATE;
        V_SQLSTRING   VARCHAR2(32767);
        V_SQL         VARCHAR2(8188);
        V_CURTIME     NUMBER(20, 2);
        V_USETIME     NUMBER(20, 2);
        V_SID         NUMBER(30);
        V_SES   V$SESSIONS%ROWTYPE;
        V_CALL_STACK   VARCHAR2(4000);
BEGIN
        V_TIME      :=SYSDATE;
        V_CURTIME   :=DBMS_UTILITY.GET_TIME;
        V_SQLSTRING :=TRIM(I_SQLSTRING);
        --去除SQL语句多余的空格
        WHILE   INSTR(V_SQLSTRING, ' ')>0
        LOOP
                V_SQLSTRING:=REPLACE(V_SQLSTRING,   ' ', ' ');
        END LOOP;
        --执行SQL语句
        EXECUTE IMMEDIATE   V_SQLSTRING;
        V_ROWNUMID  :=SQL%ROWCOUNT;
        V_USETIME   :=(DBMS_UTILITY.GET_TIME-V_CURTIME)/100;
        --运行时间超过0.5秒,记录语句日志
        IF V_USETIME        >=0.5 THEN
                V_SQL       :=TRIM(SUBSTRB(V_SQLSTRING,   1, 4000));
                V_SID       :=   SYS_CONTEXT('USERENV', 'SID');
                V_CALL_STACK:=DBMS_UTILITY.FORMAT_CALL_STACK;
                INSERT
                INTO
                        SQL_TRACE
                        (
                                USER_NAME ,
                                BEGIN_TIME,
                                END_TIME  ,
                                SQL_STR   ,
                                ROW_COUNT ,
                                SESSION_ID,
                                IP_ADDRESS,
                                CALL_STACK,
                                TIMES
                        )
                        VALUES  
                        (
                                SYS_CONTEXT('USERENV',   'CURRENT_SCHEMA'),
                                V_TIME                                    ,
                                SYSDATE                                   ,
                                V_SQL                                     ,
                                V_ROWNUMID                                ,
                                V_SID                                     ,
                                SYS_CONTEXT('USERENV',   'IP_ADDRESS')    ,
                                V_CALL_STACK                              ,
                                V_USETIME
                        );
                commit;
        END IF;
END EXECSQL;

2.3、创建存储过程测试方法

CREATE OR REPLACE
PROCEDURE   "TEST"."PR_ERROR_CODE_TEST" AUTHID DEFINER
is
        declare
        begin
                call   "TEST"."EXECSQL"('delete from   "TEST"."DMTEST01" where id>10;');
        exception
        when others then
                insert into   TEST.T_PROC_ERRMSG values
                        ( sqlcode, sqlerrm, SYSDATE
                        );
                
               print sqlcode;
               print  sqlerrm;
                commit;
        end;