Ｏｒａｃｌｅ Ｄａｔａｂａｓｅ １０ｇ對正則表達式的支持分析

摘要：正則表達式是源于Unix系統的一種非常強大的數據檢索技術。Oracle Database 10g開始新增加了一種功能特性：支持正則表達式。論文主要介紹正則表達式的概念和基本語，并分析Oracle 10g數據庫中對正則表達式的支持描述，最后通過例子分析如何在數據庫應用程序設計中應用正則表達式。

關鍵詞：Oracle；正則表達式；數據庫開發

中圖分類號：TP311文獻標識碼：A 文章編號：1009-3044(2008)27-1869-03

The Analysis of Supporting Regular Expression in Oracle Database 10g

GU Yun-feng

(Jinling Institute of Technology， Nanjing 210001， China)

Abstract：Regular Expression is a kind of very strong data index technique that comes from the Unix system. The oracle database 10g started to add a kind of new function characteristic: support the regular expression. This text mainly introduces the concept of Regular Expression， and analyses the description of how the Oracle database 10g supports the regular expression， finally through some examples analyses how to apply regular expression in the procedure of database application design.

Key words：Oracle; regular expression; database develop

1 前言

Oracle數據庫在有效地存儲和管理大數據容量方面有著出色的能力。比如Oracle 10g中可以存儲8千兆兆字節或8388608萬億字節的數據。隨著數據容量的不斷增長，對數據的實時方式的訪問和提供更為靈活和有效的處理方式就變得越來越重用了。為了適應這種需求，Oracle公司在database 10g版本中新增加了一種功能特性：支持正則表達式。

2 正則表達式的概念

正則表達式是為了編程處理文本對文本模式進行描述的方式。正則表達式是源于Unix系統的一種非常強大的數據檢索技術。它是由元字符（metacharacter）與規則字符組成，答應用戶對搜索條件有更靈活的控制。而且對于規范像電話號話、email地址、文件名等的格式也非常有用。

正則表達式由標準的元字符（metacharacters）所構成。那么什么是元字符？元字符提供了如何組成一個正則表達式的運算法則。不同的元字符對應著不同的意義。具體來說，Oracle支持的元字符有如下一些：[1]

· '^' 表示字符串的開始

·'$' 表示字符串的結束

·'.' 表示任何字符

·[]表示字符的范圍，比如說'[a-z]'，表示任何ASCII小寫字母，與字符類\"[[:lower:]]\"\" 等價

·'?' 答應一個后繼字符匹配零次或一次

·'+' 答應一個后繼字符匹配一次或多次

·'*' 表示零次或多次

可以使用\"{m，n}\"指定一個精確地出現范圍，其意思是“出現從m次到n次”；\"{m}\" 表示“正好m次”；而\"{m，}\"表示“至少m次”。還可以使用圓括號組合字符的集合，使用\"\"（豎線）表示可替換。例如，字符串'^([a-z]+[0-9]+)$'將匹配所有由小寫字母或數字組合成的字符串。

數據有效性的確認、文字過濾、字符串的分析等等都是正則表達式中可以使用的地方。因此可以通過正則表達式來確保電話號碼、郵政編碼、email地址、身份證號、IP地址、文件路徑名等等的正確性。而且，可以將匹配樣式定為HTML標簽、數字、日期或者任何東西，只要他們處在一個文本類型的數據中并且匹配一定的模式，而且可以以任何其他的模式來替換他們。

3 Oracle10g支持基于POSIX標準的正則表達式

在Oracle Database 10g以前，應用程序開發人員在數據庫應用程序里可以有幾種方法處理文本數據。例如通過SQL操作符如LIKE、INSTR、SUBSTR和REPLACE等進行基本的文本操作。或者是使用Oracle Java虛擬機，即裝載一個正則表達式處理器的Java實現。有了Database 10g版本，就可以有更有效、更直觀、可移植、更容易維護的方法來解決這個問題。因為Oracle 10g版本中在數據庫SQL引擎內部本地性支持正則表達式。Oracle Database 10g的正則表達式的實現基于POSIX標準定義的語法和語義－這是一個廣泛使用的、方便的正則表達式。

