从事数据中心的工资已经超过5个年头了，对于做数据的程序员来说，打交道最多的就是存储过程了，见过很多写的优秀的存过，也见过很多特别想吐槽的糟心的，那么，如何编写一个少让人吐槽，看着舒服，方便维护，又高效的存储过程呢？

先来了解下存储过程的结构，一般来说存储过程包括以下6个主要部分

（1）声明

（2）注释

（3）初始化

（4）程序主体

（5）异常处理

（6）清理及日志

根据我个的经验，从以上6个方面做一些探讨，小结一下我的心得。

一声明

声明部分需要注意的主要是命名规范，合理的命名能让维护人员方便的查看的程序。

（1）统一对象命名规则

对象名称的命名在一个系统里面需要统一规范，最好有专人进行命名及维护。

对象命名前缀参考

序号	对象	前缀
1	包	PKG_
2	存储过程	PRC_
3	入参	I_
4	出参	O_
5	变量	V_
6	常量	C_
7	游标	CUR_
8	中间表	TMP_

 

（2）建立常用名词英文对照表

在没有规范约束的条件下，命名会比较随意，比如客户ID字段，有人命名为cust_id,有人命名为customer_id,说不定也有人拼音学的好一些，直接就是KH_ID。相同的字段相同的意思，在不同的表里命名不一致，会导致程序不易阅读和维护。比较好的做法是建立常用名称简写中英文对照，已经常用字段命名字典，统一命名规范。

 

常用名称中英文对照参考:

序号	常用词	英文对照
1	基金	FUND
2	客户	CUST
3	用户	USER,USR
4	账号	ACCT,ACCOUNT
5	信息	INFO
6	份额	SHARE
7	新增标志	N_FLAG

 

二注释

注释是程序的一部分，注释是必须的。一个好的注释能帮助运维人员迅速了解程序员的意图，

程序注释应该至少包括如下部分

（1）程序说明

（2）程序参数说明

（3）程序开发日期

（4）程序作者

（5）标注程序的来源表中间表及结果表

（6）修改记录

第（5）（6）点是我需要强调的，大部分人不会添加，但对于维护人员特别实用

 

程序开始部分注释示例:

三程序初始化

程序初始化部分包括

（1）参数的初始化

保证所有的参数在使用前进行合理的初始化。

（2）消息及依赖判断

程序在执行前需要确保依赖的表数据已经生成，如果不能同时被多次调用的过程还需要做相应锁处理。

（3）中间表初始化

中间表在使用前确保原来的数据已经被全部清除

（4）目标表初始化

很多程序需要重跑或者多次调用，再执行前需要确保目标表已经被合理的初始化了。

 

程序初始化示例：

四程序主体

（1）性能

大批量的数据计算是数据中心的核心功能之一，所以性能在任何时候都是需要考虑的问题之一。如何书写一段高性能的程序需要比较扎实的功底，篇幅原因不能做过多的展开，我这里只给出一些常见的要点

• 1避免使用游标

  游标很耗费资源,且大部分情况下为了保证执行效率都会和索引配合使用，需要额外的资源开销。

• 2避免带复杂SQL语句的自定义函数

带SQL语句的自定义函数和游标一样，性能特别差，需要避免使用，请使用中间表和join来进行替代。

• 4禁止触发器和JOB

触发器相对来说特别不容易维护，且影响性能，数据中心一般用不上，可以考虑禁止使用，JOB则完全可以使用ETL替代。

（2）模型设计

• 1谨慎使用主外键

数据中心的表绝大部分情况下，是拉链表，或者有周期的日切表，月切表，或者指标的汇总结果表，本身并不需要主外键及，计算的时候多数情况下会按账期全表访问。

• 2 谨慎使用索引

  索引能提高查询效率，但也需要做合理的维护和增加资源开销，数据中心的表一般都是批量计算批量导入和删除，一般用不上索引，有索引也经常发生索引失效问题。故需要减少索引使用，确实需要使用，请在导入数据后定时做表分析，或重建索引。

• 3 合理使用分区表

  数据统计分析大部分情况下都使用最近一段时间的数据，按时间进行分区能极大的提高程序的执行效率。建议千万级别的表尽量使用分区表

（3）可维护性

• 1 禁用DBLINK

  Oracle的dblink能让我们方便的从源库抽取数据，或直接使用源库的表，但因为dblinksession在基于连接池的管理中可能会引起源库的Session泛滥，从而消耗进程资源且dblink没有详细的日志记录不易排查问题,故需要禁止DBLINK的使用。

• 2 慎用4层及4层以上的嵌套子查询的SQL语句

  请不要总想着用一个SQL搞定一个存储过程，请不要一个SQL写500行甚至2000行，请使用中间表进行过度,

• 3 所有复合或者计算出来的字段统一命名别名

• 4 请为每段代码添加必要的注释

（5）健壮性

• 1 禁用 select * from

• 2禁用不带字段的 insert

比如insert into table_a select * fromtable_b;

 

五异常处理

异常处理一般包括如下几个部分

（1）异常捕获

（2）异常处理

（3）异常日志

（4）常见异常

对于捕捉到的异常，通常需要进行异常处理，配合出参和公共的日志调用程序把把错信息进行输出方便进行问题处理

 

六清理及日志

确保中间表在程序退出前删除数据，以便腾出存储空间，退出时对出参进行合理赋值方便外部调用程序比如ETL能获取程序的执行情况。写入消息和日志，通知下游程序或应用，此时可以开始使用数据了，所以在程序结束时需要做如下工作

（1）中间表清理

（2）出参赋值

（3）消息写入

（4）日志

 

清理及日志代码示例