41.13. Портирование из Oracle PL/SQL #
В этом разделе рассматриваются различия между языками Postgres Pro PL/pgSQL и Oracle PL/SQL, чтобы помочь разработчикам, переносящим приложения из Oracle® в Postgres Pro.
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
может быть лучшим решением.В Postgres Pro тело функции должно быть записано в виде строки. Поэтому нужно использовать знак доллара в качестве кавычек или экранировать одиночные кавычки в теле функции. (См. Подраздел 41.12.1.)
Имена типов данных часто требуют корректировки. Например, в Oracle строковые значения часто объявляются с типом
varchar2
, не являющимся стандартным типом SQL. В Postgres Pro вместо него нужно использоватьvarchar
илиtext
. Подобным образом, типnumber
нужно заменять наnumeric
или другой числовой тип, если найдётся более подходящий.Для группировки функций вместо пакетов используются схемы.
Так как пакетов нет, нет и пакетных переменных. Это несколько раздражает. Вместо этого можно хранить состояние каждого сеанса во временных таблицах.
Целочисленные циклы
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
в Postgres Pro. Кроме того,IS
становитсяAS
, и нужно добавить предложениеLANGUAGE
, потому что PL/pgSQL — не единственный возможный язык.В Postgres Pro тело функции является строкой, поэтому нужно использовать кавычки или знаки доллара. Это заменяет завершающий
/
в подходе Oracle.Команда
show errors
не существует в Postgres Pro и не требуется, так как ошибки будут выводиться автоматически.
Вот как эта функция будет выглядеть после портирования в Postgres Pro:
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;
В конечном итоге в Postgres Pro эта функция может выглядеть так:
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
) и манипулирующую строками. В Postgres Pro нет встроенной функции instr
, но её можно создать, используя комбинацию других функций. В Подраздел 41.13.3 приведена реализации instr
на PL/pgSQL, которая может быть полезна вам при портировании ваших функций.
Пример 41.11. Портирование из PL/SQL в PL/pgSQL процедуры, которая манипулирует строками и содержит OUT
параметры
Следующая процедура на языке Oracle PL/SQL разбирает URL и возвращает составляющие его элементы (сервер, путь и запрос).
Версия Oracle:
CREATE OR REPLACE PROCEDURE cs_parse_url( v_url IN VARCHAR2, v_host OUT VARCHAR2, -- Возвращается как результат v_path OUT VARCHAR2, -- И это тоже v_query OUT VARCHAR2) -- И это 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, -- Возвращается как результат v_path OUT VARCHAR, -- И это тоже v_query OUT VARCHAR) -- И это 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; -- освободить блокировку 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; -- ничего не делать, если задание уже есть 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; -- освободить блокировку 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) -- ничего не делать, если задание уже есть END; COMMIT; END; $$ LANGUAGE plpgsql;
Синтаксис | |
Имена исключений, поддерживаемые PL/pgSQL, отличаются от исключений в Oracle. Количество встроенных имён исключений значительно больше (см. Приложение A). В настоящее время нет способа задать пользовательское имя исключения, хотя вместо этого можно вызывать ошибку с заданным пользователем значением SQLSTATE. |
41.13.2. На что ещё обратить внимание #
В этом разделе рассматриваются ещё несколько вещей, на которые нужно обращать внимание при портировании функций из Oracle PL/SQL в Postgres Pro.
41.13.2.1. Неявный откат изменений после возникновения исключения #
В PL/pgSQL при перехвате исключения в секции EXCEPTION
все изменения в базе данных с начала блока автоматически откатываются. В Oracle это эквивалентно следующему:
BEGIN SAVEPOINT s1; ... здесь код ... EXCEPTION WHEN ... THEN ROLLBACK TO s1; ... здесь код ... WHEN ... THEN ROLLBACK TO s1; ... здесь код ... 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 #
Для оптимизации исполнения Postgres Pro предоставляет два модификатора при создании функции: «изменчивость» (будет ли функция всегда возвращать тот же результат при тех же аргументах) и «строгость» (возвращает ли функция 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;