PostgreSQL之FUNCTION存储过程

总结一下PostgreSQL的存储过程相关知识。
PostgreSQL的存储过程也叫函数。 PostgreSQL存储过程是存储在数据库服务器上并可以使用SQL对话框调用的一组SQL和过程语句(声明，分配，循环，控制流程等)。在数据库中，需要多次查询和往返操作单个函数时很方便。

1、存储过程的语法

CREATE [OR REPLACE] FUNCTION function_name (arguments)   
RETURNS return_datatype AS $variable_name$  
  DECLARE  
    declaration;  
    [...]  
  BEGIN  
    < function_body >  
    [...]  
    RETURN { variable_name | value }  
  END; LANGUAGE plpgsql;

相关参数说明：

function_name：指定函数的名称。
[OR REPLACE]：是可选的，允许修改或替换现有的函数。
RETURN：指定要从函数返回的数据类型。数据类型可以是基础类型，复合类型，或者引用表列的类型。
function_body：function_body具体可执行的增删改查部分。
plpgsql：指定实现该函数的语言的名称。

2、一个简单的小例子
我们来设计一个查找customer表里工资最高的函数。

首先在数据库里建立一个customer表，并插入一些数据

接着在SQL对话框执行以下SQL：

CREATE OR REPLACE FUNCTION maxSalary()  
RETURNS integer AS $topsalary$  
declare  
    topsalary integer;  
BEGIN  
   SELECT max(salary) into topsalary FROM CUSTOMER;  
   RETURN topsalary;  
END;  
$topsalary$ LANGUAGE plpgsql;

如下图：

此时可以在数据库中的函数中新创建了一个maxsalary()的函数：

最后在SQL对话框中执行调用SELECT maxSalary()这个函数可以得到的结果：

可以看到，我们成功的找到了customer表里所有人的工资最大值。

3、使用存储过程的优势：

（1）减少应用与数据库服务器的通信开销，提升整体性能；
（2）第一次被调用后即被编译，后面再调用时无需再次编译，可以直接执行，提高了性能；
（3）同一套业务逻辑可被不同应用程序共用，减少了应用程序的开发复杂度，同时也保证了不同应用程序使用的一致性；
（4）保护数据库元信息。使用存储过程时可以不把数据库表结构暴露给应用程序，而直接使用SQL语句查询数据库则会暴露；
（5）更细粒度的数据库权限管理。存储过程可以在存储过程中将应用程序无权访问的数据屏蔽；
（6）能够实现业务与应用程序的解耦。当业务需求更新时，只需更改存储过程的定义，而不需要更改应用程序。

4、使用存储过程劣势

（1）不便于调试。
（2）不便于移植到其它数据库。使用存储过程时，由于不同数据库的存储过程有不同的定义方式，语言及语法，会造成较高的移植成本。