PostgreSQL存储过程(二)：创建函数入门

准备工作——创建用户数据库和模式：

在数据库中新建用来学习的数据库mydb，并在mydb数据库中新增mysc，即my database和my schema的缩写，接下来的示例脚本将在mydb数据库下的mysc模式下创建。脚本如下，


-- 1.创建自己的数据库
CREATE DATABASE mydb
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'C'
    LC_CTYPE = 'C'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;
-- 添加备注
COMMENT ON DATABASE mydb
    IS 'my administrative connection database';


-- 2.创建自己的schema
CREATE SCHEMA mysc
   AUTHORIZATION postgres;
-- 添加备注
COMMENT ON SCHEMA mysc
    IS 'my schema';
-- 授权
GRANT ALL ON SCHEMA mysc TO postgres;

创建简单自定义函数示例：

pl/sql语法比较灵活，但在创建函数时还是要尽量符合语言约定的规范写法，比如参数名称使用可读性较好的写法（驼峰式、短横线式等写法），在示例脚本中尽量使用符合约定的写法。

创建加法计算函数：

创建一个实现两数相加的自定义函数，具体脚本如下，

p_a：计算参数1
p_b：计算参数2
p_sum：返回结果变量
-------------------
CREATE OR REPLACE FUNCTION mydb.mysc.add(IN p_a integer,IN p_b integer,OUT p_sum integer)
    AS 
	$BODY$ 
		BEGIN
			p_sum:= p_a + p_b;  -- 进行加法运算，并将计算结果赋值给输出的参数p_sum
		END;
	$BODY$ 
    LANGUAGE 'plpgsql'			-- 指定函数的程序语言
    VOLATILE					-- 优化器不进行优化
    RETURNS NULL ON NULL INPUT; -- 当传入参数含有null时返回null

调用定义的加法函数输出结果为：

创建通用计算函数：

在加法计算函数的基础上进行扩展，实现加减乘除运算的函数，脚本如下，

p_a：计算参数1
p_b：计算参数2
p_operate：要进行的加减乘除操作
p_result：返回结果变量
-------------------
CREATE OR REPLACE FUNCTION mydb.mysc.calc(IN p_a integer,IN p_b integer,IN p_operate char,OUT p_result decimal(16,2))
    AS 
	$BODY$ 
			declare tmp_sql varchar(100);  -- 定义变量

		BEGIN
			tmp_sql:= 'select ('||p_a||'*1.0)'|| p_operate ||'('||p_b||'*1.0)';  -- 拼接要执行的函数
			execute tmp_sql into p_result; -- 将查询结果赋值给p_result
		END;
	$BODY$ 
    LANGUAGE 'plpgsql'			-- 指定函数的程序语言
    VOLATILE					-- 优化器不进行优化
   RETURNS NULL ON NULL INPUT; -- 当传入参数含有null时返回null

调用函数两数相加：