postgreSQL存储过程写法示例

转载自&＃xff1a;http://panyongzheng.iteye.com/blog/2194815

PostgreSQL的存储过程简单入门 http://blog.csdn.net/rachel_luo/article/details/8073458 

存储过程事物 
http://www.php100.com/manual/PostgreSQL8/tutorial-transactions.html
 


PL/pgSQL - SQL存储过程语言 
https://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E4%B8%89%E5%8D%81%E4%B9%9D%E7%AB%A0
 




postgreSQL存储过程写法示例
http://blog.sina.com.cn/s/blog_448574810101f64u.html
 


结构 


PL/pgSQL是一种块结构的语言&＃xff0c;比较方便的是用pgAdmin III新建Function&＃xff0c;填入一些参数就可以了。基本上是这样的&＃xff1a; 





变量类型 


除了postgresql内置的变量类型外&＃xff0c;常用的还有 RECORD &＃xff0c;表示一条记录。 


赋值 


赋值和Pascal有点像&＃xff1a;“变量 :&＃61; 表达式;” 


有些奇怪的是连接字符串的是“||”&＃xff0c;比如 sql :&＃61; ‘SELECT * FROM’ || table || ‘WHERE …’; 


判断 


判断又和VB有些像&＃xff1a; 


IF 条件 THEN 


… 


ELSEIF 条件 THEN 


… 


ELSE 


… 


END IF; 


循环 


循环有好几种写法&＃xff1a; 


WHILE expression LOOP 


statements 


END LOOP; 


还有常用的一种是&＃xff1a;&＃xff08;从1循环到9可以写成FOR i IN 1..9 LOOP&＃xff09; 


FOR name IN [ REVERSE ] expression .. expression LOOP 


statements 


END LOOP; 


其他 


还有几个常用的函数&＃xff1a; 


SELECT INTO record …; 表示将select的结果赋给record变量&＃xff08;RECORD类型&＃xff09; 


PERFORM query; 表示执行query并丢弃结果 


EXECUTE sql; 表示执行sql语句&＃xff0c;这条可以动态执行sql语句&＃xff08;特别是由参数传入构造sql语句的时候特别有用&＃xff09; 




参数:
 


传递给函数的参数都是用 $1&＃xff0c;$2&＃xff0c;等等这样的标识符。有时候为了增强可读性&＃xff0c;我们可以为 $n 参数名声明别名。然后通过这个别名或者数字标识符可以指向这个参数值。 


有两种方法创建一个别名。最好的方法是用CREATE FUNCTION命令给予这个参数一个名字&＃xff0c;例如&＃xff1a; 





另一个方法是&＃xff0c;在PostgreSQL 8.0之前唯一的方法&＃xff0c;明确的用别名进行声明&＃xff0c;用以下的语法进行声明&＃xff1a; 


   
name ALIAS FOR $n;
 


这个风格的同一个例子看起来像下面这样 &＃xff1a; 





注意&＃xff1a;这两个例子不是完全一样的。在第一种情况&＃xff0c;subtotal可以用sales_tax.subtotal进行引用&＃xff0c;但是在第二种情况下不能这么做。&＃xff08;如果我们给这个内部块附加了一个标签&＃xff0c;subtotal能够替代这个标签&＃xff09; 


一些更多的例子&＃xff1a; 





当一个PL/pgSQL函数用输出参数来进行声明时&＃xff0c;给予这个输出参数$n名和一个任意的别名跟正常输入参数是同样的方法。即使这个输出参数以NULL开始时也是一个有效的变量&＃xff0c;它应该在函数的执行过程中被分配。这个参数最好的值将被返回。例如&＃xff0c;这个sales-tax例子也可以用这种方法完成&＃xff1a; 





注意&＃xff1a;我们省略了RETURNS real---我们可以将它包括在内&＃xff0c;但它是多余的。 


当返回多个值的时候输出参数将非常有用&＃xff0c;一个简单的例子是&＃xff1a; 





如在Section 35.4.4中的讨论&＃xff0c;这将为这个函数的结果创建一个匿名的记录类型。如果使用了RETURNS字句&＃xff0c;那么必须给它指明RETURNS记录。 


另外一种方法声明PL/pgSQL函数是用RETURNS TABLE,例如&＃xff1a; 





这跟声明一个或者多个OUT参数和制定RETURNS SETOF这些类型是同样的方法。 


当返回的PL/pgSQL函数的类型被声明为一个多态类型&＃xff08;anyelement, anyarray, anynonarray, 或者anyenum&＃xff09;&＃xff0c;特殊参数$0将被创建。它的数据类型将实际的返回函数的类型&＃xff0c;从实际的输入类型返回&＃xff08;见Section 35.2.5&＃xff09;。这运行这个函数访问这个实际的返回类型如Section 39.3.3显示的那样。$0初始值为空并且能够被函数修改&＃xff0c;如果需要&＃xff0c;它可以用于保留返回值&＃xff0c;虽然这不是必须的。$0也可以被给予一个别名。例如&＃xff0c;这个函数能在任意一个有&＃43;操作符的数据类型上工作&＃xff1a; 





声明一个或者多个多态类型的输出参数也是同样的效果。这种情况下这个特殊的$0参数将不会被用到&＃xff0c;这个输出参数本身也是同样的作用&＃xff0c;例如&＃xff1a; 







39.3.2. 别名
 


   newname ALIAS FOR oldname; 


这个ALIAS语法比以前的章节中介绍的更加普通&＃xff1a;你可以为任意一个变量声明一个别名&＃xff0c;不只是函数的参数。这实际的用途是用预定义的名字为变量定义不同的名字&＃xff0c;如触发器过程中的NEW或者OLD。 例子&＃xff1a; 





因此&＃xff0c;ALIAS使同样的对象有两种不同的方式命名&＃xff0c;如果不限制的使用&＃xff0c;将会变得混乱。这种方法最好只用于覆盖预定义的名字。 








最后&＃xff0c;贴出解决上面这个问题的存储过程吧&＃xff1a; 











下面的例子是要调用一个存储过程自动创建对应的一系列表: 









自动创建序列 


第一个例子



调用: 







第二个例子 





调用