Документация по PostgreSQL 9.4.1 | |||
---|---|---|---|
Пред. | Уровень выше | Глава 40. PL/pgSQL — процедурный язык SQL | След. |
40.12. Портирование из Oracle PL/SQL
В этом разделе рассматриваются различия между языками PostgreSQL PL/pgSQL и Oracle PL/SQL, чтобы помочь разработчикам, переносящим приложения из Oracle® в PostgreSQL.
PL/pgSQL во многих аспектах похож на PL/SQL . Это блочно-структурированный, императивный язык, в котором все переменные должны объявляться. Присвоения, циклы, условные операторы в обоих языках похожи. Основные отличия, которые необходимо иметь в виду при портировании с PL/SQL в PL/pgSQL, следующие:
Если имя, используемое в SQL-команде, может быть как именем столбца таблицы, так и ссылкой на переменную функции, то PL/SQL считает, что это имя столбца таблицы. Это соответствует поведению PL/pgSQL при plpgsql.variable_conflict = use_column, что не является значением по умолчанию, как описано в Подразделе 40.10.1. В первую очередь, было бы правильно избегать таких двусмысленностей, но если требуется портировать большое количество кода, зависящее от данного поведения, то установка переменной variable_conflict может быть лучшим решением.
В PostgreSQL тело функции должно быть записано в виде строки. Поэтому нужно использовать знак доллара в качестве кавычек или экранировать одиночные кавычки в теле функции. (См. Подраздел 40.11.1.)
Для группировки функций вместо пакетов используются схемы.
Так как пакетов нет, нет и пакетных переменных. Это несколько раздражает. Вместо этого можно хранить состояние каждого сеанса во временных таблицах.
Целочисленные циклы FOR с указанием REVERSE работают по-разному. В PL/SQL значение счётчика уменьшается от второго числа к первому, в то время как в PL/pgSQL счётчик уменьшается от первого ко второму. Поэтому при портировании нужно менять местами границы цикла. Это печально, но вряд ли будет изменено. (См. Подраздел 40.6.3.5.)
Циклы FOR по запросам (не курсорам) также работают по-разному. Переменная цикла должна быть объявлена, в то время как в PL/SQL она объявляется неявно. Преимущество в том, что значения переменных доступны и после выхода из цикла.
Существуют некоторые отличия в нотации при использовании курсорных переменных.
40.12.1. Примеры портирования
Пример 40-8 показывает, как портировать простую функцию из PL/SQL в PL/pgSQL.
Пример 40-8. Портирование простой функции из PL/SQL в PL/pgSQL
Функция Oracle PL/SQL:
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar, v_version varchar) RETURN varchar IS BEGIN IF v_version IS NULL THEN RETURN v_name; END IF; RETURN v_name || '/' || v_version; END; / show errors;
Пройдемся по этой функции и посмотрим различия по сравнению с PL/pgSQL:
Ключевое слово 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;
Пример 40-9 показывает, как портировать функцию, которая создаёт другую функцию, и как обрабатывать проблемы с кавычками.
Пример 40-9. Портирование функции, создающей другую функцию, из 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 VARCHAR, v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR 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 содержат кавычки.
Пример 40-10 показывает, как портировать функцию с OUT параметрами и манипулирующую строками. PostgreSQL не имеет встроенной функции instr
, но её можно создать, используя комбинацию других функций. Подраздел 40.12.3 содержит реализацию instr
в PL/pgSQL, которую можно использовать для облегчения портирования.
Пример 40-10. Портирование из PL/SQL в PL/pgSQL процедуры, которая манипулирует строками и содержит OUT параметры
Следующая процедура на языке Oracle PL/SQL разбирает URL и возвращает составляющие его элементы (сервер, путь и запрос).
Версия Oracle:
CREATE OR REPLACE PROCEDURE cs_parse_url( v_url IN VARCHAR, v_host OUT VARCHAR, -- Возвращается обратно v_path OUT VARCHAR, -- И это возвращается v_query OUT VARCHAR) -- И это 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');
Пример 40-11 показывает, как портировать процедуру, использующую большое количество специфических для Oracle возможностей.
Пример 40-11. Портирование процедуры из 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, 'Не удалось создать новое задание. Задание сейчас выполняется.'); 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, sysdate); EXCEPTION WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists END; COMMIT; END; / show errors
Подобные процедуры легко преобразуются в функции PostgreSQL, возвращающие void. На примере этой процедуры можно научиться следующему:
- (1)
- В PostgreSQL нет оператора PRAGMA.
- (2)
- Если выполнить LOCK TABLE в PL/pgSQL, блокировка не будет снята, пока не завершится вызывающая транзакция.
- (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 'Не удалось создать новое задание. Задание сейчас выполняется.';(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. Количество встроенных имён исключений значительно больше (см. Приложение A). В настоящее время нет способа задать пользовательское имя исключения, хотя вместо этого можно вызывать ошибку с заданным пользователем значением SQLSTATE.
40.12.2. На что ещё обратить внимание
В этом разделе рассматриваются ещё несколько вещей, на которые нужно обращать внимание при портировании функций из Oracle PL/SQL в PostgreSQL.
40.12.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 используются по-другому, то придётся подумать.
40.12.2.2. EXECUTE
PL/pgSQL версия EXECUTE работает аналогично версии в PL/SQL, но нужно помнить об использовании quote_literal
и quote_ident
, как описано в Подразделе 40.5.4. Без использования этих функций конструкции типа EXECUTE 'SELECT * FROM $1'; будут работать ненадёжно.
40.12.2.3. Оптимизация функций на PL/pgSQL
Для оптимизации исполнения PostgreSQL предоставляет два модификатора при создании функции: "изменчивость" (будет ли функция всегда возвращать тот же результат при тех же аргументах) и "строгость" (возвращает ли функция NULL, если хотя бы один из аргументов NULL). Для получения подробной информации обратитесь к справочной странице CREATE FUNCTION.
При использовании этих атрибутов оптимизации оператор CREATE FUNCTION может выглядеть примерно так:
CREATE FUNCTION foo(...) RETURNS integer AS $$ ... $$ LANGUAGE plpgsql STRICT IMMUTABLE;
40.12.3. Приложение
Этот раздел содержит код для совместимых с Oracle функций instr
, которые можно использовать для упрощения портирования.
-- -- instr functions that mimic Oracle's counterpart -- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters. -- -- Searches string1 beginning at the nth character for the mth occurrence -- of string2. If n is negative, search backwards. If m is not passed, -- assume 1 (search starts at first character). -- CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$ DECLARE pos integer; BEGIN pos:= instr($1, $2, 1); RETURN pos; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE FUNCTION instr(string varchar, string_to_search 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 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); length := char_length(string); beg := length + beg_index - ss_length + 2; WHILE beg > 0 LOOP temp_str := substring(string FROM beg FOR ss_length); pos := position(string_to_search IN temp_str); IF pos > 0 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 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 beg_index > 0 THEN beg := beg_index; temp_str := substring(string FROM beg_index); FOR i IN 1..occur_index LOOP pos := position(string_to_search IN temp_str); IF i = 1 THEN beg := beg + pos - 1; ELSE beg := beg + pos; END IF; temp_str := substring(string FROM beg + 1); END LOOP; IF pos = 0 THEN RETURN 0; ELSE RETURN beg; END IF; ELSIF beg_index < 0 THEN ss_length := char_length(string_to_search); length := char_length(string); beg := length + beg_index - ss_length + 2; WHILE beg > 0 LOOP temp_str := substring(string FROM beg FOR ss_length); pos := position(string_to_search IN temp_str); IF pos > 0 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;
Пред. | Начало | След. |
Советы по разработке на PL/pgSQL | Уровень выше | PL/Tcl — процедурный язык Tcl |