数据仓库服务 GAUSSDB(DWS)-GaussDB(DWS)存储过程开发规范:建议4.1 避免使用复杂的存储过程,避免存储过程嵌套

时间:2024-12-24 11:19:59

建议4.1 避免使用复杂的存储过程,避免存储过程嵌套

违反规范的影响

  • 复杂和嵌套的存储过程维护成本高,故障定位难度大,恢复耗时长。

方案建议

  • 不使用存储过程或只使用一层存储过程,不嵌套。
  • 开发存储过程设计对应的日志表,将关键步骤前后的信息记录到日志表中,操作步骤如下。

保存并查看日志操作步骤

  1. 创建日志表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE func_exec_log
    (
    id varchar2(32) default lower(sys_guid()),
    pro_name varchar2(60),
    exec_times int,
    log_date date,
    deal_date date,
    log_mesage text
    );
    

  2. 创建表和导入数据

    1
    2
    CREATE TABLE demo_table(data_id int, data_number int);
    INSERT INTO demo_table values(generate_series(1,1000),generate_series(1,1000));
    

  3. 创建业务存储过程

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    CREATE OR REPLACE FUNCTION demo_table_process(out exe_info text)
    LANGUAGE plpgsql
    AS $$
    declare v_count int;
    pro_result text;
    fun_name   text;
    exec_times   int;
    begin
    fun_name := 'demo_table_process';
    select nvl(max(exec_times), '0') + 1 into exec_times from func_exec_log where pro_name = fun_name;
    --业务表插入数据
    insert into demo_table values (dbms_random.value(1, 1000)::int,generate_series(1, dbms_random.value(10000, 20000)::int));
    get diagnostics v_count = ROW_COUNT;
    exe_info = sysdate || '# step1:insert count:' || v_count || ' rows;';
    --删除业务表指定数据
    delete from demo_table where data_id = dbms_random.value(1, 1000)::int;
    get diagnostics v_count = ROW_COUNT;
    exe_info = exe_info || sysdate || '# step2:delete count:' || v_count || ' rows;';
    --更新业务表数据
    update demo_table set data_number = dbms_random.value(1, 100)::int where data_id = dbms_random.value(1, 1000)::int;
    exe_info = exe_info || sysdate || '# step3:update count:' || sql%rowcount || ' rows';
    --在整个程序结束前记录日志,也可以在每个步骤结束后分别记录日志,也可以创建记录日志的函数供调用,灵活使用即可
    insert into func_exec_log(pro_name, exec_times, log_date, deal_date, log_mesage) values (fun_name,exec_times,sysdate,split_part(regexp_split_to_table(exe_info, ';'), '#', 1),split_part(regexp_split_to_table(exe_info, ';'), '#', 2));
    --EXCEPTION用于保证当insert/update/delete等操作步骤异常退出时,也能够正常记录日志
    EXCEPTION
    WHEN OTHERS THEN
    pro_result := exe_info || sysdate || '# exception error message is: ' || sqlerrm;
    insert into func_exec_log(pro_name, exec_times, log_date, deal_date, log_mesage) values(fun_name,exec_times,sysdate,split_part(regexp_split_to_table(pro_result, ';'), '#', 1),split_part(regexp_split_to_table(pro_result, ';'), '#', 2));
    END; $$;
    

  4. 调用存储过程(正常执行)

    1
    SELECT demo_table_process();
    

  5. 查看日志(确认业务运行情况)

    SELECT * FROM func_exec_log ORDER BY log_date desc,deal_date,log_mesage;

  6. 再次调用存储过程(构造执行异常)。

    SELECT demo_table_process();  --先删除demo_table的data_number列构造异常,之后再调用

  7. 查看日志(确认业务运行情况)。
support.huaweicloud.com/devg-910-dws/dws_04_0106.html