41.13. Перенос из PL/SQL Oracle

41.13. Перенос из PL/SQL Oracle
Назад	Наверх	Глава 41. PL/pgSQL — SQL Процедурный Язык	Начало	Далее

---

41.13. Перенос из PL/SQL Oracle

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

41.13.2. Другие вещи, на которые следует обратить внимание

41.13.3. Приложение

В данном разделе объясняются различия между языком PL/pgSQL Tantor SE и языком PL/SQL Oracle, чтобы помочь разработчикам, переносящим приложения из одной системы в другую. Оракл® в Tantor SE.

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 может быть лучшим решением.

• В Tantor SE тело функции должно быть записано в виде строкового литерала. Поэтому необходимо использовать долларовую кавычку или экранировать апострофы в теле функции. (См. Раздел 41.12.1).

• Имена типов данных часто требуют перевода. Например, в Oracle строковые значения обычно объявляются как тип varchar2, который является нестандартным для SQL типом. В Tantor SE вместо этого используйте тип varchar или text. Аналогично, замените тип number на numeric, или используйте другой числовой тип данных, если есть более подходящий.

• Используйте схемы вместо пакетов для организации ваших функций в группы.

• Поскольку в PostgreSQL нет пакетов, нет и переменных на уровне пакета. Это немного раздражает. Вместо этого вы можете сохранять состояние на сессию во временных таблицах.

• Циклы FOR с REVERSE работают по-разному: 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 должно быть изменено на varchar или text. В примерах в этом разделе мы будем использовать varchar, но text часто является более предпочтительным выбором, если вам не нужны конкретные ограничения на длину строки.

• Ключевое слово RETURN в прототипе функции (а не в теле функции) становится RETURNS в Tantor SE. Также, IS становится AS, и вам нужно добавить предложение LANGUAGE, потому что PL/pgSQL не является единственным возможным языком функции.

• В Tantor SE тело функции считается строковым литералом, поэтому вам нужно использовать кавычки или знаки доллара вокруг него. Это заменяет завершающий / в подходе Oracle.

• Команда show errors не существует в Tantor SE и не требуется, так как ошибки автоматически отображаются.

Вот как будет выглядеть эта функция после переноса в Tantor SE:

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;

Вот как эта функция будет выглядеть в Tantor SE:

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 содержат кавычки.

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

Пример 41.11. Перенос процедуры с обработкой строк и параметрами OUT из PL/SQL в PL/pgSQL

Следующая процедура PL/SQL Oracle используется для разбора 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 в Tantor SE.

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;

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

41.13.2.2. EXECUTE

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

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

Tantor SE предоставляет два модификатора создания функций для оптимизации выполнения: “volatility” (определяет, всегда ли функция возвращает одинаковый результат при одинаковых аргументах) и “strictness” (определяет, возвращает ли функция null, если хотя бы один из аргументов равен null). Подробности смотрите на странице справки CREATE FUNCTION.

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

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

41.13.3. Приложение

Этот раздел содержит код для набора функций instr, совместимых с 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;

---

Назад	Наверх	Далее
41.12. Советы по разработке в PL/pgSQL	Начало	Глава 42. PL/Tcl — Tcl Процедурный Язык