排序有文化，合理要求总有招
可以了解：Oracle中的正则表达式和函数索引使用自定义函数时的特性。
本文发表于 《软件报》 2009年12月28日 第19版
在大业务系统中，往往会定义公用代码，通过它来存放并统一管理各类标准字典。以HIS为例，如公用职称、职务、地区、民族等基础信息，数据都会组织到同一张表中，通过分类来进行管理。
业务表随着变化而扩展
如下图（图001）：
所示的业务表，其中ZIFU和SHUZI类字段是随业务变化而继续扩展，但使用时，往往要求同类字段摆放在一起，使特性维护时容易理解原始设计思路，字段确定后，原来顺序号在前的类别（我们把除数字之外的同名字段当作一个类别）仍保留在前，也就是说在进行数据列对照或者是数据维护时需要下面的顺序：
解决思路
Oracle有数据库表信息视图user_tab_columns 和user_col_comments，假定我们要取得表gy_daima的信息，利用With来预先定义这样的虚表：
with kf_biaolie as (
select a.column_id shunxuhao, a.table_name biaoming, a.column_name lieming, b.comments zhongwenmc from user_tab_columns a, user_col_comments b where a.table_name = b.table_name and a.COLUMN_NAME = b.column_name and a.table_name = 'GY_DAIMA')
在图001中看到，我们要达成目标，需要：
- 去除数据列中的数字；
- 以列名进行分组取得每一组的最小序号；
- 上述结果和表自关联，每一类取得相应的最小序号；
- 利用相应项目的最小序号和原始序号进行排序。
下面我们就以SQL加注解的方式看实现过程：
select a.shunxuhao, a.lieming, a.zhongwenmc, -- 3相当于自关联取类的设计序号和序号
b.zhuming,b.xuhao * 1000 + a.shunxuhao xinxuhao
from kf_biaolie a,
(select min(shunxuhao) xuhao, zhuming
from ((select shunxuhao, lieming,
regexp_replace(lieming, '[0-9]', '') zhuming -- 1 取分类，利用正则表达式去除数字
from kf_biaolie))
group by zhuming) b --2 取每一类的最小值
where regexp_replace(a.lieming, '[0-9]', '') = b.zhuming -- 3相当于自关联取类的设计序号和序号
order by b.xuhao * 1000 + a.shunxuhao --4 按类序号和物理顺序进行排序
上述SQL和前面定义的虚表结合起来，在SQL中直接注释不仅可以执行，也易于理解，字段顺序就符合设计思路了。再来一个类似的问题：
“路牌”里的文化
先看看在Windows平台中，对于文件名是如何进行排序的。
文件名中如果有数字，前缀相同时，是按照数字值的大小进行排序，它不是简单地按字符串大小来处理，就象路牌号一样按数字顺序。此问题不妨称之为“路牌”问题（类似问题，其实我们还是会遇到很多）。解决时只要：取得门牌号，不足6位前面补0，如果有分门牌号，同样补0，使它形成一个可对比的字符串，在函数fun_my_dizhixh中是使用了：
li_pos := regexp_instr(prm_dizhi, '[0-9]'); --查找数字开始位置
li_npos := regexp_instr(ls_str, '[^0-9]'); --查找数字后的第一个非数字
使用正则表达式来查找数字和非数字的开始，并根据需要进行补0处理，如“解放路12-2号”信息就变成了“解放路000012.00002号”，而地址“解放路12-10号”也就变成了“解放路000012.00010号”，之后对按字符串排序就OK。在一般的小区进行人口信息管理时同样有效，只要结构符合“n-n”即可，效果如下：
当然对于fun_my_dizhixh函数来说，如果不使用正则表达式也能做到。我们使用了regexp_instr、regexp_replace，当然正则表达式一家亲的函数还有其它几个。由于计算结果做条件查询时，就易受性能影响。我们需要：
函数索引来帮助
gy_bingrenxx是一张有17000多条数据的记录表，现在要做这样的查询：
select jiatingdz, fun_my_dizhixh(jiatingdz) from gy_bingrenxx where fun_my_dizhixh(jiatingdz) like '春天花园%' order by fun_my_dizhixh(jiatingdz);
此查询必须全表扫描，当数据量继续增加后，我们需要利用函数索引，它的好处是“加快应用速度，不需要修改逻辑或查询”。首先我们对表建立这样的索引：
create index IDX_GY_BINGRENXX_JTDZ on GY_BINGRENXX (FUN_MY_DIZHIXH(JIATINGDZ));
自定义函数做索引项目时要告诉Oracle，我们自己定义的函数是可信的，即它一个确定的输入得到的输出是固定的，象random之类的函数每次输出值不一样，是不能应用于函数索引的。只要在定义函数时加上deterministic即可，为了函数索引生效，只要分表分析一下：
ANALYZE TABLE gy_bingrenxx COMPUTE STATISTICS;
现在我们的执行效率前后对比，性能差异巨大：
正则表达式除了解决我们的“设计分组”，也能解决“路牌问题”，再加上函数索引，性能问题也迎刃而解。
附fun_my_dizhixh.fnc的源码：
代码
create or replace function fun_my_dizhixh( prm_dizhi in varchar2 ) return varchar2 deterministic
is
Result varchar(500);
ls_head varchar(500);
ls_sub varchar(100);
ls_str varchar(500);
ls_char varchar(10);
li_pos integer;
li_npos integer;
begin
li_pos := regexp_instr(prm_dizhi, '[0-9]'); --查找数字开始位置
ls_head := substr(prm_dizhi,1,li_pos -1 ); --前缀
ls_str := substr( prm_dizhi,li_pos );
li_npos := regexp_instr(ls_str, '[^0-9]'); --查找数字后的第一个非数字
if li_npos = 0 then --表示后面可能全是数字
Result := ls_str;
ls_str := '';
else
Result := substr( ls_str,1, li_npos -1 );
ls_str := substr( ls_str, li_npos );
end if;
if length(Result)<6 then
Result := substr('000000',1, 6 - length(Result)) || Result;
end if;
if length(ls_str)>0 then --后续内容
if substr(ls_str,1,1) <> '-' then
return ls_head || Result || ls_str;
end if; --有子门牌号
ls_str := substr(ls_str, 2 );
ls_char := substr(ls_str,1,1);
if ls_char>='0' and ls_char<='9' then --查看- 符号后面是否有数字
li_npos := regexp_instr(ls_str, '[^0-9]'); --查看 -后面的第一个非字符
if li_npos = 0 then
li_npos := length(ls_str)+1;
end if;
ls_sub := substr(ls_str, 1, li_npos - 1 );
ls_str := substr(ls_str, li_npos );
if length(ls_sub)<6 then
ls_sub := substr('0000', 1, 5 - length(ls_sub)) || ls_sub;
result := Result || '.' || ls_sub; --拼接门牌号和子门牌号
end if;
end if;
end if;
Return ls_head || Result || ls_str;
end fun_my_dizhixh;
2009年11月18日完稿于浙江杭州
2009年11月23日修订
