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 相同