IDS(OL)_T_STD_ETL編程規(guī)范_ORACLESQL分冊

1、在線經(jīng)營分析系統(tǒng)在線經(jīng)營分析系統(tǒng)ETL編程規(guī)范編程規(guī)范Oracle SQL分冊分冊當(dāng)前版本：當(dāng)前版本： V0.1版本日期：版本日期： 2013年年04月月07日日文檔信息文檔信息項(xiàng)目名稱：項(xiàng)目名稱：在線經(jīng)營分析系統(tǒng)項(xiàng)目項(xiàng)目經(jīng)理：項(xiàng)目經(jīng)理：文檔版本編號：文檔版本編號：0.1項(xiàng)目管理階段：項(xiàng)目管理階段：設(shè)計分析階段文檔版本日期：文檔版本日期：2013-04-07質(zhì)量復(fù)審方法：質(zhì)量復(fù)審方法：項(xiàng)目經(jīng)理和架構(gòu)師復(fù)審起草人起草人：盧衛(wèi)平起草日期起草日期：2013-04-07復(fù)審人：復(fù)審人：復(fù)審日期：復(fù)審日期：分發(fā)名單分發(fā)名單From 自自日期日期部門部門職務(wù)職務(wù)Email / 電話電話 給給 To 部門部

2、門職務(wù)職務(wù)行動行動*截止日期截止日期Email / 電話電話*行動類別：批準(zhǔn)，復(fù)審，通知，存檔，所需行動，參加會議，其它（請指明）版本歷史版本歷史版本編號版本編號 版本日期版本日期修改人修改人描述描述文件名文件名0.12013-04-07盧衛(wèi)平目目 錄錄1 前言.41.1 邊界定義.41.2 原則定義.42格式編排約定格式編排約定.52.1 基本要求.52.2 編寫規(guī)范.52.2.1字段排列要求.52.2.2 字段分割符,點(diǎn)書寫位置要求.52.2.3字段別名 AS 語句編寫要求.52.2.4 SELECT 子句排列要求.52.2.5運(yùn)算符前后間隔要求.62.2.6 CASE 語句的編寫.62.

3、2.7 DECODE 語句：.72.2.8子查詢嵌套編寫規(guī)范.72.2.9表別名定義約定.83語法及數(shù)據(jù)操作約定語法及數(shù)據(jù)操作約定.93.1 JOIN 操作語法要求.93.2 INSERT 語句編寫規(guī)范 .93.3 DELETE 語句編寫規(guī)范 .93.4 NULL 值處理約定.103.4.1 數(shù)字型字段NULL 值的處理.103.4.2 字符型字段NULL 值的處理.103.4.3提取日期型字段的處理.103.5 除法運(yùn)算被 0 除的處理.103.6 注釋約定.114 小技巧小技巧.114.1 如何刪除表中的重復(fù)記錄.124.2 橫轉(zhuǎn)豎表的轉(zhuǎn)換.124.3 窗口函數(shù)與聚合函數(shù)比較.124.4

4、關(guān)于 NOT IN 的使用.134.5 關(guān)于統(tǒng)計信息的收集.134.6 求M行到N行的數(shù)據(jù).134.7 一條記錄根據(jù)條件多表插入.134.8 實(shí)現(xiàn)分組取前 N 條記錄（TOP N 算法）.145 知識點(diǎn)知識點(diǎn).145.1 SQL 連接.145.2 ORACLE SQL 中的 UPDATE 用法.166 存儲過程模板存儲過程模板.171 前言前言為了統(tǒng)一軟件開發(fā)過程中關(guān)于SQL(Structured Query Language)編碼的格式，使編碼人員編寫SQL 代碼遵從特定的風(fēng)格，并養(yǎng)成良好的開發(fā)習(xí)慣，從而增強(qiáng)代碼的可讀性，便于交流和維護(hù)，特此收集、整理已經(jīng)積累的技術(shù)資料，修訂和編制了本編碼規(guī)

