# DWS存储过程开发规范
#### 建议5.1 避免使用复杂的存储过程，避免存储过程嵌套
![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png)
**违反规范的影响**：
- 复杂和嵌套的存储过程维护成本高，故障定位难度大，恢复耗时长。
**方案建议**：
- 不使用存储过程或只使用一层存储过程，不嵌套。
- 开发存储过程设计对应的日志表，将关键步骤前后的信息记录到日志表中，操作步骤如下。
 
保存并查看日志操作步骤。
1. **创建日志表** 。
   
   ```
   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_message text
   );
   ```
   
   
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. **创建业务存储过程** 。
   
   ```
   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_message) 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_message) 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. **调用存储过程（正常执行）** 。
   
   ```
   SELECT demo_table_process();
   ```
   
   
5. **查看日志（确认业务运行情况）** 。
   
   ```
   SELECT * FROM func_exec_log ORDER BY log_date desc,deal_date,log_message;
   ```
   ![](https://support.huaweicloud.com/devg-dws/figure/zh-cn_image_0000002100390820.png "点击放大")
   
   
6. **再次调用存储过程（构造执行异常）。**
   
   ```
   SELECT demo_table_process();  --先删除demo_table的data_number列构造异常，之后再调用
   ```
   
   
7. **查看日志（确认业务运行情况）。**
 
#### 规则5.2 存储过程内避免执行非CREATE类的DDL操作
![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png)
**违反规范的影响**：
- 存储过程是一个大事务，如果有非CREATE类DDL操作（持锁级别高），整个存储过程执行时间窗内会阻塞外部对相关表的访问。
**方案建议**：
- 存储过程中不使用非CREATE类的DDL操作，如必须使用，严格评估存储过程耗时和DDL的影响耗时，与外部访问业务错峰执行。