2024年9月26日:PostgreSQL 17 发布!
支持的版本:当前 (17) / 16 / 15 / 14 / 13 / 12
开发版本:开发版
不支持的版本:11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1

41.13. 从 Oracle PL/SQL 移植 #

本节说明 PostgreSQLPL/pgSQL 语言和 Oracle 的 PL/SQL 语言之间的差异,以帮助将应用程序从 Oracle® 移植到 PostgreSQL 的开发人员。

PL/pgSQL 在许多方面类似于 PL/SQL。它是一种块结构的命令式语言,所有变量都必须声明。赋值、循环和条件语句都很相似。从 PL/SQL 移植到 PL/pgSQL 时,您应该牢记的主要区别是

  • 如果 SQL 命令中使用的名称可能是命令中使用的表的列名,也可能是对函数变量的引用,PL/SQL 会将其视为列名。默认情况下,PL/pgSQL 将抛出一个错误,抱怨名称不明确。您可以指定 plpgsql.variable_conflict = use_column 将此行为更改为与 PL/SQL 匹配,如 第 41.11.1 节 中所述。通常最好一开始就避免这种歧义,但是如果您必须移植大量依赖于此行为的代码,设置 variable_conflict 可能是最佳解决方案。

  • PostgreSQL 中,函数体必须写成字符串字面量。因此,您需要在函数体中使用美元符引用或转义单引号。(参见 第 41.12.1 节。)

  • 数据类型名称通常需要转换。例如,在 Oracle 中,字符串值通常声明为 varchar2 类型,这是一种非 SQL 标准类型。在 PostgreSQL 中,请改用 varchartext 类型。类似地,用 numeric 替换 number 类型,或者如果存在更合适的数值数据类型,则使用它。

  • 使用模式而不是包来组织函数到组中。

  • 由于没有包,因此也没有包级变量。这有点令人讨厌。您可以将每个会话状态保存在临时表中。

  • 带有 REVERSE 的整数 FOR 循环的工作方式不同:PL/SQL 从第二个数字递减到第一个数字,而 PL/pgSQL 从第一个数字递减到第二个数字,在移植时需要交换循环边界。这种不兼容性很不幸,但不太可能改变。(参见 第 41.6.5.5 节。)

  • FOR 循环遍历查询(游标除外)的工作方式也不同:目标变量必须已声明,而 PL/SQL 总是隐式地声明它们。这样做的好处是,在循环退出后仍然可以访问变量值。

  • 游标变量的使用存在各种符号差异。

41.13.1. 移植示例 #

示例 41.9 显示了如何将简单的函数从 PL/SQL 移植到 PL/pgSQL

示例 41.9. 将简单的函数从 PL/SQL 移植到 PL/pgSQL

这是一个 Oracle PL/SQL 函数

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
                                                  v_version varchar2)
RETURN varchar2 IS
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
/
show errors;

让我们逐步了解此函数,并查看与 PL/pgSQL 相比的差异

  • 类型名称 varchar2 必须更改为 varchartext。在本节中的示例中,我们将使用 varchar,但是如果您不需要特定的字符串长度限制,则 text 通常是更好的选择。

  • 函数原型(而不是函数体)中的 RETURN 关键字在 PostgreSQL 中变为 RETURNS。此外,IS 变为 AS,并且您需要添加 LANGUAGE 子句,因为 PL/pgSQL 不是唯一可能的函数语言。

  • PostgreSQL 中,函数体被视为字符串字面量,因此您需要在其周围使用引号或美元符引用。这取代了 Oracle 方法中的终止 /

  • show errors 命令在 PostgreSQL 中不存在,并且不需要,因为错误会自动报告。

这是将此函数移植到 PostgreSQL 后的样子

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                  v_version varchar)
RETURNS varchar AS $$
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
$$ LANGUAGE plpgsql;

示例 41.10 显示了如何移植创建另一个函数的函数以及如何处理随之而来的引用问题。

示例 41.10. 将创建另一个函数的函数从 PL/SQL 移植到 PL/pgSQL

以下过程从 SELECT 语句中获取行并使用结果构建一个大型函数,其中包含 IF 语句,以提高效率。

这是 Oracle 版本

CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
    CURSOR referrer_keys IS
        SELECT * FROM cs_referrer_keys
        ORDER BY try_order;
    func_cmd VARCHAR(4000);
BEGIN
    func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2,
                 v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN';

    FOR referrer_key IN referrer_keys LOOP
        func_cmd := func_cmd ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ''' || referrer_key.key_string
          || ''' THEN RETURN ''' || referrer_key.referrer_type
          || '''; END IF;';
    END LOOP;

    func_cmd := func_cmd || ' RETURN NULL; END;';

    EXECUTE IMMEDIATE func_cmd;
END;
/
show errors;

以下是此函数在 PostgreSQL 中最终的样子

CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$
DECLARE
    referrer_keys CURSOR IS
        SELECT * FROM cs_referrer_keys
        ORDER BY try_order;
    func_body text;
    func_cmd text;
BEGIN
    func_body := 'BEGIN';

    FOR referrer_key IN referrer_keys LOOP
        func_body := func_body ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ' || quote_literal(referrer_key.key_string)
          || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
          || '; END IF;' ;
    END LOOP;

    func_body := func_body || ' RETURN NULL; END;';

    func_cmd :=
      'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
                                                        v_domain varchar,
                                                        v_url varchar)
        RETURNS varchar AS '
      || quote_literal(func_body)
      || ' LANGUAGE plpgsql;' ;

    EXECUTE func_cmd;
END;
$func$ LANGUAGE plpgsql;

请注意,函数体是如何单独构建并通过 quote_literal 传递以使其中的任何引号加倍。这是必要的,因为我们不能安全地使用美元符引用来定义新函数:我们无法确定从 referrer_key.key_string 字段中插入的字符串是什么。(我们在这里假设 referrer_key.kind 可以始终被信任为 hostdomainurl,但 referrer_key.key_string 可能为任何内容,特别是它可能包含美元符号。)此函数实际上是对 Oracle 原版的改进,因为它在 referrer_key.key_stringreferrer_key.referrer_type 包含引号时不会生成错误的代码。


示例 41.11 显示了如何移植带有 OUT 参数和字符串操作的函数。PostgreSQL 没有内置的 instr 函数,但您可以使用其他函数的组合来创建一个。在 第 41.13.3 节 中,有一个 PL/pgSQL 实现的 instr,您可以使用它来使移植更容易。

示例 41.11. 将带有字符串操作和 OUT 参数的过程从 PL/SQL 移植到 PL/pgSQL

以下 Oracle PL/SQL 过程用于解析 URL 并返回多个元素(主机、路径和查询)。

这是 Oracle 版本

CREATE OR REPLACE PROCEDURE cs_parse_url(
    v_url IN VARCHAR2,
    v_host OUT VARCHAR2,  -- This will be passed back
    v_path OUT VARCHAR2,  -- This one too
    v_query OUT VARCHAR2) -- And this one
IS
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
/
show errors;

以下是将其转换为 PL/pgSQL 的可能方法

CREATE OR REPLACE FUNCTION cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- This will be passed back
    v_path OUT VARCHAR,  -- This one too
    v_query OUT VARCHAR) -- And this one
AS $$
DECLARE
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
$$ LANGUAGE plpgsql;

此函数可以这样使用

SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');

示例 41.12 显示了如何移植使用许多特定于 Oracle 的功能的过程。

示例 41.12. 将过程从 PL/SQL 移植到 PL/pgSQL

Oracle 版本

CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
    a_running_job_count INTEGER;
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        COMMIT; -- free lock
        raise_application_error(-20000,
                 'Unable to create a new job: a job is currently running.');
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
    EXCEPTION
        WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
    END;
    COMMIT;
END;
/
show errors

以下是我们将此过程移植到 PL/pgSQL 的方法

CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$
DECLARE
    a_running_job_count integer;
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        COMMIT; -- free lock
        RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- (1)
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
    EXCEPTION
        WHEN unique_violation THEN -- (2)
            -- don't worry if it already exists
    END;
    COMMIT;
END;
$$ LANGUAGE plpgsql;

(1)

RAISE 的语法与 Oracle 的语句有很大不同,尽管基本情况 RAISE exception_name 的工作方式类似。

(2)