Oracle Database10g對正則表達式的支持是作為搜索和操作文本數據的數據庫函數集來實現的。Oracle Database10g提供了四個新的函數，分別是：REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR和REGEXP_REPLACE。它們在用法上與Oracle SQL函數LIKE、INSTR、SUBSTR 和REPLACE用法，但是它們使用POSIX正則表達式代替了老的百分號（%）和通配符（_）字符，并且支持char、nchar、varchar2、nvarchar2、clob和nclob等數據類型作為輸入參數。[2]

(1) REGEXP_LIKE

REGEXP_LIKE與LIKE操作符相似，是返回一個boolean數據。它指出模式是否匹配給定的字符串。它不提供字符串中發生匹配的方式和位置。假如第一個參數匹配正則表達式它就解析為TRUE。

該命令語法如下：

REGEXP_LIKE(src，pattern[，match_option])

其中src是要搜索的文本數據，pattern是設計的正則表達式，match_option是匹配的可選項。

例如WHERE REGEXP_LIKE(ENAME ，'^J[AO]'，'i') 將在ENAME以JA或JO開始的情況下返回一行數據。'i'參數指定正則表達式是大小寫敏感的。

(2) REGEXP_INSTR

REGEXP_INSTR與INSTR函數類似。主要是返回與模式相匹配的字符的開始或結尾的字符位置。

其語法如下：

REGEXP_INSTR(src，pattern[，position [，occurrence [，return_option [，match_option]]]])

其中：src是要搜索的文本數據；pattern是設計的正則表達式；position是開始搜索的位置；occurrence是指匹配出現的次數；return_option 可選0或1，0表示返回開始位置，1表示返回結束位置；match_option是匹配的可選項。

它返回一個字符串中匹配一個正則表達式的第一個子串的開始位置。

例如：SELECT REGEXP_INSTR('The total is $400 for your purchase.'，'$[[:digit:]]+') FROM DUAL;

這個查詢返回14，即$400在字符串的開始位置。

(3) REGEXP_SUBSTR

REGEXP_SUBSTR函數擴展了substr的功能，可以在一個文本數據中搜索一個正則表達式，并返回匹配模式的數據。

命令語法如下：

REGEXP_SUBSTR(source_string，pattern[，position [，occurrence [，match_option]]])

其中各參數的意義如同REGEXP_INSTR。雖然結合使用SUBSTR和REGEXP_INSTR及LENGTH也可以實現這一功能，但是使用這個函數卻更為簡單。

例如：SELECT REGEXP_SUBSTR('first field，second field，third field'，'， [^，]*，') FROM dual;

這個查詢返回\"，second field，\"字符串。

(4) REGEXP_REPLACE

REGEXP_REPLACE函數擴展了replace的功能，返回初始參數被匹配子串替換之后的結果即src數據。

命令語法如下：

REGEXP_REPLACE(src，pattern[，replace [，position [，occurrence [，match_option]]]])

其中src、pattern、position、occurrence、match_option參數意義如同其他函數；replace參數表示要更換的正則表達式，可包含向后引用實例，格式是\，這里n是1到9的數字。

例如：

SELECT REGEXP_REPLACE('The temperature is 23°F'，'([[:digit:]])+°F'，('\\1'-32)*5/9'°C') FROM DUAL;

這個查詢將查找一個華氏溫度并將其轉換為攝氏度。它將返回：'The temperature is -5°C'。

再例如：

SELECT REGEXP_REPLACE('Ellen Hildi Smith'，'(.*) (.*) (.*)'， '\\3， \\1 \\2') FROM dual

該查詢結果如下：Smith， Ellen Hildi。這個例子中間，字符串'Ellen Hildi Smith'被REGEXP_REPLACE通過向后引用實例即'\\3， \\1 \\2'轉換為'Smith，Ellen Hildi'。[3]

