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，如需转载请自行联系原作者