5、范。 本規(guī)范適用于已掌握Oracle數(shù)據(jù)庫系統(tǒng)的基本知識及T-SQL 編程技能者閱讀，對從事Oracle數(shù)據(jù)庫系統(tǒng)的代碼開發(fā)、測試、檢查人員起指導(dǎo)的作用，也即對從事Oracle 數(shù)據(jù)庫系統(tǒng)技術(shù)工作人員必不可少的閱讀材料。1.1 邊界定義邊界定義數(shù)據(jù)庫系統(tǒng)的開發(fā)更多的是對數(shù)據(jù)進(jìn)行加載、整理、抽取的工作，在項(xiàng)目實(shí)施過程中經(jīng)常要用到對數(shù)據(jù)進(jìn)行各種形式操作的開發(fā)工作，因此對數(shù)據(jù)操作的代碼編寫進(jìn)行規(guī)范化的約定顯的優(yōu)為重要。本規(guī)范主要用于Oracle 數(shù)據(jù)庫系統(tǒng)的結(jié)構(gòu)化查詢語言代碼編寫的標(biāo)準(zhǔn)化要求，結(jié)合Oracle 的特點(diǎn)以及代碼開發(fā)人員在日常編寫SQL 的DML(Data Manipulation La

6、nguage)經(jīng)驗(yàn)，為規(guī)范Oracle數(shù)據(jù)庫系統(tǒng)的SQL 的DML 開發(fā)建立統(tǒng)一的標(biāo)準(zhǔn)。規(guī)范主要應(yīng)用于Oracle SQL 的DML 以下幾個語句編寫約定Select 語句；Insert 語句；Delete 語句；邏輯判斷語句；類型轉(zhuǎn)換語句；以及相關(guān)的Oracle 函數(shù)；Oracle數(shù)據(jù)庫項(xiàng)目實(shí)施過程中數(shù)據(jù)表現(xiàn)形態(tài)約定等內(nèi)容。在Oracle數(shù)據(jù)庫中不建議使用UPDATE 語句來進(jìn)行數(shù)據(jù)的更新操作，可以采用替代的中間過度表完成表中數(shù)據(jù)的重新組織，從而實(shí)現(xiàn)原表數(shù)據(jù)值的更新。1.2 原則定義原則定義1、要求代碼行清晰、整齊，具有一定的可觀賞性；2、代碼編寫要充分考慮執(zhí)行速度最優(yōu)的原則；3、代碼行整體

7、層次分明、結(jié)構(gòu)化強(qiáng)；4、代碼中應(yīng)有必要的注釋以增強(qiáng)代碼的可讀性；5、規(guī)范要求非強(qiáng)制性約束代碼開發(fā)人員的代碼編寫行為，在實(shí)際應(yīng)用中在不違反常規(guī)要求的前提下允許存在可理解的偏差。 本規(guī)范在對日常的代碼開發(fā)工作起到指導(dǎo)作用的同時也將得到不斷的完善和補(bǔ)充。2格式編排約定格式編排約定2.1 基本要求基本要求1 代碼段中應(yīng)用到的所有Oracle 關(guān)鍵字、保留字都應(yīng)大寫大寫 如 SELECT 、FROM、WHERE、AND、OR、UNION、INSERT、DELETE、GROUP、HAVING、COUNT 等；代碼行中的關(guān)鍵字不允許使用簡寫的方式（如：SEL、DEL）； 1 表名、字段名、字段別名以首字大寫

8、加下劃線連接符來命名，建議使用規(guī)范命名(更完整的命名規(guī)則不在本規(guī)范中闡述)，1 四個空格四個空格為一個縮進(jìn)量，所有的縮進(jìn)皆為一個縮進(jìn)量的整數(shù)位所有的縮進(jìn)皆為一個縮進(jìn)量的整數(shù)位 。1 對應(yīng)的括號通常要求在同一列的位置上；1 每行寬度不超過120120字符字符 (每個字符為8 個點(diǎn)陣寬)，超過行寬的代碼可折行與上行左對齊編排；2.2 編寫規(guī)范編寫規(guī)范2.2.1 字段排列要求字段排列要求示例1： 1 SELECT 語句選擇的字段按每行一個字段方式編排；2 SELECT 單字后面一個縮進(jìn)量一個縮進(jìn)量，后直接跟首個選擇的字段，即字段離首起二個縮二個縮進(jìn)量進(jìn)量； 3 其它字段前導(dǎo)二個縮進(jìn)量再跟一二個縮進(jìn)量

