PostgreSQL DB PLPGSQL Procedure Fundamental Info

1、POSTGRESQL DB PL/PGSQL Procedure Fundamental INFO GLORYSOFT INC.ZP Li2011.12.24QQ:80184001目录PL/PGSQL 函数PL/PGSQL 函数示例PL/PGSQL 的结构PL/PGSQL变量声明PL/PGSQL基本语句PL/PGSQL控制结构PL/PGSQL游标PL/PGSQL错误和消息处理PL/PGSQL附录PL/PGSQL 函数PL/PGSQL 函数的代码都是在 CREATE FUNCTION 里以一个字符串文本的方式声明的。且 字符串文本体 需要使用符号界定符来标志，一般建议用美元符界定的字符串文本来写

2、函数体。可如下写 CREATE FUNCTION 命令,创建函数CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $BODY$ .$BODY$ LANGUAGE plpgsql;注：其中$BODY$ 可成对代替为$PROC$, $ 等其他界定符PL/PGSQL 函数示例CREATE FUNCTION somefunc() RETURNS integer AS $DECLARE quantity integer := 30;BEGIN RAISE NOTICE Quantity here is %, quantity;

3、 - 在这里的数量是 30 quantity := 50; - - 创建一个子块 - DECLARE quantity integer := 80; BEGIN RAISE NOTICE Quantity here is %, quantity; - 在这里的数量是 80 END; RAISE NOTICE Quantity here is %, quantity; - 在这里的数量是 50 RETURN quantity;END;$ LANGUAGE plpgsql;PL/PGSQL 的结构的结构PL/PGSQL 是一种块结构的语言。如下定义一个块： DECLARE declarations

4、 BEGIN statements END label;块中的每个声明和每条语句都是用一个分号终止的，如果一个子块在另外一个块里，那么 END 后面必须有个分号；不过结束函数体的最后的 END 可以不要这个分号。块语句段里的任何语句都可以是一个子块。PL/PGSQL块组成PL/PGSQL函数体；PL/PGSQL变量声明1.变量声明语法name CONSTANT type NOT NULL DEFAULT | := expression;2.变量声明例子user_id integer; quantity numeric(5); url varchar; myrow tablename%ROWTY

5、PE; myfield tablename.columnname%TYPE; arow RECORD;PL/PGSQL变量声明3.函数输入输出参数变量的声明CREATE FUNCTION sum_n_product(IN x int, IN y int, OUT sum int, OUT prod int) AS $ BEGIN sum := x + y; prod := x * y; END; $ LANGUAGE plpgsql;PL/PGSQL变量声明4.拷贝类型拷贝类型variable%TYPE%TYPE 提供一个变量或者表字段的数据类型。 比如，假如在 users 表里面有一个 us

6、er_id 字段。要声明一个和 users.user_id 类型相同的变量，可以这样定义：user_id users.user_id%TYPE;PL/PGSQL变量声明5.5.行类型行类型name table_name%ROWTYPE; 一个复合类型变量叫做行变量(或者 row-type 变量)。这样的一个变量可以保存一次 SELECT 或 FOR 命令结果的完整一行，只要命令的字段集匹配该变量声明的类型。行数值的字段使用点表示法访问，比如 rowvar.field 。6.6.记录类型记录类型name RECORD;记录变量类似行类型变量，但是它们没有预定义的结构。它们在 SELECT 或 F

7、OR 命令中获取实际的行结构。附：除以上类型外，下表是基本的标量数据类型列表附：除以上类型外，下表是基本的标量数据类型列表名字名字别名别名描述描述bigintint8有符号 8 字节整数bigserialserial8自增 8 字节整数bit (n) 定长位串bit varying (n) varbit变长位串booleanbool逻辑布尔值(真/假)box平面中的矩形bytea二进制数据(字节数组)character varying (n) varchar (n) 变长字符串character (n) char (n) 定长字符串cidrIPv4 或 IPv6 网络地址circle平面中的圆

8、date日历日期(年, 月, 日)double precisionfloat8双精度浮点数字inetIPv4 或 IPv6 网络地址integerint, int4有符号 4 字节整数interval (p) 时间间隔line平面中的无限长直线lseg平面中的线段macaddrMAC 地址numeric (p, s) decimal (p, s) 可选精度的准确数字path平面中的几何路径point平面中的点polygon平面中的封闭几何路径realfloat4单精度浮点数smallintint2有符号 2 字节整数serialserial4自增 4 字节整数text变长字符串time (p)

