论文部分内容阅读
摘要：正则表达式是源于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，这里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.
关键词：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，这里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.