|
转载自：http://panyongzheng.iteye.com/blog/2194815
PostgreSQL的存储过程简单入门http://www.52php.cn/article/p-wipiavos-bda.html
存储过程事物
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是一种块结构的语言，比较方便的是用pgAdmin III新建Function，填入一些参数就可以了。基本上是这样的：
- CREATEORREPLACEFUNCTION函数名(参数1，[整型int4,整型数组_int4,...])
- RETURNS返回值类型AS
- $BODY$
- DECLARE
- 变量声明
- BEGIN
- 函数体
- END;
- 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
其他
还有几个常用的函数：
SELECT INTO record …; 表示将select的结果赋给record变量（RECORD类型）
PERFORM query; 表示执行query并丢弃结果
EXECUTE sql; 表示执行sql语句，这条可以动态执行sql语句（特别是由参数传入构造sql语句的时候特别有用）
参数:
传递给函数的参数都是用 $1，$2，等等这样的标识符。有时候为了增强可读性，我们可以为 $n 参数名声明别名。然后通过这个别名或者数字标识符可以指向这个参数值。
有两种方法创建一个别名。最好的方法是用CREATE FUNCTION命令给予这个参数一个名字，例如：
CREATEFUNCTIONsales_tax(subtotalreal)RETURNSrealAS$$
RETURNsubtotal*0.06;
$$LANGUAGEplpgsql;
另一个方法是，在PostgreSQL 8.0之前唯一的方法，明确的用别名进行声明，用以下的语法进行声明：
name ALIAS FOR $n;
这个风格的同一个例子看起来像下面这样 ：
FUNCTIONsales_tax( subtotalALIASFOR$1;
注意：这两个例子不是完全一样的。在第一种情况，subtotal可以用sales_tax.subtotal进行引用，但是在第二种情况下不能这么做。（如果我们给这个内部块附加了一个标签，subtotal能够替代这个标签）
一些更多的例子：
FUNCTIONinstr(varchar,integer)integer v_stringALIASindexALIASFOR$2;
$$LANGUAGEplpgsql;
FUNCTIONconcat_selected_fields(in_tsometablename)RETURNStextRETURNin_t.f1||in_t.f3||in_t.f5||in_t.f7;
$$LANGUAGEplpgsql
当一个PL/pgSQL函数用输出参数来进行声明时，给予这个输出参数$n名和一个任意的别名跟正常输入参数是同样的方法。即使这个输出参数以NULL开始时也是一个有效的变量，它应该在函数的执行过程中被分配。这个参数最好的值将被返回。例如，这个sales-tax例子也可以用这种方法完成：
real,85); font-weight:bold">OUTtax tax:=subtotal*0.06;
注意：我们省略了RETURNS real---我们可以将它包括在内，但它是多余的。
当返回多个值的时候输出参数将非常有用，一个简单的例子是：
FUNCTIONsum_n_product(xint,yOUTsumOUTprodint) sum:=x+y;
prod:=x*y;
如在Section 35.4.4中的讨论，这将为这个函数的结果创建一个匿名的记录类型。如果使用了RETURNS字句，那么必须给它指明RETURNS记录。
另外一种方法声明PL/pgSQL函数是用RETURNS TABLE,例如：
FUNCTIONextended_sales(p_itemnoint)
TABLE(quantitynumeric)RETURNQUERYSELECTquantity,quantity*priceFROMsales
WHEREitemno=p_itemno;
这跟声明一个或者多个OUT参数和制定RETURNS SETOF这些类型是同样的方法。
当返回的PL/pgSQL函数的类型被声明为一个多态类型（anyelement,anyarray,anynonarray,或者anyenum），特殊参数$0将被创建。它的数据类型将实际的返回函数的类型，从实际的输入类型返回（见Section 35.2.5）。这运行这个函数访问这个实际的返回类型如Section 39.3.3显示的那样。$0初始值为空并且能够被函数修改，如果需要，它可以用于保留返回值，虽然这不是必须的。$0也可以被给予一个别名。例如，这个函数能在任意一个有+操作符的数据类型上工作：
FUNCTIONadd_three_values(v1anyelement,v2anyelement,v3anyelement)
RETURNSanyelement resultALIASFOR$0;
result:=v1+v2+v3;
RETURNresult;
声明一个或者多个多态类型的输出参数也是同样的效果。这种情况下这个特殊的$0参数将不会被用到，这个输出参数本身也是同样的作用，例如：
sumanyelement)
sum:=v1+v2+v3;
39.3.2. 别名
newname ALIAS FOR oldname;
这个ALIAS语法比以前的章节中介绍的更加普通：你可以为任意一个变量声明一个别名，不只是函数的参数。这实际的用途是用预定义的名字为变量定义不同的名字，如触发器过程中的NEW或者OLD。 例子：
priorALIASFORold;
updatedALIASFORnew;
因此，ALIAS使同样的对象有两种不同的方式命名，如果不限制的使用，将会变得混乱。这种方法最好只用于覆盖预定义的名字。
最后，贴出解决上面这个问题的存储过程吧：
FUNCTIONmessage_deletes(ids"varchar",useridint8)
RETURNSint4 rRECORD;
delbool;
numint4:=0;
sql"varchar";
sql:='selectid,receiveuserid,senduserid,senddelete,receivedeletefrommessagewhereidin('||ids||')';
FORrINEXECUTEsqlLOOP
del:=false;
IFr.receiveuserid=useridandr.senduserid=useridTHEN
true;
ELSEIFr.receiveuserid=userid IFr.senddelete=falseupdatemessagesetreceivedelete=truewhereid=r.id;
ELSE
ENDIF;
ELSEIFr.senduserid=userid IFr.receivedelete=setsenddelete= IFdeldeletefrommessage num:=num+1;
ENDLOOP;
returnnum;
LANGUAGE'plpgsql'VOLATILE;
下面的例子是要调用一个存储过程自动创建对应的一系列表:
FUNCTIONcreate_table_for_client(id sql:='createtable_'||id||'_company(idint,nametext)';
EXECUTEsql;
sql:='createtable_'||id||'_employee(idint,nametext)'; sql:='createtable_'||id||'_sale_bill(idint,250); line-height:18px"> .......
$BODY$LANGUAGEplpgsqlVOLATILE
自动创建序列
第一个例子
FUNCTIONauto_gen_seq()bigint rdRECORD;
seq_sqlvarchar;
sql:='SELECTtablenameFROMpg_tablesWHEREtablenameNOTLIKE''pg%''ANDtablenameNOTLIKE''sql_%''ORDERBYtablename;';
FORrd seq_sql:='CREATESEQUENCESQ_'||rd.tablename||'START1000000CACHE30;';
EXECUTEseq_sql;
EXCEPTION
WHENTOO_MANY_ROWS RAISEEXCEPTION'employee%notunique',seq_sql;
WHENOTHERSreturn-1;
LANGUAGEplpgsqlVOLATILENOTLEAKPROOF
COST100;
调用:
selectauto_gen_seq()
第二个例子
--Function:auto_gen_seq(character)
--DROPFUNCTIONauto_gen_seq(character);
FUNCTIONauto_gen_seq("tbName"character)
charactervarying $BODY$/*
调用示例:
SELECTtablenameastableName,250); line-height:18px"> 'sq_'||REPLACE(REPLACE(tablename,'tb_am_',''),'tb_sm_','tb_pm_','tb_pc_','tb_ps_','rh_','TB_','RH_','tb_','')ASsqName
,auto_gen_seq(tablename||'')assuccessFlag,85); font-weight:bold">current_date,85); font-weight:bold">current_timeFROMpg_tablesWHEREtablenameNOTLIKE'pg%'ANDtablenameLIKE'sql_%'ORDERBYsuccessFlag,tablename;
*/
flag_str sq_name sq_datetime seq_sql:='createtable_sequence_table(idSERIALnotnull,codeVARCHAR(200)null,increment_numINT8null,minvalue_numINT8null,maxvalue_numINT8null,start_numINT8null,cache_numINT8null,cycle_flagVARCHAR(100)null,create_datetimetimestampwithouttimezone,constraintPK__SEQUENCE_TABLEprimarykey(id));CREATEUNIQUEINDEXINDEX__sequence_tableON_sequence_table(code);';
flag_str:='失败';
--sq_name:=replace(replace($1,'TB_',''),'RH_','');
--sq_name:=replace(replace(sq_name,'tb_','rh_',250); line-height:18px"> sq_name:='sq_'||REPLACE($1,'');
/*
seq_sql:='dropSEQUENCE'||sq_name||';';
seq_sql:='CREATESEQUENCE'||sq_name||'START1000000CACHE30;';
return'失败,创建序列';
sq_datetime:=to_timestamp(current_date||''||current_time,'yyyy-mm-ddhh24:mi:ss');
seq_sql:='INSERTINTO_sequence_table(code,increment_num,minvalue_num,start_num,cache_num,create_datetime)VALUES('''||sq_name||''',1,1000000,30,'''||sq_datetime||''');';
return'成功';
LANGUAGEplpgsqlVOLATILESTRICT
COST100;
ALTERFUNCTIONauto_gen_seq( OWNERTOpostgres;
调用
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
