|
23.11. 从 Oracle 的 PL/SQL 向 PL/pgSQL移植
本节解释了 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 所具有 的大部分功能并且还在稳步前进. 23.11.1. 主要的区别在从 Oracle 向 PostgreSQL 移植的时候必须记住一些事情∶
23.11.1.1. 给我加引号∶逃逸单引号在 PostgreSQL 里,你需要在自给的函数定义里逃逸单引号. 有时候这么做会产生相当有趣的代码,尤其是如果你所创建的 函数是生成其它函数的函数,如我们在 Example 23-6 . 里所说的那样,逃逸大量单引号要注意的一件事是∶ 除了开始/结尾的引号之外,所有其它的都是偶数数目的. Table 23-1 给出了这个范围.( 你会很喜欢这个小小的表格的.) Table 23-1. 单引号逃逸表
23.11.2. 移植函数Example 23-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 之后这个函数是什么样子∶ 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 23-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 23-7. 一个有许多字串操作和 OUT 参数的过程 下面的 Oracle PL/SQL 过程用于分析一个 URL 并且返回若干个元素 (主机,路径和查询).它是一个过程是因为在 PL/pgSQL 函数里, 只有一个函数可以返回(参阅 Section 23.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';
23.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 里.我们对这个过程特别感兴趣是因为 它可以教我们一些东西∶ 因此让我们看看我们把这个过程移植到 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; -- free lock 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';
23.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,datetime,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';
23.11.5. 其它要注意的东西23.11.5.1. EXECUTEPostgreSQL 版本的 EXECUTE 运转的很好, 不果你必须记住要象 Section 23.5.4 里描述的那样 用 quote_literal(TEXT) 和 quote_string(TEXT) . 如果你不用这些函数,那么象 EXECUTE ''SELECT * FROM $1''; 这样的构造是不会运转的. 23.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);
23.11.6. 附录23.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'; |