40.12. 從Oracle PL/SQL進行移植 · PostgreSQL 中文文檔 9.3

# 40.12\. 從Oracle PL/SQL進行移植本節解釋了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` 動作（不是默認動作），參考[Section 40.10.1](#calibre_link-1045)中的描述。首先，最好是避免這種模糊的方式，但如果不得不移植一個依賴于該動作的大量的代碼，那么設置`variable_conflict`是個不錯的主意。 * 在PostgreSQL里，函數體必須寫成字符串文本，因此你需要使用美元符界定或者逃逸函數體里面的單引號(見[Section 40.11.1](#calibre_link-1577))。 * 應該用模式把函數組織成不同的組，而不是用包。 * 因為沒有包，所以也沒有包級別的變量。這一點有時候挺討厭。你可以在臨時表里保存會話級別的狀態。 * 帶有`REVERSE`的整數的`FOR`循環的工作模式是不一樣的： PL/SQL中是從第二個數向第一個數倒計，而PL/pgSQL是從第一個數想第二個數倒計，因此在移植時，需要交換循環邊界。不幸的是這種不兼容性是不太可能改變的（參閱[Section 40.6.3.5](#calibre_link-1591)）。 * 遍歷查詢的`FOR`循環（而不是循環游標）同樣有不同的工作模式：必須已經聲明了目標變量，在這一點上PL/SQL通常是隱式的聲明。這樣做的優點是，在退出循環后，仍然可以獲得變量值。 * 在使用游標變量方面，存在一些記數法差異。 ## 40.12.1\. 移植樣例 [Example 40-8](#calibre_link-1592)演示了如何從PL/SQL向PL/pgSQL移植一個簡單的函數。 **Example 40-8\. 從PL/SQL向PL/pgSQL移植一個簡單的函數** 下面是一個Oracle PL/SQL函數: ``` 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; ``` 讓我們讀一遍這個函數然后看PL/pgSQL與之的不同： * 在函數原型里的`RETURN`(不是函數體里的)關鍵字到了PostgreSQL里就是`RETURNS`。還有， `IS`變成`AS`，并且你還需要增加一個`LANGUAGE`子句，因為PL/pgSQL并非唯一可用的函數語言。 * 在PostgreSQL里，函數體被認為是一個字符串文本，所以你需要使用單引號或者美元符界定它，這個包圍符代替了Oracle 最后的那個`/`。 * 在PostgreSQL里沒有`show errors`命令，不需要這個命令是因為錯誤是自動報告的。下面是這個函數移植到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 40-9](#calibre_link-1579)演示了如何移植一個創建另外一個函數的函數的方法，以及演示了如何處理引號逃逸的問題。 **Example 40-9\. 從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 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; ``` 下面是這個函數在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; ``` 請注意函數體是如何獨立制作并且傳遞給`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 40-10](#calibre_link-1593)演示了如何移植一個帶有`OUT`參數和字符串處理的函數。 PostgreSQL里面沒有內置`instr`函數，但是你可以用其它函數的組合來繞開它。在[Section 40.12.3](#calibre_link-1594)里有一個PL/pgSQL 的`instr`實現，你可以用它讓你的移植變得更簡單些。 **Example 40-10\. 從PL/SQL向 PL/pgSQL移植一個字符串操作和`OUT`參數的過程** 下面的Oracle PL/SQL過程用于分析一個URL并且返回若干個元素(主機、路徑、命令)。下面是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; ``` 這個函數可以這么用: ``` SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz'); ``` [Example 40-11](#calibre_link-1595)演示了如何一個使用各種Oracle專有特性的過程。 **Example 40-11\. 從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;<a name="CO.PLPGSQL-PORTING-PRAGMA">**(1)**</a> BEGIN LOCK TABLE cs_jobs IN EXCLUSIVE MODE;<a name="CO.PLPGSQL-PORTING-LOCKTABLE">**(2)**</a> 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<a name="CO.PLPGSQL-PORTING-COMMIT">**(3)**</a> 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`的函數移植到PostgreSQL里。對這個過程特別感興趣是因為它可以教一些東西： [**(1)**](#calibre_link-1596) 在PostgreSQL里沒有`PRAGMA`語句。 [**(2)**](#calibre_link-1597) 如果你在PL/pgSQL里做一個`LOCK TABLE`，那么這個鎖在調用該命令的事務完成之前將不會釋放。 [**(3)**](#calibre_link-1598) 你不能在PL/pgSQL函數里發出`COMMIT`。函數是在外層的事務里運行的，因此`COMMIT`蘊涵著結束函數的執行。不過，在這個特殊場合下，這是不必要的了，因為`LOCK TABLE`獲取的鎖將在拋出錯誤的時候釋放。下面是把這個過程移植到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';<a name="CO.PLPGSQL-PORTING-RAISE">**(1)**</a> 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 <a name="CO.PLPGSQL-PORTING-EXCEPTION">**(2)**</a> -- don't worry if it already exists END; END; $$ LANGUAGE plpgsql; ``` [**(1)**](#calibre_link-1599) `RAISE`的語法和Oracle的類似語句差別相當明顯。盡管`RAISE``_exception_name_`運行的基本情況相似。 [**(2)**](#calibre_link-1600) PL/pgSQL里支持的異常的名字和Oracle的不同。內置的異常名要大的多(參閱[Appendix A](#calibre_link-120))。目前還不能聲明用戶定義的異常名。整個過程和Oracle的等效的主要的功能型差別是，在`cs_jobs`上持有的排他鎖將保持到調用的事務結束。同樣，如果調用者后來退出(比如說因為錯誤)，這個過程的效果將被回滾掉。 ## 40.12.2\. 其它注意事項本節解釋幾個從Oracle PL/SQL函數向PostgreSQL 移植的幾個其它方面的事情。 ### 40.12.2.1\. 異常后的隱含回滾在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`，那么就需要想想了。 ### 40.12.2.2\. `EXECUTE` PL/pgSQL版本的`EXECUTE`類似PL/SQL運轉，不過你必須記住要像[Section 40.5.4](#calibre_link-1590)里描述的那樣用`quote_literal`和`quote_ident`。如果你不用這些函數，那么像`EXECUTE 'SELECT * FROM $1';`這樣的構造是不會運轉的。 ### 40.12.2.3\. 優化PL/pgSQL函數 PostgreSQL給你兩個創建函數的修飾詞用來優化執行："volatility" (易變的，在給出的參數相同時，函數總是返回相同結果)和"strictness" (嚴格的，如果任何參數是NULL，那么函數返回NULL)。參考[CREATE FUNCTION](#calibre_link-4)的手冊獲取細節。如果要使用這些優化屬性，那么你的`CREATE FUNCTION`語句可能看起來像這樣： ``` CREATE FUNCTION foo(...) RETURNS integer AS $$ ... $$ LANGUAGE plpgsql STRICT IMMUTABLE; ``` ## 40.12.3\. 附錄本節包含Oracle兼容的`instr`函數，你可以用它簡化你的移植過程。 ``` -- -- 模擬 Oracle 概念的 instr 函數 -- 語法: instr(string1, string2, [n], [m]) 這里的 [] 表示可選參數 -- -- 從 string1 的第 n 個字符開始尋找 string2 的第 m 個出現。 -- 如果 n 是負數，則從后向前著。如果沒有傳遞 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; ELSIF beg_index < 0 THEN 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; ELSE 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; ELSIF beg_index < 0 THEN 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; ELSE RETURN 0; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; ```