大話資料庫程式設計規範

節選自《劍破冰山——oracle開發藝術》一書，書籍銷售地址：

1.1             

目前在軟體圈內有這麼一個現象，就是：DBA不太懂寫PL/SQL，而開發人員寫的又是五花八門，而且效率不高。如此以來，造成諸多弊端：

1.可讀性差。讀別人寫的一個程式花費的時間，比自己寫一個程式的花費時間還要長；非但別人看不懂，時間久了連自己也看不懂了。

2.可維護性差。程式越寫越長，越改越爛，像懶婆娘的裹腳布，又臭又長。

3.可移植性差。今天用oracle寫一套，明天換成SQL Server的時候再寫一套，眾多的資料庫開發人員在程式的苦海中重複著低階勞動……

4.效率和效能差。一個儲存過程或SQL執行效率簡直可以讓你感覺到對時間的絕望，你很快就理解什麼是相對論了。

1.2             

    事實上為了統一軟體開發過程中關於資料庫設計時的命名規範和程式設計規範，正規一些的IT公司都會制定一些關於資料庫物件的命名和程式設計規範。

否則的話，你寫你的我寫我的，各自為戰不相容，彼此看不懂，甚至到最後連自己都弄不明白了，這樣的經歷相信每個人都曾經遇到過。

例如下面這段簡單的程式碼，您看的明白嗎？就算暫時明白，過個一年半載您還明白嗎？就算您記憶超群，想必也會忘記，不是麼？

A:=1;b:=2;SeLeCT username from EmPLOyee where id=a aNd Type=b;

如果你改為下面這樣的寫法，相信稍微懂點資料庫的人都應該看的明白不是？

vID:=1;   --A為ID

vType=2;  --B為型別

SELECT username FROM employee WHERE id=vID AND type=vType;

1.3             

醜陋的書寫規範不僅可讀性較差，而且給人以敬而遠之的感覺，就是您是大俠也不行啊；而良好的書寫規範則給人以享受和藝術的體驗。

大小寫風格

規則1.3.1.1

所有資料庫關鍵字和保留字均使用大寫；關於欄位、變數的大小寫風格在1.4詳細介紹。

縮排風格

規則 1.3.2.1

程式塊嚴格採用縮排風格書寫，保證程式碼清晰易讀，風格一致，縮排格數統一為2 /4個。

必須使用空格，不允許使用TAB 鍵。以免用不同的編輯器閱讀程式時，因TAB 鍵所設定的空格數目不同而造成程式佈局不整齊。

規則1.3.2.2

同一條語句需要佔用多於一行時，每行的其它關鍵字與第一行的關鍵字進行右對齊。

IF flag=1 THEN

  SELECT username             --同上一行相比縮排4個空格

    INTO vuserinfo            --INTO與SELECT進行右對齊

    FROM userinfo             --FROM與SELECT進行右對齊

   WHERE userid=:iuserid;     --WHERE與SELECT進行右對齊

END IF;

空格及換行

規則1.3.3.1

不允許把多個語句寫在一行中，即一行只寫一條語句。

規則1.3.3.2

避免將複雜的SQL 語句寫到同一行，建議要在關鍵字和謂詞處換行。

規則1.3.3.3

相對獨立的程式塊之間必須加空行。

BEGIN、END 獨立成行

規則1.3.3.4

太長的表示式應在低優先順序運算子處換行，運算子或關鍵字放在新行之首。劃分出新行應當適當地縮排，使排版整齊，語句可讀。

不同型別的運算子混合使用時，建議使用括號進行隔離，以使程式碼清晰。

規則1.3.3.5

減少控制語句的檢查次數，如在 IF…ELSE控制語句中，對最常用符合條件，儘量前置以被檢查到。

DECLARE

  --定義區域性變數

  vFlag VARCHAR2(10);  --判斷標誌

  …

BEGIN  

 

  IF ((a=b AND a=c AND a=d) OR  --在OR處斷行，可使得邏輯更為清晰

      (a=e AND e=f)) THEN

--Process something

 

  IF vFlag=1 THEN      --vFlag=1為經常出現之條件，可有效減少判斷檢查次數

--Process something

  ELSIF vFlag=2 THEN  --vFlag=2為次之出現的條件