9、再跟一, ,點(diǎn)點(diǎn)后放置字段名；2.2.2 字段分割符字段分割符,點(diǎn)書寫位置要求點(diǎn)書寫位置要求 兩個字段之間的,點(diǎn)分割符緊跟在第二個字段的前面2.2.3 字段別名字段別名 AS 語句編寫要求語句編寫要求 AS語句應(yīng)與相應(yīng)的字段在同一行； 多個字段的AS建議盡量對齊在同一列上； 字段別名在同一個查詢例程中可以被引用2.2.4 SELECT 子句排列要求子句排列要求示例3： SELECT 語句中所用到的FROM、WHERE、GROUP BY、HAVING、ORDER BY、JOIN、UNION 等子句：1 換行編寫；2 與相應(yīng)的SELECT 語句對齊；3 子句后續(xù)的代碼離子句首字母二個縮進(jìn)量二個縮進(jìn)

10、量起編寫；4 WHERE 子句下的邏輯判斷符 AND 、OR 等與WHERE 右對齊編排5 超過兩個縮進(jìn)量長度的子句加一空格后編寫后續(xù)代碼，如：ORDER BY、GROUP BY 等；2.2.5 運(yùn)算符前后間隔要求運(yùn)算符前后間隔要求示例4： 算術(shù)運(yùn)算符、邏輯運(yùn)算符的前后至少要保留一個空格2.2.6 CASE 語句的編寫語句的編寫示例5： SELECT 語句中對字段值進(jìn)行判斷取值的操作將用到的CASE 語句，正確的編排CASE 語句的寫法對加強(qiáng)代碼行的可閱讀性也是很關(guān)鍵的一部分。我們對CASE 語句編排作如下約定：1 CASE 語句從CASE 開頭到END 結(jié)束要用括弧包括起來，并給結(jié)果 值賦別

11、名字段；2 WHEN 子語在CASE 語句的下一行并縮進(jìn)二個縮進(jìn)量二個縮進(jìn)量后編寫；3 每個WHEN 子語一行編寫，當(dāng)然如果語句較長可換行編排；4 CASE 語句必須包含ELSE 子語；2.2.7 DECODE 語句：語句： DECODE語句是ORACLE獨(dú)有的，在封裝程度和使用上皆方便于CASE語句，但是，咱們是需要面對多種的數(shù)據(jù)庫編程的話，最好使用CASE語句，因?yàn)檫@才是T-SQL中的標(biāo)準(zhǔn)語句。2.2.8 子查詢嵌套編寫規(guī)范子查詢嵌套編寫規(guī)范用好子查詢是提高代碼執(zhí)行速度的一個好方法，采用子查詢來縮小結(jié)果集之間的操作將使系統(tǒng)運(yùn)算的開銷更小，當(dāng)然Oracle的優(yōu)化器也會優(yōu)化代碼的處理過程以最優(yōu)

12、的路徑來完成用戶的需求，但我們在編寫代碼時也要考慮性能的優(yōu)化。子查詢嵌套在Oracle 數(shù)據(jù)庫系統(tǒng)開發(fā)中是經(jīng)常要用到的，因此代碼的分層編排就非常重要。示例6： 2.2.9 表別名定義約定表別名定義約定因?yàn)橐坏┰赟ELECT 語句中給操作表定義了別名，那么在整個語句中對此表的引用都必須慣以別名替代，因此我們考慮到編寫代碼的方便性，我們約定別名盡量簡單、簡潔，表別名可以用 a、b、c、a1、a2、sale、buy 等來定義，當(dāng)然要避免使用Oracle中的保留字如：order、partiton 等。 表別名采用簡單字符命名； 多層次的嵌套子查詢別名之前要體現(xiàn)層次關(guān)系； 有需要的情況下對表別名加注釋；

