Перейти к основному содержимому

Портирование из Oracle PL/SQL

примечание

Эта страница переведена при помощи нейросети GigaChat.

Этот раздел объясняет различия между языком PostgreSQL PL/pgSQL и языком PL/SQL компании Oracle, чтобы помочь разработчикам переносить приложения с Oracle® на PostgreSQL.

PL/pgSQL во многом похож на PL/SQL. Это структурированный блоками императивный язык, и все переменные должны быть объявлены. Присваивания, циклы и условные операторы аналогичны. Основные отличия, которые следует помнить при переносе из PL / SQL в PL/pgSQL, заключаются в следующем:

  • Если имя, используемое в команде SQL, может быть либо именем столбца таблицы, используемой в команде, либо ссылкой на переменную функции, PL/SQL обрабатывает его как имя столбца. По умолчанию PL/pgSQL выдаст ошибку, жалуясь на то, что имя неоднозначно. Можно указать plpgsql.variable_conflict = use_column, чтобы изменить это поведение для соответствия PL/SQL, как объясняется в разделе 43.11.1. Часто лучше всего избегать таких двусмысленностей с самого начала, но если нужно перенести большой объем кода, зависящего от этого поведения, установка variable_conflict может быть лучшим решением.
  • В PostgreSQL тело функции должно быть написано в виде строкового литерала. Поэтому нужно использовать долларовые кавычки или экранировать одиночные кавычки в теле функции.
  • Имена типов данных часто нуждаются в переводе. Например, в Oracle строковые значения обычно объявляются как имеющие тип varchar2, который является типом, не соответствующим стандарту SQL. В PostgreSQL используйте вместо него тип varchar или text. Аналогично замените тип number на numeric или используйте другой числовой тип данных, если есть более подходящий.
  • Вместо пакетов используйте схемы для организации функций в группы.
  • Поскольку нет пакетов, то нет и переменных уровня пакета. Это немного раздражает. Можно хранить состояние сеанса в временных таблицах вместо этого.
  • Циклы целых чисел с работают иначе: PL/SQL считает от второго числа до первого, тогда как PL/pgSQL считает от первого числа до второго, требуя при переносе поменять границы цикла. Эта несовместимость является неудачной, но вряд ли изменится.
  • FOR циклы по запросам (кроме курсоров) также работают иначе: целевая переменная(ые) должна быть объявлена, тогда как PL/SQL всегда объявляет их неявно. Преимущество этого заключается в том, что значения переменных остаются доступными после выхода из цикла.
  • Существуют различные различия в обозначениях для использования переменных курсора.

Примеры портирования​

Пример ниже показывает, как перенести простую функцию из PL/SQL в PL/pgSQL.

Пример. Портирование простой функции из 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 в прототипе функции (не в теле функции) становится RETURNS в PostgreSQL. Кроме того, 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;

В примере ниже показано, как перенести функцию, которая создает другую функцию, и как решить возникающие проблемы с кавычками.

Пример. Перенос функции, создающей другую функцию из 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 можно доверять всегда быть host, domain или url, но referrer_key.key_string может быть чем угодно, в частности он может содержать знаки доллара.) На самом деле эта функция представляет собой улучшение оригинальной версии Oracle, поскольку она не будет генерировать поврежденный код при наличии кавычек в referrer_key.key_string или referrer_key.referrer_type.

Пример ниже показывает, как перенести функцию с OUT параметрами и манипуляцией строками. В PostgreSQL нет встроенной функции instr, но можете создать ее, используя комбинацию других функций. В разделе «Приложение» представлена реализация PL/pgSQL для instr, которую можно использовать для упрощения переноса.

Пример. Портирование процедуры с манипуляцией строками и параметрами 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');

Пример «Портирование процедуры из PL/SQL в PL/pgSQL» показывает, как перенести процедуру, которая использует множество функций, специфичных для Oracle.

Пример. Портирование процедуры из 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.

Другие вещи, за которыми стоит следить​

В этом разделе объясняется несколько других моментов, на которые следует обратить внимание при переносе функций Oracle PL/SQL в PostgreSQL.

Неявный откат после исключений​

В PL/pgSQL при перехвате исключения с помощью предложения EXCEPTION, все изменения в базе данных, сделанные после начала блока, автоматически отменяются. То есть поведение эквивалентно тому, что получилось бы в Oracle с использованием:

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

Если переводится процедура Oracle, которая использует SAVEPOINT и ROLLBACK TO таким образом, то задача проста: просто исключите SAVEPOINT и ROLLBACK TO. Если есть процедура, использующая SAVEPOINT и ROLLBACK TO иным способом, то потребуется некоторое реальное размышление.

EXECUTE​

Версия PL/pgSQL работает аналогично версии PL/SQL, но нужно помнить о необходимости использовать quote_literal и quote_ident, как описано в разделе «Выполнение динамических команд». Конструкции типа EXECUTE 'SELECT * FROM $1'; не будут работать надежно, если эти функции не используются.

Оптимизация функций PL/pgSQL​

PostgreSQL предоставляет два модификатора создания функций для оптимизации выполнения: «изменчивость» (всегда ли функция возвращает один и тот же результат при одних и тех же аргументах) и «строгость» (возвращает ли функция нулевое значение, если любой из аргументов равен нулю).

Когда используются эти атрибуты оптимизации, заявление CREATE FUNCTION может выглядеть примерно так:

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

Приложение​

Этот раздел содержит код для набора функций, совместимых с Oracle, которые можно использовать для упрощения усилий по переносу.

--
-- 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;