Oracle數(shù)據(jù)庫開發(fā)規(guī)范

1、 項(xiàng)目編號(hào)：×××xxxOracle數(shù)據(jù)庫開發(fā)規(guī)范Oracle DB Development Standardization<Language：Version><部門名稱>*年*月*日文檔信息：文檔名稱：文檔編號(hào)：文檔版本日期：起草人：起草日期：復(fù)審人：復(fù)審日期：版本歷史：版本日期作者更改參考說明審批信息：簽字/日期審核審批目錄1概述41.1編寫目的41.2文檔約定41.3預(yù)期的讀者和閱讀建議41.4參考文獻(xiàn)52數(shù)據(jù)庫對(duì)象命名62.1命名總體原則62.2表名62.3視圖62.4同義詞62.5序列72.6索引72.7存儲(chǔ)過程72.8

2、存儲(chǔ)函數(shù)82.9存儲(chǔ)程序包82.10觸發(fā)器82.11字段82.12其他93設(shè)計(jì)規(guī)范93.1范圍93.2表空間93.3字符集103.4主外鍵約束103.5分區(qū)表103.6RAC下的序列設(shè)計(jì)103.7字段103.8表結(jié)構(gòu)設(shè)計(jì)113.9索引設(shè)計(jì)113.10臨時(shí)表114SQL編寫規(guī)范124.1書寫規(guī)范124.2SQL語句的索引使用134.3SQL語句降低系統(tǒng)負(fù)荷155PL/SQL編程規(guī)范185.1書寫規(guī)范185.2常用數(shù)據(jù)庫操作語句編碼規(guī)范195.3常用過程控制結(jié)構(gòu)205.4Condition215.5Cursor225.6變量定義與賦值225.7過程與函數(shù)調(diào)用235.8例外處理 (Exception

3、)235.9例外處理的錯(cuò)誤消息245.10注釋 (Comment)255.11應(yīng)用調(diào)試控制275.12并發(fā)控制275.13代碼測試、維護(hù)291 概述1.1 編寫目的為規(guī)范軟件開發(fā)人員的Oracle數(shù)據(jù)庫開發(fā)提供參考依據(jù)和統(tǒng)一標(biāo)準(zhǔn)。1.2 文檔約定說明本文檔中所用到的專用術(shù)語定義或解釋，縮略詞定義。1.3 預(yù)期的讀者和閱讀建議本文檔適用于所有開發(fā)員。1.4 參考文獻(xiàn) 列出有關(guān)的參考文件，如： a屬于本項(xiàng)目的其他已發(fā)表文件； b本文件中各處引用的文檔資料。 列出這些文件的標(biāo)題、作者，說明能夠得到這些文件資料的來源。2 數(shù)據(jù)庫對(duì)象命名2.1 命名總體原則本規(guī)范所涉及數(shù)據(jù)庫對(duì)象主要是指表、視圖、同義詞

4、、索引、序列、存儲(chǔ)過程、函數(shù)、觸發(fā)器等；命名應(yīng)使用富有意義的英文詞匯，盡量避免使用縮寫，多個(gè)單詞組成的，中間以下劃線分割；避免使用Oracle的保留字或關(guān)鍵字，如LEVEL和TYPE；各表之間相關(guān)列名盡量同名；除數(shù)據(jù)庫模式對(duì)象名稱長度為18個(gè)字符，其余對(duì)象名稱均要求不超過30個(gè)字符；命名只能使用大寫英文字母，數(shù)字和下劃線，且以英文字母開頭。2.2 表名規(guī)則：XXX_MMM_DDDD說明：XXX代表子系統(tǒng)或模塊名稱（2-3個(gè)字母構(gòu)成）；MMM代表子模塊名稱（2-3個(gè)字母構(gòu)成，根據(jù)實(shí)際情況可以沒有）；DDDD為表的簡稱含義，使用英文單詞或詞組構(gòu)成，可包括下劃線，但不得使用漢語拼音。示例：PO_HE

5、ADERS_ALL2.3 視圖規(guī)則：XXX_MMM_DDDD_V說明：XXX代表子系統(tǒng)或模塊名稱（2-3個(gè)字母構(gòu)成）；MMM代表子模塊名稱（2-3個(gè)字母構(gòu)成，根據(jù)實(shí)際情況可以沒有）；DDDD為視圖簡稱含義，使用英文單詞或詞組構(gòu)成，可包括下劃線，但不得使用漢語拼音；V表示視圖，如有多個(gè)功能類似的視圖，名稱以V,V1,V2Vn區(qū)分。示例：PO_HEADER_RELEASE_V2.4 同義詞規(guī)則：與引用的原對(duì)象同名示例：PO_HEADERS_ALL2.5 序列規(guī)則：XXX_MMM_DDDD_S說明：XXX代表子系統(tǒng)或模塊名稱（2-3個(gè)字母構(gòu)成）；MMM代表子模塊名稱（2-3個(gè)字母構(gòu)成，根據(jù)實(shí)際情況可

