Postgresql 中文操作指南
43.13. Porting from Oracle PL/SQL #
本部分说明了 PostgreSQL 的 PL/pgSQL 语言和 Oracle 的 PL/SQL 语言之间的区别，以帮助将应用程序从 Oracle® 移植到 PostgreSQL 的开发者。
PL/pgSQL 在许多方面与 PL/SQL 类似。它是一种块结构的命令式语言，所有变量都必须声明。赋值、循环和条件类似。从 PL/SQL 移植到 PL/pgSQL 时应记住的主要区别是：
43.13.1. Porting Examples #
Example 43.9 展示如何将一个简单的函数从 PL/SQL 移植到 PL/pgSQL。
Example 43.9. Porting a Simple Function from PL/SQL to 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 相比的区别：
将此函数移植到 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 43.10 展示如何移植一个创建其他函数的函数，以及如何处理随之而来的引用问题。
Example 43.10. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL
以下过程从 SELECT 语句获取行，并为结果构建一个大型函数，以提高效率。
这是 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 包含引号，它也不会生成错误代码。
Example 43.11 展示如何移植一个使用 OUT 参数和字符串操作的函数。PostgreSQL 没有一个内置的 instr 函数，但你可以使用其他函数的组合创建一个这样的函数。在 Section 43.13.3 中，有一个 PL/pgSQL 实现 instr，你可以使用它简化移植。
Example 43.11. Porting a Procedure With String Manipulation and OUT Parameters from PL/SQL to 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 43.12 展示如何移植一个使用许多特定于 Oracle 的特性的过程。
Example 43.12. Porting a Procedure from PL/SQL to 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;43.13.2. Other Things to Watch For #
本部分说明了将 Oracle PL/SQL 函数移植到 PostgreSQL 时需要注意的其他一些事项。
43.13.2.1. Implicit Rollback after Exceptions #
在 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 的过程，则需要考虑一些实际情况。
43.13.2.2. EXECUTE #
PL/pgSQL 版本的 EXECUTE 与 PL/SQL 版本类似，但你必须记住使用 quote_literal 和 quote_ident，如 Section 43.5.4 中所述。如果你不使用这些函数，EXECUTE 'SELECT * FROM $1'; 类型的结构将不可靠地运行。
43.13.2.3. Optimizing PL/pgSQL Functions #
PostgreSQL 提供两个函数创建修饰符以优化执行：“可变性”（函数是否始终在给定相同参数时返回相同结果）和“严格性”（函数是否在任何参数为空时返回空）。有关详细信息，请参阅 CREATE FUNCTION 参考页面。
利用这些优化属性时，您的 CREATE FUNCTION 语句可能如下所示：
CREATE FUNCTION foo(...) RETURNS integer AS $$
...
$$ LANGUAGE plpgsql STRICT IMMUTABLE;43.13.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;