postgres=# select FindCourse('aaaa');
ERROR: cannot begin/end transactions in PL/pgSQL
HINT: Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT: PL/pgSQL function findcourse(character varying) line 21 at SQL statement出现这种错误的原因是：
看我改后的代码：
[postgres@lex pgsql]$ cat ./data/test.sqlCREATE OR REPLACE Function FindCourse
( name_in IN varchar )
RETURNS integer LANGUAGE plpgsql AS $$
DECLARE
cnumber integer;
c1 CURSOR
FOR
SELECT course_number, instructor
from course_tbl
where course_name = name_in
FOR UPDATE;
BEGIN
BEGIN
open c1;
fetch c1 into cnumber;
IF not found THEN
cnumber := 9999;
ELSE
UPDATE course_tbl
SET instructor = 'SMITH'
WHERE CURRENT OF c1;
COMMIT;
END IF;
close c1;
EXCEPTION
WHEN OTHERS THEN
END;
RETURN cnumber;
END;$$;
[postgres@lex pgsql]$
而我之前的是：
[postgres@lex pgsql]$ cat ./data/test.sqlCREATE OR REPLACE Function FindCourse
( name_in IN varchar )
RETURNS integer LANGUAGE plpgsql AS $$
DECLARE
cnumber integer;
c1 CURSOR
FOR
SELECT course_number, instructor
from course_tbl
where course_name = name_in
FOR UPDATE;
BEGIN
BEGIN
open c1;
fetch c1 into cnumber;
IF not found THEN
cnumber := 9999;
ELSE
UPDATE course_tbl
SET instructor = 'SMITH'
WHERE CURRENT OF c1;
COMMIT;
END IF;
close c1;
EXCEPTION
WHEN OTHERS THEN
END;
RETURN cnumber;
END;$$;
[postgres@lex pgsql]$
这就是差别了。
本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/archive/2013/06/07/3124278.html，如需转载请自行联系原作者
