42.12. 从 Oracle PL/SQL 移植

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

PL/pgSQL 在很多方面类似于 PL/SQL。它是一种块结构的命令式语言，必须声明所有变量。分配，循环和条件类似。从 PL/SQL 移植到 PL/pgSQL 时应记住的主要区别是：

  • 如果在 SQL 命令中使用的名称可以是表的列名或对该函数变量的引用，则 PL/SQL 会将其视为列名。如Section 42.10.1中所述，这对应于 PL/pgSQL 的plpgsql.variable_conflict = use_column行为，这不是默认行为。通常最好首先避免这种歧义，但是如果您必须移植大量依赖此行为的代码，则设置variable_conflict可能是最佳解决方案。

  • 在 PostgreSQL 中，函数体必须以字符串 Literals 形式编写。因此，您需要在函数体中使用美元引号或对单引号进行转义。 (请参阅Section 42.11.1。)

  • 数据类型名称经常需要翻译。例如，在 Oracle 中，字符串值通常声明为varchar2类型，这是非 SQL 标准类型。在 PostgreSQL 中，请改用varchar或text类型。同样，将number类型替换为numeric，或者使用其他一些数字数据类型(如果有的话)。

  • 使用模式将功能组织到组中，而不是使用包。

  • 由于没有包，所以也没有包级变量。这有点烦人。您可以将每个会话的状态保留在临时表中。

  • 带有REVERSE的整数FOR循环的工作方式不同：PL/SQL 从第二个数字递减到第一个，而 PL/pgSQL 从第一个数字递减到第二个，要求在移植时交换循环边界。这种不兼容是不幸的，但不太可能改变。 (请参阅Section 42.6.3.5。)

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

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

42 .12.1. 移植实例

Example 42.9显示了如何将简单功能从 PL/SQL 移植到 PL/pgSQL。

实施例 42.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必须更改为varchar或text。在本节的示例中，我们将使用varchar，但是如果您不需要特定的字符串长度限制，则text通常是更好的选择。

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

  • 在 PostgreSQL 中，函数主体被视为字符串 Literals，因此您需要在其周围使用引号或美元引号。这代替了 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;

Example 42.10显示了如何移植创建另一个功能的功能，以及如何处理随后的引用问题。

实施例 42.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 FUNCTION cs_update_referrer_type_proc() RETURNS void 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可以始终被认为是host，domain或url，但是referrer_key.key_string可以是任何东西，特别是它可能包含美元符号.)该功能实际上是对 Oracle 原版的改进，因为它不会referrer_key.key_string或referrer_key.referrer_type包含引号时，将生成损坏的代码。

Example 42.11显示了如何使用OUT参数和字符串操作来移植功能。 PostgreSQL 没有内置的instr函数，但是您可以使用其他函数的组合来创建一个。在Section 42.12.3中，有一个instr的 PL/pgSQL 实现，可用于简化移植。

实施例 42.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');

Example 42.12显示了如何移植使用 Oracle 特定功能的过程。

实施例 42.12. 将过程从 PL/SQL 移植到 PL/pgSQL

Oracle 版本：

CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
    a_running_job_count INTEGER;
    PRAGMA AUTONOMOUS_TRANSACTION; -- (1)
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE; -- (2)

    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 (3)
        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

这样的过程可以轻松地转换为返回void的 PostgreSQL 函数。这个过程特别有趣，因为它可以教给我们一些知识：

  • ((1)) PostgreSQL 中没有PRAGMA语句。
  • ((2)) 如果您在 PL/pgSQL 中执行LOCK TABLE，则直到调用事务完成后才会释放锁定。
  • ((3)) 您不能在 PL/pgSQL 函数中发出COMMIT。该函数在某些外部事务中运行，因此COMMIT表示将终止该函数的执行。但是，在这种特殊情况下，无论如何都没有必要，因为当我们引发错误时，由LOCK TABLE获得的锁定将被释放。

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

CREATE OR REPLACE FUNCTION cs_create_job(v_job_id integer) RETURNS void 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
        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;
END;
$$ LANGUAGE plpgsql;
  • ((1)) RAISE的语法与 Oracle 的语句有很大不同，尽管基本情况RAISE * exception_name *的工作原理类似。
  • ((2)) PL/pgSQL 支持的异常名称与 Oracle 的异常名称不同。内置的异常名称集要大得多(请参见Appendix A)。尽管您可以抛出用户选择的 SQLSTATE 值，但目前尚无方法声明用户定义的异常名称。

此过程与 Oracle 对等程序之间的主要功能区别在于，在调用事务完成之前，将保留cs_jobs表上的互斥锁。同样，如果调用方以后中止(例如由于错误)，此过程的效果将回滚。

42 .12.2. 其他注意事项

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

42 .12.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;

如果您以这种方式转换使用SAVEPOINT和ROLLBACK TO的 Oracle 过程，那么您的任务很简单：只需省略SAVEPOINT和ROLLBACK TO即可。如果您有一个以不同方式使用SAVEPOINT和ROLLBACK TO的过程，那么将需要一些实际的想法。

42.12.2.2. EXECUTE

EXECUTE的 PL/pgSQL 版本与 PL/SQL 版本类似，但是您必须记住要使用quote_literal和quote_ident，如Section 42.5.4中所述。除非使用这些函数，否则EXECUTE 'SELECT * FROM $1';类型的构造将无法可靠地工作。

42 .12.2.3. 优化 PL/pgSQL 函数

PostgreSQL 提供了两个函数创建修饰符来优化执行：“波动性”(给定相同参数时函数是否总是返回相同结果)和“严格性”(严格意义)(如果任何参数为 null 则函数返回 null)。有关详细信息，请查阅CREATE FUNCTION参考页。

使用这些优化属性时，您的CREATE FUNCTION语句可能看起来像这样：

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

42.12.3. Appendix

本部分包含一组与 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;
Name PostgreSQL 中文文档
Version
Language
Badge
Last Updated 2021-07-07T13:01:31