Oracle中的pl/sql代碼會涉及到兩個執行引擎，一個專門處理標準的SQL語句，另外一個處理pl/sql的過程代碼，一般在引擎切換上會帶來cpu額外的開銷。 比如foreach循環語句和普通for循環的區別，就是foreach消除了引擎切換，一直駐留在執行標準SQL的引擎中，從而縮短了執行時間。
現在有一個客戶信息表tacustomer， 包含了birthday, certificatetype, certificateno等字段，現在想要獲知客戶的生日信息。由于客戶注冊時生日等字段是可選擇填寫項，故大多數為空，所以要從證件號碼certificateno中提取，certificatetype為證件類型，除身份證之外還有軍官證、士兵證、戶口本等枚舉值，輸入的值也不可靠，本來'0'代表身份證，但由于錄入錯誤，使得非'0'值可能也指身份證，而'0'也可能輸入的不是身份證。結合這些考慮因素，寫了一個函數，輸入參數為上述三個字段，輸出為生日的年份。寫了兩個版本，在筆者的windows2003數據庫服務器上（Intel(R) Xeon(R) CPU 5140 @ 2.33GHZ，4cpu，4.00GB內存），執行語句如下（tacustomer行數為200百萬左右）：-- 測試簡單字符串連接的執行時間
SELECT COUNT(t.certificatetype t.certificateno t.birthday)
FROM tacustomer t
WHERE 1 = 1
AND ROWNUM < 2000000-- 測試自定義函數的執行時間
SELECT COUNT(f_extract_birthday(t.certificatetype, t.certificateno, t.birthday))
FROM tacustomer t
WHERE 1 = 1
AND ROWNUM < 2000000執行時間如下（秒）：
8.563
19.844（版本1）
57.953（版本2） 可以看出基本是3倍的關系（將上面的條件改為ROWNUM < 1000000，同樣滿足3倍的關系），版本1和版本2之間的區別是內部實現不同。第一代身份證15位必須全為0-9的數字，第二代18位的前17位全為數字，最后一個為數字或'X'。版本1和2對這個是否數字的校驗采用了不同的方式。版本1逐個檢查，版本2用cast(certno as numeric)并捕捉異常的方式，結果版本2比1快3倍。從中得出的結論，自定義函數一般性能比不上系統內建函數。附兩個版本的代碼：--版本1
create or replace FUNCTION f_extract_birthday(id in VARCHAR, birthday in VARCHAR)
RETURN VARCHAR
IS
i integer;
--id VARCHAR(18);
--birthday VARCHAR(8); yyyy VARCHAR(4);
len SMALLINT;
trans BOOLEAN;
c SMALLINT;
ret VARCHAR(4);
val NUMERIC(18, 0);
BEGIN
i := 1;
--id := '430302810315405';
--id := '43030219810315405X';
--birthday := '19790315';
trans := FALSE;
len := length(id);
--val := CAST(id AS NUMERIC);
--dbms_output.put_line(val); if (len = 15) then
-- 檢查是否全為數字，否則轉換操作會拋異常，導致程序崩潰，ascii('0') = 48, ascii('9') = '57'
--for i in 1 .. 15 loop
WHILE i <= 15 LOOP
--ch := substr(id, i, 1); -- 非數字會報錯
c := ascii(substr(id, i, 1));
if c < 48 or c > 57 then
exit;
end if;
i := i + 1;
end loop;
IF i = 16 THEN
trans := TRUE;
ret := '19' substr(id, 7, 2);
END IF;
end if; if (len = 18) then
-- 2006-10-18 luocm
-- 檢查是否全為數字，否則轉換操作會拋異常，導致程序崩潰，ascii('0') = 48, ascii('9') = '57'
--for i in 1 .. 17 loop
WHILE i <= 17 LOOP
--ch := substr(id, i, 1); -- 非數字會報錯
c := ascii(substr(id, i, 1));
if c < 48 or c > 57 then
exit;
end if;
i := i + 1;
end loop; c := ascii(substr(id, 18, 1));
--if i <> 18 OR (c <> 88 AND c <> 120) OR c < 48 or c > 57 THEN -- 第18位為[0-9xX]
if i = 18 AND (c = 88 OR c = 120 OR c >= 48 or c <= 57) THEN -- 第18位為[0-9xX]
trans := TRUE;
ret := substr(id, 7, 4);
end if;
end if; IF trans = FALSE THEN
i := 1; WHILE i <= 8 LOOP
c := ascii(substr(birthday, i, 1));
if c < 48 or c > 57 then
exit;
end if;
i := i + 1;
end loop; IF i > 8 THEN
yyyy := substr(ltrim(rtrim(birthday)), 1, 4);
IF yyyy < 1900 OR yyyy > 2000 THEN
ret := '';
ELSE
ret := yyyy;
END IF;
ELSE
ret := yyyy;
END IF;
END IF; RETURN ret;
end f_extract_birthday;
--版本2
create or replace FUNCTION f_extract_birthday(certtype IN varchar, certno in VARCHAR, birthday in VARCHAR)
RETURN VARCHAR
IS
len SMALLINT;
c SMALLINT;
val NUMERIC(18, 0);
ret VARCHAR(4);
BEGIN
ret := '';
IF certtype = '0' THEN -- 身份證
BEGIN
len := length(certno); if (len = 15) THEN
val := CAST(certno AS NUMERIC);
ret := '19' substr(certno, 7, 2);
ELSIF (len = 18) THEN
val := CAST(substr(certno, 1, 17) AS NUMERIC);
c := ascii(substr(certno, 18, 1)); --if i <> 18 OR (c <> 88 AND c <> 120) OR c < 48 or c > 57 THEN -- 第18位為[0-9xX]
if (c = 88 OR c = 120 OR c >= 48 or c <= 57) THEN -- 第18位為[0-9xX]
ret := substr(certno, 7, 4);
end if;
end if;
EXCEPTION
WHEN value_error THEN -- 字符串轉實數錯誤
NULL;
END;
END IF; BEGIN
IF ret IS NULL THEN
val := CAST(birthday AS NUMERIC);
ret := substr(birthday, 1, 4);
END IF; EXCEPTION
WHEN value_error THEN
NULL;
END; BEGIN
-- 判定年份是否合法，暫定[1900, 2000]區間，過幾年需要修改
--IF (ret <> '') THEN
IF (ret IS NOT NULL) THEN
val := CAST(ret AS INT);
IF (val < 1900 OR val > 2000) THEN
ret := '';
END IF;
END IF;
EXCEPTION
WHEN value_error THEN -- 字符串轉實數錯誤
ret := '';
--dbms_output.put_line(certno ',' ret);
END;
RETURN ret;
end f_extract_birthday;