6、以沒有）；DDDD為序列簡稱含義，使用英文單詞或詞組構(gòu)成，可包括下劃線，但不得使用漢語拼音；S表示序列。示例：PO_HEADERS_S2.6 索引規(guī)則：XXX_MMM_DDDD_Tn說明：XXX代表子系統(tǒng)或模塊名稱（2-3個(gè)字母構(gòu)成）；MMM代表子模塊名稱（2-3個(gè)字母構(gòu)成，根據(jù)實(shí)際情況可以沒有）；DDDD為索引簡稱含義，要求同表名，如總長度超過限制，可適當(dāng)縮寫；T有三種值：U表示唯一性索引，N表示非唯一性索引，B表示位圖索引；n表示序號(hào)，用數(shù)字構(gòu)成。示例：PO_HEADERS_ALL_U12.7 存儲(chǔ)過程規(guī)則：XXX_MMM_DDDD_P說明：XXX代表子系統(tǒng)或模塊名稱（2-3個(gè)字母構(gòu)成）；

7、MMM代表子模塊名稱（2-3個(gè)字母構(gòu)成，根據(jù)實(shí)際情況可以沒有）；DDDD為存儲(chǔ)過程簡稱含義，使用英文單詞或詞組構(gòu)成，可包括下劃線，不得使用漢語拼音；P表示存儲(chǔ)過程。示例：PO_HEADERS_P2.8 存儲(chǔ)函數(shù)規(guī)則：XXX_MMM_DDDD_F說明：XXX代表子系統(tǒng)或模塊名稱（2-3個(gè)字母構(gòu)成）；MMM代表子模塊名稱（2-3個(gè)字母構(gòu)成，根據(jù)實(shí)際情況可以沒有）；DDDD為存儲(chǔ)函數(shù)簡稱含義，使用英文單詞或詞組構(gòu)成，可包括下劃線，不得使用漢語拼音；F表示存儲(chǔ)函數(shù)。示例：PO_HEADERS_F2.9 存儲(chǔ)程序包規(guī)則：XXX_MMM_DDDD_PKG說明：XXX代表子系統(tǒng)或模塊名稱（2-3個(gè)字母構(gòu)成）

8、；MMM代表子模塊名稱（2-3個(gè)字母構(gòu)成，根據(jù)實(shí)際情況可以沒有）；DDDD為存儲(chǔ)程序包簡稱，使用英文單詞或詞組構(gòu)成，可包括下劃線，不得使用漢語拼音；PKG表示存儲(chǔ)程序包。示例：PO_HEADERS_PKG2.10 觸發(fā)器規(guī)則：XXX_MMM_DDDD_Tn說明：XXX代表子系統(tǒng)或模塊名稱（2-3個(gè)字母構(gòu)成）；MMM代表子模塊名稱（2-3個(gè)字母構(gòu)成，根據(jù)實(shí)際情況可以沒有）；DDDD為觸發(fā)器簡稱含義，要求同表名，如總長度超過限制，可適當(dāng)縮寫；T表示觸發(fā)器；n表示序號(hào)，用數(shù)字構(gòu)成。示例：PO_HEADERS_T12.11 字段一般性字段：使用英文單詞、詞組或其有意義的縮寫表示，可包括下劃線，不得使用

9、漢語拼音，多個(gè)類似含義的字段，可在最后加數(shù)字序號(hào)；特殊字段：(1)代表主鍵的字段：表名或縮寫+“_ID”，如PO_HEADER_ID(2)代表外鍵的字段：要求與相關(guān)表對(duì)應(yīng)字段相同(3)記錄跟蹤字段（代表記錄創(chuàng)建人、創(chuàng)建時(shí)間、最后修改人、最后修改時(shí)間）：字段名類型NULL?說明CREATED_BYNUMBERNOT NULL這些字段僅用于跟蹤數(shù)據(jù)的變化，而不能用于其他用途。CREATION_DATEDATENOT NULLLAST_UPDATED_BYNUMBERNOT NULLLAST_UPDATE_DATEDATENOT NULL(4)擴(kuò)展字段（預(yù)留未來使用）： ATTRIBUTE+n，n代

