資料庫程式設計注意事項

--說明:本文大多引用網上資料,版權由原作者擁有

一 常用資料索引及論壇

oracle中國使用者組

常用技巧集

入門問題集

各版精華區

(資料庫管理)

(深入討論區)

(入門技術區)

軟體下載區

二 常用工具軟體及介紹

建模工具 PowerDesigner9.5

目前所有資料庫設計文件是word格式，對文件的版本控制及資料庫同步都很不方便，有時修改了資料庫，在文件上就很難同步，大部分的建模工具都可以滿足文件與資料庫同步等任務，希望大家能夠喜歡用PowerDesigner來進行資料庫設計，設計的圖表更便於交流及與其它成員共享。

下載地址:

最佳化工具 Lecco sqlexpert for oracle3.2

Lecco與toad、pl/sql developer相比，lecco更專業，可以格式化寫得較亂的SQL，也可以最佳化SQL併產生帶有hits的SQL，用lecco來編寫pl/sql程式碼，除錯等都很方便，是一款不得多得的oracle開發工具。

下載址址:

（好象收費啦）

三 SQL或PL/SQL書寫規範

1 語句中出現的所有表名、欄位名全部小寫，系統保留字、內建函式名、Sql保留

字大寫，連線符or、in、and、以及＝、<=、>=等前後加上一個空格。

2 對較為複雜的sql語句加上註釋，說明演算法、功能，註釋風格：註釋單獨成行。

(1)應對不易理解的分支條件表示式加註釋；

(2)對重要的計算應說明其功能；

(3)過長的函式實現，應將其語句按實現的功能分段加以概括性說明；

(4)每條SQL語句均應有註釋說明（表名、欄位名）。

(5)常量及變數註釋時，應註釋被儲存值的含義(必須)，合法取值的範圍(可選)

3 SQL語句的縮排風格

(1)一行有多列，超過80個字元時，基於列對齊原則，採用下行縮排

(2)where子句書寫時，每個條件佔一行，語句令起一行時，以保留字或者連線符開

始，連線符右對齊。

4 多表連線時，使用表的別名來引用列。

5 供別的檔案或函式呼叫的函式，絕不應使用全域性變數交換資料。

6 變數令名不能超出ORACLE的限制（30個字元），令名要規範，要用英文令名，從變

量上能看到變數的作用，如

g名稱 全域性變數 m名稱 區域性變數

c名稱 游標 p名稱 引數

7 查詢資料庫表或檢視時，只能取出確實需要的那些欄位，不要使用*來代替所有列名。要清楚明白地使用列名，而不能使用列的序號。

8 功能相似的過程和函式，儘量寫到同一個包中，加強管理。

9 儘量不要使用檢視、臨時表、自定義函式、自定義過程之類的物件進行輔助算運，儘量用一條SQL實現要求。

10 在寫查詢條件時注意使用索引，注意複合索引組成欄位的順序，如果沒有索引，請及時與DBA聯絡，注意where條件中的欄位順序應該以表中的欄位順序為準，儘量有優於後期的資料庫最佳化。

11 在查詢語句中查詢表示式左邊不允許出現函式及其它運算表示式，所有左邊的表示式都可以用其它的方法在右邊實現

12 所有寫好的SQL最好能在Lecco sqlexpert上進行格式化並進行最佳化，注意執行計劃及執行時間，如果有問題請及時與DBA聯絡。

13 SQL書寫最佳化效能建議

(1) 避免巢狀連線、子查詢（多級） 如：A = B and B = C and C = D

(2) where條件中儘量減少使用常量比較，改用Bind Variable變數

(3) 大量的排序操作影響系統效能，所以儘量減少order by和group by排序操作。

14 必須使用排序操作，請遵循如下規則：

(1) 排序儘量建立在有索引的列上。

(2) 如結果集不需唯一，使用union all代替union。

15 關於索引的使用

(1) 儘量避免對索引列進行計算，如對索引列計算較多，請系統管理員建立函式索引。

(2) 儘量注意比較值與索引列資料型別的一致性，不要發生隱性轉換

(3) 對於複合索引，SQL語句必須使用主索引列

(4) 索引中，儘量避免使用NULL。

(5) 對於索引的比較，儘量避免使用NOT=（!=）

(6) 查詢列和排序列與索引列次序保持一致

16 儘量避免相同語句由於書寫格式的不同，而導致多次語法分析，儘量使用Bind變數。

17 查詢的WHERE過濾原則，應使過濾記錄數最多的條件放在最前面。

18任何對列的操作都將導致表掃描，它包括資料庫函式、計算表示式等等，查詢時要儘可能將操作移至等號右邊。

19 in、or子句常會使用工作表，使索引失效；如果不產生大量重複值，可以考慮把子句拆開；拆開的子句中應該包含索引。