9、 without time zone 一天中的时间time (p) with time zonetimetz一天里的时间，包括时区timestamp (p) without time zone 日期和时间timestamp (p) with time zonetimestamptz日期和时间，包括时区PL/PGSQL基本语句1.赋值语句赋值语句给一个变量或行/记录赋值用下面方法：identifier := expression;例子：user_id := 20; tax := subtotal * 0.06;PL/PGSQL基本语句2.执行一个仅有单行结果的执行一个仅有单行结果的SELECT

10、INTO 查询语句查询语句如果一个 SQL 命令的结果是一个单独的行(可能有多个字段)，那么可以将其赋予一个记录变量、行类型变量、标量变量的列表。这可以通过在基本 SQL 命令之后添加一个 INTO 子句达到。如下：SELECT select_expressions INTO STRICT target FROM .; INSERT . RETURNING expressions INTO STRICT target; UPDATE . RETURNING expressions INTO STRICT target; DELETE . RETURNING expressions INTO S

11、TRICT target;注： 其中target 是一个变量；如果没有指定 STRICT ，那么 target 将被设为查询返回结果的第一行或者 NULL(查询返回零行)，可以检查特殊变量 FOUND来判断查询是否至少返回一行。如果指定了 STRICT 选项，那么查询必须返回恰好一行，否则将产生 NO_DATA_FOUND(没有行) 或者 TOO_MANY_ROWS(多于一行)运行时错误。可以使用异常块来捕获这些错误。例子BEGINSELECT * INTO myrec FROM emp WHERE empname = myname; IF NOT FOUND THEN RAISE EXCEP

12、TION employee % not found, myname; END IF;END;BEGINSELECT * INTO STRICT myrec FROM emp WHERE empname = myname; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION employee % not found, myname; WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION employee % not unique, myname; END;PL/PGSQL基本语句3.赋值语句赋值语句什么也不做的占位语句,可

13、以使用 NULL 语句或空语句达到这个目的。如下：NULL;例，下面两段代码对等：BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN NULL; - 忽略错误 END; BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN -空语句，忽略错误 END;PL/PGSQL基本语句4.执行动态命令执行动态命令EXECUTE语句语句EXECUTE 语句：EXECUTE command-string INTO STRICT target;例子例子EXECUTE UPDATE t SET ke

14、y | = | quote_literal(newkey) | WHERE id = | newid;注：给表字段赋值的变量为字符串时，该语句中需使用函数注：给表字段赋值的变量为字符串时，该语句中需使用函数quote_literal。PL/PGSQL控制结构控制结构可能是 PL/PGSQL 中最有用的(以及最重要)的部分了。1.从函数返回从函数返回有两个命令可以用来从函数中返回数据：RETURN 和 RETURN NEXT 。1.1.RETURNRETURN expression;带表达式的 RETURN 用于终止函数并把 expression的值返回给调用者。1.2.RETURN NEXTR

15、ETURN NEXT expression;RETURN NEXT 实际上并不从函数中返回，它只是简单地把表达式的值保存起来。然后执行继续执行 PL/pgSQL 函数里的下一条语句。随着后继的 RETURN NEXT 命令的执行，结果集就建立起来了。最后一个 RETURN 应该没有参数，它导致控制退出该函数(或者你可以简单地让控制到达函数的结尾)。使用 RETURN NEXT 的函数应该按照下面的风格调用：SELECT * FROM some_func();PL/PGSQL控制结构1.3.函数返回示例CREATE TABLE foo (fooid INT, foosubid INT, foon

16、ame TEXT); INSERT INTO foo VALUES (1, 2, three); INSERT INTO foo VALUES (4, 5, six); CREATE OR REPLACE FUNCTION getallfoo() RETURNS SETOF foo AS$BODY$ DECLARE r foo%rowtype; BEGIN FOR r IN SELECT * FROM foo WHERE fooid 0 LOOP - can do some processing here RETURN NEXT r; - return next row of SELECT E