10、表序號(hào)，如ATTRIBUTE1，類型統(tǒng)一為VARCHAR2(150)；對(duì)應(yīng)擴(kuò)展屬性分類字段，命名為ATTRIBUTE_CATEGORY,類型為VARCHAR2(30)。2.12 其他其他對(duì)象參照上述原則和對(duì)象命名。3 設(shè)計(jì)規(guī)范3.1 范圍設(shè)計(jì)規(guī)范主要是對(duì)與開發(fā)關(guān)系密切的數(shù)據(jù)庫整體設(shè)計(jì)部分進(jìn)行說明，以便于DBA更有效的管理和維護(hù)數(shù)據(jù)庫。主要包括表空間、字符集、主外鍵約束、分區(qū)表、字段類型等內(nèi)容。3.2 表空間一致性：無論是基于商品化軟件的開發(fā)，還是獨(dú)立的客制化開發(fā)系統(tǒng)，對(duì)于客制化對(duì)象，每個(gè)子系統(tǒng)或模塊都使用統(tǒng)一的一組表空間（數(shù)據(jù)表空間和索引表空間各一個(gè)，且數(shù)據(jù)表空間為默認(rèn)表空間）；獨(dú)立性：數(shù)據(jù)和

11、索引獨(dú)立存儲(chǔ)，表空間由DBA統(tǒng)一管理和維護(hù)，任何新建表均應(yīng)使用默認(rèn)的數(shù)據(jù)表空間，新建索引均需使用指定的索引表空間；新建表和索引時(shí)，原則上不允許指定除表空間以外的任何參數(shù)，特殊情況必須指定有別于表空間的默認(rèn)參數(shù)時(shí)，需經(jīng)過單獨(dú)評(píng)審。示例：CREATE TABLE TABLE_NAME (FIELD1) TABLESPACE XXX;例外情況：當(dāng)使用分區(qū)表時(shí)，可根據(jù)情況建立獨(dú)立的表空間。3.3 字符集應(yīng)用系統(tǒng)數(shù)據(jù)庫應(yīng)使用UTF8字符集，應(yīng)用程序開發(fā)本身也需要支持UTF8字符集錄入和查詢。注意事項(xiàng)：UTF8字符集默認(rèn)排序規(guī)則非中文拼音，如有需要應(yīng)在代碼級(jí)或數(shù)據(jù)庫會(huì)話級(jí)做特殊處理。3.4 主外鍵約束數(shù)據(jù)

12、庫表對(duì)象原則上禁止使用主外鍵約束，主外鍵所承擔(dān)的主要作用應(yīng)通過其他方式實(shí)現(xiàn)，如主子表數(shù)據(jù)完整性由應(yīng)用層實(shí)現(xiàn)，索引功能通過建立單獨(dú)索引實(shí)現(xiàn)。3.5 分區(qū)表為了降低數(shù)據(jù)庫的維護(hù)復(fù)雜度，非必要情況下，不使用分區(qū)表；或經(jīng)常需要跨分區(qū)查詢數(shù)據(jù)，此時(shí)也不應(yīng)使用分區(qū)表；特殊應(yīng)用場景確有必要時(shí)，可選擇范圍分區(qū)表、列表分區(qū)表、哈希分區(qū)表、組合分區(qū)表幾種方式，并根據(jù)分區(qū)類型和分區(qū)依據(jù)，調(diào)整索引，使相關(guān)數(shù)據(jù)查詢盡量在一個(gè)分區(qū)中完成。3.6 RAC下的序列設(shè)計(jì)當(dāng)數(shù)據(jù)庫部署架構(gòu)為RAC時(shí)，原則上禁止使用序列生成依賴其大小排序的單據(jù)號(hào)碼，如確有必要，則序列必須設(shè)計(jì)為禁用緩存模式。3.7 字段數(shù)值類型使用NUMBER，需要

13、存儲(chǔ)整型數(shù)值時(shí)，可指定小數(shù)位精度為0；枚舉類型優(yōu)先使用數(shù)值型替代字符型，且需要備注說明枚舉類型的各個(gè)不同取值的含義；ID結(jié)尾的字段為應(yīng)為數(shù)值型，CODE結(jié)尾的字段可為字符型或數(shù)值型；用CHAR（1）表示布爾值的取大寫：“Y”，“N”，或改為數(shù)值型，用0和1表示；原則上使用VARCHAR2代替CHAR類型，以節(jié)省存儲(chǔ)空間，但對(duì)性能要求太別高的應(yīng)用場景例外；日期時(shí)間型應(yīng)使用DATE，禁止使用字符型；使用CLOB代替LONG，BLOB代替LONG RAW；一般性字段應(yīng)有含義注釋，對(duì)于可以有缺省值的字段，應(yīng)設(shè)為非空類型，并給出缺省值。3.8 表結(jié)構(gòu)設(shè)計(jì)原則上表結(jié)構(gòu)設(shè)計(jì)應(yīng)符合第三范式，減少冗余字段，但在