--Process something

  ELSE

   --Process something

  END IF;

 

 

其它

規則1.3.4.1

避免使用SELECT * 語句；不要用*來代替所有欄位，應給出欄位列表，以避免表結構發生變化時應用程式出現無法識別的情況。

規則1.3.4.2

INSERT語句必須給出欄位列表，以避免表結構發生變化時發生編譯錯誤。

規則1.3.4.3

當一個PL/SQL 或SQL 語句中涉及到多個表時，始終使用別名來限定表名和欄位名，這使其它人閱讀起來更方便，避免了含義模糊的引用，並能夠別名中清晰地判斷出表名和相關欄位名。

規則1.3.4.4

確保變數和引數在型別和長度與表資料列型別和長度相匹配。說明：如果與表資料列寬度不匹配，則當較寬或較大的資料傳進來時會產生執行異常。

DECLARE

  --定義相關表欄位變數

  vDeptNo      salary.Deptno%type;     --not VARCHAR2(10)，以適應變化

  vEmployeeNo  salary.EmployeeNo%type; --not VARCHAR2(10)，以適應變化

  vSalary      salary.Salary%type;     --not NUMBER，以適應變化

 

BEGIN

  --Process something

END;

1.4             

    一千個讀者就有一千個哈姆雷特，對於命名規範來說，想做到完全統一的確是不可能的任務。命名規範更多的是個人層面的愛好，就算有命名規範，也不過是體現制訂規範的相關人的愛好而已。

因此即使無法完全做到一致，但是我們仍然要儘量去遵守，必要的時候需要透過程式碼檢查和專家評審來進行約束，因為一個不成熟的規範總會勝過沒有規範。

表和欄位命名規範

在此僅提供幾種常見的命名方法(表和欄位的命名方式雷同)。

以使用者許可權欄位/表為例：

UserPrivilege	適合那些英文比較好，並且喜歡抑揚頓挫和有藝術美感的人。
userprivilege	適合那些英文好，且比較嚴謹的人，畢竟全部小寫很容易與資料庫關鍵字相區別。
tbl_user_privilege	適合那些做開發的人，開發的人會習慣性的給變數加字首。 (這裡指表的命名，欄位一般很少加字首)
yhqx	熱愛中文的人，前提是恐怕您得對這些縮寫先做好相關備註，等大家習慣了才行。

實際上這幾種命名規範各有千秋，很難去指責或否定哪種更好，完全取決於整個公司多數人的習慣，記住沒有十全十美的命名規範，只有絕大多數人心甘情願的去遵從了，那就是好的命名規範。

就我個人而言，我更偏向於第一種命名習慣。

規則1.4.1.1

不建議使用資料庫關鍵字和保留字（不建議並不意味著不能使用），只是為了避免不必要的衝突和麻煩；

例如name,id,level,remark,description等等。

有興趣的話，大家可以參考下SELECT * FROM v$reserved_words WHERE reserved='Y'

實際上oracle不建議大家使用v$reserved_words表中所有的關鍵字，無奈這些關鍵字太多了；reserved='Y'的關鍵字則是被完全禁止的。

規則1.4.1.2

嚴禁使用帶空格的名稱來對欄位和表命名；在產生資料庫指令碼並重新載入的時候可能會出現意想不到的錯誤而被迫終止。

其它物件命名

使用者自定義的資料庫物件名包括表、檢視、主外來鍵、索引、觸發器、函式、儲存過程、序列、同義詞、資料庫連結、包和包體等等。

規則1.4.2.1

其它物件的命名也與表和欄位的命名規則類似，風格保持一致即可

規則1.4.2.2

除資料庫名稱長度為1－8個字元，其餘為1－30個字元，database link名稱也不要超過30個字元；

命名只能使用英文字母，數字和下劃線

規則1.4.2.3

除表外，其它各種物件的命名最好用不同的字首加以區別。採用字首的方式來命名物件則很容易透過排序對物件進行區別。

如在命名規範中各組成部分以_分割，則字首建議也以_分割；反之則可加可不加

