本节说明 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
将此行为更改为与 PL/SQL 匹配,如 第 41.11.1 节 中所述。通常最好一开始就避免这种歧义,但是如果您必须移植大量依赖于此行为的代码,设置 variable_conflict
可能是最佳解决方案。
在 PostgreSQL 中,函数体必须写成字符串字面量。因此,您需要在函数体中使用美元符引用或转义单引号。(参见 第 41.12.1 节。)
数据类型名称通常需要转换。例如,在 Oracle 中,字符串值通常声明为 varchar2
类型,这是一种非 SQL 标准类型。在 PostgreSQL 中,请改用 varchar
或 text
类型。类似地,用 numeric
替换 number
类型,或者如果存在更合适的数值数据类型,则使用它。
使用模式而不是包来组织函数到组中。
由于没有包,因此也没有包级变量。这有点令人讨厌。您可以将每个会话状态保存在临时表中。
带有 REVERSE
的整数 FOR
循环的工作方式不同:PL/SQL 从第二个数字递减到第一个数字,而 PL/pgSQL 从第一个数字递减到第二个数字,在移植时需要交换循环边界。这种不兼容性很不幸,但不太可能改变。(参见 第 41.6.5.5 节。)
FOR
循环遍历查询(游标除外)的工作方式也不同:目标变量必须已声明,而 PL/SQL 总是隐式地声明它们。这样做的好处是,在循环退出后仍然可以访问变量值。
游标变量的使用存在各种符号差异。
示例 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
关键字在 PostgreSQL 中变为 RETURNS
。此外,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;
示例 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;
以下是此函数在 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
包含引号时不会生成错误的代码。
示例 41.11 显示了如何移植带有 OUT
参数和字符串操作的函数。PostgreSQL 没有内置的 instr
函数,但您可以使用其他函数的组合来创建一个。在 第 41.13.3 节 中,有一个 PL/pgSQL 实现的 instr
,您可以使用它来使移植更容易。
示例 41.11. 将带有字符串操作和 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');
示例 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;
本节说明在将 Oracle PL/SQL 函数移植到 PostgreSQL 时需要注意的其他一些事项。
在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
的过程,则需要进行一些实际的思考。
EXECUTE
#PL/pgSQL版本的EXECUTE
的工作方式与PL/SQL版本类似,但您必须记住使用quote_literal
和quote_ident
,如第41.5.4节所述。除非您使用这些函数,否则EXECUTE 'SELECT * FROM $1';
类型的构造将无法可靠地工作。
PostgreSQL提供了两个函数创建修饰符来优化执行:“易变性”(当给定相同的参数时,函数是否始终返回相同的结果)和“严格性”(如果任何参数为null,函数是否返回null)。请参阅CREATE FUNCTION参考页面以获取详细信息。
在使用这些优化属性时,您的CREATE FUNCTION
语句可能如下所示
CREATE FUNCTION foo(...) RETURNS integer AS $$ ... $$ LANGUAGE plpgsql STRICT IMMUTABLE;
本节包含一组与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;
如果您在文档中发现任何不正确的内容,与您对特定功能的体验不符,或者需要进一步澄清,请使用此表单报告文档问题。