14、不影響數(shù)據(jù)一致性的情況下，為了提高性能或開發(fā)實(shí)現(xiàn)的方便性，可以適當(dāng)增加冗余字段。創(chuàng)建表時(shí)，應(yīng)添加相關(guān)注釋，簡要說明主要用途。表與表之間的關(guān)系應(yīng)提供E-R圖。3.9 索引設(shè)計(jì)對(duì)代表唯一記錄的字段或字段組合應(yīng)建立唯一索引，對(duì)于查詢中需要作為查詢條件的字段，可以考慮建立非唯一索引，對(duì)于作為查詢條件的枚舉型字段，可考慮建立位圖索引。建立復(fù)合索引時(shí)，應(yīng)根據(jù)條件字段的查詢頻率決定索引字段順序。開發(fā)過程中新編寫的SQL，應(yīng)對(duì)照查詢條件檢查有無相關(guān)索引，除需要特別指定全表掃描的情況外，原則上應(yīng)建立相關(guān)索引。3.10 臨時(shí)表臨時(shí)表指創(chuàng)建表時(shí)指定臨時(shí)表參數(shù)的數(shù)據(jù)表，并非指表名中含有TEMP字符的表。盡量避免在臨時(shí)

15、表上使用DISTINCT、ORDER BY、GROUP BY、HAVING、JOIN，以減輕臨時(shí)表負(fù)擔(dān)，同時(shí)應(yīng)避免頻繁創(chuàng)建和刪除臨時(shí)表，減少系統(tǒng)表資源的消耗。在新建臨時(shí)表時(shí)，如果一次性插入數(shù)據(jù)量很大，應(yīng)使用SELECT INTO代替CREATE TABLE，避免數(shù)據(jù)庫LOG，提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，可先CREATE TABLE，然后INSERT。如果臨時(shí)表的數(shù)據(jù)量較大，需要建立索引，那么應(yīng)該將創(chuàng)建臨時(shí)表和建立索引的過程放在單獨(dú)一個(gè)子存儲(chǔ)過程中，以保證系統(tǒng)能夠很好的使用到該臨時(shí)表的索引。如果在程序中動(dòng)態(tài)創(chuàng)建了臨時(shí)表，最后務(wù)必將其顯式刪除，并優(yōu)先TRUNCATE TABLE

16、，然后DROP TABLE，以避免系統(tǒng)表的較長時(shí)間鎖定。4 SQL編寫規(guī)范為了提高系統(tǒng)性能，避免未優(yōu)化的SQL語句降低系統(tǒng)性能，要求SQL語句寫法必須考慮以下兩個(gè)方面：a、使用恰當(dāng)?shù)乃饕齜、減少系統(tǒng)負(fù)荷。同時(shí)為保障規(guī)范的落地執(zhí)行，SQL開發(fā)工具統(tǒng)一使用PL/SQL Developer，并使用統(tǒng)一的Beautifier設(shè)置和模板。4.1 書寫規(guī)范4.1.1 大小寫保留字統(tǒng)一使用大寫字符，其他字符除常量值外，一律使用小寫字符。4.1.2 對(duì)齊SELECT、UPDATE、DELETE、INSERT、FROM、WHERE等保留字原則上要獨(dú)立引領(lǐng)一行，保持左對(duì)齊并與其后字符保留空格；WHERE條件中“=

17、”兩邊各保留一個(gè)空格，條件Column保持對(duì)齊。示例：語句名格式SELECTSELECT column1 ,column2 ,column3FROM table_nameWHERE column1 = v_condition1AND column2 = v_condition2GROUP BY column_nameORDER BY column_name;4.1.3 別名當(dāng)一個(gè)SQL 語句中涉及到多個(gè)表時(shí)，應(yīng)始終使用別名來限定字段名，避免含義模糊的引用；別名命名時(shí)，避免使用無意義的代號(hào)a、b 、c ， 而應(yīng)該有意義(如表mtl_system_items_b 對(duì)應(yīng)別名為msi，po_heade

18、rs_all 別名對(duì)應(yīng)為pha)。4.2 SQL語句的索引使用除特殊情況外，原則上SQL語句應(yīng)使用索引，且一般要遵守以下規(guī)則。4.2.1 避免索引列上的函數(shù)如果函數(shù)中使用到索引列字段，則索引是不被利用的，可以通過改變SQL寫法來回避。SUBSTR不使用SELECT e.ename FROM emp e WHERE SUBSTR(e.ename, 1, 1) = 'S'使用SELECT e.ename FROM emp e WHERE e.ename LIKE 'S%'以上假設(shè)emp表的ename建立了索引。TO_CHAR、TO_DATE不使用SELECT e.e

