?
Dokumen ini menggunakan Manual laman web PHP Cina Lepaskan
本節(jié)解釋了Oracle的PL/SQL和PostgreSQL的PL/pgSQL語言之間的差別, 希望能對那些從Oracle?向PostgreSQL移植應(yīng)用的人有所幫助。
PL/pgSQL與PL/SQL在許多方面都非常類似。 它是一種塊結(jié)構(gòu)的,祈使語氣(命令性)的語言并且必須聲明所有變量。 賦值、循環(huán)、條件等都很類似。 在從PL/SQL向PL/pgSQL移植的時候必須記住一些事情:
如果一個SQL命令中使用的名字是一個表中的列名,或者是一個函數(shù)中變量的引用, 那么PL/SQL會將它當(dāng)作一個變量名。 這對應(yīng)的是PL/pgSQL的 plpgsql.variable_conflict = use_column動作(不是默認(rèn)動作), 參考Section 39.10.1中的描述。 首先,最好是避免這種模糊的方式,但如果不得不移植一個依賴于該動作的大量的代碼,那么設(shè)置variable_conflict是個不錯的主意。
在PostgreSQL里,函數(shù)體必須寫成字符串文本, 因此你需要使用美元符界定或者逃逸函數(shù)體里面的單引號(見Section 39.11.1)。
應(yīng)該用模式把函數(shù)組織成不同的組,而不是用包。
因?yàn)闆]有包,所以也沒有包級別的變量。這一點(diǎn)有時候挺討厭。 你可以在臨時表里保存會話級別的狀態(tài)。
帶有REVERSE的整數(shù)的FOR循環(huán)的工作模式是不一樣的: PL/SQL中是從第二個數(shù)向第一個數(shù)倒計,而PL/pgSQL是從第一個數(shù)想第二個數(shù)倒計, 因此在移植時,需要交換循環(huán)邊界。 不幸的是這種不兼容性是不太可能改變的(參閱Section 39.6.3.5)
遍歷查詢的FOR循環(huán)(而不是循環(huán)游標(biāo))同樣有不同的工作模式: 必須已經(jīng)聲明了目標(biāo)變量,在這一點(diǎn)上PL/SQL通常是隱式的聲明。 這樣做的有點(diǎn)是,在退出循環(huán)后,仍然可以獲得變量值。
在使用游標(biāo)變量方面,存在一些記數(shù)法差異。
Example 39-6 演示了如何從PL/SQL向PL/pgSQL移植一個簡單的函數(shù)
Example 39-6. 從PL/SQL向PL/pgSQL移植一個簡單的函數(shù)
下面是一個OraclePL/SQL函數(shù)
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;
讓我們讀一遍這個函數(shù)然后看PL/pgSQL與之的不同:
在函數(shù)原型里的RETURN(不是函數(shù)體里的)關(guān)鍵字到了PostgreSQL里就是RETURNS。 還有, IS變成AS,并且你還需要增加一個LANGUAGE子句, 因?yàn)?span id="377j5v51b" class="APPLICATION">PL/pgSQL并非唯一可用的函數(shù)語言。
在PostgreSQL里,函數(shù)體被認(rèn)為是一個字符串文本, 所以你需要使用單引號或者美元符界定它, 這個包圍符代替了Oracle 最后的那個/
在PostgreSQL里沒有show errors命令, 不需要這個命令是因?yàn)殄e誤是自動報告的。
下面是這個函數(shù)移植到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 39-7演示了如何移植一個創(chuàng)建另外一個函數(shù)的函數(shù)的方法, 以及演示了如何處理引號逃逸的問題
Example 39-7. 從PL/SQL向PL/pgSQL移植一個創(chuàng)建其它函數(shù)的函數(shù)
下面的過程從一個SELECT語句中抓取若干行,然后為了提高效率, 又用IF語句中的結(jié)果制作了一個巨大的函數(shù)。
這是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;
下面是這個函數(shù)在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;
請注意函數(shù)體是如何獨(dú)立制作并且傳遞給quote_literal,對其中的單引號復(fù)制雙份的。 需要這個技巧是因?yàn)闊o法使用美元符界定定義新函數(shù): 沒法保證referrer_key.key_string字段過來的字符串會解析成什么樣子。 可以假設(shè)referrer_key.kind是只有host, domain,或url, 但是referrer_key.key_string可能是任何東西, 特別是它可能包含美元符。 這個函數(shù)實(shí)際上是對原來 Oracle 版本的一個改進(jìn), 因?yàn)槿绻?tt class="STRUCTFIELD">referrer_key.key_string或referrer_key.referrer_type包含單引號的時候, 它不會生成有毛病的代碼。
Example 39-8演示了如何移植一個帶有OUT參數(shù)和字符串處理的函數(shù)。
PostgreSQL里面沒有內(nèi)置instr
函數(shù),但是你可以用其它函數(shù)的組合來繞開它。
在Section 39.12.3里有一個PostgreSQL的instr
實(shí)現(xiàn),
你可以用它讓你的移植變得更簡單些。
Example 39-8. 從PL/SQL向PL/pgSQL移植一個字符串操作和OUT參數(shù)的過程
下面的OraclePL/SQL 過程用于分析一個URL并且返回若干個元素(主機(jī)、路徑、命令)。
下面是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;
這個函數(shù)可以這么用:
SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
Example 39-9 演示了如何一個使用各種Oracle專有特性的過程
Example 39-9. 從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, '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 END; COMMIT; END; / show errors
像這樣的過程可以很容易用返回void的函數(shù)移植到PostgreSQL里。 對這個過程特別感興趣是因?yàn)樗梢越桃恍〇|西:
下面是把這個過程移植到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 '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) -- 如果已經(jīng)存在了,也不需要擔(dān)心 END; END; $$ LANGUAGE plpgsql;
本節(jié)解釋幾個從OraclePL/SQL函數(shù)向PostgreSQL移植的幾個其它方面的事情
在PL/pgSQL里,如果一個異常被EXCEPTION子句捕獲, 那么所有自這個塊的BEGIN以來的數(shù)據(jù)庫改變都會被自動回滾。 也就是說,這個行為等于你在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,那么就需要想想了。
PL/pgSQL版本的EXECUTE類似PL/pgSQL運(yùn)轉(zhuǎn),
不過你必須記住要像Section 39.5.4里描述的那樣用quote_literal
和quote_ident
。
如果你不用這些函數(shù),那么像EXECUTE 'SELECT * FROM $1';;
這樣的構(gòu)造是不會運(yùn)轉(zhuǎn)的。
PostgreSQL給你兩個創(chuàng)建函數(shù)的修飾詞用來優(yōu)化執(zhí)行:"volatility"(易變的,在給出的參數(shù)相同時, 函數(shù)總是返回相同結(jié)果)和"strictness"(嚴(yán)格的,如果任何參數(shù)是 NULL ,那么函數(shù)返回 NULL)。 參考CREATE FUNCTION的手冊獲取細(xì)節(jié)。
如果要使用這些優(yōu)化屬性,那么你的CREATE FUNCTION語句可能看起來像這樣:
CREATE FUNCTION foo(...) RETURNS integer AS $$ ... $$ LANGUAGE plpgsql STRICT IMMUTABLE;
本節(jié)包含Oracle兼容的instr
函數(shù),你可以用它簡化你的移植過程
-- -- 模擬 Oracle 概念的 instr 函數(shù) -- 語法: instr(string1, string2, [n], [m]) 這里的 [] 表示可選參數(shù) -- -- 從 string1 的第 n 個字符開始尋找 string2 的第 m 個出現(xiàn)。 -- 從 string1 的第 n 個字符開始尋找 string2 的第 m 個出現(xiàn)。 -- 如果 n 是負(fù)數(shù),則從后向前著。如果沒有傳遞 m ,假定為 1(從第一個字符開始找)。 -- 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; 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 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; 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 STRICT IMMUTABLE;