PL/pgSQL 支持的异常名称与 Oracle 的不同。内置异常名称的集合要大得多(参见 附录 A)。目前还没有方法声明用户定义的异常名称,尽管您可以改为抛出用户选择的 SQLSTATE 值。


41.13.2. 其他需要注意的事项 #

本节说明在将 Oracle PL/SQL 函数移植到 PostgreSQL 时需要注意的其他一些事项。

41.13.2.1. 异常后隐式回滚 #

PL/pgSQL中,当异常被EXCEPTION子句捕获时,从代码块的BEGIN开始的所有数据库更改都会自动回滚。也就是说,其行为等同于你在Oracle中使用以下方式获得的结果

BEGIN
    SAVEPOINT s1;
    ... code here ...
EXCEPTION
    WHEN ... THEN
        ROLLBACK TO s1;
        ... code here ...
    WHEN ... THEN
        ROLLBACK TO s1;
        ... code here ...
END;

如果你正在翻译一个使用这种风格的SAVEPOINTROLLBACK TO的Oracle过程,你的任务很简单:只需省略SAVEPOINTROLLBACK TO。如果你有一个以不同方式使用SAVEPOINTROLLBACK TO的过程,则需要进行一些实际的思考。

41.13.2.2. EXECUTE #

PL/pgSQL版本的EXECUTE的工作方式与PL/SQL版本类似,但您必须记住使用quote_literalquote_ident,如第41.5.4节所述。除非您使用这些函数,否则EXECUTE 'SELECT * FROM $1';类型的构造将无法可靠地工作。

41.13.2.3. 优化PL/pgSQL函数 #

PostgreSQL提供了两个函数创建修饰符来优化执行:易变性(当给定相同的参数时,函数是否始终返回相同的结果)和严格性(如果任何参数为null,函数是否返回null)。请参阅CREATE FUNCTION参考页面以获取详细信息。

在使用这些优化属性时,您的CREATE FUNCTION语句可能如下所示

CREATE FUNCTION foo(...) RETURNS integer AS $$
...
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

41.13.3. 附录 #

本节包含一组与Oracle兼容的instr函数的代码,您可以使用这些函数来简化移植工作。

--
-- instr functions that mimic Oracle's counterpart
-- Syntax: instr(string1, string2 [, n [, m]])
-- where [] denotes optional parameters.
--
-- Search string1, beginning at the nth character, for the mth occurrence
-- of string2.  If n is negative, search backwards, starting at the abs(n)'th
-- character from the end of string1.
-- If n is not passed, assume 1 (search starts at first character).
-- If m is not passed, assume 1 (find first occurrence).
-- Returns starting index of string2 in string1, or 0 if string2 is not found.
--

CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
BEGIN
    RETURN instr($1, $2, 1);
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
                      beg_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index > 0 THEN
        temp_str := substring(string FROM beg_index);
        pos := position(string_to_search_for IN temp_str);

        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN pos + beg_index - 1;
        END IF;
    ELSIF beg_index < 0 THEN
        ss_length := char_length(string_to_search_for);
        length := char_length(string);
        beg := length + 1 + beg_index;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            IF string_to_search_for = temp_str THEN
                RETURN beg;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
                      beg_index integer, occur_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    i integer;
    length integer;
    ss_length integer;
BEGIN
    IF occur_index <= 0 THEN
        RAISE 'argument ''%'' is out of range', occur_index
          USING ERRCODE = '22003';
    END IF;

    IF beg_index > 0 THEN
        beg := beg_index - 1;
        FOR i IN 1..occur_index LOOP
            temp_str := substring(string FROM beg + 1);
            pos := position(string_to_search_for IN temp_str);
            IF pos = 0 THEN
                RETURN 0;
            END IF;
            beg := beg + pos;
        END LOOP;

        RETURN beg;
    ELSIF beg_index < 0 THEN
        ss_length := char_length(string_to_search_for);
        length := char_length(string);
        beg := length + 1 + beg_index;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            IF string_to_search_for = temp_str THEN
                occur_number := occur_number + 1;
                IF occur_number = occur_index THEN
                    RETURN beg;
                END IF;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

提交更正

如果您在文档中发现任何不正确的内容,与您对特定功能的体验不符,或者需要进一步澄清,请使用此表单报告文档问题。