通過上面的簡單例子，可以看到正則表達式能夠讓我們可以以以前從未想過的方法來查詢、更換、驗證數據，并且有著高度的靈活性。

4 Oracle Database 10g正則表達式使用舉例

數據庫中正則表達式的引入使構建和設計一個應用程序的方式有了新的靈活性。有了這些新的功能，就有可能將越來越多的客戶端應用程序或中間層的處理移到實際數據處進行，進而有效利用了數據庫固有的可擴展性、安全性和全局性。

4.1 創建視圖

視圖一般用于格式化一個關系型查詢，或找出關系型查詢的子集，這樣就會將正確格式下正確數量的信息返回到客戶端。我們在數據庫應用程序開發時，可以借助正則表達式執行這些功能。

例如下面代碼創建一個試圖來顯示product_information表中的子集，其條件滿足描述信息包含文本“Standard Edition”或“professional Edition”。對視圖進行的查詢只顯示具有匹配描述的行。

CREATE OR REPLACE VIEW v1 AS

SELECT product_name，product_description

FROM product_information

WHERE REGEXP_LIKE(product_information，'(Standard|professional) Edition')

而且，為了格式化查詢結果，我們可以通過使用regexp_replace，將產品的版本包括在加粗的標記（，）中。具體代碼如下：

CREATE OR REPLACE VIEW v2 AS

SELECT product_name，REGEXP_REPLACE(product_description，'(Standard| professional) Edition'，'\\1')formatted_description FROM product_information WHERE REGEXP_LIKE(product_information，'(Standard| professional) Edition')

4.2 創建約束

正則表達式可與數據庫約束一起來使用來驗證數據條目的有效性。

下面代碼中顯示了我們在創建數據庫表時創建一個check約束，它強制電話號碼為xxx-xxxxxxxx格式。

CREATE TABLE t1 (

Telephone VARCHAR2 (12)，

CHECK (REGEXP_LIKE(Telephone，'^[0-9]{3}-[0-9]{8}$'))

);

通過創建此約束，任何試圖將不符合該格式的電話號碼插入到表中的操作都違背類約束，因此被拒絕。

另外我們也可以為已經存在的表添加在CHECK約束和函數索引中指定REGEXP_LIKE。例如編寫如下語句：

ALTER TABLE EMP ADD CONSTRAINT REGEX01CHECK (REGEXP_LIKE(ENAME，'^[[:alpha:]]+$'));

這條語句使得ENAME字段只能包含字母和數字字符（也就是說沒有空格或者標點符號）。試圖插入或者更新這些數據將導致一個ORA-2290異常，或者檢查約束的有效性。

4.3 創建函數

函數是執行某種功能的代碼塊，它擁有可供引用的標識符，并且擁有可以傳遞信息的參數。Oracle允許根據特殊的需要，創建自定義的函數。我們在設計自定義函數時就可以充分利用正則表達式，來擴展數據庫函數的功能。下面的示例就是創建一個使用正則表達式的Oracle自定義函數，用于驗證身份證號碼合法性。

CREATE OR REPLACE FUNCTION fn_checkidcard (p_idcard IN VARCHAR2)

RETURN INT //函數返回int類型的數據

IS

v_regstrVARCHAR2 (2000);//正則表達式pattern字符串

v_areacodeVARCHAR2 (2000):= '11，12，…91，';//身份證地區代碼：例如11：北京，12：天津…91：國外等

BEGIN

CASE LENGTHB (p_idcard)//判斷身份證號碼的位數

WHEN 18//身份證為18位

THEN

IF INSTRB(v_areacode，SUBSTR (p_idcard，1，2)||'，') = 0

THEN

RETURN 0;// 身份證號中地區代碼是非法的，函數返回0

END IF;

