转自:http://bianbian.org/52/用plpgsql写postgresql的存储过程/
今天学会了用 PL/pgSQL 写 postgreSQL 的存储过程,网上资料实在少得可怜,唯一能搜到的一些还是抄来抄去的;还是翻postgresql的文档吧,把今天解决的问题说一下吧,希望对其他人有帮助。 问题是这样的,有一张message表: CREATE TABLE message ( id int8 NOT NULL, receiveuserid int8, senduserid int8, receivedelete bool DEFAULT false, senddelete bool DEFAULT false, …… CONSTRAINT usermessage_pkey PRIMARY KEY (id) ) 略去其他字段,senduserid是发信息的用户id,senddelete如为true则表示这条消息被发信人所删除;至于receive我就不用说了。一条信息只有被发信人和收信人都删除,才能真正从表里删除(这是显然的,否则收信人删了一条消息后,发信人的“发件箱”里就会找不到这条消息)。 所以删除消息(可能是多条消息)的时候要进行各种判断(是否是发信人?是否是收信人?是否真正删除?),用单条SQL语句完成这个工作显然有些困难(当然用循环嵌套select不考虑效率的话也是可以实现的),只好写存储过程了。 顺便介绍常用的PL/pgSQL结构和语法吧:
- 结构
-
PL/pgSQL是一种块结构的语言,比较方便的是用pgAdmin III新建Function,填入一些参数就可以了。基本上是这样的:
CREATE OR REPLACE FUNCTION 函数名(参数1,[整型 int4,整型数组 _int4,...]) RETURNS 返回值类型 AS $BODY$ DECLARE 变量声明 BEGIN 函数体 END; $BODY$ LANGUAGE ‘plpgsql’ VOLATILE;
- 变量类型
-
除了postgresql内置的变量类型外,常用的还有 RECORD ,表示一条记录。
- 赋值
-
赋值和Pascal有点像:“变量 := 表达式;” 有些奇怪的是连接字符串的是“||”,比如 sql := ‘SELECT * FROM’ || table || ‘WHERE …’;
- 判断
-
判断又和VB有些像: IF 条件 THEN … ELSEIF 条件 THEN … ELSE … END IF;
- 循环
-
循环有好几种写法: WHILE expression LOOP statements END LOOP; 还有常用的一种是:(从1循环到9可以写成FOR i IN 1..9 LOOP) FOR name IN [ REVERSE ] expression .. expression LOOP statements END LOOP;
- 其他
-
还有几个常用的函数: SELECT INTO record …; 表示将select的结果赋给record变量(RECORD类型) PERFORM query; 表示执行query并丢弃结果 EXECUTE sql; 表示执行sql语句,这条可以动态执行sql语句(特别是由参数传入构造sql语句的时候特别有用)
最后,贴出解决上面这个问题的存储过程吧:
- CREATEORREPLACEFUNCTIONmessage_deletes(ids"varchar",useridint8)
- RETURNSint4AS
- $BODY$
- DECLARE
- rRECORD;
- delbool;
- numint4:=0;
- sql";
- BEGIN
- sql:='select id,receiveuserid,senduserid,senddelete,receivedelete from message where id in ('||ids||')';
- FORrINEXECUTEsqlLOOP
- del:=false;
- IFr.receiveuserid=useridandr.senduserid=useridTHEN
- del:=true;
- ELSEIFr.receiveuserid=useridTHEN
- IFr.senddelete=falseTHEN
- updatemessagesetreceivedelete=truewhereid=r.id;
- ELSE
- del:=true;
- ENDIF;
- ELSEIFr.senduserid=useridTHEN
- IFr.receivedelete=falseTHEN
- updatemessagesetsenddelete=truewhereid=r.id;
- ENDIF;
- IFdelTHEN
- deletefrommessagewhereid=r.id;
- num:=num+1;
- ENDLOOP;
- returnnum;
- END;
- LANGUAGE'plpgsql'VOLATILE;
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|