對(duì)非1NF關(guān)系查詢的探討

許俊

(四川職業(yè)技術(shù)學(xué)院，四川遂寧 629000)

對(duì)非1NF關(guān)系查詢的探討

許俊

(四川職業(yè)技術(shù)學(xué)院，四川遂寧 629000)

當(dāng)oracle表字符類型的列中存放多個(gè)值時(shí)，對(duì)這種非1NF關(guān)系的多表查詢，希望得到滿足1NF的結(jié)果，本文探討了拆分非1NF的列值，及改進(jìn)設(shè)計(jì)的多種方法，并編碼實(shí)現(xiàn)得到1NF形式的查詢結(jié)果.

oracle數(shù)據(jù)庫(kù)；非1NF關(guān)系；查詢

1 問(wèn)題提出

某項(xiàng)目采用oracle11g作為后臺(tái)數(shù)據(jù)庫(kù)，其中a、b二個(gè)表的設(shè)計(jì)如下.

b表結(jié)構(gòu)：

提供的測(cè)試數(shù)據(jù)如下：

a表數(shù)據(jù)：

b表數(shù)據(jù)：

查詢a表和b表，期望得到如下所示的查詢結(jié)果：

即打散b表role_id列值，把逗號(hào)分隔的每一項(xiàng)單獨(dú)與a表內(nèi)連接得到查詢結(jié)果的一行.

2 表的分析

由于b表role_id存放多個(gè)值，當(dāng)它作為一個(gè)整體與a表role_id單值作連接時(shí)，是不會(huì)等值匹配，必須打散拆分b表role_id列值，使列值中逗號(hào)分隔的每個(gè)數(shù)字成為若干個(gè)單一值，以此單一值匹配a表的role_id作等值內(nèi)連接，得到查詢結(jié)果記錄集.

具體思路：使用游標(biāo)提取b表每一行，拆分非1NF的列值，拆分出每一項(xiàng)role_id與本行1NF的列值構(gòu)成一條新的記錄，插入到全局臨時(shí)表.若某行的非1NF列值有逗號(hào)分隔的n項(xiàng)，則該行變成n條記錄暫存于全局臨時(shí)表.當(dāng)b表所有行都提取、拆分、重構(gòu)新記錄后，全局臨時(shí)表里的記錄就是b表所有列的1NF表示，再對(duì)a表和全局臨時(shí)表作等值內(nèi)連接，匹配role_id得到所需結(jié)果.

3 實(shí)現(xiàn)代碼

根據(jù)以上思路，編寫(xiě)實(shí)現(xiàn)代碼，為便于調(diào)用，把實(shí)現(xiàn)代碼封裝為存儲(chǔ)過(guò)程.

3.1 首先創(chuàng)建全局臨時(shí)表usr_type_temp：

createglobaltemporarytableusr_type_temp

拆分非1NF列role_id中的數(shù)字：用循環(huán)取b表role_id列的每一字符，如果該字符不是逗號(hào)，則作字符連接并暫存到變量s中，并繼續(xù)取出下一個(gè)字符；若該字符是逗號(hào)，則表示拆分到了一項(xiàng)，即一個(gè)單獨(dú)的role_id.流程圖如下：

上述是拆分字符串的傳統(tǒng)方法，若一個(gè)char類型非1NF列的長(zhǎng)度為n,則循環(huán)n次，判斷n次，全列掃描的時(shí)間復(fù)雜度較大，代碼不夠簡(jiǎn)潔，但這種方法適宜于oracle任何版本.

3.3 使用函數(shù)regexp_substr改寫(xiě)

Oracle10g引進(jìn)了正則表達(dá)式函數(shù)regexp_su bstr，它擴(kuò)展了函數(shù)substr的功能，能按正則表達(dá)式的匹配要求返回字符串的子串.

語(yǔ)法：regexp_substr(srcstr,pattern[,position[,occurrence[,match_option]]])

srcstr：源字符串

pattern：正則表達(dá)式

position：搜索的開(kāi)始位置

occurrence：返回第幾個(gè)匹配的字符串

match_option：匹配選項(xiàng)

對(duì)包體xjpackage_cf提取字符串中數(shù)字的for循環(huán)改寫(xiě)為使用函數(shù)regexp_substr.

此方法效率較高、形式簡(jiǎn)潔，只能在10g及以上版本使用.

4 改進(jìn)的設(shè)計(jì)

上述的代碼是在不改變b表結(jié)構(gòu)的基礎(chǔ)上實(shí)現(xiàn)的，但要滿足b表role_id列既要存儲(chǔ)多個(gè)值，又要方便查詢，結(jié)合oracle集合的特征改進(jìn)設(shè)計(jì)，修改b表role_id列的類型為嵌套表.

創(chuàng)建嵌套表類型t_nested_roleid的語(yǔ)句：createorreplacetypet_nested_roleidastable ofnumber;把b表role_id列的類型改為t_nest ed_roleid.對(duì)a、b表連接查詢得到期望結(jié)果，語(yǔ)句如下：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;改進(jìn)結(jié)構(gòu)后查詢極其簡(jiǎn)單，不需要全局臨時(shí)表了，由于使用集合緩存了數(shù)據(jù)，查詢效率提高了，但要對(duì)前臺(tái)程序增加、修改記錄的語(yǔ)句稍作修改.

[1]楊小艷.Oracle數(shù)據(jù)庫(kù)查詢優(yōu)化方法研究[J].計(jì)算機(jī)與現(xiàn)代化，2008,(4).

[2]馮亞麗.基于表結(jié)構(gòu)及索引的Oracle查詢優(yōu)化研究[J].哈爾濱商業(yè)大學(xué)學(xué)報(bào)，2011,(6).

[3]楊俊杰.Oracle數(shù)據(jù)庫(kù)設(shè)計(jì)開(kāi)發(fā)階段性能優(yōu)化策略[J].計(jì)算機(jī)與信息技術(shù),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

責(zé)任編輯：張隆輝

2013-04-24

四川省教育廳科研項(xiàng)目《基于混合算法的物流配送路徑優(yōu)化方案研究》（編號(hào)：13ZB0031）成果之一。

許俊(1969-)，男，四川蓬溪人，四川職業(yè)技術(shù)學(xué)院計(jì)算機(jī)科學(xué)系副教授，碩士。