17、ND LOOP; RETURN; END $BODY$ LANGUAGE plpgsql; SELECT * FROM getallfoo();PL/PGSQL控制结构2.条件条件IF 语句让你可以根据某种条件执行命令。PL/PGSQL 有五种形式的 IF ：IF . THENIF . THEN . ELSEIF . THEN . ELSE IFIF . THEN . ELSIF . THEN . ELSEIF . THEN . ELSEIF . THEN . ELSEPL/PGSQL控制结构2.1. IF-THENIF boolean-expression THEN statementsEN

18、D IF;IF-THEN 语句是 IF 的最简单形式。如果条件为真，在 THEN 和 END IF 之间的语句将被执行。否则，将忽略它们。例子：IF v_user_id 0 THEN UPDATE users SET email = v_email WHERE user_id = v_user_id;END IF;PL/PGSQL控制结构 2.2. IF-THEN-ELSEIF boolean-expression THEN statementsELSE statementsEND IF;IF-THEN-ELSE 语句增加了 IF-THEN 的分支，让你可以声明在条件为假的时候执行的语句。例子

19、：IF parentid IS NULL OR parentid = THEN RETURN fullname;ELSE RETURN hp_true_filename(parentid) | / | fullname;END IF;IF v_count 0 THEN INSERT INTO users_count (count) VALUES (v_count); RETURN t;ELSE RETURN f;END IF;PL/PGSQL控制结构2.3. IF-THEN-ELSE IFIF 语句可以像下面的例子那样嵌套：IF demo_row.sex = m THEN pretty_sex

20、 := man;ELSE IF demo_row.sex = f THEN pretty_sex := woman; END IF;END IF;注：这种形式实际上就是在另外一个 IF 语句的 ELSE 部分嵌套了另一个 IF 语句。PL/PGSQL控制结构2.4. IF-THEN-ELSIF-ELSEIF boolean-expression THEN statementsELSIF boolean-expression THEN statementsELSIF boolean-expression THEN statements .ELSE statementsEND IF;IF-THEN

21、-ELSIF-ELSE 提供了一种更方便的方法用于在一条语句中检查许多候选条件。形式上它和嵌套的 IF-THEN-ELSE-IF-THEN 命令相同，但是只需要一个 END IF 。PL/PGSQL控制结构2.5. IF-THEN-ELSEIF-ELSEELSEIF 是 ELSIF 的别名。IF-THEN-ELSIF-ELSE 例子：IF number = 0 THEN result := zero;ELSIF number 0 THEN result := positive;ELSIF number 0 THEN result := negative;ELSE - hmm, the only

22、 other possibility is that number is null result := NULL;END IF;PL/PGSQL控制结构3. 简单循环简单循环使用 LOOP, EXIT, CONTINUE, WHILE, FOR 语句，可以控制 PL/PGSQL 函数重复一系列命令。3.1. LOOPLOOP statementsEND LOOP label;LOOP 定义一个无条件的循环，无限循环，直到由 EXIT 或 RETURN 语句终止。可选的 label 可以由 EXIT 和 CONTINUE 语句使用，用于在嵌套循环中声明应该应用于哪一层循环。PL/PGSQL控制结

23、构3.2. EXITEXIT label WHEN expression;如果没有给出 label ，那么退出最内层的循环，然后执行跟在 END LOOP 后面的语句。如果给出 label ，那么它必须是当前或者更高层的嵌套循环块或者语句块的标签。然后该命名块或者循环就会终止，而控制落到对应循环/块的 END 语句后面的语句上。如果声明了 WHEN ，循环退出只有在 expression 为真的时候才发生，否则控制会落到 EXIT 后面的语句上。EXIT 可以用于在所有的循环类型中，它并不仅仅限制于在无条件循环中使用。在和 BEGIN 块一起使用的时候，EXIT 把控制交给块结束后的下一个语句

24、。PL/PGSQL控制结构EXIT例子：例子：LOOP - 一些计算 IF count 0 THEN EXIT; - 退出循环 END IF;END LOOP;LOOP - 一些计算 EXIT WHEN count 0; - same result as previous exampleEND LOOP;BEGIN - 一些计算 IF stocks 100000 THEN EXIT; - 导致从 BEGIN 块里退出 END IF;END;PL/PGSQL控制结构3.3. CONTINUECONTINUE label WHEN expression;如果没有给出 label ，那么就开始最内层

