一、概述:
PL/pgSQL函数在第一次被调用时,其函数内的源代码(文本)将被解析为二进制指令树,但是函数内的表达式和SQL命令只有在首次用到它们的时候,PL/pgSQL解释器才会为其创建一个准备好的执行规划,随后对该表达式或SQL命令的访问都将使用该规划。如果在一个条件语句中,有部分SQL命令或表达式没有被用到,那么PL/pgSQL解释器在本次调用中将不会为其准备执行规划,这样的好处是可以有效地减少为PL/pgSQL函数里的语句生成分析和执行规划的总时间,然而缺点是某些表达式或SQL命令中的错误只有在其被执行到的时候才能发现。
由于PL/pgSQL在函数里为一个命令制定了执行计划,那么在本次会话中该计划将会被反复使用,这样做往往可以得到更好的性能,但是如果你动态修改了相关的数据库对象,那么就有可能产生问题,如:
CREATE FUNCTION populate() RETURNS integer AS $$
DECLARE
-- 声明段
BEGIN
PERFORM my_function();
END;
$$ LANGUAGE plpgsql;
鉴于以上规则,在PL/pgSQL里直接出现的SQL命令必须在每次执行时均引用相同的表和字段,换句话说,不能将函数的参数用作SQL命令的表名或字段名。如果想绕开该限制,可以考虑使用PL/pgSQL中的EXECUTE语句动态地构造命令,由此换来的代价是每次执行时都要构造一个新的命令计划。
使用PL/pgSQL函数的一个非常重要的优势是可以提高程序的执行效率,由于原有的SQL调用不得不在客户端与服务器之间反复传递数据,这样不仅增加了进程间通讯所产生的开销,而且也会大大增加网络IO的开销。
二、PL/pgSQL的结构:
PL/pgSQL是一种块结构语言,函数定义的所有文本都必须在一个块内,其中块中的每个声明和每条语句都是以分号结束,如果某一子块在另外一个块内,那么该子块的END关键字后面必须以分号结束,不过对于函数体的最后一个END关键字,分号可以省略,如:
[ <<label>> ]
[ DECLARE declarations ]
BEGIN
statements
END [ label ];
CREATE FUNCTION somefunc() RETURNS integer AS $$
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; --在这里的数量是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;
#执行该函数以进一步观察其执行的结果。
postgres=# select somefunc();
NOTICE: Quantity here is 30
NOTICE: Quantity here is 80
NOTICE: Quantity here is 50
somefunc
----------
50
(1 row)
三、声明:
所有在块里使用的变量都必须在块的声明段里先进行声明,唯一的例外是FOR循环里的循环计数变量,该变量被自动声明为整型。变量声明的语法如下:
variable_name [ CONSTANT ] variable_type [ NOT NULL ] [ { DEFAULT | := } expression ];
1. 函数参数的别名:
传递给函数的参数都是用$1、$2这样的标识符来表示的。为了增加可读性,我们可以为其声明别名。之后别名和数字标识符均可指向该参数值,见如下示例:
1). 在函数声明的同时给出参数变量名。
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION sales_tax(REAL) RETURNS real AS $$
DECLARE
subtotal ALIAS FOR $1;
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
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;
variable%TYPE
user_id users.user_id%TYPE;
3. 行类型:
见如下形式的变量声明:
name table_name%ROWTYPE; name composite_type_name;
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
t2_row table2%ROWTYPE;
BEGIN
SELECT * INTO t2_row FROM table2 WHERE id = 1 limit 1;
RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;
name RECORD;
四、基本语句:
1. 赋值:
PL/pgSQL中赋值语句的形式为:identIFier := expression,等号两端的变量和表达式的类型或者一致,或者可以通过PostgreSQL的转换规则进行转换,否则将会导致运行时错误,见如下示例:
user_id := 20;
tax := subtotal * 0.06;
SELECT INTO myrec * FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;
DECLARE
users_rec RECORD;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id = 3;
IF users_rec.homepage IS NULL THEN
RETURN 'http://';
END IF;
END;
PERFORM create_mv('cs_session_page_requests_mv', my_query);
和所有其它PL/pgSQL命令不同的是,一个由EXECUTE语句运行的命令在服务器内并不会只prepare和保存一次。相反,该语句在每次运行的时候,命令都会prepare一次。因此命令字符串可以在函数里动态的生成以便于对各种不同的表和字段进行操作,从而提高函数的灵活性。然而由此换来的却是性能上的折损。见如下示例:
EXECUTE 'UPDATE tbl SET ' || quote_ident(columnname) || ' = ' || quote_literal(newvalue);
五、控制结构:
1. 函数返回:
1). RETURN expression
该表达式用于终止当前的函数,然后再将expression的值返回给调用者。如果返回简单类型,那么可以使用任何表达式,同时表达式的类型也将被自动转换成函数的返回类型,就像我们在赋值中描述的那样。如果要返回一个复合类型的数值,则必须让表达式返回记录或者匹配的行变量。
2). RETURN NEXT expression
如果PL/pgSQL函数声明为返回SETOF sometype,其行记录是通过RETURN NEXT命令进行填充的,直到执行到不带参数的RETURN时才表示该函数结束。因此对于RETURN NEXT而言,它实际上并不从函数中返回,只是简单地把表达式的值保存起来,然后继续执行PL/pgSQL函数里的下一条语句。随着RETURN NEXT命令的迭代执行,结果集最终被建立起来。该类函数的调用方式如下:
SELECT * FROM some_func();
它被放在FROM子句中作为数据源使用。最后需要指出的是,如果结果集数量很大,那么通过该种方式来构建结果集将会导致极大的性能损失。
2. 条件:
在PL/pgSQL中有以下三种形式的条件语句。
1). IF-THEN
IF boolean-expression THEN
statements
END IF;
IF boolean-expression THEN
statements
ELSE
statements
END IF;
IF boolean-expression THEN
statements
ELSIF boolean-expression THEN
statements
ELSIF boolean-expression THEN
statements
ELSE
statements
END IF;
3. 循环:
1). LOOP
LOOP
statements
END LOOP [ label ];
EXIT [ label ] [ WHEN expression ];
LOOP
-- do something
EXIT WHEN count > 0;
END LOOP;
CONTINUE [ label ] [ WHEN expression ];
LOOP
-- do something
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
END LOOP;
[ <<label>> ]
WHILE expression LOOP
statements
END LOOP [ label ];
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
--do something
END LOOP;
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression LOOP
statements
END LOOP [ label ];
FOR i IN 1..10 LOOP
--do something
RAISE NOTICE 'i IS %', i;
END LOOP;
FOR i IN REVERSE 10..1 LOOP
--do something
END LOOP;
[ <<label>> ]
FOR record_or_row IN query LOOP
statements
END LOOP [ label ];
FOR rec IN SELECT * FROM some_table LOOP
PERFORM some_func(rec.one_col);
END LOOP;
[ <<label>> ]
FOR record_or_row IN EXECUTE text_expression LOOP
statements
END LOOP [ label ];
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
WHEN condition [ OR condition ... ] THEN
handler_statements
END;
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
x := x + 1;
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
RETURN x;
END;
1. 声明游标变量:
在PL/pgSQL中对游标的访问都是通过游标变量实现的,其数据类型为refcursor。 创建游标变量的方法有以下两种:
1). 和声明其他类型的变量一样,直接声明一个游标类型的变量即可。
2). 使用游标专有的声明语法,如:
name CURSOR [ ( arguments ) ] FOR query;
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
2. 打开游标:
游标在使用之前必须先被打开,在PL/pgSQL中有三种形式的OPEN语句,其中两种用于未绑定的游标变量,另外一种用于绑定的游标变量。
1). OPEN FOR:
其声明形式为:
OPEN unbound_cursor FOR query;
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
OPEN unbound_cursor FOR EXECUTE query-string;
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
OPEN bound_cursor [ ( argument_values ) ];
OPEN curs2;
OPEN curs3(42);
3. 使用游标:
游标一旦打开,就可以按照以下方式进行读取。然而需要说明的是,游标的打开和读取必须在同一个事物内,因为在PostgreSQL中,如果事物结束,事物内打开的游标将会被隐含的关闭。
1). FETCH
其声明形式为:
FETCH cursor INTO target;
FETCH curs1 INTO rowvar; --rowvar为行变量
FETCH curs2 INTO foo, bar, baz;
CLOSE cursor;
CLOSE curs1;
七、错误和消息:
在PostgreSQL中可以利用RAISE语句报告信息和抛出错误,其声明形式为:
RAISE level 'format' [, expression [, ...]];
RAISE NOTICE 'Calling cs_create_job(%)',v_job_id; --v_job_id变量的值将替换format中的%。
RAISE EXCEPTION 'Inexistent ID --> %',user_id;
PostgreSQL PL/pgSQL 调试器能指导你一步一步调试 PL/pgSQL 过程或函数。若要启动调试器,请点击函数设计器内的 “调试”按钮。 你可以使用工具栏或菜单运行最常用的调试动作: 按钮 描述 运行 开始在调试模式下运行代码。如有需要,输入参数。调试器会运行你的代码直到代码结束或到达下一个断点。键盘快捷键:F9 逐过程 恢复运行。当前的行将被运行。如果该行是一个过程或函数调用,它会
PostgreSQL PL/pgSQL 调试器能指导你一步一步调试 PL/pgSQL 过程或函数。若要启动调试器,请点击函数设计器内的 按钮。 你可以使用工具栏或菜单运行最常用的调试动作: 按钮 描述 开始在调试模式下运行代码。如有需要,输入参数。调试器会运行你的代码直到代码结束或到达下一个断点。键盘快捷键:F9 停止逐步运行代码。运行将停止,并且无法恢复。 恢复运行。当前的行将被运行。如果该行是
PostgreSQL PL/pgSQL 调试器能指导你一步一步调试 PL/pgSQL 过程或函数。若要启动调试器,请点击函数设计器内的 “调试” 按钮。 你可以使用工具栏或菜单运行最常用的调试动作: 按钮 描述 运行 开始在调试模式下运行代码。如有需要,输入参数。调试器会运行你的代码直到代码结束或到达下一个断点。键盘快捷键:F9 逐过程 恢复运行。当前的行将被运行。如果该行是一个过程或函数调用,它
主要内容:PL/SQL的特点,PL/SQL的优点PL/SQL是是由甲骨文公司在90年代初开发,以提高SQL的功能。 PL/SQL是嵌入在Oracle数据库中的三个关键的编程语言之一(随着SQL本身和Java)。 PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点,所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。
主要内容:什么是数据库?,ORDBMS 术语,PostgreSQL 特征,相关资源PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS),在灵活的BSD许可证下发行。 PostgreSQL 开发者把它念作 post-gress-Q-L。 PostgreSQL 的 Slogan 是 "世界上最先进的开源关系型数据库"。 参考内容:PostgreSQL 10.1 手册 什么是数据库? 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。 每个数据
十二、项目:编程语言 确定编程语言中的表达式含义的求值器只是另一个程序。 Hal Abelson 和 Gerald Sussman,《计算机程序的构造和解释》 构建你自己的编程语言不仅简单(只要你的要求不要太高就好),而且对人富有启发。 希望通过本章的介绍,你能发现构建自己的编程语言其实并不是什么难事。我经常感到某些人的想法聪明无比,而且十分复杂,以至于我都不能完全理解。不过经过一段时间的阅读和实