原创文章,转载请务必在文章开头处注明出自Jason’s Blog,并给出原文链接http://www.jasongj.com/2015/12/27/SQL4_%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B_Store%20Procedure/
存储过程简介
什么是存储过程
百度百科是这么描述存储过程的:存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,存储在数据库中,首次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果有)来执行它。它是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
维基百科是这样定义的:A stored procedure (also termed proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a subroutine available to applications that access a relational database management system (RDMS). Such procedures are stored in the database data dictionary。
PostgreSQL对存储过程的描述是:存储过程和用户自定义函数(UDF)是SQL和过程语句的集合,它存储于数据库服务器并能被SQL接口调用。
总结下来存储过程有如下特性:
- 存储于数据库服务器
- 一次编译后可多次调用
- 设计良好的数据库应用程序很可能会用到它
- 由SQL和过程语句来定义
- 应用程序通过SQL接口来调用
使用存储过程的优势及劣势
首先看看使用存储过程的优势
- 减少应用与数据库服务器的通信开销,从而提升整体性能。笔者在项目中使用的存储过程,少则几十行,多则几百行甚至上千行(假设一行10个字节,一千行即相当于10KB),如果不使用存储过程而直接通过应用程序将相应SQL请求发送到数据库服务器,会增大网络通信开销。相反,使用存储过程能降低该开销,从而提升整体性能。尤其在一些BI系统中,一个页面往往要使用多个存储过程,此时存储过程降低网络通信开销的优势非常明显
- 一次编译多次调用,提高性能。存储过程存于数据库服务器中,第一次被调用后即被编译,之后再调用时无需再次编译,直接执行,提高了性能
- 同一套业务逻辑可被不同应用程序共用,减少了应用程序的开发复杂度,同时也保证了不同应用程序使用的一致性
- 保护数据库元信息。如果应用程序直接使用SQL语句查询数据库,会将数据库表结构暴露给应用程序,而使用存储过程是应用程序并不知道数据库表结构
- 更细粒度的数据库权限管理。直接从表读取数据时,对应用程序只能实现表级别的权限管理,而使用存储过程是,可在存储过程中将应用程序无权访问的数据屏蔽
- 将业务实现与应用程序解耦。当业务需求更新时,只需更改存储过程的定义,而不需要更改应用程序
- 可以通过其它语言并可及其它系统交互。比如可以使用PL/Java与Kafka交互,将存储过程的参数Push到Kafka或者将从Kafka获取的数据作为存储过程的结果返回给调用方
当然,使用存储过程也有它的劣势
- 不便于调试。尤其在做性能调优时,以PostgreSQL为例,可使用EXPLAIN ANALYZE检查SQL查询计划,从而方便的进行性能调优。而使用存储过程时,EXPLAIN ANALYZE无法显示其内部查询计划
- 不便于移植到其它数据库。直接使用SQL时,SQL存于应用程序中,对大部分标准SQL而言,换用其它数据库并不影响应用程序的使用。而使用存储过程时,由于不同数据库的存储过程定义方式不同,支持的语言及语法不同,移植成本较高
存储过程在PostgreSQL中的使用
PostgreSQL支持的过程语言
PostgreSQL官方支持PL/pgSQL,PL/Tcl,PL/Perl和PL/Python这几种过程语言。同时还支持一些第三方提供的过程语言,如PL/Java,PL/PHP,PL/Py,PL/R,PL/Ruby,PL/Scheme,PL/sh。
基于SQL的存储过程定义
CREATE OR REPLACE FUNCTION add(a INTEGER, b NUMERIC)
RETURNS NUMERIC
AS $$
SELECT a+b;
$$ LANGUAGE SQL;
调用方法
SELECT add(1,2);
add
-----
3
(1 row)
SELECT * FROM add(1,2);
add
-----
3
(1 row)
上面这种方式参数列表只包含函数输入参数,不包含输出参数。下面这个例子将同时包含输入参数和输出参数
CREATE OR REPLACE FUNCTION plus_and_minus
(IN a INTEGER, IN b NUMERIC, OUT c NUMERIC, OUT d NUMERIC)
AS $$
SELECT a+b, a-b;
$$ LANGUAGE SQL;
调用方式
SELECT plus_and_minus(3,2);
add_and_minute
----------------
(5,1)
(1 row)
SELECT * FROM plus_and_minus(3,2);
c | d
---+---
5 | 1
(1 row)
该例中,IN代表输入参数,OUT代表输出参数。这个带输出参数的函数和之前的add
函数并无本质区别。事实上,输出参数的最大价值在于它为函数提供了返回多个字段的途径。
在函数定义中,可以写多个SQL语句,不一定是SELECT语句,可以是其它任意合法的SQL。但最后一条SQL必须是SELECT语句,并且该SQL的结果将作为该函数的输出结果。
CREATE OR REPLACE FUNCTION plus_and_minus
(IN a INTEGER, IN b NUMERIC, OUT c NUMERIC, OUT d NUMERIC)
AS $$
SELECT a+b, a-b;
INSERT INTO test VALUES(