数据库之存储过程和函数
简介
什么是存储过程和函数？
首先我们要知道过程化SQL主要有两种类型，即命名块和匿名块。所谓匿名块即是我们在程序中所直接写的SQL语句， 每次执行都要编译，它不会存储到数据库中，也不能在其他过程化SQL块中调用。而存储过程和函数则是命名块，它们被编译后保存在数据库中，称为持久性存储模块。可以被反复调用，并且运行速度较快。
详细介绍
存储过程
一、概念
存储过程是由过程化SQL语句书写的过程，这个过程经过编译和优化之后存储在数据库服务器中，因此称之为存储过程，在使用的时候直接调用即可。
简单来说，就是由一些T-SQL语句组成的代码块，像一个方法一样实现了一些功能，例如对单表和多表的CRUD操作，然后再给这个代码块取一个名字，再用到此功能调用就可以了。
二、优点
- 我们知道数据库执行SQL语句时，是先编译优化，再执行。而存储过程则是已经编译完毕的代码块。无需再次执行编译过程，所以它的运行效率高。提供了在服务器端快速执行SQL语句的有效途径。
- 客户机上的应用程序只需要提供调用存储过程的名称和参数，就可以执行一个复杂的存储过程，并进行相应的数据处理，只需要把最终的处理结果返回给客户端。这就降低了客户机和服务端的通信量，提高了通信的效率。
- 使用存储过程能够使得没有权限的用户在控制之下间接的存取数据，一定程度上保证了数据的安全。
- 如果对数据库中的数据的某些规则是动态的，我们可以将这些规则写成存储过程，如果这些规则发生变化，我们只需要修改对应的存储过程即可，而不是去修改应用程序的源代码。
三、缺点
- 不便于调试。在做性能调优的时候，无法显示内部的查询计划。
- 不方便移植到其他数据库。在使用存储过程时，对于不同的数据库，对应的存储过程的定义方式不同，支持的语言以及语法不同，移植的成本很高。
四、基本使用
我们可以通过如下SQL语句创建、执行、修改和删除存储过程。
1、创建存储过程
CREATE OR REPLACE PRODUCRE 过程名([arg1, arg2...])
AS
<过程化SQL语句块>;
2、执行存储过程
CALL/PERFORM PROCEDURE 过程名([arg1, arg2...])
3、修改存储过程
# 重命名存储过程
ALTER PROCEDURE 过程名1 RENAME TO 过程名2
# 重新编译存储过程
ALTER PROCEDURE 过程名 COMPILE
4、删除存储过程
DROP PROCEDURE 过程名()
函数
一、概念
函数也称自定义函数，因为是用户自己使用过程化SQL语句设计定义的。函数和存储过程类似，都是持久性存储模块。
Tips:
不同之处：
函数必须指定返回的类型
二、基本语法格式
1、函数定义语句
CREATE OR REPLACE FUNCTION 函数名([arg1, arg2...])
RETURNS <类型>
AS
<过程化SQL语句块>；
2、函数执行格式
CALL/SELECT 函数名([arg1, arg2...]);
3、修改函数
# 重命名函数
ALTER FUNCTION 函数名1 RENAME TO 函数名2;
# 重新编译函数
ALTER FUNCTION 函数名 COMPILE;
PostgreSQL中的存储过程(函数)
简介
PostgreSQL对存储过程的描述是：存储过程和用户自定义函数（UDF）是SQL和过程语句的集合，它存储于数据库服务器并能被SQL接口调用。
PostgreSQL官方支持PL/pgSQL，PL/Tcl，PL/Perl和PL/Python这几种过程语言，同时还支持一些第三方提供的过程语言，如PL/JAVA，PL/PHP，PL/R，PL/Scheme，PL/sh。
注意：
PostgreSQL数据库并没有区分存储过程和函数。
基于SQL的存储过程
定义方式
# 创建一个查询所有结果的函数
CREATE OR REPLACE FUNCTION GetAllUser()
RETURNS RECORD
AS $$
SELECT * FROM USERS
$$ LANGUAGE SQL;
调用方式
select GetAllUser();
基于PL/PgSQL的存储过程定义
PL/PgSQL是一个块结构语言，函数定义的所有文本都必须是一个块。参考More
其定义如下：
[<<label>>]
[ DECLARE
declaerations]
BEGIN
statements
END [lable];
一个简单的例子：
CREATE OR REPLACE FUNCTION GetUser()
RETURNS setof users
AS $$
BEGIN
SELECT * FROM USERS;
END;
$$ LANGUAGE PLPGSQL;
调用返回格式
select GetUser()
返回所有的users表中的结果