19、name FROM emp e WHERE TO_CHAR(e.hiredate,'YYYY/MM/DD') = '1981/11/17'使用SELECT e.ename FROM emp e WHERE e.hiredate = TO_DATE('1981/11/17','YYYY/MM/DD');以上假設(shè)emp表的hiredate建立了索引。NVL不使用SELECT e.ename FROM emp e WHERE nvl(m,0) < 1000;使用SELECT e.ename FROM emp e WHERE m I

20、S NULL OR m < 1000;以上假設(shè)emp表的comm建立了索引。TRUNC不使用SELECT e.ename FROM emp e WHERE TRUNC(e.hiredate) BETWEEN to_date('2005/01/01','YYYY/MM/DD') AND to_date('2005/01/31','YYYY/MM/DD');使用SELECT e.ename FROM emp e WHERE e.hiredate >= to_date('2005/01/01','YY

21、YY/MM/DD') AND e.hiredate < to_date('2005/01/31','YYYY/MM/DD')+1;以上假設(shè)emp表的hiredate建立了索引。4.2.2 避免索引列上的計(jì)算公式如果索引列上使用了計(jì)算公式，則索引不能使用，可以通過更改計(jì)算公式來避免。不使用SELECT e.ename FROM emp e WHERE e.sal*1.1 > 900;使用SELECT e.ename FROM emp e WHERE e.sal > 900/1.1;以上假設(shè)emp表的sal建立了索引。4.2.3 避免使用n

22、ot in，使用not exists代替使用not in時(shí)，可能導(dǎo)致從表索引無法使用，可以使用not exists來避免。不使用SELECT e.ename FROM emp e WHERE e.deptno NOT IN (SELECT d.deptno FROM dept d);使用SELECT e.ename FROM emp e WHERE e.deptno NOT EXISTS (SELECT 'x' FROM dept d WHERE d.deptno = e.deptno);4.2.4 LIKE的使用LIKE用于模糊檢索，LIKE檢索的樣式有三種：前匹配(XX%)

23、、中間匹配(X%X)、后匹配(%XX)。對(duì)于前匹配可以使用索引，而使用中間匹配和后匹配，都不能使用索引。因此除非必要，否則應(yīng)盡量避免使用中間匹配和后匹配。不使用SELECT e.ename FROM emp e WHERE e.ename LIKE '%B'使用SELECT e.ename FROM emp e WHERE e.ename LIKE 'B%'4.2.5 復(fù)合索引的使用要使用復(fù)合索引，where語句中必須包括復(fù)合索引中的所有列或前幾個(gè)列。如果復(fù)合索引的第一個(gè)列不在where語句中則不能使用該復(fù)合索引。不使用SELECT e.ename FROM e

24、mp e WHERE e.job = 'MANAGER'使用SELECT e.ename FROM emp e WHERE e.deptno = 20 AND e.job = 'MANAGER'使用SELECT e.ename FROM emp e WHERE e.deptno = 20;以上假設(shè)emp表的deptno, job建立了索引。4.2.6 索引提示使用對(duì)于特別的應(yīng)用場景可利用hint固化執(zhí)行計(jì)劃，以使用正確索引。4.3 SQL語句降低系統(tǒng)負(fù)荷4.3.1 編寫可再利用性的SQL語句對(duì)已經(jīng)運(yùn)行過的SQL語句，Oracle會(huì)將它放在SQL緩沖池中，當(dāng)有SQ

25、L需要運(yùn)行時(shí)，Oracle會(huì)先從SQL緩沖池中查詢是否該語句已經(jīng)存在，如存在的話則直接執(zhí)行，否則需要進(jìn)行編譯、解析的操作。因此編寫可再利用的SQL語句可以提高系統(tǒng)性能。為了提高SQL語句的可再利用，必須注意以下兩點(diǎn)：遵守SQL編寫規(guī)范：原則上，對(duì)于不是完全一樣的SQL，oracle不會(huì)去再利用。即使是大小寫不同，多了1個(gè)空格，都被認(rèn)為是不同的SQL語句。所以，要徹底的遵循SQL/PLSQL編程規(guī)范，來保證SQL語句的再利用性。利用變量綁定：當(dāng)需要?jiǎng)討B(tài)組合條件時(shí)，應(yīng)避免直接將變量值組合到條件中去，而應(yīng)該使用變量綁定，從而提高SQL語句的再利用性。4.3.2 使用表別名通過對(duì)表附加別名，SQL編譯