13、示例7： 3語法及數(shù)據(jù)操作約定語法及數(shù)據(jù)操作約定3.1 JOIN 操作語法要求操作語法要求示例8： 多個表JOIN 取值在非INNER JOIN 的情況下大多會取到空值，對這些空值在程序代碼中需要進(jìn)行處理，同時JOIN 操作也要注意被JOIN 表的關(guān)鍵字段值是否唯一，因此對JOIN 語句作如下約定：1 對有可能匹配不上而產(chǎn)生的空值要進(jìn)行轉(zhuǎn)換處理（各種類型數(shù)據(jù)的空值轉(zhuǎn)換見下文描述）2 在此不采納使用 RIGHT JOIN 進(jìn)行表的JOIN 操作，因?yàn)镽IGHT JOIN 完全可以用LEFT JOIN 來實(shí)現(xiàn)，同時RIGHT JOIN 在代碼的邏輯上看起來不太容易理解。3.2 INSERT 語句編

14、寫規(guī)范語句編寫規(guī)范INSERT 語句在異構(gòu)表之前的數(shù)據(jù)拷貝一定要帶字段進(jìn)行值的插入。如下所示：示例9： 3.3 DELETE 語句編寫規(guī)范語句編寫規(guī)范DELETE 語句將會刪除數(shù)據(jù)表的信息，所以對進(jìn)行DELETE 的操作要格外小心，一定要檢查DELETE 限定條件是否正確。通常我們在代碼開發(fā)過程中對數(shù)據(jù)進(jìn)行刪除操作后再進(jìn)行數(shù)據(jù)的插入操作，對這種類型的操作我們在編寫代碼時是將兩個例程作為一個事務(wù)提交以保證整個事務(wù)的完整性。示例10： 3.4 NULL 值處理約定值處理約定我們約定在提取數(shù)據(jù)的結(jié)束集中不允許存在NULL 的值，因此對有可能出現(xiàn)NULL 值的各種類型字段需要進(jìn)行特殊的值轉(zhuǎn)換處理。3.

15、4.1 數(shù)字型字段數(shù)字型字段 NULL 值的處理值的處理 NVL （數(shù)字型字段,0）說明：通過以上函數(shù)處理后，如果字段值為NULL 將返回 0 值。3.4.2 字符型字段字符型字段 NULL 值的處理值的處理NVL(字符型字段,)3.4.3 提取日期型字段的處理提取日期型字段的處理NVL(日期型字段,YYYY-MM-DD HH24:MI:SS)3.5 除法運(yùn)算被除法運(yùn)算被 0 除的處理除的處理為不避免在進(jìn)行除法運(yùn)算時被 0 除出錯，我們需要對除法運(yùn)算有可能出現(xiàn)被 0 除的情況作特殊處理。示例11： 說明：對有可能出現(xiàn)0 的除數(shù)如果是0 值時要把它轉(zhuǎn)換為NULL，任何數(shù)與NULL 相除等于NUL

16、L，因此不會使程序出錯，同時對除法運(yùn)算返回值如果是NULL 時要把它轉(zhuǎn)換為0. 3.6 注釋約定注釋約定 1、該程序開發(fā)的相關(guān)環(huán)境，也就是在程序開始之前的注釋，必須要有。2、開發(fā)程序當(dāng)中每句話都注釋，按照常理是越清楚越好，但是這會牽扯太多的開發(fā)精力，所以適當(dāng)即可。但是程序是可以分成很多模塊的，在每個模塊開始務(wù)必寫清楚該模塊的含義。3、在程序的后面，不管程序運(yùn)行正確與否，盡量都要記錄正確或者錯誤的日志。對于較為復(fù)雜的數(shù)據(jù)操作例程應(yīng)有充分的注釋，注明實(shí)現(xiàn)的功能，業(yè)務(wù)邏輯關(guān)系輸入輸出關(guān)系等內(nèi)容多行注釋可用 /* */ 來標(biāo)識單行注釋可用 -來標(biāo)識示例14： 對于較為復(fù)雜的過程必須注明代碼實(shí)現(xiàn)的功能以

