postgre存储过程简单实用方法 (过程语言: PL/pgSQL)

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

 

 

### PostgreSQL 存储过程概述 存储过程是在数据库中封装了一组 SQL 语句的对象,能够简化复杂查询和重复任务的操作。通过使用存储过程,可以提高应用程序性能以及数据的一致性和安全性[^1]。 ### 创建存储过程PostgreSQL 中创建存储过程通常涉及以下几个方面: #### 定义语言环境 PostgreSQL 支持多种编程语言用于编写存储过程,而 PL/pgSQL 是最为常用的内置扩展之一。这种语言提供了类似于其他高级程序设计语言的功能特性,如条件判断、循环控制结构等[^3]。 ```sql CREATE OR REPLACE PROCEDURE example_procedure() LANGUAGE plpgsql AS $$ BEGIN -- 这里放置要执行的具体逻辑代码... END; $$; ``` 此段脚本展示了如何声明一个新的名为 `example_procedure` 的简单存储过程,并指定了其使用的语言PL/pgSQL[^2]。 #### 参数传递与返回值处理 当需要向存储过程中传入参数或获取结果时,则可以在定义阶段指定输入输出变量及其类型。对于有返回值得情况,还可以利用 OUT 或者 INOUT 关键字来进行标记[^4]。 ```sql CREATE OR REPLACE FUNCTION get_user_name(id INT) RETURNS TEXT LANGUAGE sql STABLE AS $$ SELECT name FROM users WHERE id = $1; $$; DO $$ BEGIN PERFORM get_user_name(1); END $$; ``` 上述例子明了怎样构建带有一个整数型输入参数 (`id`) 和字符串形式输出的结果集 (即用户名) 的函数版本的存储过程;并通过 DO 块调用了该方法来展示实际应用方式。 ### 使用存储过程 一旦成功建立了所需的存储过程之后,在日常工作中就可以方便快捷地对其进行调用以完成特定的任务需求。下面给出了一些常见的应用场景实例: - **批量更新记录**: 可以把多条 UPDATE 语句组合成单个事务提交给服务器端运行; - **自动化维护工作流**: 利用定时器触发某些预定好的操作序列,比如定期清理过期的数据表项; - **增强安全机制**: 将敏感信息访问权限严格限制于经过验证后的内部模块内侧,从而减少外部攻击风险。 ### 示例文档链接 更多详细的官方指南和技术细节可参阅 [PostgreSQL 扩展语言手册](http://www.postgres.cn/docs/10/xplang.html)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值