26、時(shí)可以明確列的來源表，從而使得SQL的編譯時(shí)間縮短，另外還可以增加SQL語句的可讀性。4.3.3 ROWNUM的使用ROWNUM可以限制檢索數(shù)據(jù)的數(shù)量，如果為了判斷對(duì)象是否存在，使用ROWNUM=1是非常有效的。檢索是否存在部門20的員工SELECT 'Y' FROM emp e WHERE e.deptno = 20 AND ROWNUM = 1;注意：由于ROWNUM是獲得數(shù)據(jù)的順序號(hào)，在有ORDER BY子句的情況下，ROWNUM列無法按分類有序遞增。4.3.4 UNION和UNION ALL為了排除有UNION重復(fù)的data，系統(tǒng)自動(dòng)進(jìn)行分類處理。如果確定聯(lián)合中不會(huì)出現(xiàn)

27、重復(fù)數(shù)據(jù)的話，必須設(shè)定UNION ALL來取消自動(dòng)分類以提高檢索速度。4.3.5 替代DISTINCT使用DISTINCT將引起內(nèi)部排序處理，如有可能，應(yīng)盡量使用EXISTS、NOT EXISTS或子查詢來避免。使用DISTINCTSELECT DISTINCT d.deptno, d.dname FROM dept d, emp e WHERE d.deptno = e.deptno;使用EXISTSSELECT d.deptno, d.dname FROM dept d WHERE EXISTS (SELECT 'x' FROM emp e WHERE d.deptno =

28、 e.deptno);4.3.6 避免視圖濫用如果查詢只檢索基表的幾個(gè)字段，應(yīng)避免直接使用視圖，造成不必要的數(shù)據(jù)塊檢索。視圖CREATE VIEW CRM_emp_v SELECT e.empno, e.ename, e.sal, d.deptno, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;使用視圖SELECT e.empno, e.ename FROM CRM_emp_v;使用基表SELECT e.empno, e.ename FROM emp;4.3.7 組合查詢語句盡量將幾個(gè)簡單的SQL語句組合成一個(gè)SQL進(jìn)行查詢，可以減少數(shù)

29、據(jù)檢索次數(shù)，提高檢索效率，但會(huì)造成可讀性較差。組合前SELECT e.sal FROM emp e WHERE e.ename = 'Jimmy'SELECT AVG(e.sal) FROM emp e WHERE e.deptno = 20;組合后SELECT e.sal, d.sal_avgFROM (SELECT e1.sal FROM emp e1 WHERE e1.ename = 'Jimmy') e (SELECT AVG(e2.sal) sal_avg FROM emp e2 WHERE e2.deptno = 20 ) d;4.3.8 WHERE

30、條件中不調(diào)用自定義函數(shù)或過程在WHERE條件中調(diào)用自定義函數(shù)或過程極易造成SQL性能嚴(yán)重降低，原則上應(yīng)禁止使用，除非十分有必要且能百分百保證此條件不會(huì)成為查詢驅(qū)動(dòng)條件。4.3.9 避免強(qiáng)制類型轉(zhuǎn)換SQL中的字符類型數(shù)據(jù)應(yīng)該統(tǒng)一使用單引號(hào)，特別對(duì)純數(shù)字的字符串，必須用單引號(hào)，以免導(dǎo)致內(nèi)部轉(zhuǎn)換而引起性能問題或索引失效。SQL中的變量、常量、表關(guān)聯(lián)字段等與對(duì)應(yīng)條件字段應(yīng)保持相同類型或使用顯式轉(zhuǎn)換，避免數(shù)據(jù)庫內(nèi)部的強(qiáng)制轉(zhuǎn)換。5 PL/SQL編程規(guī)范5.1 書寫規(guī)范5.1.1 SQL書寫SQL書寫應(yīng)符合SQL編寫規(guī)范。5.1.2 大小寫程序代碼中要求保留字統(tǒng)一使用大寫字符，其他字符除常量值外，一律使用小

31、寫字符；程序注釋中不做大小寫要求。5.1.3 變量和參數(shù)程序內(nèi)部變量名稱要求以“v_”開頭；輸入?yún)?shù)名稱要求以“p_”開頭；輸出參數(shù)或變量名稱要求以“x_”開頭；游標(biāo)變量要求以“cur_”開頭；游標(biāo)記錄變量要求以“rec_”開頭；與表記錄或字段關(guān)聯(lián)的變量，變量類型統(tǒng)一使用%ROWTYPE或%TYPE，以便保持與相關(guān)表或字段一致。5.2 常用數(shù)據(jù)庫操作語句編碼規(guī)范語句名格式SELECTSELECT column1 ,column2 ,column3INTO variable1 ,variable2 ,varibale3FROM table_nameWHERE Condition1AND Cond