25、循环的下一次执行。也就是说，控制传递回给循环控制表达式(如果有)，然后重新计算循环体。如果出现了 label ，它声明即将继续执行的循环的标签。如果声明了 WHEN ，那么循环的下一次执行只有在 expression 为真的情况下才进行。否则，控制传递给 CONTINUE 后面的语句。CONTINUE 可以用于所有类型的循环；它并不仅仅限于无条件循环。CONTINUE例子：LOOP - 一些计算 EXIT WHEN count 100; CONTINUE WHEN count 50; - 一些在 count 数值在50 . 100里面时候的计算END LOOP;PL/PGSQL控制结构3.4.

26、 WHILEWHILE expression LOOP statementsEND LOOP label;只要条件表达式为真， WHILE 语句就会不停的在一系列语句上进行循环，条件是在每次进入循环体的时候检查的。WHILE 例子:WHILE amount_owed 0 AND gift_certificate_balance 0 LOOP - 一些计算END LOOP;WHILE NOT boolean_expression LOOP - 一些计算END LOOP;PL/PGSQL控制结构3.5. FOR (integer variant)FOR name IN REVERSE expres

27、sion . expression BY expression LOOP statementsEND LOOP label;这种形式的 FOR 对一定范围的整数进行迭代的循环。变量 name 会自动定义为 integer 类型并且只在循环里存在(任何该变量名的现存定义在此循环内都将被忽略)。给出范围上下界的两个表达式在进入循环的时候计算一次。BY 子句指定迭代步长(缺省为 1)，但如果声明了 REVERSE 步长将变为相应的负值。整数 FOR 循环的例子：FOR i IN 1.10 LOOP - 一些计算 RAISE NOTICE i is %, i;END LOOP;FOR i IN REV

28、ERSE 10.1 LOOP - 一些计算END LOOP;FOR i IN REVERSE 10.1 BY 2 LOOP - 一些计算 RAISE NOTICE i is %, i;END LOOP;PL/PGSQL控制结构4. 遍历命令结果遍历命令结果使用不同类型的使用不同类型的 FOR 循环，你可以遍历一个命令的结果并且对其进行相应的操作。循环，你可以遍历一个命令的结果并且对其进行相应的操作。语法：语法：FOR target IN query LOOP statementsEND LOOP label;FOR-IN-EXECUTE 语句是遍历所有行的另外一种方法：语句是遍历所有行的另外一

29、种方法：FOR target IN EXECUTE text_expression LOOP statementsEND LOOP label;注：注：target 是一个记录变量、行变量、逗号分隔的标量变量列表。target 被连续不断被赋予所有来自 query 的行，并且循环体将为每行执行一次。PL/PGSQL控制结构FOR遍历记录例子遍历记录例子CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT); INSERT INTO foo VALUES (1, 2, three); INSERT INTO foo VALUES (4,

30、5, six); CREATE OR REPLACE FUNCTION getallfoo() RETURNS SETOF foo AS$BODY$ DECLARE r foo%rowtype; BEGIN FOR r IN SELECT * FROM foo WHERE fooid 0 LOOP - can do some processing here RETURN NEXT r; - return next row of SELECT END LOOP; RETURN; END $BODY$ LANGUAGE plpgsql; SELECT * FROM getallfoo();PL/P

31、GSQL控制结构5. 捕获错误捕获错误缺省时，一个在 PL/pgSQL 函数里发生的错误退出函数的执行，并且实际上其周围的事务也会退出。你可以使用一个带有 EXCEPTION 子句的 BEGIN 块捕获错误并且从中恢复。其语法是正常的 BEGIN 块语法的一个扩展：DECLARE declarationsBEGIN statementsEXCEPTION WHEN condition OR condition . THEN handler_statements WHEN condition OR condition . THEN handler_statements .END;如果没有发生错误

32、，这种形式的块只是简单地执行所有 statements ，然后转到下一个 END 之后的语句。但是如果在 statements 内部发生了一个错误，则对 statements 的进一步处理将废弃，然后转到 EXCEPTION 列表。系统搜索这个列表，寻找匹配错误的第一个 condition 。如果找到匹配，则执行对应的 handler_statements ，然后转到 END 之后的下一个语句。如果没有找到匹配，该错误就会广播出去，就好像根本没有 EXCEPTION 子句一样：该错误可以被一个包围块用 EXCEPTION 捕获，如果没有包围块，则退出函数的处理。condition 的名字可以是