20 其他經驗性規則

(1) 儘量少用巢狀查詢 如必須，請用not exist代替not in子句。如例（2）

(2) 用多表連線代替EXISTS子句。如例（3）

(3) 少用DISTINCT，用EXISTS代替 如例（4）

(4) 使用UNION ALL、MINUS、INTERSECT提高效能

(5) 使用ROWID提高檢索速度

(6) 使用最佳化索引機制進行訪問路徑控制,使用cursor時，顯示游標優於隱式游標

本規範示例：

例一：

SELECT aka042 -- 單位繳費劃入個人帳戶比例

INTO prm_aaa043

FROM ka01 --醫療保險單位繳費劃入個人帳戶比例分段資訊

WHERE akc021 = rec_kc01.akc021 -- 醫療人員類別

AND aka041 >= rec_kc01.akc023 -- 年齡上限

AND aka040 <= rec_kc01.akc023 -- 年齡下限

AND aae030 <= prm_date -- 開始時間

AND ( aae031 >= prm_date OR aae031 IS NULL ); -- 終止時間

例二：

SELECT ......

FROM emp

WHERE dept_no NOT IN ( SELECT dept_no

FROM dept

WHERE dept_cat='A');

SELECT ......

FROM emp e

WHERE NOT EXISTS ( SELECT 'X'

FROM dept

WHERE dept_no=e.dept_no

AND dept_cat='A');

例三：

SELECT ......

FROM emp

WHERE EXISTS ( SELECT 'X'

FROM dept

WHERE dept_no=e.dept_no

AND dept_cat='A');

SELECT ......

FROM emp e,dept d

WHERE e.dept_no=d.dept_no

AND dept_cat='A';

例四：

SELECT DISTINCT d.dept_code,d.dept_name

FROM dept d ,emp e

WHERE e.dept_code=d.dept_code;

SELECT dept_code,dept_name

FROM dept d

WHERE EXISTS ( SELECT 'X'

FROM emp e

WHERE e.dept_code=d.dept_code);

過程註釋：

過程都以sp_開頭，注意過程名稱要符合令名要求

/**************************************************************************

name:sp_Write_log

parameter:p_textContext in varchar2 引數描述

create date:2003-04-1

creater:chen jiping

desc:過程總功能描述

****************************************************************************/

函式註釋

函式以f開頭，令名符合令名標準

/**************************************************************************

name:f_Get_JobId

parameter:p_Name in varchar2 引數描述

return number:返回值描述

create date:2003-04-1

creater:chen jiping

desc:函式總功能描述

****************************************************************************/

四 資料庫設計規範[csdn 大力]

1資料庫設計原則

(1) 程式有關的狀態資訊使用域進行限制和定義，不允許終端使用者修改定義

(2) 使用者可修改的狀態列表資訊，用資料表來定義，一經建立後，使用者只可修改名稱，不能修改程式碼，修改後的名稱與原名稱應代表相同的意義。

(3) 使用者採用列表錄入的文字資訊，在一個統一的‘系統資訊’表中定義，使用者可任意建立和修改。用此種方法錄入的資訊，錄入的資訊與選擇列表不存在約束關係，列表只是作為文字錄入的一種輔助手段。

(4) 輔助編碼表應至少有兩列—程式碼和名稱。只有在名稱非常穩定的情況下才可以不使用程式碼。使用編碼表的好處是在應用程式介面中，不同的地方出現同一種資料的錄入（如錄入資料或查詢過濾條件）時只需定義一次選擇列表，不會出現兩處衝突的現象，方便程式設計。

(5) 所有記錄業務資料的表中都設有‘錄入員’和‘錄入日期’列，由系統自動記錄。

(6) 記錄業務資料的表中，根據需要設定‘過程’和‘狀態’列，‘過程’列用一位數字或字元記錄如錄入 稽核 記賬等過程狀態；‘狀態’列用一位數字或字元記錄如正常 作廢 刪除等狀態。所有表的‘過程’狀態的改變日期和操作人由一單獨表來記錄，表中記錄狀態改變所涉及的表標識 記錄標識 操作人和操作日期。

(7) 基本編碼表中應設定‘編碼’ ‘縮語’ ‘名稱’，‘編碼’作為主鍵與其它表的外來鍵形成對應關係，完成基於主-外來鍵的完整性約束。

(8) 記錄錄入單據的表中設定‘自動單據號’，由一字元開始以區分單據型別，後跟一數字序列表示序號。‘自動單據號’由系統自動生成，作為主表的主鍵，不允許使用者修改，並作為連線主表和明細表的外來鍵,明細表中設定序號列，用於同一單據中排列各行的序號。

