postgre存储过程简单实用方法 （过程语言： PL/pgSQL）
一，介绍常用的PL/pgSQL结构和语法：
1，结构
PL/pgSQL是一种块结构的语言，比较方便的是用pgAdmin III新建Function，填入一些参数就可以了。基本上是这样的：
CREATE OR REPLACE FUNCTION 函数名(参数1，[整型 int4, 整型数组 _int4, …])
RETURNS 返回值类型 AS
$BODY$
DECLARE
变量声明
BEGIN
函数体
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;
2，变量类型 除了postgresql内置的变量类型外，常用的还有 RECORD ，表示一条记录。
赋值 ：“变量 := 表达式;”
连接字符串的是“||”，比如 sql := ‘SELECT * FROM’ || table || ‘WHERE …’;
3，判断
IF 条件 THEN
…
ELSEIF 条件 THEN
…
ELSE
…
END IF;
4，循环 循环有好几种写法：
WHILE expression LOOP
statements
END LOOP;
还有常用的一种是：（从1循环到9可以写成FOR i IN 1..9 LOOP）
FOR name IN [ REVERSE ] expression .. expression LOOP
statements
END LOOP;
二 跟mysql对比较
1，postgre 中的limit不支持LIMIT #,# 这样的语法。
而是支持 LIMIT and OFFSET clauses 语法
mysql上面的两种方式都支持。
2，存储过程中在ibatis中的使用：
(1)，mysql存储过程可以直接返回结果集，同时可以有out参数
例如：
存储过程：
CREATE PROCEDURE `test`
(IN _login VARCHAR(32),
IN _psw VARCHAR(32),
OUT _ret INTEGER(10),
OUT _id INTEGER(10),
OUT _name VARCHAR(32),
OUT _email VARCHAR(32),
OUT _phone VARCHAR(20),
OUT _active INTEGER(11))//同时返回多个结果集合
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND set _ret =-1;
set _ret = 0 ;
select id,name,email,phone,active
into _id,_name,_email,_phone,_active
from test
where tx_account.`loginname`=_login and tx_account.`password`=MD5(_psw) and active=1;
---------返回结果集-----
if _ret = 0 then
select a.id as id ,a.name as name,a.priority as priority
from test b left join test1 a on b.role=a.id
where b.account=_id;
end if;
END;
直接返回结果集
ibatis文件
<parameterMap id="testParameterMap" class="params">
<parameter property="loginname" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="password" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="ret" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
<parameter property="id" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
<parameter property="name" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
<parameter property="phone" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
<parameter property="email" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
<parameter property="active" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
</parameterMap>
<procedure id="test" parameterMap="testMap" resultMap="AccountRoleResultMap">
{call test(?,?,?,?,?,?,?,?)}
</procedure>
dao 的实现
定义一个传参的map params ,
HashMap<String,Object> params = new HashMap<String,Object>();
//把需要的参数放到map中
params.put("id",account.getId());
params.put("ret",null);
params.put("loginname", null);
params.put("name", null);
params.put("email", null);
params.put("phone",null);
params.put("active", null);
定义一个list
List list=null;
list= (List)(getSqlMapClientTemplate().queryForList("test",params));
//上面这样操作就可以获得存储过程返回的结果集。
Object var;
var = params.get("ret");//从map 中获得制定的输出参数的值。
在mysql中不需要的ibatis的配置文件中，声明返回的结果集。
(2) postgre的函数返回结果集
在postgre中返回结果集一定要在ibatis中定义输出参数。
方法1：不能输出参数，使用直接返游标的方法
例如：
函数：
CREATE OR REPLACE FUNCTION test(IN _login VARCHAR(32))//只有输入参数
RETURNS
refcursor //制定返回类型为游标。
AS
$BODY$
declare video_cur refcursor;
BEGIN
open video_cur for
select id , title from test;
return video_cur ;//返回游标
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test(integer) OWNER TO postgres;
ibatis文件
<parameterMap id="testParameters" class="java.util.HashMap">
<parameter property="result" jdbcType="OTHER" javaType="java.sql.ResultSet" mode="OUT"/>//返回结果集
<parameter property="loginName" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="loginPasswd" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
</parameterMap>
<procedure id="test" resultMap="testResultMap" parameterMap="testParameters" >
{? = call test(?,?)}
</procedure>
上面的map文件描述了3个参数,按照调用方式: ? = call test(?, ?)的顺序，
第一个参数是返回结果集的，这里的jdbcType填写OTHER，javaType填写java.sql.ResultSet，
如果是ORACLE的存储过程通过游标返回结果集的话，jdbcType应该填写为ORACLECURSOR，
不过在PostgreSQL中不能用ORACLECURSOR，得用OTHER。
dao的实现：
定义map文件 parameters ；
List list;
HashMap<String, String> parameters = new HashMap<String, String>();
parameters.put("loginName", loginName);
parameters.put("loginPasswd", loginPasswd);
list=getSqlMapClientTemplate().queryForList("test", parameters);//这样来得到返回的结果集。
return list;
方法2： 同时返回多个结果，
函数：
CREATE OR REPLACE FUNCTION test(IN _login VARCHAR(32),
IN _psw VARCHAR(32),
OUT _ret INTEGER,
OUT _id INTEGER,
OUT _name VARCHAR(32),
OUT _email VARCHAR(32),
OUT _phone VARCHAR(20),
OUT _ref refcursor ---返回一个游标
)
RETURNS record
AS
$BODY$
declare video_cur refcursor;
BEGIN
select id,name,email,phone
into _id,_name,_email,_phone
from test
where tx_account.`loginname`=_login and tx_account.`password`=MD5(_psw) and active=1;
open _ref for
select id , title from test1;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test(integer) OWNER TO postgres;
如果返回多个结果集，就要使用返回伪类型 record可以在输出参数中指定游标为其中一个out参数
ibatis文件
out 参数输出游标
<parameterMap id="ParameterMap" class="map" >
<parameter property="login " jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="password" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="ret" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
<parameter property="id" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
<parameter property="name" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
<parameter property="phone" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
<parameter property="email" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
<parameter property="ref" jdbcType="OTHER" javaType="java.sql.ResultSet" mode="OUT"/> //返回结果集
</parameterMap>
<procedure id="test" parameterMap="ParameterMap" resultMap="ResultMap">
{call test(?,?,?,?,?,?,?,?)}
</procedure>
dao的实现跟方法1 相同
- 数据表
- 模式Schema
- 表的继承和分区
- 常用数据类型
- 函数和操作符-一
- 函数和操作符-二
- 函数和操作符-三
- 索引
- 事物隔离
- 性能提升技巧
- 服务器配置
- 角色和权限
- 数据库管理
- 数据库维护
- 系统表
- 系统视图
- SQL语言函数
- PL-pgSQL过程语言
- PostgreSQL 序列（SEQUENCE）
- PostgreSQL的时间-日期函数使用
- PostgreSQL 查看数据库，索引，表，表空间大小
- 用以查询某表的详细 包含表字段的注释信息
- PostgreSQL 系统表查看系统信息
- postgre存储过程简单实用方法
- PostgreSQL实用日常维护SQL
- PostgreSQL的时间函数使用整理
- 命令
- pg_ctl控制服务器
- initdb 初始化数据库簇
- createdb创建数据库
- dropdb 删除数据库
- createuser创建用户
- dropuser 删除用户
- psql交互式工具
- psql命令手册
- pg_dump 数据库转储
- pg_restore恢复数据库
- vacuumdb 清理优化数据库
- reindexdb 数据库重创索引
- createlang 安装过程语言
- droplang 删除过程语言
- pg_upgrade 升级数据库簇
- 调试存储过程
- 客户端命令-一
- 客户端命令-二
- 使用技巧
- PostgreSQL删除重复数据
- postgresql 小技巧
- PostgreSQL的10进制与16进制互转
- PostgreSQL的汉字转拼音
- Postgres重复数据的更新一例
- PostgreSQL使用with一例
- PostgreSQL在函数内返回returning
- PostgreSQL中的group_concat使用
- PostgreSQL数据库切割和组合字段函数
- postgresql重复数据的删除
- PostgreSQL的递归查询(with recursive)
- PostgreSQL函数如何返回数据集
- PostgreSQL分区表（Table Partitioning）应用 - David_Tang - 博客园
- PostgreSQL: function 返回结果集多列和单列的例子
- 利用pgAgent创建定时任务
- 浅谈 PostgreSQL 类型转换类似Oracle
- postgresql在windows(包括win7)下的安装配置
- PostgreSQL简介、安装、用户管理、启动关闭、创建删除数据库 (2010-11-08 12-52-51)转载▼标签： 杂谈分类： PostgreSQL
- PostgreSQL的generate_series函数应用
- PostgreSQL 8.3.1 全文检索(Full Text Search)
- postgresql record 使用
- 备份恢复
- PostgreSQL基于时间点恢复(PITR)
- Postgresql基于时间点恢复PITR案例(二)
- Postgres逻辑备份脚本
- Postgres invalid command \N数据恢复处理