postgreSQL存储过程写法示例

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%A0postgreSQL存储过程写法示例http://blog.sina.com.cn/s/blog_448574810101f64u.html
结构
PL/pgSQL是一种块结构的语言，比较方便的是用pgAdmin III新建Function，填入一些参数就可以了。基本上是这样的：

Sql代码

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语句的时候特别有用）

参数:
传递给函数的参数都是用 $1，$2，等等这样的标识符。有时候为了增强可读性，我们可以为 $n 参数名声明别名。然后通过这个别名或者数字标识符可以指向这个参数值。
有两种方法创建一个别名。最好的方法是用CREATE FUNCTION命令给予这个参数一个名字，例如：

Sql代码

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$

BEGIN

RETURN subtotal * 0.06;

END;

$$ LANGUAGE plpgsql;

另一个方法是，在PostgreSQL 8.0之前唯一的方法，明确的用别名进行声明，用以下的语法进行声明：
name ALIAS FOR $n;
这个风格的同一个例子看起来像下面这样：

Sql代码

CREATE FUNCTION sales_tax(real) RETURNS real AS $$

DECLARE

subtotal ALIAS FOR $1;

BEGIN

RETURN subtotal * 0.06;

END;

$$ LANGUAGE plpgsql;

注意：这两个例子不是完全一样的。在第一种情况，subtotal可以用sales_tax.subtotal进行引用，但是在第二种情况下不能这么做。（如果我们给这个内部块附加了一个标签，subtotal能够替代这个标签）
一些更多的例子：

Sql代码

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$

DECLARE

v_string ALIAS FOR $1;

index ALIAS FOR $2;

BEGIN

-- some computations using v_string and index here

END;

$$ LANGUAGE plpgsql;

CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$

BEGIN

RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;

END;

$$ LANGUAGE plpgsql

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

Sql代码

CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$

BEGIN

tax := subtotal * 0.06;

END;

$$ LANGUAGE plpgsql;

注意：我们省略了RETURNS real—我们可以将它包括在内，但它是多余的。
当返回多个值的时候输出参数将非常有用，一个简单的例子是：

Sql代码

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$

BEGIN

sum := x + y;

prod := x * y;

END;

$$ LANGUAGE plpgsql;

如在Section 35.4.4中的讨论，这将为这个函数的结果创建一个匿名的记录类型。如果使用了RETURNS字句，那么必须给它指明RETURNS记录。
另外一种方法声明PL/pgSQL函数是用RETURNS TABLE,例如：

Sql代码

CREATE FUNCTION extended_sales(p_itemno int)

RETURNS TABLE(quantity int, total numeric) AS $$

BEGIN

RETURN QUERY SELECT quantity, quantity * price FROM sales

WHERE itemno = p_itemno;

END;

$$ LANGUAGE plpgsql;

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

Sql代码

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)

RETURNS anyelement AS $$

DECLARE

result ALIAS FOR $0;

BEGIN

result := v1 + v2 + v3;

RETURN result;

END;

$$ LANGUAGE plpgsql

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

Sql代码

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,

OUT sum anyelement)

AS $$

BEGIN

sum := v1 + v2 + v3;

END;

$$ LANGUAGE plpgsql;

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

Sql代码

DECLARE

prior ALIAS FOR old;

updated ALIAS FOR new;

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

最后，贴出解决上面这个问题的存储过程吧：

Sql代码

CREATE OR REPLACE FUNCTION message_deletes(ids "varchar", userid int8)

RETURNS int4 AS

$BODY$

DECLARE

r RECORD;

del bool;

num int4 := 0;

sql "varchar";

BEGIN

sql := 'select id,receiveuserid,senduserid,senddelete,receivedelete from message where id in (' || ids || ')';

FOR r IN EXECUTE sql LOOP

del := false;

IF r.receiveuserid=userid and r.senduserid=userid THEN

del := true;

ELSEIF r.receiveuserid=userid THEN

IF r.senddelete=false THEN

update message set receivedelete=true where id = r.id;

ELSE

del := true;

END IF;

ELSEIF r.senduserid=userid THEN

IF r.receivedelete=false THEN

update message set senddelete=true where id = r.id;

ELSE

del := true;

END IF;

IF del THEN

delete from message where id = r.id;

num := num + 1;

END IF;

END LOOP;

return num;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

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

Sql代码

CREATE OR REPLACE FUNCTION create_table_for_client(id int)

RETURNS integer AS

$BODY$

DECLARE

num int4 := 0;

sql "varchar";

BEGIN

sql := 'create table _' || id || '_company(id int, name text)';

EXECUTE sql;

sql := 'create table _' || id || '_employee(id int, name text)';EXECUTE sql;

sql := 'create table _' || id || '_sale_bill(id int, name text)';EXECUTE sql;