32、ition2GROUP BY column_nameORDER BY column_name;注：SELECT,INTO,WHERE等保留字左對(duì)齊并與其后參數(shù)保留空格。Column盡量與其對(duì)應(yīng)Variable對(duì)齊。語句名格式UPDATEUPDATE table_nameSET column1 = value1 ,column2 = value2WHERE Condition;注：UPDATE,SET,WHERE等保留字左對(duì)齊并與其后參數(shù)保留空格?！?”兩邊各保留一個(gè)空格。Column保持對(duì)齊。語句名格式INSERTINSERT INTO table_name( column1 ,column2

33、 ,column3)VALUES ( value1 ,value2 ,value3);注：“(“緊跟table_name和VALUES，”)”獨(dú)立一行。Column盡量與其對(duì)應(yīng)Variable對(duì)齊。語句名格式DELETEDELETE FROM table_nameWHERE Condition;注：DELETE,WHERE等保留字左對(duì)齊并與其后參數(shù)保留空格?！?”兩邊各保留一個(gè)空格。Condition保持對(duì)齊。5.3 常用過程控制結(jié)構(gòu)結(jié)構(gòu)名格式IF-ELSEIF Condition THEN ELSIF Condition THEN ELSIF Condition THEN ELSE END

34、IF;注：模塊中的縮進(jìn)為4個(gè)空格，盡量不使用IF嵌套。結(jié)構(gòu)名格式LOOPLOOP variable:= variable + 1; IF variable = 10 THEN EXIT; END IF;END LOOP;LOOP variable := variable + 1; EXIT WHEN variable = 10;END LOOP;注：模塊中的縮進(jìn)為4個(gè)空格，"="兩邊保留1個(gè)空格。結(jié)構(gòu)名格式FOR LOOPFOR variable1 IN 12 . 150 LOOP variable1 := variable1 + (variable2 * 10); EXI

35、T WHEN variable1 > 12900;END LOOP;結(jié)構(gòu)名格式WHILE LOOPWHILE variable < value LOOP SELECT variable1, variable2, variable3 INTO value1, value2, value3 FROM table_name WHERE variable4 = value4;END LOOP;5.4 Condition規(guī)范內(nèi)容格式所有復(fù)合條件必須使用括號(hào)來區(qū)分,且AND與OR右對(duì)齊AND ConditionAAND (ConditonB)OR (ConditionC)對(duì)于空值的判斷必須使用

36、IS NULL/ IS NOT NULLIF variable IS NULL THEN END IF;避免使用Hard Code，特別是常數(shù)，必須使用Hard Code的情況，要符合注釋規(guī)范要求錯(cuò)誤：ORG_ID = 90 正確：ORG_ID = FND_PROFILE.VALUE(ORG_ID)5.5 Cursor規(guī)范內(nèi)容格式Cursor必須顯式定義, 不能隱式定義隱式定義：OPEN cur_Meaning IS SELECT << cur_Meaning_Loop >>LOOP FETCH cur_Meaning INTO variable; EXIT WHEN

37、cur_Meaning%NOTFOUND;END LOOP cur_Meaning_Loop; 顯示定義：DECLARE CURSOR cur_Meaning IS SELECT ;BEGIN END;Cursor可以顯式使用, 也可以隱式使用顯式使用：DECLARE CURSOR cur_Meaning IS SELECT ;BEGIN OPEN cur_Meaning; LOOP FETCH cur_Meaning INTO ; EXIT WHEN cur_Meaning%NOTFOUND; END LOOP; CLOSE Cur_Meaning; END;隱式使用：DECLARE CUR

38、SOR Cur_Meaning IS SELECT ;BEGIN FOR variable_rec IN Cur_Meaning LOOP END LOOP; END;5.6 變量定義與賦值規(guī)范內(nèi)容格式變量用":="賦值，":="兩邊保留1個(gè)空格；v_meaning := value不等號(hào)用"<>"表示，"<>"兩邊保留1個(gè)空格；v_variable1 <> variable2用于查詢數(shù)據(jù)庫的變量類型與數(shù)據(jù)庫中對(duì)應(yīng)變量類型相同DECLARE v_meaning tablename

