對非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-)，男，四川蓬溪人，四川職業技術學院計算機科學系副教授，碩士。