19.11. 从 Oracle 的 PL/SQL 向 PL/pgSQL移植
作者: Roberto Mello (<rmello@fslc.usu.edu>)
本节解释了 Oracle 的 PL/SQL 和PostgreSQL的 PL/pgSQL语言 之间的差别，希望能对那些从 Oracle 向PostgreSQL移植应用的 人有所帮助．这里的大部分代码都来自 ArsDigita Clickstream module，这些东西都是我在 2000 年夏天和 OpenForce Inc. 合作向PostgreSQL移植时搞的．
PL/pgSQL与 PL/SQL 在许多方面都非常类似． 它是一种块结构的， 祈使语气(命令性)的语言(必须声明所有变量)．PL/SQL 比 PostgreSQL 的PL/pgSQL有更多的特性，但是 PL/pgSQL里面允许使用 PL/SQL 所具有 的大部分功能并且还在稳步前进．
19.11.1. 主要的区别
在从 Oracle 向PostgreSQL移植的时候必须记住一些事情∶
在PostgreSQL中没有缺省参数．
你可以在PostgreSQL里重载函数．这个特性常用于绕开 缺乏缺省参数的问题．
赋值，循环和条件都很类似．
在PostgreSQL里不需要游标， 只要把查询放在 FOR 语句里就可以了 (见下面的查询)
在PostgreSQL里你一定要逃逸单引号． 见 Section 19.11.1.1．
19.11.1.1. 给我加引号∶逃逸单引号
在PostgreSQL里，你需要在自给的函数定义里逃逸单引号． 有时候这么做会产生相当有趣的代码，尤其是如果你所创建的 函数是生成其它函数的函数，如我们在 Example 19-6. 里所说的那样，逃逸大量单引号要注意的一件事是∶ 除了开始/结尾的引号之外，所有其它的都是偶数数目的．
Table 19-1 给出了这个范围．( 你会很喜欢这个小小的表格的．)
Table 19-1. 单引号逃逸表
| 引号的数目 | 用法 | 例子 | 结果 |
|---|---|---|---|
| 1 | 用于开始/结束函数体 | CREATE FUNCTION foo() RETURNS INTEGER AS '...' LANGUAGE 'plpgsql'; | 如例所示 |
| 2 | 用于赋值 SELECT 中，用于分隔字串等 | a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar''; | SELECT * FROM users WHERE f_name='foobar'; |
| 4 | 在你需要在结果字串中出现两个单引号，但又不中止该字串的时候． | a_output := a_output || '' AND name
LIKE ''''foobar'''' AND ...'' | AND name LIKE 'foobar' AND ... |
| 6 | 在你需要在结果字串中出现两个单引号，而且 终止该字串的时候． | a_output := a_output || '' AND name
LIKE ''''foobar'''''' | AND name LIKE 'foobar' |
| 10 | 如果你需要在结果字串中出现两个单引号(这里需要 8 个引号) 而且终止改字串(再加两个)． 你可能只有在用函数生成其它函数的时候才需要这个东西( 比如 Example 19-6 )． | a_output := a_output || '' IF v_'' ||
referrer_keys.kind || '' LIKE ''''''''''
|| referrer_keys.key_string || ''''''''''
THEN RETURN '''''' || referrer_keys.referrer_type
|| ''''''; END IF;''; | IF v_<...> LIKE ''<...>'' THEN RETURN ''<...>''; END IF; |
19.11.2. 移植函数
Example 19-5. 一个简单的函数
下面是一个 Oracle 函数∶
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN VARCHAR, v_version IN 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与之的不同∶
PostgreSQL 没有命名的参数． 你必须在自己的函数中明确地给它们取个别名．
Oracle 可以有 IN，OUT， 和 INOUT 参数传递给函数．比如， INOUT 的意思是该参数将接受一个数值并且返回 另外一个．PostgreSQL只有 "IN" 参数并且函数只能 返回一个数值．
在函数原型里的 RETURN(不是函数体里的) 关键字到了 PostgreSQL里就是RETURNS．
在PostgreSQL里，函数是使用单引号做为分隔符创建的， 因此你必须在你的函数里逃逸单引号(有时候会相当麻烦；参阅 Section 19.11.1.1)．
在PostgreSQL里没有 /show errors 命令．
现在让我们看看移植到PostgreSQL之后这个函数是什么样子∶
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(VARCHAR, VARCHAR)
RETURNS VARCHAR AS '
DECLARE
v_name ALIAS FOR $1;
v_version ALIAS FOR $2;
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
END IF;
RETURN v_name || ''/'' || v_version;
END;
' LANGUAGE 'plpgsql';
Example 19-6. 一个创建其它函数的函数
下面的过程从一个 SELECT 语句中抓取 若干行，然后为了提高效率，又用 IF 语句中的 结果制作了一个巨大的函数． 请特别注意在PostgreSQL里游标， FOR 循环和需要逃逸的 单引号的区别．
CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
CURSOR referrer_keys IS
SELECT * FROM cs_referrer_keys
ORDER BY try_order;
a_output VARCHAR(4000);
BEGIN
a_output := '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
a_output := a_output || ' IF v_' || referrer_key.kind || ' LIKE ''' ||
referrer_key.key_string || ''' THEN RETURN ''' || referrer_key.referrer_type ||
'''; END IF;';
END loop;
a_output := a_output || ' RETURN NULL; END;';
EXECUTE IMMEDIATE a_output;
END;
/
show errors
下面是这个函数在PostgreSQL里面的样子∶
CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
DECLARE
referrer_keys RECORD; -- 声明一个在 FOR 里用的通用的记录
a_output VARCHAR(4000);
BEGIN
a_output := ''CREATE FUNCTION cs_find_referrer_type(VARCHAR,VARCHAR,VARCHAR)
RETURNS VARCHAR AS ''''
DECLARE
v_host ALIAS FOR $1;
v_domain ALIAS FOR $2;
v_url ALIAS FOR $3;
BEGIN '';
--
-- 请注意我们是如何在一个 FOR 循环中使用 FOR <record> 构造
-- 扫描各条结果的
--
FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE ''''''''''
|| referrer_keys.key_string || '''''''''' THEN RETURN ''''''
|| referrer_keys.referrer_type || ''''''; END IF;'';
END LOOP;
a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';'';
-- 这样是可以运行的原因是因为我们没有代换任何变量
-- 否则它可就不能玩了．请查阅 PERFORM 获取另外一种运行函数的方法
EXECUTE a_output;
END;
' LANGUAGE 'plpgsql';
Example 19-7. 一个有许多字串操作和 OUT 参数的过程
下面的 Oracle PL/SQL 过程用于分析一个 URL 并且返回若干个元素 (主机，路径和查询)．它是一个过程是因为在PL/pgSQL函数里， 只有一个函数可以返回(参阅 Section 19.11.3)．在 PostgreSQL里， 一个绕开这个局限的方法就是把过程分裂成三个不同的函数∶ 一个返回主机，另外一个返回路径，最后一个返回查询．
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, '//'); --PostgreSQL没有 instr 函数
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;
下面就是把这个过程翻译成PostgreSQL的样子∶
CREATE OR REPLACE FUNCTION cs_parse_url_host(VARCHAR) RETURNS VARCHAR AS '
DECLARE
v_url ALIAS FOR $1;
v_host VARCHAR;
v_path VARCHAR;
a_pos1 INTEGER;
a_pos2 INTEGER;
a_pos3 INTEGER;
BEGIN
v_host := 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 v_host;
END IF;
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 );
RETURN v_host;
END;
' LANGUAGE 'plpgsql';
注意: PostgreSQL没有 instr 函数， 因此你可以用其它函数的组合来绕开这个局限．我自己 觉得干这件事太累，所以就创建了我自己的 instr， 它的行为和 Oracle 的完全一样(可以让我的生活更轻松一些)． 参阅 Section 19.11.6 获取代码．
19.11.3. 过程
Oracle 的过程给开发人员的灵活性更多一些，因为它不需要明确返回 任何东西，但是它可以通过使用INOUT和OUT 参数来返回数据．
例子∶
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, '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, sysdate);
EXCEPTION WHEN dup_val_on_index THEN NULL; -- don't worry IF it already exists(4)
END;
COMMIT;
END;
/
show errors
象这样的过程可以很容易用返回 INTEGER 的 函数移植到PostgreSQL里．我们对这个过程特别感兴趣是因为 它可以教我们一些东西∶
(1)
在PostgreSQL里没有 pragma 语句．
(2)
如果你在PL/pgSQL里做一个 LOCK TABLE ， 那么这个锁在调用该命令的事务完成之前将不会释放．
(3)
你也不能在PL/pgSQL过程里使用事务． 整个函数(以及其它从那里面 调用的函数)都是在一个事务里执行的并且如果出错了，那么 PostgreSQL 将回滚结果．因此只允许一个 BEGIN 语句．
(4)
EXCEPTION WHEN 不得不用一个 IF 语句替代．
因此让我们看看我们把这个过程移植到PL/pgSQL里的一种方法∶
CREATE OR REPLACE FUNCTION cs_create_job(INTEGER) RETURNS INTEGER AS '
DECLARE
v_job_id aliAS for $1;
a_running_job_count INTEGER;
a_num INTEGER;
-- pragma autonomous_transaction;
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.'';
END IF;
DELETE FROM cs_active_job;
INSERT INTO cs_active_job(job_id) values(v_job_id);
SELECT count(*) INTO a_num FROM cs_jobs WHERE job_id=v_job_id;
IF NOT FOUND THEN -- If nothing wAS RETURNed in the last query
-- 该作业不在该表中，因此让我们把它插入
INSERT INTO cs_jobs(job_id, start_stamp) values(v_job_id, sysdate());
RETURN 1;
ELSE
RAISE NOTICE ''Job already running.'';(1)
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
(1)
请注意你在PL/pgSQL 里抛出注意信息(或者错误)的方法．
19.11.4. 包
注意: 我自己也没怎么用过包，所以如果这里有错误，请告诉我．
包是 Oracle 给我们提供的将 PL/SQL 语句和函数都封装到一个实体 (象 Java 的类)里的方法，你可以在那里定义方法和对象．你可以用 一个 "." (点)来访问这些对象/方法． 下面是一个来自 ACS4( ArsDigita 社区系统) 的 Oracle 包的例子∶
CREATE OR REPLACE PACKAGE BODY acs
as
FUNCTION add_user (
user_id in users.user_id%TYPE DEFAULT NULL,
object_type in acs_objects.object_type%TYPE
DEFAULT 'user',
creation_date in acs_objects.creation_date%TYPE
DEFAULT sysdate,
creation_user in acs_objects.creation_user%TYPE
DEFAULT NULL,
creation_ip in acs_objects.creation_ip%TYPE DEFAULT NULL,
...
) RETURN users.user_id%TYPE
IS
v_user_id users.user_id%TYPE;
v_rel_id membership_rels.rel_id%TYPE;
BEGIN
v_user_id := acs_user.new (user_id, object_type, creation_date,
creation_user, creation_ip, email,
...
RETURN v_user_id;
END;
END acs;
/
show errors
我们通过一种标准的命名规则为不同的 Oracle 包对象创建不同函数 的方法来把这个函数移植到PostgreSQL里． 我们必须注意其它的一些 细节，比如在PostgreSQL函数里缺少缺省参数等． 上面的包可能会变得象下面这样∶
CREATE FUNCTION acs__add_user(INTEGER,INTEGER,VARCHAR,TIMESTAMP,INTEGER,INTEGER,...)
RETURNS INTEGER AS '
DECLARE
user_id ALIAS FOR $1;
object_type ALIAS FOR $2;
creation_date ALIAS FOR $3;
creation_user ALIAS FOR $4;
creation_ip ALIAS FOR $5;
...
v_user_id users.user_id%TYPE;
v_rel_id membership_rels.rel_id%TYPE;
BEGIN
v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...);
...
RETURN v_user_id;
END;
' LANGUAGE 'plpgsql';
19.11.5. 其它要注意的东西
19.11.5.1. EXECUTE
PostgreSQL版本的 EXECUTE 运转的很好， 不果你必须记住要象 Section 19.5.4 里描述的那样 用 quote_literal(TEXT) 和 quote_string(TEXT)． 如果你不用这些函数，那么象 EXECUTE ''SELECT * FROM $1''; 这样的构造是不会运转的．
19.11.5.2. 优化PL/pgSQL函数
PostgreSQL给你两个函数创建的修饰词用来优化执行∶ iscachable (在给出的参数相通时， 函数总是返回相通结果)和 isstrict ( 如果任何参数是 NULL，那么函数返回 NULL)． 参考 CREATE FUNCTION 的手册获取细节．
如果要使用这些优化属性，那么你必须在你的 CREATE FUNCTION 语句中使用 WITH 修饰词． 象这样∶
CREATE FUNCTION foo(...) RETURNS INTEGER AS '
...
' LANGUAGE 'plpgsql'
WITH (isstrict, IScachable);
19.11.6. 附录
19.11.6.1. 我的 instr 函数的代码
--
-- instr functions that mimic Oracle's counterpart
-- Syntax: instr(string1,string2,[n],[m]) where [] denotes optional params.
--
-- 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).
--
-- by Roberto Mello (rmello@fslc.usu.edu)
-- modified by Robert Gaszewski (graszew@poland.com)
-- Licensed under the GPL v2 or later.
--
CREATE OR REPLACE FUNCTION instr(VARCHAR,VARCHAR) RETURNS INTEGER AS '
DECLARE
pos INTEGER;
BEGIN
pos:= instr($1,$2,1);
RETURN pos;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION instr(VARCHAR,VARCHAR,INTEGER) RETURNS INTEGER AS '
DECLARE
string ALIAS FOR $1;
string_to_search ALIAS FOR $2;
beg_index ALIAS FOR $3;
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;
ELSE
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;
END IF;
END;
' LANGUAGE 'plpgsql';
--
-- Written by Robert Gaszewski (graszew@poland.com)
-- Licensed under the GPL v2 or later.
--
CREATE OR REPLACE FUNCTION instr(VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS '
DECLARE
string ALIAS FOR $1;
string_to_search ALIAS FOR $2;
beg_index ALIAS FOR $3;
occur_index ALIAS FOR $4;
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;
ELSE
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;
END IF;
END;
' LANGUAGE 'plpgsql';
876
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
