一、概述:
@H_
301_4@
@H_
301_4@ PL/pg
sql函数在第一次被
调用时,其
函数内的源
代码(文本)将被解析为二进制指令树,但是
函数内的表达式和
sql命令只有在首次用到它们的时候,PL/pg
sql解释器才会为其创建一个准备好的执行规划,随后对该表达式或
sql命令的访问都将使用该规划。如果在一个条件语句中,有部分
sql命令或表达式没有被用到,那么PL/pg
sql解释器在本次
调用中将不会为其准备执行规划,这样的好处是可以有效地减少为PL/pg
sql函数里的语句
生成分析和执行规划的总时间,然而缺点是某些表达式或
sql命令中的
错误只有在其被执行到的时候才能发现。
@H_
301_4@ 由于PL/pg
sql在
函数里为一个命令制定了执行计划,那么在本次会话中该计划将会被反复使用,这样做往往可以得到更好的
性能,但是如果你动态
修改了相关的
数据库对象,那么就有可能产生问题,如:
@H_
301_4@
CREATE FUNCTION populate() RETURNS integer AS $$
@H_
301_4@
DECLARE
@H_
301_4@
-- 声明段
@H_
301_4@
BEGIN
@H_
301_4@
PERFORM my_function();
@H_
301_4@
END;@H_301_4@ $$ LANGUAGE plpgsql;
@H_
301_4@ 在
调用以上
函数时,PERFORM语句的执行计划将引用my_function对象的OID。在此之后,如果你重建了my_function
函数,那么populate
函数将无法再找到原有my_function
函数的OID。要
解决该问题,可以选择重建populate
函数,或者重新
登录建立新的会话,以使Postgre
sql重新编译该
函数。要想规避此类问题的发生,在重建my_function时可以使用CREATE OR REPLACE FUNCTION命令。
@H_
301_4@ 鉴于以上规则,在PL/pg
sql里直接出现的
sql命令必须在每次执行时均引用相同的表和字段,换句话说,不能将
函数的参数用作
sql命令的表名或字段名。如果想绕开该限制,可以考虑使用PL/pg
sql中的EXECUTE语句动态地构造命令,由此换来的代价是每次执行时都要构造一个新的命令计划。
@H_
301_4@ 使用PL/pg
sql函数的一个非常重要的优势是可以提高程序的执行效率,由于原有的
sql调用不得不在客户端与服务器之间反复传递数据,这样不仅
增加了进程间通讯所产生的开销,而且也会大大
增加网络IO的开销。
@H_
301_4@
@H_
301_4@
二、PL/pgsql的结构:
@H_
301_4@
@H_
301_4@ PL/pg
sql是一种块结构语言,
函数定义的所有文本都必须在一个块内,其中块中的每个声明和每条语句都是以分号结束,如果某一子块在另外一个块内,那么该子块的END关键字后面必须以分号结束,不过对于
函数体的最后一个END关键字,分号可以省略,如:
@H_
301_4@
[ <<label>> ]
@H_
301_4@
[ DECLARE declarations ]
@H_
301_4@
BEGIN
@H_
301_4@
statements
@H_
301_4@
END [ label ];
@H_
301_4@ 在PL/pg
sql中有两种注释类型,双破折号
(--)表示单行注释。
/* */表示多行注释,该注释类型的规则等同于C语言中的多行注释。
@H_
301_4@ 在语句块前面的声明段中定义的变量在每次进入语句块(BEGIN)时都会将声明的变量初始化为它们的缺省值,而不是每次
函数调用时初始化一次。如:
@H_
301_4@
CREATE FUNCTION somefunc() RETURNS integer AS $$
@H_
301_4@
DECLARE
@H_
301_4@
quantity integer := 30;
@H_
301_4@
BEGIN
@H_
301_4@
RAISE NOTICE 'Quantity here is %',quantity; --在这里的数量是30
@H_
301_4@
quantity := 50;
@H_
301_4@
--
@H_
301_4@
-- 创建一个子块
@H_
301_4@
--
@H_
301_4@
DECLARE
@H_
301_4@
quantity integer := 80;
@H_
301_4@
BEGIN
@H_
301_4@
RAISE NOTICE 'Quantity here is %',quantity; --在这里的数量是80
@H_
301_4@
END;
@H_
301_4@
RAISE NOTICE 'Quantity here is %',quantity; --在这里的数量是50
@H_
301_4@
RETURN quantity;
@H_
301_4@
END;
@H_
301_4@
$$ LANGUAGE plpgsql;
@H_
301_4@
#执行该函数以进一步观察其执行的结果。
@H_
301_4@
postgres=# select somefunc();
@H_
301_4@ NOTICE:Quantity here is 30
@H_
301_4@ NOTICE:Quantity here is 80
@H_
301_4@ NOTICE:Quantity here is 50
@H_
301_4@ somefunc
@H_
301_4@ ----------
@H_
301_4@ 50
@H_
301_4@ (1 row)
@H_
301_4@ 最后需要说明的是,目前版本的Postgre
sql并
不支持嵌套事务,
函数中的事物总是由外层命令(
函数的
调用者)来控制的,它们本身无法开始或提交事务。
@H_
301_4@
@H_
301_4@
三、声明:
@H_
301_4@
@H_
301_4@ 所有在块里使用的变量都必须在块的声明段里先进行声明,唯一的例外是FOR循环里的循环计数变量,该变量被
自动声明为整型。变量声明的语法如下:
@H_
301_4@
variable_name [ CONSTANT ] variable_type [ NOT NULL ] [ { DEFAULT | := } expression ];
@H_
301_4@ 1).
sql中的数据类型均可作为PL/pg
sql变量的数据类型,如integer、varchar和char等。
@H_
301_4@ 2). 如果给出了DEFAULT子句,该变量在进入BEGIN块时将被初始化为该缺省值,否则被初始化为
sql空值。缺省值是在每次进入该块时进行计算的。因此,如果把now()赋予一个类型为timestamp的变量,那么该变量的缺省值将为
函数实际
调用时的时间,而不是
函数预编译时的时间。
@H_
301_4@ 3). CONSTANT选项是为了避免该变量在进入BEGIN块后被重新赋值,以保证该变量为常量。
@H_
301_4@ 4). 如果声明了NOT NULL,那么赋予NULL数值给该变量将导致一个运行时
错误。因此所有声明为NOT NULL的变量也必须在声明时定义一个非空的缺省值。
@H_
301_4@
@H_
301_4@
1. 函数参数的别名:
@H_
301_4@ 传递给
函数的参数都是用$1、$2这样的标识符来表示的。为了
增加可读性,我们可以为其声明别名。之后别名和数字标识符均可指向该参数值,见如下示例:
@H_
301_4@ 1). 在
函数声明的同时给出参数变量名。
@H_
301_4@
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
@H_
301_4@
BEGIN
@H_
301_4@
RETURN subtotal * 0.06;
@H_
301_4@
END;
@H_
301_4@
$$ LANGUAGE plpgsql;
@H_
301_4@ 2). 在声明段中为参数变量定义别名。
@H_
301_4@
CREATE FUNCTION sales_tax(REAL) RETURNS real AS $$
@H_
301_4@
DECLARE
@H_
301_4@
subtotal ALIAS FOR $1;
@H_
301_4@
BEGIN
@H_
301_4@
RETURN subtotal * 0.06;
@H_
301_4@
END;
@H_
301_4@
$$ LANGUAGE plpgsql;
@H_
301_4@ 3). 对于
输出参数而言,我们仍然可以遵守1)和2)中的规则。
@H_
301_4@
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
@H_
301_4@
BEGIN
@H_
301_4@
tax := subtotal * 0.06;
@H_
301_4@
END;
@H_
301_4@
$$ LANGUAGE plpgsql;
@H_
301_4@ 4). 如果PL/pg
sql函数的返回类型为多态类型(anyelement或anyarray),那么
函数就会创建一个特殊的参数:$0。我们仍然可以为该变量设置别名。
@H_
301_4@
CREATE FUNCTION add_three_values(v1 anyelement,v2 anyelement,v3 anyelement)
@H_
301_4@
RETURNS anyelement AS $$
@H_
301_4@
DECLARE
@H_
301_4@
result ALIAS FOR $0;
@H_
301_4@
BEGIN
@H_
301_4@
result := v1 + v2 + v3;
@H_
301_4@
RETURN result;
@H_
301_4@
END;
@H_
301_4@
$$ LANGUAGE plpgsql;
@H_
301_4@
@H_
301_4@
2. 拷贝类型:
@H_
301_4@ 见如下形式的变量声明:
@H_
301_4@
variable%TYPE
@H_
301_4@ %TYPE表示一个变量或表字段的数据类型,PL/pg
sql允许通过该方式声明一个变量,其类型等同于variable或表字段的数据类型,见如下示例:
@H_
301_4@
user_id users.user_id%TYPE;
@H_
301_4@ 在上面的例子中,变量user_id的数据类型等同于users表中user_id字段的类型。
@H_
301_4@ 通过使用%TYPE,一旦引用的变量类型今后发生改变,我们也无需
修改该变量的类型声明。最后需要说明的是,我们可以在
函数的参数和返回值中使用该方式的类型声明。
@H_
301_4@
@H_
301_4@
3. 行类型:
@H_
301_4@ 见如下形式的变量声明:
@H_
301_4@
name table_name%ROWTYPE;
@H_
301_4@
name composite_type_name;
@H_
301_4@ table_name%ROWTYPE表示指定表的行类型,我们在创建一个表的时候,Postgre
sql也会随之创建出一个与之相应的复合类型,该类型名等同于表名,因此,我们可以通过以上两种方式来声明行类型的变量。由此方式声明的变量,可以保存SELECT返回结果中的一行。如果要访问变量中的某个域字段,可以使用点表示法,如rowvar.field,但是行类型的变量只能访问
自定义字段,无法访问系统提供的隐含字段,如OID等。对于
函数的参数,我们只能使用复合类型标识变量的数据类型。最后需要说明的是,推荐使用%ROWTYPE的声明方式,这样可以具有更好的可移植性,因为在
Oracle的PL/
sql中也存在相同的概念,其声明方式也为%ROWTYPE。见如下示例:
@H_
301_4@
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
@H_
301_4@
DECLARE
@H_
301_4@
t2_row table2%ROWTYPE;
@H_
301_4@
BEGIN
@H_
301_4@
SELECT * INTO t2_row FROM table2 WHERE id = 1 limit 1;
@H_
301_4@
RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
@H_
301_4@
END;
@H_
301_4@
$$ LANGUAGE plpgsql;
@H_
301_4@
@H_
301_4@
4. 记录类型:
@H_
301_4@ 见如下形式的变量声明:
@H_
301_4@
name RECORD;
@H_
301_4@ 记录变量类似于行类型变量,但是它们没有预定义的结构,只能通过SELECT或FOR命令来
获取实际的行结构,因此记录变量在被初始化之前无法访问,否则将引发运行时
错误。
@H_
301_4@ 注:RECORD不是真正的数据类型,只是一个占位符。
@H_
301_4@
@H_
301_4@
四、基本语句:
@H_
301_4@
@H_
301_4@
1. 赋值:
@H_
301_4@ PL/pg
sql中赋值语句的形式为:
identIFier := expression,等号两端的变量和表达式的类型或者一致,或者可以通过Postgre
sql的转换规则进行转换,否则将会导致运行时
错误,见如下示例:
@H_
301_4@
user_id := 20;
@H_
301_4@
tax := subtotal * 0.06;
@H_
301_4@
@H_
301_4@
2. SELECT INTO:
@H_
301_4@ 通过该语句可以为记录变量或行类型变量进行赋值,其表现形式为:
SELECT INTO target select_expressions FROM ...,该赋值方式一次只能赋值一个变量。表达式中的target可以表示为是一个记录变量、行变量,或者是一组用逗号分隔的简单变量和记录/行字段的列表。select_expressions以及剩余部分和普通
sql一样。
@H_
301_4@ 如果将一行或者一个变量列表用做目标,那么选出的数值必需精确匹配目标的结构,否则就会产生运行时
错误。如果目标是一个记录变量,那么它
自动将自己构造成命令结果列的行类型。如果命令返回零行,目标被赋予空值。如果命令返回多行,那么将只有第一行被赋予目标,其它行将被忽略。在执行SELECT INTO语句之后,可以通过检查内置变量FOUND来判断本次赋值是否成功,如:
@H_
301_4@
SELECT INTO myrec * FROM emp WHERE empname = myname;
@H_
301_4@
IF NOT FOUND THEN
@H_
301_4@
RAISE EXCEPTION 'employee % not found',myname;
@H_
301_4@
END IF;
@H_
301_4@ 要测试一个记录/行结果是否为空,可以使用IS NULL条件进行判断,但是对于返回多条记录的情况则无法判断,如:
@H_
301_4@
DECLARE
@H_
301_4@
users_rec RECORD;
@H_
301_4@
BEGIN
@H_
301_4@
SELECT INTO users_rec * FROM users WHERE user_id = 3;
@H_
301_4@
IF users_rec.homepage IS NULL THEN
@H_
301_4@
RETURN 'http://';
@H_
301_4@
END IF;
@H_
301_4@
END;
@H_
301_4@
@H_
301_4@
3. 执行一个没有结果的表达式或者命令:
@H_
301_4@ 在
调用一个表达式或执行一个命令时,如果对其返回的结果不感兴趣,可以考虑使用PERFORM语句:
PERFORM query,该语句将执行PERFORM之后的命令并忽略其返回的结果。其中query的写法和普通的
sql SELECT命令是一样的,只是把开头的关键字SELECT替换成PERFORM,如:
@H_
301_4@
PERFORM create_mv('cs_session_page_requests_mv',my_query);
@H_
301_4@
@H_
301_4@
4. 执行动态命令:
@H_
301_4@ 如果在PL/pg
sql函数中操作的表或数据类型在每次
调用该
函数时都可能会发生变化,在这样的情况下,可以考虑使用PL/pg
sql提供的EXECUTE语句:
EXECUTE command-string [ INTO target ],其中command-string是用一段文本表示的表达式,它包含要执行的命令。而target是一个记录变量、行变量或者一组用逗号分隔的简单变量和记录/行域的列表。这里需要特别注意的是,该命令字符串将不会发生任何PL/pg
sql变量代换,变量的数值必需在构造命令字符串时插入到该字符串中。
@H_
301_4@ 和所有其它PL/pg
sql命令不同的是,一个由EXECUTE语句运行的命令在服务器内并不会只prepare和保存一次。相反,该语句在每次运行的时候,命令都会prepare一次。因此命令字符串可以在
函数里动态的
生成以便于对各种不同的表和字段进行操作,从而提高
函数的灵活性。然而由此换来的却是
性能上的折损。见如下示例:
@H_
301_4@
EXECUTE 'UPDATE tbl SET ' || quote_ident(columnname) || ' = ' || quote_literal(newvalue);
@H_
301_4@
@H_
301_4@
五、控制结构:
@H_
301_4@
@H_
301_4@
1. 函数返回:
@H_
301_4@ 1). RETURN expression
@H_
301_4@ 该表达式用于终止当前的
函数,然后再将expression的值返回给
调用者。如果返回简单类型,那么可以使用任何表达式,同时表达式的类型也将被
自动转换成
函数的返回类型,就像我们在赋值中描述的那样。如果要返回一个复合类型的数值,则必须让表达式返回记录或者匹配的行变量。
@H_
301_4@ 2). RETURN NEXT expression
@H_
301_4@ 如果PL/pg
sql函数声明为返回SETOF sometype,其行记录是通过RETURN NEXT命令进行填充的,直到执行到不带参数的RETURN时才表示该
函数结束。因此对于RETURN NEXT而言,它实际上并不从
函数中返回,只是简单地把表达式的值保存起来,然后继续执行PL/pg
sql函数里的下一条语句。随着RETURN NEXT命令的迭代执行,结果集最终被建立起来。该类
函数的
调用方式如下:
@H_
301_4@
SELECT * FROM some_func();
@H_
301_4@ 它被放在FROM子句中作为数据源使用。最后需要指出的是,如果结果集
数量很大,那么通过该种方式来构建结果集将会导致极大的
性能损失。
@H_
301_4@
@H_
301_4@
2. 条件:
@H_
301_4@ 在PL/pg
sql中有以下三种形式的条件语句。
@H_
301_4@ 1). IF-THEN
@H_
301_4@
IF boolean-expression THEN
@H_
301_4@
statements
@H_
301_4@
END IF
;
@H_
301_4@ 2). IF-THEN-ELSE
@H_
301_4@
IF boolean-expression THEN
@H_
301_4@
statements
@H_
301_4@
ELSE
@H_
301_4@
statements
@H_
301_4@
END IF;
@H_
301_4@ 3). IF-THEN-ELSIF-ELSE
@H_
301_4@
IF boolean-expression THEN
@H_
301_4@
statements
@H_
301_4@
ELSIF boolean-expression THEN
@H_
301_4@
statements
@H_
301_4@
ELSIF boolean-expression THEN
@H_
301_4@
statements
@H_
301_4@
ELSE
@H_
301_4@
statements
@H_
301_4@
END IF;
@H_
301_4@ 关于条件语句,这里就不在做过多的赘述了。
@H_
301_4@
@H_
301_4@
3. 循环:
@H_
301_4@ 1). LOOP
@H_
301_4@
LOOP
@H_
301_4@
statements
@H_
301_4@
END LOOP [ label ];
@H_
301_4@ LOOP定义一个无条件的循环,直到由EXIT或者RETURN语句终止。可选的label可以由EXIT和CONTINUE语句使用,用于在嵌套循环中声明应该应用于哪一层循环。
@H_
301_4@ 2). EXIT
@H_
301_4@
EXIT [ label ] [ WHEN expression ];
@H_
301_4@ 如果没有给出label,就
退出最内层的循环,然
后执行跟在END LOOP后面的语句。如果给出label,它必须是当前或更高层的嵌套循环块或语句块的
标签。之后该命名块或循环就会终止,而控制则直接转到对应循环/块的END语句后面的语句上。
@H_
301_4@ 如果声明了WHEN,EXIT命令只有在expression为真时才被执行,否则将直接执行EXIT后面的语句。见如下示例:
@H_
301_4@
LOOP
@H_
301_4@
-- do something
@H_
301_4@
EXIT WHEN count > 0;
@H_
301_4@
END LOOP;
@H_
301_4@ 3). CONTINUE
@H_
301_4@
CONTINUE [ label ] [ WHEN expression ];
@H_
301_4@ 如果没有给出label,CONTINUE就会跳到最内层循环的开始处,重新进行判断,以决定是否继续执行循环内的语句。如果指定label,则跳到该label所在的循环开始处。如果声明了WHEN,CONTINUE命令只有在expression为真时才被执行,否则将直接执行CONTINUE后面的语句。见如下示例:
@H_
301_4@
LOOP
@H_
301_4@
-- do something
@H_
301_4@
EXIT WHEN count > 100;
@H_
301_4@
CONTINUE WHEN count < 50;
@H_
301_4@
END LOOP;
@H_
301_4@ 4). WHILE
@H_
301_4@
[ <<label>> ]
@H_
301_4@
WHILE expression LOOP
@H_
301_4@
statements
@H_
301_4@
END LOOP [ label ];
@H_
301_4@ 只要条件表达式为真,其块内的语句就会被循环执行。条件是在每次进入循环体时进行判断的。见如下示例:
@H_
301_4@
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
@H_
301_4@
--do something
@H_
301_4@
END LOOP;
@H_
301_4@ 5). FOR
@H_
301_4@
[ <<label>> ]
@H_
301_4@
FOR name IN [ REVERSE ] expression .. expression LOOP
@H_
301_4@
statements
@H_
301_4@
END LOOP [ label ];
@H_
301_4@ 变量name
自动被定义为integer类型,其作用域仅为FOR循环的块内。表示范围上下界的两个表达式只在进入循环时计算一次。每次迭代name值自增1,但如果声明了REVERSE,name变量在每次迭代中将自减1,见如下示例:
@H_
301_4@
FOR i IN 1..10 LOOP
@H_
301_4@
--do something
@H_
301_4@
RAISE NOTICE 'i IS %',i;
@H_
301_4@
END LOOP;
@H_
301_4@
@H_
301_4@
FOR i IN REVERSE 10..1 LOOP
@H_
301_4@
--do something
@H_
301_4@
END LOOP;
@H_
301_4@
@H_
301_4@
4. 遍历命令结果:
@H_
301_4@
[ <<label>> ]
@H_
301_4@
FOR record_or_row IN query LOOP
@H_
301_4@
statements
@H_
301_4@
END LOOP [ label ];
@H_
301_4@ 这是另外一种形式的FOR循环,在该循环中可以遍历命令的结果并操作相应的数据,见如下示例:
@H_
301_4@
FOR rec IN SELECT * FROM some_table LOOP
@H_
301_4@
PERFORM some_func(rec.one_col);
@H_
301_4@
END LOOP;
@H_
301_4@ PL/pg
sql还提供了另外一种遍历命令结果的方式,和上面的方式相比,唯一的差别是该方式将SELECT语句存于字符串文本中,然后再交由EXECUTE命令动态的执行。和前一种方式相比,该方式的灵活性更高,但是效率较低。
@H_
301_4@
[ <<label>> ]
@H_
301_4@
FOR record_or_row IN EXECUTE text_expression LOOP
@H_
301_4@
statements
@H_
301_4@
END LOOP [ label ];
@H_
301_4@
@H_
301_4@
5. 异常捕获:
@H_
301_4@ 在PL/pg
sql函数中,如果没有异常捕获,
函数会在发生
错误时直接
退出,与其相关的事物也会随之回滚。我们可以通过使用带有EXCEPTION子句的BEGIN块来捕获异常并使其从中恢复。见如下声明形式:
@H_
301_4@
[ <<label>> ]
@H_
301_4@
[ DECLARE
@H_
301_4@
@H_
301_4@
BEGIN
@H_
301_4@
statements
@H_
301_4@
EXCEPTION
@H_
301_4@
WHEN condition [ OR condition ... ] THEN
@H_
301_4@
handler_statements
@H_
301_4@
WHEN condition [ OR condition ... ] THEN
@H_
301_4@
handler_statements
@H_
301_4@
END;
@H_
301_4@ 如果没有
错误发生,只有BEGIN块中的statements会被正常执行,然而一旦这些语句中有任意一条发生
错误,其后的语句都将被跳过,直接
跳转到EXCEPTION块的开始处。此时系统将
搜索异常条件列表,寻找匹配该异常的第一个条件,如果找到匹配,则执行相应的handler_statements,之后再执行END的下一条语句。如果没有找到匹配,该
错误就会被继续向外抛出,其结果与没有EXCEPTION子句完全等同。如果此时handler_statements中的语句发生新
错误,它将不能被该EXCEPTION子句捕获,而是继续向外传播,交由其外层的EXCEPTION子句捕获并处理。见如下示例:
@H_
301_4@
INSERT INTO mytab(firstname,lastname) VALUES('Tom','Jones');
@H_
301_4@
BEGIN
@H_
301_4@
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
@H_
301_4@
x := x + 1;
@H_
301_4@
y := x / 0;
@H_
301_4@
EXCEPTION
@H_
301_4@
WHEN division_by_zero THEN
@H_
301_4@
RAISE NOTICE 'caught division_by_zero';
@H_
301_4@
RETURN x;
@H_
301_4@
END;
@H_
301_4@ 当以上
函数执行到y := x / 0语句时,将会引发一个异常
错误,
代码将
跳转到EXCEPTION块的开始处,之后系统会寻找匹配的异常捕捉条件,此时division_by_zero完全匹配,这样该条件内的
代码将会被继续执行。需要说明的是,RETURN语句中返回的x值为x := x + 1执行后的新值,但是在除零之前的update语句将会被回滚,BEGIN之前的insert语句将仍然生效。
@H_
301_4@
@H_
301_4@
六、游标:
@H_
301_4@
@H_
301_4@
1. 声明游标变量:
@H_
301_4@ 在PL/pg
sql中对游标的访问都是通过游标变量实现的,其数据类型为refcursor。 创建游标变量的
方法有以下两种:
@H_
301_4@ 1). 和声明其他类型的变量一样,直接声明一个游标类型的变量即可。
@H_
301_4@ 2). 使用游标专有的声明语法,如:
@H_
301_4@
name CURSOR [ ( arguments ) ] FOR query;
@H_
301_4@ 其中arguments为一组逗号分隔的name datatype列表,见如下示例:
@H_
301_4@
curs1 refcursor;
@H_
301_4@
curs2 CURSOR FOR SELECT * FROM tenk1;
@H_
301_4@
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
@H_
301_4@ 在上面三个例子中,只有第一个是未绑定游标,剩下两个游标均已被绑定。
@H_
301_4@
@H_
301_4@
2. 打开游标:
@H_
301_4@ 游标在使用之前必须先被打开,在PL/pg
sql中有三种形式的OPEN语句,其中两种用于未绑定的游标变量,另外一种用于绑定的游标变量。
@H_
301_4@ 1). OPEN FOR:
@H_
301_4@ 其声明形式为:
@H_
301_4@
OPEN unbound_cursor FOR query;
@H_
301_4@ 该形式只能用于未绑定的游标变量,其
查询语句必须是SELECT,或其他返回记录行的语句,如EXPLAIN。在Postgre
sql中,该
查询和普通的
sql命令平等对待,即先替换变量名,同时也将该
查询的执行计划缓存起来,以供后用。见如下示例:
@H_
301_4@
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
@H_
301_4@ 2). OPEN FOR EXECUTE
@H_
301_4@ 其声明形式为:
@H_
301_4@
OPEN unbound_cursor FOR EXECUTE query-string;
@H_
301_4@ 和上面的形式一样,该形式也仅适用于未绑定的游标变量。EXECUTE将动态执行其后以文本形式表示的
查询字符串。
@H_
301_4@
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
@H_
301_4@ 3). 打开一个绑定的游标
@H_
301_4@ 其声明形式为:
@H_
301_4@
OPEN bound_cursor [ ( argument_values ) ];
@H_
301_4@ 该形式仅适用于绑定的游标变量,只有当该变量在声明时包含接收参数,才能以传递参数的形式打开该游标,这些参数将被实际代入到游标声明的
查询语句中,见如下示例:
@H_
301_4@
OPEN curs2;
@H_
301_4@
OPEN curs3(42);
@H_
301_4@
@H_
301_4@
3. 使用游标:
@H_
301_4@ 游标一旦打开,就可以按照以下方式进行读取。然而需要说明的是,游标的打开和读取必须在同一个事物内,因为在Postgre
sql中,如果事物结束,事物内打开的游标将会被隐含的
关闭。
@H_
301_4@ 1). FETCH
@H_
301_4@ 其声明形式为:
@H_
301_4@
FETCH cursor INTO target;
@H_
301_4@ FETCH命令从游标中读取下一行记录的数据到目标中,其中目标可以是行变量、记录变量,或者是一组逗号分隔的普通变量的列表,读取成功与否,可通过PL/pg
sql内置变量FOUND来判断,其规则等同于SELECT INTO。见如下示例:
@H_
301_4@
FETCH curs1 INTO rowvar;--rowvar为行变量
@H_
301_4@
FETCH curs2 INTO foo,bar,baz;
@H_
301_4@ 2). CLOSE
@H_
301_4@ 其声明形式为:
@H_
301_4@
CLOSE cursor;
@H_
301_4@
关闭当前已经打开的游标,以释放其占有的系统资源,见如下示例:
@H_
301_4@
CLOSE curs1;
@H_
301_4@
@H_
301_4@
七、错误和消息:
@H_
301_4@
@H_
301_4@ 在Postgre
sql中可以利用RAISE语句报告信息和抛出
错误,其声明形式为:
@H_
301_4@
RAISE level 'format' [,expression [,...]];
@H_
301_4@ 这里包含的级别有
DEBUG(向服务器日志写信息)、
LOG(向服务器日志写信息,优先级更高)、
INFO、
NOTICE和
WARNING(把信息写到服务器日志以及转发到客户端应用,优先级逐步升高)和
EXCEPTION抛出一个
错误(通常
退出当前事务)。某个优先级别的信息是报告给客户端还是写到服务器日志,还是两个均有,是由
log_min_messages和
client_min_messages这两个系统初始化参数控制的。
@H_
301_4@ 在format部分中,%表示为占位符,其实际值仅在RAISE命令执行时由后面的变量替换,如果要在format中表示%自身,可以使用%%的形式表示,见如下示例:
@H_
301_4@
RAISE NOTICE 'Calling cs_create_job(%)',v_job_id;--v_job_id变量的值将替换format中的%。