39、.column1%type;BEGIN SELECT column1 INTO v_meaning FROM tablename;END;5.7 過程與函數(shù)調(diào)用規(guī)范內(nèi)容格式調(diào)用Procedure / Function的參數(shù)對(duì)齊CUX_EMPLOYEE_PRC( value1 ,value2 ,value3);建議調(diào)用Procedure / Function的參數(shù)時(shí)使用顯式調(diào)用并對(duì)齊XXX_EMPLOYEESAV_PRC( P_parameter1 => value1 ,P_parameter2 => value2 ,P_parameter3 => value3);5.8 例外

40、處理 (Exception)規(guī)范內(nèi)容格式所有的SELECT INTO SQL都必須有Exception, 必須包括NO_DATA_FOUND, TOO_MANY_ROWS和OTHERS處理; 每一句SELECT語句必須有自己的Exception處理BEGIN SELECT INTO FROM WHERE condition;EXCEPTION WHEN no_data_found THEN WHEN too_many_rows THEN WHEN OTHERS THEN END;對(duì)Cursor的NO_DATA_FOUND例外，必須要有Exception Handle；在處理Exception時(shí)

41、，必須將所用到的Cursor關(guān)閉DECLARECURSOR Cur_Meaning IS SELECT ;BEGIN OPEN Cur_Meaning; FETCH Cur_Meaning INTO ;EXCEPTION WHEN NO_DATA_FOUND THEN IF Cur_Meaning %ISOPEN THEN CLOSE Cur_Meaning; END IF;END;5.9 例外處理的錯(cuò)誤消息不允許在程序中寫死錯(cuò)誤消息或提示信息，而應(yīng)分系統(tǒng)統(tǒng)一定義消息和調(diào)用接口；每個(gè)消息應(yīng)有唯一代碼，程序中僅需通過接口調(diào)用消息代碼；消息內(nèi)容應(yīng)支持變量替代（&代表變量定義），以便靈活顯示

42、消息內(nèi)容。錯(cuò)誤消息定義示例（未考慮多語言）：消息名稱模塊簡稱消息內(nèi)容XXX_NO_DATA_FOUNDXXXNo data found when find &FINDING ! Condition: &CONDITIONXXX_TOO_MANY_ROWSXXXMore than one record found when find &FINDING ! Condition: &CONDITIONXXX_OTHERS_ERRORXXXFetal Error when find &FINDING ! Error: &ERROR錯(cuò)誤消息調(diào)用示例：規(guī)范內(nèi)

43、容格式調(diào)用已定義的錯(cuò)誤消息BEGIN SELECT unit_price INTO v_unit_price FROM oe_order_lines_all WHERE line_id = v_line_id;EXCEPTION WHEN NO_DATA_FOUND THEN xxx_message.set_name('XXX','XXX_NO_DATA_FOUND'); xxx_message.set_token('FINDING','unit_price'); xxx_message.set_token('CONDIT

44、ION','LINE_ID = '|to_char(v_line_id); xxx_message.error; -拋出錯(cuò)誤消息 WHEN TOO_MANY_ROWS THEN xxx_message.set_name('XXX','XXX_TOO_MANY_ROWS'); xxx_message.set_token('FINDING','unit_price'); xxx_message.set_token('CONDITION','LINE_ID = ' |to_char

45、(v_line_id); xxx_message.error; -拋出錯(cuò)誤消息 WHEN OTHERS THEN fnd_message.set_name('CUX','CUX_OTHERS_ERROR'); fnd_message.set_token('FINDING','unit_price'); fnd_message.set_token('ERROR',substr(SQLERRM, 1, 200); fnd_message.error;END;5.10 注釋 (Comment)規(guī)范內(nèi)容格式程序頭部注釋/*

46、= Copyright (C) xxx Application AllRights Reserved =* =* PROGRAM NAME:* * DESCRIPTION: * * HISTORY: * 1.00 mm/dd/yyyy Author Name* Creation Description* 1.01 mm/dd/yyyy Author Name * Changes Description * =*/在每一個(gè)Procedure / Function的開始放置程序注釋, 只在Package Header中即可/* =* FUNCTION / PROCEDURE* NAME £

47、;º* DESCRIPTION: * * ARGUMENT: p_sequences_id IN - 序列標(biāo)識(shí) * * RETURN: x_price OUT 價(jià)格* HISTORY: * 1.00 mm/dd/yyyy Author Name* Creation Description* 1.01 mm/dd/yyyy Author Name * Changes Description * =*/在每一個(gè)變量聲明的旁邊添加注釋DECLAREv_UOM_code table.column%TYPE; - 物料單位v_flag varchar2(1); - Y/N 用于控制是否繼續(xù)IF語句必須加注釋，給出注釋來說明不同條件下不同的處理邏輯IF conditionA THEN ELSIF conditionB THEN - IF conditionA THEN EL