33、 附录A里显示的任何名字。一个范畴名匹配任意该范畴里的错误。特殊的条件名 OTHERS 匹配除了 QUERY_CANCELED 之外的所有错误类型。可以用名字捕获 QUERY_CANCELED ，不过通常是不明智的。条件名是大小写无关的。如果在选中的 handler_statements 里发生了新错误，那么它不能被这个 EXCEPTION 子句捕获，而是传播出去。一个外层的 EXCEPTION 子句可以捕获它。PL/PGSQL控制结构捕获捕获UPDATE/INSERT 异常例子异常例子CREATE TABLE db (a INT PRIMARY KEY, b TEXT);CREATE FUN

34、CTION merge_db(key INT, data TEXT) RETURNS VOID AS$BEGIN LOOP UPDATE db SET b = data WHERE a = key; IF found THEN RETURN; END IF; BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN - do nothing END; END LOOP;END;$LANGUAGE plpgsql;SELECT merge_db(1, david);SELE

35、CT merge_db(1, dennis);PL/PGSQL游标1. 声明游标变量声明游标变量在 PL/pgSQL 里对游标的访问都是通过游标变量实现的，它总是特殊的数据类型 refcursor 。创建游标变量的一个方法是把它声明为一个类型为 refcursor 的变量。另外一个方法是使用游标声明语法，如下：name CURSOR ( arguments ) FOR query;其中 FOR 可以被替换为 IS 。如果有 arguments ，那么它是一个逗号分隔的 name datatype 对的列表，它们定义那些将会用参数值替换掉的所给出命令中的名字。实际用于代换这些名字的数值将在在游标

36、打开之后声明。例子：DECLARE curs1 refcursor; curs2 CURSOR FOR SELECT * FROM tablename; curs3 CURSOR (key integer) IS SELECT * FROM tablename WHERE unique1 = key;所有这三个变量都是 refcursor 类型。PL/PGSQL游标2. 打开游标打开游标PL/PGSQL 有三种形式的 OPEN 语句，两种用于未绑定的游标变量，另外一种用于已绑定的游标变量。2.1. OPEN FOR queryOPEN unbound_cursor FOR query;例：例：

37、 OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;2.2. OPEN FOR EXECUTEOPEN unbound_cursor FOR EXECUTE query_string;例：例： OPEN curs1 FOR EXECUTE SELECT * FROM | quote_ident($1);2.3. 打开一个绑定的游标打开一个绑定的游标OPEN bound_cursor ( argument_values );例：例：OPEN curs2;OPEN curs3(42);PL/PGSQL游标3. 使用游标使用游标一旦已经打开了一个游

38、标，那么就可以用下面的语句操作它。3.1. FETCHFETCH cursor INTO target;FETCH 从游标中检索下一行到目标中，目标可以是一个行变量、记录变量、逗号分隔的普通变量列表。可以使用特殊变量 FOUND 检查是否检索出一个行。例子：FETCH curs1 INTO rowvar;FETCH curs2 INTO foo, bar, baz;PL/PGSQL游标3.2. CLOSECLOSE cursor;CLOSE 关闭支撑在一个打开的游标下面的信使。这样就可以在事务结束之前释放资源，或者释放掉该游标变量，用于稍后再次打开。例：CLOSE curs1;PL/PGSQL

39、游标3.3. 返回游标返回游标PL/PGSQL 函数可以向调用者返回游标。这个功能用于从函数里返回多行或多列，特别是巨大的结果集。要想这么做，该函数必须打开游标并且把该游标的名字返回给调用者，或者简单的使用指定的入口名或调用者已知的名字打开游标。调用者然后从游标里抓取行。游标可以由调用者关闭，或者是在事务结束的时候自动关闭。PL/PGSQL游标下面的例子显示了一个调用者声明游标名字的方法：CREATE TABLE test (col text);INSERT INTO test VALUES (123);select * from test ;CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS $BEGIN OPEN $1 FOR SELECT col FROM test; RETURN $1;END;$ LANGUAGE plpgsql;BEGIN;SELECT