(9) 用於記錄業務資料的資料表與用於報表的資料表分別存於不同的資料庫中，分別命名為‘業務資料庫’和‘決策資料庫’。‘業務資料庫’獨立於‘決策資料庫’進行設計，‘決策資料庫’中的資料來源於‘業務資料庫’。

(10) 任意業務流程中應考慮建立 稽核 記賬 修改 刪除 衝紅 結存（月結 年結）的處理

及滿足的條件。

(11) 對於主-明細表結構，設計對應的檢視將兩表連線用於查詢。

(12) 禁止直接用編碼或名稱列表（in）作為固定的過濾條件對資料進行查詢。應將包含此過濾條件

的查詢做成特定的檢視；或在編碼表中加入一屬性欄位用於分類。

(13) 對於業務資料表應設定一個‘存檔標記列’，對於新增行或修改行設定該標誌，以便將業務資料

庫中的資料向決策資料庫複製時用以選擇需更新的資料。當採用資料庫複製技術時，也希望設

定此標誌，以備用。此標誌的設定可以由應用程式或觸發器來執行。

(14) 決策資料庫中應複製基本編碼表和輔助編碼表，以保持決策資料庫資料的完整性。

(15) 同時採用多種分類方法：如果某一實體有多於一種的分類方法，或實體中的每個例項所屬的類

的數量是不定的，這時實體表和分類表之間的關聯是多對多的，即一個實體屬於一個分類而同

時也屬於其它的分類。對於具有可多選的分類標誌的表，與分類名稱列表之間存在多對多的關

系，須按多對多的關係來處理。

--實體表-- --實體-分類表-- --分類表—

例項1 例項 分類 分類1->類別1.1

例項2 1對N ... 1對N 分類1->類別1.2

例項3 ... 分類2->類別2.1

(16) 業務過程的記錄：如果一個業務表（或主表+明細表）依賴另外一個業務表的資料，或在流程

上一個業務表根據另一個業務表生成，則該業務表中應有一欄位記錄另一業務表對應記錄的

標識。這樣的結構使得在流程上的所有資料形成一個鏈。他有兩個作用：其一，可以追述某

一過程進行到哪一步；其二，對於流程的回退有用，當流程需要回退到某一步時，將該步驟

生成的記錄作廢，同時作廢由此記錄生成的所有後續記錄，即可以回退到流程的任意一點。

由於資料的依賴關係，回退時實際上是從後向前作廢記錄，直到希望回退到的位置為止。

一個業務流程應作為一個事務（在資料庫管理系統中的定義），要保證這一事務的完整性。

首先一個事務要有一個標識，在這個事務進行過程中所產生的一切資料都應打上事務標識作

為標記。可以‘回滾’整個事務，或‘回滾’（從後向前）到事務進行的某一點上，然後再繼

續進行直到完成事務。

(17) 分類欄位的冗餘：業務資料表往往關聯許多的基本資訊表，這些基本資訊表中可能含有一些

分類欄位，而業務資料表的分類統計可能引用這些欄位。如果這些分類欄位的內容會改變，

而又不希望這種改變影響業務資料的統計結果，則在這些業務資料表中應包含這些分類欄位，用以記錄業務發生時該欄位的內容。

(18) 類別和狀態的多選：多選分為必選（1..n）和可選（0..n）。如是必選，在設計時要有說

明，在程式實現中應有控制和檢查。兩個可選的類別或狀態表可以合併為一個表，再與引用此表的主表形成多對多的關係。

(19) 財務記賬的處理：由憑證生成各種帳目（總帳 明細帳 日記帳 部門帳 往來帳 專案帳以

及備查帳）時，有兩種處理方法：一種是各賬本不生成表，需要時臨時生成，在月結時一次生

成月結帳；另一方法是生成表，當對憑證進行修改時，先做刪除處理（可用資料庫的級聯刪除

功能），再補新憑證然後記帳，或，先做作廢處理（各賬目中與憑證相關的記錄由憑證號關聯），

再補新憑證然後記帳。當各賬本與憑證之間由憑證號關聯時，可進行關聯查詢。

2 命名原則

(1) 表和檢視命名原則

系統資訊表：TS_

基本編碼表：TB_

決策資料表：TD_

業務資料表：

不同的業務以不同的縮寫（2-3個字元）開始，如‘TCRM_’；

如是輔助編碼表則在代表業務的縮寫後加A_，如‘TCRM_A_’,

關聯資料表：TRE__表A_表B

檢視：在表命名的基礎上加字首改為V_

字尾：主-明細結構的表，主表為 _M；明細表為 _L

(2) 列命名規則
第一個字母小寫，後面的單詞或拼音，採用第一個字母大寫，命名規則只來自於業務，儘量

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-84790/，如需轉載，請註明出處，否則將追究法律責任。