对非1NF关系查询的探讨

许俊

(四川职业技术学院，四川遂宁 629000)

对非1NF关系查询的探讨

许俊

(四川职业技术学院，四川遂宁 629000)

当oracle表字符类型的列中存放多个值时，对这种非1NF关系的多表查询，希望得到满足1NF的结果，本文探讨了拆分非1NF的列值，及改进设计的多种方法，并编码实现得到1NF形式的查询结果.

oracle数据库；非1NF关系；查询

1 问题提出

某项目采用oracle11g作为后台数据库，其中a、b二个表的设计如下.

b表结构：

提供的测试数据如下：

a表数据：

b表数据：

查询a表和b表，期望得到如下所示的查询结果：

即打散b表role_id列值，把逗号分隔的每一项单独与a表内连接得到查询结果的一行.

2 表的分析

由于b表role_id存放多个值，当它作为一个整体与a表role_id单值作连接时，是不会等值匹配，必须打散拆分b表role_id列值，使列值中逗号分隔的每个数字成为若干个单一值，以此单一值匹配a表的role_id作等值内连接，得到查询结果记录集.

具体思路：使用游标提取b表每一行，拆分非1NF的列值，拆分出每一项role_id与本行1NF的列值构成一条新的记录，插入到全局临时表.若某行的非1NF列值有逗号分隔的n项，则该行变成n条记录暂存于全局临时表.当b表所有行都提取、拆分、重构新记录后，全局临时表里的记录就是b表所有列的1NF表示，再对a表和全局临时表作等值内连接，匹配role_id得到所需结果.

3 实现代码

根据以上思路，编写实现代码，为便于调用，把实现代码封装为存储过程.

3.1 首先创建全局临时表usr_type_temp：

createglobaltemporarytableusr_type_temp

拆分非1NF列role_id中的数字：用循环取b表role_id列的每一字符，如果该字符不是逗号，则作字符连接并暂存到变量s中，并继续取出下一个字符；若该字符是逗号，则表示拆分到了一项，即一个单独的role_id.流程图如下：

上述是拆分字符串的传统方法，若一个char类型非1NF列的长度为n,则循环n次，判断n次，全列扫描的时间复杂度较大，代码不够简洁，但这种方法适宜于oracle任何版本.

3.3 使用函数regexp_substr改写

Oracle10g引进了正则表达式函数regexp_su bstr，它扩展了函数substr的功能，能按正则表达式的匹配要求返回字符串的子串.

语法：regexp_substr(srcstr,pattern[,position[,occurrence[,match_option]]])

srcstr：源字符串

pattern：正则表达式

position：搜索的开始位置

occurrence：返回第几个匹配的字符串

match_option：匹配选项

对包体xjpackage_cf提取字符串中数字的for循环改写为使用函数regexp_substr.

此方法效率较高、形式简洁，只能在10g及以上版本使用.

4 改进的设计

上述的代码是在不改变b表结构的基础上实现的，但要满足b表role_id列既要存储多个值，又要方便查询，结合oracle集合的特征改进设计，修改b表role_id列的类型为嵌套表.

创建嵌套表类型t_nested_roleid的语句：createorreplacetypet_nested_roleidastable ofnumber;把b表role_id列的类型改为t_nest ed_roleid.对a、b表连接查询得到期望结果，语句如下：selecta.roleid,a.description,subquery. *fromainnerjoin(selectb.typeid,typename, c.column_valueasrole_idfromb,table(b. roleid)corderbyb.typeid,column_value)subqueryona.roleid=subqueryb.role_id;改进结构后查询极其简单，不需要全局临时表了，由于使用集合缓存了数据，查询效率提高了，但要对前台程序增加、修改记录的语句稍作修改.

[1]杨小艳.Oracle数据库查询优化方法研究[J].计算机与现代化，2008,(4).

[2]冯亚丽.基于表结构及索引的Oracle查询优化研究[J].哈尔滨商业大学学报，2011,(6).

[3]杨俊杰.Oracle数据库设计开发阶段性能优化策略[J].计算机与信息技术,2006,(11).

Researchon Non-1NF Relation Inquiries

XU Jun
(Sichuan Vocational and Technical, Suining Sichuan 629000)

When there are many numbers in the character type column of the oracle table, by inquiring such non-1NF relations multi-table, 1NF result is hoped to meet. This paper explores column values of non-1NF relations, a variety of methods to improve the design and encoding to achieve 1NF forms query results.

Oracle Database; Non-1NF Relations; Inquiries

TP31

A

1672-2094(2013)05-0158-03

责任编辑：张隆辉

2013-04-24

四川省教育厅科研项目《基于混合算法的物流配送路径优化方案研究》（编号：13ZB0031）成果之一。

许俊(1969-)，男，四川蓬溪人，四川职业技术学院计算机科学系副教授，硕士。