.......

return num;

END;

$BODY$ LANGUAGE plpgsql VOLATILE

自动创建序列
第一个例子

Sql代码

CREATE OR REPLACE FUNCTION auto_gen_seq() RETURNS bigint AS

$BODY$

DECLARE

rd RECORD;

num int4 := 0;

sql "varchar";

seq_sql varchar;

BEGIN

sql := 'SELECT tablename FROM pg_tables WHERE tablename NOT LIKE ''pg%'' AND tablename NOT LIKE ''sql_%'' ORDER BY tablename;';

FOR rd IN EXECUTE sql LOOP

seq_sql:='CREATE SEQUENCE SQ_'||rd.tablename||' START 1000000 CACHE 30;';

BEGIN

EXECUTE seq_sql;

EXCEPTION

WHEN TOO_MANY_ROWS THEN

RAISE EXCEPTION 'employee % not unique', seq_sql;

WHEN OTHERS THEN

return -1;

END;

num := num + 1;

END LOOP;

return num;

END;

$BODY$

LANGUAGE plpgsql VOLATILE NOT LEAKPROOF

COST 100;

调用:

1 2	Sql代码 select auto_gen_seq()

第二个例子

100

101

-- Function: auto_gen_his_table(character)

-- DROP FUNCTION auto_gen_his_table(character);

CREATE OR REPLACE FUNCTION auto_gen_his_table("tbName" character)

RETURNS character varying AS

$BODY$

调用示例:

SELECT tablename as tableName ,auto_gen_his_table(tablename||'') FROM pg_tables

WHERE tablename not LIKE '%his' and tablename='tb_am_server_online';

DECLARE

rd RECORD;

num BIGINT;

name varchar;

typ varchar;

primary_key varchar;

mycursor refcursor;

create_sql varchar;

primary_key_name varchar;

flag_str varchar;

test_str varchar;

BEGIN

test_str:='';

flag_str:='成功:';

primary_key_name:='';

create_sql:='CREATE TABLE '||upper($1)||'_HIS (

HIS_ID INT8 NOT NULL,

HIS_TYPE VARCHAR(50) NULL,

HIS_DATE DATE NULL,

HIS_TIME TIME NULL,

HIS_USER INT8 NULL,

open mycursor for SELECT DISTINCT

a.attnum as num,

a.attname as name,

format_type(a.atttypid, a.atttypmod) as typ,

coalesce(i.indisprimary,false) as primary_key

FROM pg_attribute a

JOIN pg_class pgc ON pgc.oid = a.attrelid

LEFT JOIN pg_index i ON

(pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)

LEFT JOIN pg_description com on

(pgc.oid = com.objoid AND a.attnum = com.objsubid)

LEFT JOIN pg_attrdef def ON

(a.attrelid = def.adrelid AND a.attnum = def.adnum)

WHERE a.attnum > 0 AND pgc.oid = a.attrelid

AND pg_table_is_visible(pgc.oid)

AND NOT a.attisdropped

AND pgc.relname = $1

ORDER BY a.attnum;

fetch mycursor into num,name,typ,primary_key;

while found loop

--create_sql:=create_sql||','||name;

create_sql:=create_sql||upper(name)||' '||upper(typ)||' NULL,

if primary_key='true' then

primary_key_name:=name;

end if;

if primary_key='t' then --实际返回

primary_key_name:=name;

end if;

test_str:=test_str||primary_key;

fetch mycursor into num,name,typ,primary_key;

end loop;

create_sql:=create_sql||'CONSTRAINT PK_'||upper($1)||'_HIS PRIMARY KEY (HIS_ID)

);

create_sql:=create_sql||'CREATE INDEX IDX_'||upper($1)||'_HIS ON '||upper($1)||'_HIS (

HIS_TYPE,

HIS_DATE,

'||upper(primary_key_name)||',

INSTANCE_ID

);';

CLOSE mycursor;

BEGIN

EXECUTE create_sql;

EXCEPTION

WHEN OTHERS THEN

flag_str:='失败:';

END;

--return flag_str||test_str||' ===> '||create_sql;

return flag_str||' ===> '||create_sql;

END;

$BODY$

LANGUAGE plpgsql VOLATILE STRICT

COST 100;

ALTER FUNCTION auto_gen_his_table(character)

OWNER TO postgres;

调用

1 2	SELECT tablename as tableName ,auto_gen_his_table(tablename\|\|'') FROM pg_tables WHERE tablename not LIKE '%his' and upper(tablename)=upper('TB_PM_INDI_QUARTER');

不静之心

postgreSQL存储过程写法示例

发表评论取消回复

访问信息

功能

近期评论

不静之心

postgreSQL存储过程写法示例

发表评论 取消回复

访问信息

功能

近期评论

发表评论取消回复