IFMOD (TO_NUMBER (SUBSTRB (p_idcard，6， 2)) + 1900，400) = 0OR (MOD(TO_NUMBER (SUBSTRB (p_idcard，6， 2)) + 1900，100) <>0AND MOD (TO_NUMBER (SUBSTRB(p_idcard，6，2)) + 1900，4) = 0)//判斷出生日期是否為閏年

THEN

ｖ＿ｒｅｇｓｔｒ：＝ ＇＾［１－９］［０－９］｛５｝１９［０－９］｛２｝（（０１｜０３｜０５｜０７｜０８｜１０｜１２）（０［１－９］｜［１－２］［０－９］｜３［０－１］）｜（０４｜０６｜０９｜１１）（０［１－９］｜［１－２］［０－９］｜３０）｜０２（０［１－９］｜［１－２］［０－９］））［０－９］｛３｝［０－９Ｘｘ］＄＇；／／ 閏年出生日期的合法性正則表達式

ＥＬＳＥ

ｖ＿ｒｅｇｓｔｒ：＝＇＾［１－９］［０－９］｛５｝１９［０－９］｛２｝（（０１｜０３｜０５｜０７｜０８｜１０｜１２）（０［１－９］｜［１－２］［０－９］｜３［０－１］）｜（０４｜０６｜０９｜１１）（０［１－９］｜［１－２］［０－９］｜３０）｜０２（０［１－９］｜１［０－９］｜２［０－８］））［０－９］｛３｝［０－９Ｘｘ］＄＇；／／ 平年出生日期的合法性正則表達式

ＥＮＤ ＩＦ；

ＩＦ ＲＥＧＥＸＰ＿ＬＩＫＥ （ｐ＿ｉｄｃａｒｄ， ｖ＿ｒｅｇｓｔｒ） ／／通過ＲＥＧＥＸＰ＿ＬＩＫＥ，判斷身份證號是否匹配正則表達式模式

ＴＨＥＮＲＥＴＵＲＮ １；／／匹配返回數字１

ＥＬＳＥＲＥＴＵＲＮ ０；／／不匹配返回數字０

ＥＮＤ ＩＦ；

ＷＨＥＮ １５ ＴＨＥＮ ／／身份證號碼為１５位

…………／／代碼略

ＥＬＳＥ

ＲＥＴＵＲＮ ０；／／身份證號碼位數不對

ＥＮＤ ＣＡＳＥ；

ＥＸＣＥＰＴＩＯＮＷＨＥＮ ＯＴＨＥＲＳ ＴＨＥＮ ＲＥＴＵＲＮ ０；

ＥＮＤ ｆｎ＿ｃｈｅｃｋｉｄｃａｒｄ；／／結束函數

5 結束語

正則表達式的功能是非常強大的，它們幫助解決復雜的文本匹配問題。在數據庫中很多以正則表達式構成的函數功能體是無法通過傳統的SQL函數來復制的。當我們學會正確使用正則表達式，就會發現它不僅在SQL中使用，在其他的編程語言中Java語言中也得到廣泛的支持，如Sun公司通過在jdk1.4的util包里增加了個regex包來正式支持正則表達的判定。雖然試驗和犯錯對獲得正確的正則表達式模式有時是不可避免的，但是它的強大的功能還是值得我們在數據庫程序開發中去嘗試使用的。

參考文獻：

[1] Alice Rischert.Writing Better SQL Using Regular Expressions [EB/OL].http://www.oracle.com/technology/oramag/webcolumns/2003/

techarticles/rischert_regexp_pt1.html，2004.7.

[2] （美）Rick Greenwald，Robert Stackowiak.Oracle高級編程[M].北京:清華大學出版社，2007，5，403.

[3] Alice Rischert.Writing Better SQL Using Regular Expressions [EB/OL]. .http://www.oracle.com/technology/oramag/webcolumns/2003/

techarticles/rischert_regexp_pt2.html，2004.7.

[4] 中國IT實驗室.用正則表達式函數驗證身份證號碼合法性[EB/OL].http://www.builder.com.cn/2007/1007/537886.shtml，2007-10-07.