postgre存儲過程簡單實用方法 · postgresql手冊

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