文档首页/ 数据仓库服务 GaussDB(DWS)/ 开发指南/ 标准数仓开发指南(9.1.0.x)/ GaussDB(DWS)开发设计建议/ GaussDB(DWS)存储过程开发规范
更新时间：2024-12-06 GMT+08:00
查看PDF
分享

GaussDB(DWS)存储过程开发规范

建议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. 查看日志（确认业务运行情况）。

规则4.2 存储过程内需避免执行非CREATE类的DDL操作

违反规范的影响：

  • 存储过程是一个大事务，如果有非CREATE类DDL操作（持锁级别高），整个存储过程执行时间窗内会阻塞外部对相关表的访问。

方案建议：

  • 存储过程中不使用非CREATE类的DDL操作，如必须使用，严格评估存储过程耗时和DDL的影响耗时，与外部访问业务错峰执行。

相关文档