17、及相關(guān)的創(chuàng)建、修改記錄； 主體上有分割的代碼行建議加一空白行以示區(qū)分。4 小技巧小技巧 這個章節(jié)的內(nèi)容其實(shí)就是一些 SQL 的使用技巧，一些疑難的 SQL 問題解決。4.1 如何刪除表中的重復(fù)記錄如何刪除表中的重復(fù)記錄 刪除重復(fù)記錄，是一個很有代表性的技術(shù)問題，有很多種方法，這里的實(shí)現(xiàn)方法采用了rowid 的唯一性的特性。示例如下： DELETE FROM table_name a WHERE rowid ( SELECT min(rowid) FROM table_name b WHERE b.pk_column_1 = a.pk_column_1 AND b.pk_column_2 = a

18、.pk_column_2 );4.2 橫轉(zhuǎn)豎表的轉(zhuǎn)換橫轉(zhuǎn)豎表的轉(zhuǎn)換 橫表和豎表的很不好處理，現(xiàn)在簡單的舉個例子，看看原理： 固定列數(shù)的行列轉(zhuǎn)換固定列數(shù)的行列轉(zhuǎn)換如student subject grade-student1 語文 80student1 數(shù)學(xué) 70student1 英語 60student2 語文 90student2 數(shù)學(xué) 80student2 英語 100轉(zhuǎn)換為 語文 數(shù)學(xué) 英語student1 80 70 60student2 90 80 100語句如下：SELECT student,SUM(DECODE(subject,語文語文, grade,null) 語文語文, SU

19、M(DECODE(subject,數(shù)學(xué)數(shù)學(xué), grade,null) 數(shù)學(xué)數(shù)學(xué), SUM(DECODE(subject,英語英語, grade,null) 英語英語FROM tableGROUP BY student不定列行列轉(zhuǎn)換不定列行列轉(zhuǎn)換這一類型的轉(zhuǎn)換必須借助于 PL/SQL 來完成，實(shí)現(xiàn)起來比較復(fù)雜，不再這里闡述。4.3 窗口函數(shù)與聚合函數(shù)比較窗口函數(shù)與聚合函數(shù)比較 Oracle的窗口函數(shù)比常規(guī)聚合函數(shù)要慢很多，所以今后的SQL 中要盡可能的向外層推窗口函數(shù)，因?yàn)樵较蛲?，結(jié)果集越小，窗口函數(shù)的代價就會降低。4.4 關(guān)于關(guān)于 NOT IN 的使用的使用在 NOT IN 表達(dá)式中，如果 N

20、OT IN 的子查詢記錄的重復(fù)系數(shù)比較高，適于在子查詢中加上 GROUP BY ，顯式過濾掉重復(fù)記錄。4.5 關(guān)于統(tǒng)計信息的收集關(guān)于統(tǒng)計信息的收集1、 統(tǒng)計信息要收集就就盡量收集全面，包括所有可能用在條件中的字段以及連接用的組合字段；2 、統(tǒng)計信息可以灌，只要保證上次的統(tǒng)計信息是好的情況下。盡量避免部分統(tǒng)計信息在不同時間段重灌，一旦需要重灌，建議先將所有現(xiàn)有信息全 DROP 掉，在一個全初始的情況下灌入。3 、統(tǒng)計信息可以手工改寫，但改寫時要格外謹(jǐn)慎，避免造成與實(shí)際情況差距太大。灌入改寫后的信息后，最好對以前查詢的 SQL 進(jìn)行一下 EXPLAIN，以確保不會對其他查詢造成影響。4 、盡管說完

21、全沒有統(tǒng)計信息時，查詢計劃也不會很壞，但要注意統(tǒng)計信息對 INDEX 的影響，因?yàn)橛行?INDEX 在沒有統(tǒng)計信息時是不起作用的。如果只灌入 INDEX 字段的統(tǒng)計信息，需格外小心，用 EXPLAIN 結(jié)果證明不會出現(xiàn)對其他查詢執(zhí)行路徑的影響。5 、看起來對 INDEX 字段的信息收集與對表相應(yīng)字段的信息收集是一樣的，只對表收集或只對 INDEX 收集即可，無需重復(fù)進(jìn)行。4.6 求求 m 行到行到 n 行的數(shù)據(jù)行的數(shù)據(jù) 使用 Oracle8i 開始支持的分析函數(shù) 這種方法取出來的數(shù)據(jù)準(zhǔn)確性相當(dāng)高, 查詢效率比較穩(wěn)定. 建議使用這種方法.SELECT col1,col2,.,colnFROM (

22、 select ROW_NUMBER() OVER(ORDER BY colx desc /asc ) rn, col1,col2,.,coln FROM table_name WHERE .)WHERE rn BETWEEN m AND n 4.7 一條記錄根據(jù)條件多表插入一條記錄根據(jù)條件多表插入9i 以上可以通過 Insert all 語句完成，僅僅是一個語句，如：INSERT ALLWHEN (id=1) THENINTO table_1 (id, name) values(id,name)WHEN (id=2) THENINTO table_2 (id, name) values(id

23、,name)ELSEINTO table_other(id, name) values(id, name)SELECT id,nameFROM a;如果沒有條件的話，則完成每個表的插入，如INSERT ALLINTO table_1 (id, name) values(id,name)INTO table_2 (id, name) values(id,name)INTO table_other (id, name) values(id, name)SELECT id,nameFROM a;4.8 實(shí)現(xiàn)分組取前實(shí)現(xiàn)分組取前 N 條記錄（條記錄（TOP N 算法）算法）TOP N 算法有很多種，而

24、且是數(shù)據(jù)分析中很經(jīng)典的一種分析方法，大致分為兩種：靜態(tài) TOP N、動態(tài) TOP N。我們這里講講簡單的靜態(tài) TOP N 實(shí)現(xiàn)方法：如獲取每個部門薪水前三名的員工或每個班成績前三名的學(xué)生。SELECT * from (SELECT depno,ename,sal, ROW_NUMBER() OVER (PARTITION BY depno ORDER BY sal desc) rn FROM emp) WHERE rn=35 知識點(diǎn)知識點(diǎn)5.1 SQL 連接連接 連接體現(xiàn)了關(guān)系型數(shù)據(jù)庫的靈魂，大家看到連接自然而然的就會想到“=”，無非就是A 表的某個字段等于 B 表的某個字段。其實(shí)連接的含義比

25、這廣泛的多，請看詳細(xì)的闡述。 連接問題分為：內(nèi)連接，外聯(lián)結(jié)，交叉連接連接問題分為：內(nèi)連接，外聯(lián)結(jié)，交叉連接.內(nèi)連接內(nèi)連接 分為 等值連接等值連接，不等值連接不等值連接，自然連接自然連接，等值連接與不等值連接都好理解,自然連接開始有一點(diǎn)費(fèi)解。很簡單就是最基本的連接語句SELECT a.st_id,,b.skill FROM students a , student_skill b WHERE a.st_id = b.st_id等價于SELECT a.st_id,,b.skill FROM students a INNER JOIN student_skill b自然連接的

26、定義自然連接的定義：自然連接自然連接：在連接條件中使用等于(=)運(yùn)算符比較被連接列 的列值，但它使用選擇列表指出查詢結(jié)果集合中所包括的列，并刪除連接表中的重復(fù)列。交叉連接交叉連接(CROSS JOIN)沒有 WHERE 子句，它返回連接表中所有數(shù)據(jù)行的笛卡爾積，其結(jié)果集合中的數(shù)據(jù)行數(shù)等于第一個表中符合查詢條件的數(shù)據(jù)行數(shù)乘以第二個表中符合查詢條件的數(shù)據(jù)行數(shù)。例，titles 表中有 6 類圖書，而 publishers 表中有 8 家出版社，則下列交叉連接檢索到的記錄數(shù)將等于 6*8=48 行。SELECT type,pub_nameFROM titles CROSS JOIN publishe

27、rsORDER BY type現(xiàn)在是連接的重點(diǎn)：外連接現(xiàn)在是連接的重點(diǎn)：外連接 外連接操作與內(nèi)連接操作的區(qū)別：外連接在查詢兩個表時要把某一個表中的數(shù)據(jù)全部列出來，而內(nèi)連接呢，只是把兩個表中符合條件的部分?jǐn)?shù)據(jù)列出來。外連接分為左外連接，右外連接，全外連接（外連接分為左外連接，右外連接，全外連接（Oracle 不支持）不支持）外連接的操作關(guān)鍵字 left outer join , right outer join , 在 Oracle 中一般用(+)表示,根據(jù)放置位置不同來確定是內(nèi)連接還是外連接，就像內(nèi)連接用“=”,等來表示一樣。SELECT a.st_id, ,b.skill FROM students a, student_skill b WHERE a.st_id = b.st_id(+);可以這么理解(+)放在哪邊,就不會以這個表為主,如果沒有相應(yīng)的數(shù)據(jù)以空補(bǔ)齊對