PostgreSQL 的 pl/pgsql 的 cannot begin/end transactions in PL/pgSQL错误

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]$ 

这就是差别了。

转载于:https://www.cnblogs.com/gaojian/archive/2013/06/07/3124278.html