物件名	字首	範例
表(table)	tbl_/t_ (或不加字首）	userinfo/t_user_info/ tbl_user_info
檢視(view)	v_/v	v_user_info/vuserinfo
序列(sequence)	seq_	seq_user_info
簇(cluster)	c_	c_user_info
觸發器(trigger)	trg_	trg_user_info
儲存過程(procedure)	sp_/p_	sp_user_info/p_user_info
函式(function)	f_/fn_	fn_user_info/f_user_info
物化檢視(materialized view)	mv_	mv_user_info
包和包體(package & package body)	pkg_	pkg_user_info
類和類體(type & type body)	typ_	typ_user_info
主鍵(primary key)	pk_	pk_user_info
外來鍵(foreign key)	fk_	fk_user_info_fieldname
唯一索引(unique index)	uk_	uk_user_info_fieldname
普通索引(normal index)	idx_	idx_user_info_fieldname
點陣圖索引(bitmap index)	bk_	bk_user_info_fieldname
同義詞(synonym)	依據於所分配的表所屬模組/模式
資料庫連結(database link)	無特殊要求

1.5             

規則1.5.1

所有PL/SQL中的變數與物件命名規則相似

變數型別	字首	範例
輸入變數	i_/i	i_user_id/iuserid
輸出變數	o_/o	o_user_name/ousername
輸出輸入變數	io_/io	io_user_name/iousername
普通變數	v_/v	v_user_id/vuserid
全域性變數	gv_/gv	gv_user_id/gvuserid
常量	大寫	PI
遊標	cur_	cur_userinfo
使用者自定義型別	type_	type_user_info
儲存點(save point)	spt_	spt_user_info

規則1.5.2

命名不允許使用中文或者特殊字元。

命名中若使用特殊約定或縮寫，則要註釋說明。

規則1.5.3

使用有意義、易於記憶、描述性強、簡短及唯一的英文單詞/拼音縮寫。自己特有的命名風格，要自始自終保持一致，不可來回變化。

說明：個人命名風格，在符合所在專案組的命名規則的前提下，才可以使用。

規則1.5.4

對於變數命名，禁止取單個字元(如i、j… )，建議除了要有具體含義外，還能表明變數型別等。

說明：變數，尤其是區域性變數，如果用單個字元表示，很容易敲錯(如i 寫成j)，而編譯時又檢查不出來，有可能為了這個小小的錯誤而花費大量的時間。

1.6             

注 釋規範是判斷一個開發人員優劣和成熟度的重要指標。一個優秀的研發人員必然是經過深思熟慮然後才洋洋灑灑妙筆生花的，註釋的書寫體現了一個人思考問題的全 過程和步驟；話又說回來，就算程式碼寫的爛，只要註釋寫的好，至少也會給人以良好的感覺；同時也能造福後人，不是麼？呵呵。

規則1.6.1

一般情況下，源程式有效註釋量必須在30%左右。

說明：註釋的原則是有助於對程式閱讀理解，在該加的地方都加了，註釋不宜太多也不能太少，註釋語言須準確、易懂、簡潔、精煉。

規則1.6.2

統一檔案頭的註釋.

主要是對相關過程、函式進行功能性描述、修訂記錄、以及入參出參說明

對儲存過程、函式的任何修改，都需要在註釋後新增修改人、修改日期及修改原因等修訂說明。

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

名稱: sp_xxx

功能描述：

 

修訂記錄：

版本號    編輯時間    編輯人  修改描述

1.0.0     2010-01-01  John    1、建立此儲存過程

1.0.1     2010-02-01  Sandy   2、增加傳入引數

 

入參出參描述：

iparameter1        IN VARCHAR2(20)  傳入引數1

iparameter2        IN VARCHAR2(20)  傳入引數2

iparameter1        OUT VARCHAR2(20)  傳入引數1

iparameter2        OUT VARCHAR2(20)  傳入引數2

 

返回值描述：(主要針對函式)

  0 - Success

  1 - normal fail

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

規則1.6.3

所有變數定義需要加註釋，說明該變數的用途和含義。

規則1.6.4

註釋內容要清晰、明瞭、含義準確，防止註釋二義性

在程式碼的功能、意圖層次上進行註釋，提供有用、額外的資訊。

避免在一行程式碼或表示式的中間插入註釋。

儘量使用”--”進行註釋；行尾註釋須使用”--”。

規則1.6.5

對程式分支必須書寫註釋。

說明：這些語句往往是程式實現某一特定功能的關鍵，對於維護人員來說，良好的註釋幫助更好的理解程式，有時甚至優於看設計文件。

在程式塊的結束行右方加註釋，以表明程式塊結束。

規則1.6.6

註釋應與其描述的程式碼相似，對程式碼註釋應放在其上方或右方(對單條語句的註釋)相近位置，不可放在下面。

註釋與所描述的內容進行同樣的縮排。

註釋上面的程式碼應空行隔開。

建議1.6.7

註釋用中文書寫

有一次，同事寫了一個900行的儲存過程，裡面定義了十幾個遊標以進行遍歷，這個儲存過程缺乏註釋，執行一次居然要一天一夜，已經達到了無法容忍的地步。

    因為缺乏註釋，我花了整整一天的時間來對該儲存過程進行分析，然後用了半天時間來進行改寫和除錯。

其實很簡單定義，我定義了一些對應的臨時表，把遊標遍歷替換成SQL的集合操作，把整個的一個大事務分割成若干小事務，只是修改了部分程式碼，結果執行時間就變成了短短的3分鐘。

當然遊標也並非不可觸及的，既然存在就有他存在的理由。

1.7             

    良好的語法規範有助於書寫出高效、完備的PL/SQL程式，同時有助於提高系統的容錯性、健壯性、可追溯性。

規則1.7.1

避免隱式的資料型別轉換。

說明：在書寫程式碼時，必須確定表的結構和表中各個欄位的資料型別，特別是書寫查詢條件時的欄位就更要注意了。這個是導致SQL效能不佳常犯的錯誤之一。

規則1.7.2

為了方便不同的資料庫平臺的移植，儘量使用SQL99標準，而不要使用Oracle的方言。

例如：DECODE函式完全可以用CASE WHEN語句代替，而且可程式設計性更強。

(+)=右關聯用RIGHT OUTER JOIN語句代替。

=(+)左關聯用 LEFT OUTER JOIN語句代替。

規則1.7.3

對於非常複雜的SQL(特別是多層巢狀，帶子句或相關的查詢)，應該先考慮是否設計不當引起的，對於複雜的一些SQL可以考慮使用程式實現，原則上遵循一句話只做一件事情。

關於處理的優先順序

1、  靜態SQL>動態SQL

2、  繫結變數的SQL>動態SQL（在OLTP系統中建議這麼做）

3、  SQL>PL/SQL的過程，極端複雜的SQL除外

4、  SQL>遊標遍歷

5、  Oracle函式>自定義函式

6、儘量使用Oracle分析函式代替同一個表多次的關聯。

規則1.7.4

原則上不要使用動態SQL，如果非得使用動態SQL，建議使用繫結變數。

規則1.7.5

一定要及時關閉和釋放遊標

規則1.7.6

建議在異常處理中，把收集到錯誤資訊記入錯誤日誌表，以備查詢和分析。

CREATE OR REPLACE PROCEDURE sp_increament_xxx

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

名稱: sp_increament_xxx

功能描述：xxx表/模組資料增量更新，錯誤原因分析透過tbl_task_table日誌表

 

修訂記錄：

版本號    編輯時間    編輯人  修改描述

1.0.0     2010-05-01  John    1、建立此儲存過程

1.0.1     2010-06-01  Sandy   2、更新xxx欄位在xxx處

 

入參出參描述：

  N/A

 

返回值描述：(主要針對函式)

  N/A 

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

AS

    v_err_num NUMBER;

    v_err_msg VARCHAR2(100);

    v_begin_date DATE;

    v_end_date DATE;

BEGIN

 

    v_err_num:=0;

    v_err_msg:='';

   

    --某表增量更新步驟

    BEGIN

        SAVEPOINT spt_xxx;

       

        --從任務表中獲取更新初始時間

        SELECT lasttime INTO v_begin_date FROM tbl_task_table T

         WHERE id='sp_increament_xxx'; 

        --從源資料表中獲取更新最後時間

        SELECT MAX(oper_date) INTO v_end_date FROM tbl_table_source;  

        --為提高執行效率，將增量資料寫入臨時表中

        INSERT INTO tmp_tbl_table_source

            (fieldname1,fieldname2,fieldname3,

             fieldname4,fieldname5,fieldname6)

        SELECT

             fieldname1,fieldname2,fieldname3,

             fieldname4,fieldname5,fieldname6

          FROM tbl_table_source sourcetable

         WHERE sourcetable.create_date > v_begin_date

           AND sourcetable.create_date <= v_end_date;

        --再講增量資料從臨時表更新到最終目標表

        MERGE INTO tbl_table_original_dest desttable

        USING tmp_tbl_table_source tmptable

           ON (desttable.primarykey = tmptable.primarykey) --匹配判斷標準，根據主鍵判斷

         WHEN MATCHED THEN                                  --如果已存在，更新原紀錄

         UPDATE SET desttable.fieldname1 = tmptable.fieldname1,

                    desttable.fieldname1 = tmptable.fieldname1,

                    desttable.fieldnamem = tmptable.fieldnamem,

                    desttable.fieldnamen = tmptable.fieldnamen

         WHEN NOT MATCHED THEN                              --如果不存在，插入新紀錄

         INSERT (fieldname1,fieldname2,fieldname3,

                 fieldname4,fieldname5,fieldname6)

         VALUES (tmptable.fieldname1,tmptable.fieldname2,tmptable.fieldname3,

                 tmptable.fieldname4,tmptable.fieldname5,tmptable.fieldname6);  

        --更新任務表相應的狀態、時間           

        UPDATE tbl_task_table

           SET lasttime_=v_end_date,status='SUCCESS'

         WHERE id='sp_increament_xxx';

        COMMIT;

   

    --異常處理，把錯誤記入相關日誌表，可以及時找到錯誤原因並進行分析。

    EXCEPTION

        WHEN OTHERS THEN

            ROLLBACK TO SAVEPOINT spt_xxx;

            v_err_num := SQLCODE;

            v_err_msg := SUBSTR(SQLERRM, 1, 100);

            UPDATE tbl_task_table

               SET lasttime_=v_begin_date,status='FAIL'

             WHERE id='sp_increament_xxx';

            COMMIT;

    END;

   

    --某表增量更新步驟

    BEGIN

        ...

    END;

   

END;

本例為資料庫定時呼叫儲存過程，同時也只是為了演示儲存過程的全部過程；對於常用的由客戶端呼叫的儲存過程，建議不要捕獲異常，而由客戶程式進行直接處理。

規則1.7.7

不要將空的變數值直接與比較運算子(符號)比較。如果變數可能為空，應使用IS NULL 或IS NOT NULL 或NVL 函式進行比較。

規則1.7.8

儘可能地使用相關表欄位的型別定義，形如%TYPE、%ROWTYPE。這樣做當表結構發生變動的時候，能夠最大程度的做到容錯性和健壯性。

規則1.7.9

儲存過程中變數的宣告應集中在AS 和BEGIN 關鍵字之間，不允許在程式碼中隨意定義變數，定義變數時，完成相同功能模組的變數應放在一起，與不同模組的變數應空行隔開，增加程式碼的可讀性。

 

1.8             

    指令碼規範有助於進行版本基線的管理、版本控制，也有助於系統的自動部署、定位和解決部署過程中出現的問題。

規則 1.8.1

所有指令碼按分類或內容分開存放，並按以下順序儲存：

1.       建立資料庫角色、使用者指令碼

2.       建立資料庫表空間、資料檔案指令碼

3.       建立資料型別指令碼，自定義的資料型別

4.       建立業務表指令碼，表是其他依賴關係的基礎

5.       建立臨時表指令碼，可能會在過程指令碼中用到

6.       建立檢視指令碼

7.       建立主外來鍵指令碼

8.       建立索引指令碼

9.       建立觸發器指令碼

10.    建立函式、儲存過程指令碼

11.    初始化資料指令碼

12.    建立作業指令碼

規則1.8.2

建立每個物件程式碼的首部應該有物件註釋

規則1.8.3

每個函式、儲存過程應單獨建立指令碼，在配置庫上按照功能模組存放到不同的目錄下。

並在相應的目錄下，建立一個執行所有指令碼的總指令碼。