大話數(shù)據(jù)庫編程規(guī)范.doc

大話數(shù)據(jù)庫編程規(guī)范1.1前言目前在軟件圈內(nèi)有這么一個現(xiàn)象，就是：DBA不太懂寫PL/SQL，而開發(fā)人員寫的又是五花八門，而且效率不高。如此以來，造成諸多弊端：1.可讀性差。讀別人寫的一個程序花費的時間，比自己寫一個程序的花費時間還要長；非但別人看不懂，時間久了連自己也看不懂了。2.可維護性差。程序越寫越長，越改越爛，像懶婆娘的裹腳布，又臭又長。3.可移植性差。今天用oracle寫一套，明天換成SQL Server的時候再寫一套，眾多的數(shù)據(jù)庫開發(fā)人員在程序的苦海中重復著低級勞動4.效率和性能差。一個存儲過程或SQL執(zhí)行效率簡直可以讓你感覺到對時間的絕望，你很快就理解什么是相對論了。1.2編程規(guī)范概述事實上為了統(tǒng)一軟件開發(fā)過程中關于數(shù)據(jù)庫設計時的命名規(guī)范和編程規(guī)范，正規(guī)一些的IT公司都會制定一些關于數(shù)據(jù)庫對象的命名和編程規(guī)范。否則的話，你寫你的我寫我的，各自為戰(zhàn)不兼容，彼此看不懂，甚至到最后連自己都弄不明白了，這樣的經(jīng)歷相信每個人都曾經(jīng)遇到過。例如下面這段簡單的代碼，您看的明白嗎？就算暫時明白，過個一年半載您還明白嗎？就算您記憶超群，想必也會忘記，不是么？A:=1;b:=2;SeLeCT username from EmPLOyee where id=a aNd Type=b;如果你改為下面這樣的寫法，相信稍微懂點數(shù)據(jù)庫的人都應該看的明白不是？vID:=1;-A為IDvType=2;-B為類型SELECT username FROM employee WHERE id=vID AND type=vType;1.3書寫規(guī)范丑陋的書寫規(guī)范不僅可讀性較差，而且給人以敬而遠之的感覺，就是您是大俠也不行??；而良好的書寫規(guī)范則給人以享受和藝術的體驗。1.3.1大小寫風格規(guī)則1.3.1.1所有數(shù)據(jù)庫關鍵字和保留字均使用大寫；關于字段、變量的大小寫風格在1.4詳細介紹。1.3.2縮進風格規(guī)則1.3.2.1程序塊嚴格采用縮進風格書寫，保證代碼清晰易讀，風格一致，縮進格數(shù)統(tǒng)一為2 /4個。必須使用空格，不允許使用TAB鍵。以免用不同的編輯器閱讀程序時，因TAB鍵所設置的空格數(shù)目不同而造成程序布局不整齊。規(guī)則1.3.2.2同一條語句需要占用多于一行時，每行的其它關鍵字與第一行的關鍵字進行右對齊。IF flag=1 THENSELECT username-同上一行相比縮進4個空格INTO vuserinfo-INTO與SELECT進行右對齊FROM userinfo-FROM與SELECT進行右對齊WHERE userid=:iuserid;-WHERE與SELECT進行右對齊END IF;1.3.3空格及換行規(guī)則1.3.3.1不允許把多個語句寫在一行中，即一行只寫一條語句。規(guī)則1.3.3.2避免將復雜的SQL語句寫到同一行，建議要在關鍵字和謂詞處換行。規(guī)則1.3.3.3相對獨立的程序塊之間必須加空行。BEGIN、END獨立成行規(guī)則1.3.3.4太長的表達式應在低優(yōu)先級操作符處換行，操作符或關鍵字放在新行之首。劃分出新行應當適當?shù)乜s進，使排版整齊，語句可讀。不同類型的操作符混合使用時，建議使用括號進行隔離，以使代碼清晰。規(guī)則1.3.3.5減少控制語句的檢查次數(shù)，如在IFELSE控制語句中，對最常用符合條件，盡量前置以被檢查到。DECLARE-定義局部變量vFlag VARCHAR2(10);-判斷標志BEGINIF (a=b AND a=c AND a=d) OR-在OR處斷行，可使得邏輯更為清晰(a=e AND e=f) THEN-Process somethingIF vFlag=1 THEN-vFlag=1為經(jīng)常出現(xiàn)之條件，可有效減少判斷檢查次數(shù)-Process somethingELSIF vFlag=2 THEN-vFlag=2為次之出現(xiàn)的條件-Process somethingELSE-Process somethingEND IF;1.3.4其它規(guī)則1.3.4.1避免使用SELECT *語句；不要用*來代替所有字段，應給出字段列表，以避免表結(jié)構發(fā)生變化時應用程序出現(xiàn)無法識別的情況。規(guī)則1.3.4.2INSERT語句必須給出字段列表，以避免表結(jié)構發(fā)生變化時發(fā)生編譯錯誤。規(guī)則1.3.4.3當一個PL/SQL或SQL語句中涉及到多個表時，始終使用別名來限定表名和字段名，這使其它人閱讀起來更方便，避免了含義模糊的引用，并能夠別名中清晰地判斷出表名和相關字段名。規(guī)則1.3.4.4確保變量和參數(shù)在類型和長度與表數(shù)據(jù)列類型和長度相匹配。說明：如果與表數(shù)據(jù)列寬度不匹配，則當較寬或較大的數(shù)據(jù)傳進來時會產(chǎn)生運行異常。DECLARE-定義相關表字段變量vDeptNosalary.Deptno%type;-not VARCHAR2(10)，以適應變化vEmployeeNosalary.EmployeeNo%type; -not VARCHAR2(10)，以適應變化vSalarysalary.Salary%type;-not NUMBER，以適應變化BEGIN-Process somethingEND;1.4命名規(guī)范一千個讀者就有一千個哈姆雷特，對于命名規(guī)范來說，想做到完全統(tǒng)一的確是不可能的任務。命名規(guī)范更多的是個人層面的愛好，就算有命名規(guī)范，也不過是體現(xiàn)制訂規(guī)范的相關人的愛好而已。因此即使無法完全做到一致，但是我們?nèi)匀灰M量去遵守，必要的時候需要通過代碼檢查和專家評審來進行約束，因為一個不成熟的規(guī)范總會勝過沒有規(guī)范。1.4.1表和字段命名規(guī)范在此僅提供幾種常見的命名方法(表和字段的命名方式雷同)。以用戶權限字段/表為例：UserPrivilege適合那些英文比較好，并且喜歡抑揚頓挫和有藝術美感的人。userprivilege適合那些英文好，且比較嚴謹?shù)娜?，畢竟全部小寫很容易與數(shù)據(jù)庫關鍵字相區(qū)別。tbl_user_privilege適合那些做開發(fā)的人，開發(fā)的人會習慣性的給變量加前綴。(這里指表的命名，字段一般很少加前綴)yhqx熱愛中文的人，前提是恐怕您得對這些縮寫先做好相關備注，等大家習慣了才行。實際上這幾種命名規(guī)范各有千秋，很難去指責或否定哪種更好，完全取決于整個公司多數(shù)人的習慣，記住沒有十全十美的命名規(guī)范，只有絕大多數(shù)人心甘情愿的去遵從了，那就是好的命名規(guī)范。就我個人而言，我更偏向于第一種命名習慣。規(guī)則1.4.1.1不建議使用數(shù)據(jù)庫關鍵字和保留字（不建議并不意味著不能使用），只是為了避免不必要的沖突和麻煩；例如name,id,level,remark,description等等。有興趣的話，大家可以參考下SELECT * FROM v$reserved_words WHERE reserved=Y實際上oracle不建議大家使用v$reserved_words表中所有的關鍵字，無奈這些關鍵字太多了；reserved=Y的關鍵字則是被完全禁止的。規(guī)則1.4.1.2嚴禁使用帶空格的名稱來對字段和表命名；在產(chǎn)生數(shù)據(jù)庫腳本并重新加載的時候可能會出現(xiàn)意想不到的錯誤而被迫終止。1.4.2其它對象命名用戶自定義的數(shù)據(jù)庫對象名包括表、視圖、主外鍵、索引、觸發(fā)器、函數(shù)、存儲過程、序列、同義詞、數(shù)據(jù)庫鏈接、包和包體等等。規(guī)則1.4.2.1其它對象的命名也與表和字段的命名規(guī)則類似，風格保持一致即可規(guī)則1.4.2.2除數(shù)據(jù)庫名稱長度為18個字符，其余為130個字符，database link名稱也不要超過30個字符；命名只能使用英文字母，數(shù)字和下劃線規(guī)則1.4.2.3除表外，其它各種對象的命名最好用不同的前綴加以區(qū)別。采用前綴的方式來命名對象則很容易通過排序?qū)ο筮M行區(qū)別。如在命名規(guī)范中各組成部分以_分割，則前綴建議也以_分割；反之則可加可不加對象名前綴范例表(table)tbl_/t_(或不加前綴）userinfo/t_user_info/tbl_user_info視圖(view)v_/vv_user_info/vuserinfo序列(sequence)seq_seq_user_info簇(cluster)c_c_user_info觸發(fā)器(trigger)trg_trg_user_info存儲過程(procedure)sp_/p_sp_user_info/p_user_info函數(shù)(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)依據(jù)于所分配的表所屬模塊/模式數(shù)據(jù)庫鏈接(database link)無特殊要求1.5變量命名規(guī)則1.5.1所有PL/SQL中的變量與對象命名規(guī)則相似變量類型前綴范例輸入變量i_/ii_user_id/iuserid輸出變量o_/oo_user_name/ousername輸出輸入變量io_/ioio_user_name/iousername普通變量v_/vv_user_id/vuserid全局變量gv_/gvgv_user_id/gvuserid常量大寫PI游標cur_cur_userinfo用戶自定義類型type_type_user_info保存點(save point)spt_spt_user_info規(guī)則1.5.2命名不允許使用中文或者特殊字符。命名中若使用特殊約定或縮寫，則要注釋說明。規(guī)則1.5.3使用有意義、易于記憶、描述性強、簡短及唯一的英文單詞/拼音縮寫。自己特有的命名風格，要自始自終保持一致，不可來回變化。說明：個人命名風格，在符合所在項目組的命名規(guī)則的前提下，才可以使用。規(guī)則1.5.4對于變量命名，禁止取單個字符(如i、j)，建議除了要有具體含義外，還能表明變量類型等。說明：變量，尤其是局部變量，如果用單個字符表示，很容易敲錯(如i寫成j)，而編譯時又檢查不出來，有可能為了這個小小的錯誤而花費大量的時間。1.6注釋規(guī)范注釋規(guī)范是判斷一個開發(fā)人員優(yōu)劣和成熟度的重要指標。一個優(yōu)秀的研發(fā)人員必然是經(jīng)過深思熟慮然后才洋洋灑灑妙筆生花的，注釋的書寫體現(xiàn)了一個人思考問題的全過程和步驟；話又說回來，就算代碼寫的爛，只要注釋寫的好，至少也會給人以良好的感覺；同時也能造福后人，不是么？呵呵。規(guī)則1.6.1一般情況下，源程序有效注釋量必須在30%左右。說明：注釋的原則是有助于對程序閱讀理解，在該加的地方都加了，注釋不宜太多也不能太少，注釋語言須準確、易懂、簡潔、精煉。規(guī)則1.6.2統(tǒng)一文件頭的注釋.主要是對相關過程、函數(shù)進行功能性描述、修訂記錄、以及入?yún)⒊鰠⒄f明對存儲過程、函數(shù)的任何修改，都需要在注釋后添加修改人、修改日期及修改原因等修訂說明。/*名稱: sp_xxx功能描述：修訂記錄：版本號編輯時間編輯人修改描述1.0.02010-01-01John1、創(chuàng)建此存儲過程1.0.12010-02-01Sandy2、增加傳入?yún)?shù)入?yún)⒊鰠⒚枋觯篿parameter1IN VARCHAR2(20)傳入?yún)?shù)1iparameter2IN VARCHAR2(20)傳入?yún)?shù)2iparameter1OUT VARCHAR2(20)傳入?yún)?shù)1iparameter2OUT VARCHAR2(20)傳入?yún)?shù)2返回值描述：(主要針對函數(shù))0 - Success1 - normal fail*/規(guī)則1.6.3所有變量定義需要加注釋，說明該變量的用途和含義。規(guī)則1.6.4注釋內(nèi)容要清晰、明了、含義準確，防止注釋二義性在代碼的功能、意圖層次上進行注釋，提供有用、額外的信息。避免在一行代碼或表達式的中間插入注釋。盡量使用”-”進行注釋；行尾注釋須使用”-”。規(guī)則1.6.5對程序分支必須書寫注釋。說明：這些語句往往是程序?qū)崿F(xiàn)某一特定功能的關鍵，對于維護人員來說，良好的注釋幫助更好的理解程序，有時甚至優(yōu)于看設計文檔。在程序塊的結(jié)束行右方加注釋，以表明程序塊結(jié)束。規(guī)則1.6.6注釋應與其描述的代碼相似，對代碼注釋應放在其上方或右方(對單條語句的注釋)相近位置，不可放在下面。注釋與所描述的內(nèi)容進行同樣的縮排。注釋上面的代碼應空行隔開。建議1.6.7注釋用中文書寫有一次，同事寫了一個900行的存儲過程，里面定義了十幾個游標以進行遍歷，這個存儲過程缺乏注釋，執(zhí)行一次居然要一天一夜，已經(jīng)達到了無法容忍的地步。因為缺乏注釋，我花了整整一天的時間來對該存儲過程進行分析，然后用了半天時間來進行改寫和調(diào)試。其實很簡單定義，我定義了一些對應的臨時表，把游標遍歷替換成SQL的集合操作，把整個的一個大事務分割成若干小事務，只是修改了部分代碼，結(jié)果執(zhí)行時間就變成了短短的3分鐘。當然游標也并非不可觸及的，既然存在就有他存在的理由。1.7語法規(guī)范良好的語法規(guī)范有助于書寫出高效、完備的PL/SQL程序，同時有助于提高系統(tǒng)的容錯性、健壯性、可追溯性。規(guī)則1.7.1避免隱式的數(shù)據(jù)類型轉(zhuǎn)換。說明：在書寫代碼時，必須確定表的結(jié)構和表中各個字段的數(shù)據(jù)類型，特別是書寫查詢條件時的字段就更要注意了。這個是導致SQL性能不佳常犯的錯誤之一。規(guī)則1.7.2為了方便不同的數(shù)據(jù)庫平臺的移植，盡量使用SQL99標準，而不要使用Oracle的方言。例如：DECODE函數(shù)完全可以用CASE WHEN語句代替，而且可編程性更強。(+)=右關聯(lián)用RIGHT OUTER JOIN語句代替。=(+)左關聯(lián)用LEFT OUTER JOIN語句代替。規(guī)則1.7.3對于非常復雜的SQL(特別是多層嵌套，帶子句或相關的查詢)，應該先考慮是否設計不當引起的，對于復雜的一些SQL可以考慮使用程序?qū)崿F(xiàn)，原則上遵循一句話只做一件事情。關于處理的優(yōu)先級1、靜態(tài)SQL動態(tài)SQL2、綁定變量的SQL動態(tài)SQL（在OLTP系統(tǒng)中建議這么做）3、SQLPL/SQL的過程，極端復雜的SQL除外4、SQL游標遍歷5、Oracle函數(shù)自定義函數(shù)6、盡量使用Oracle分析函數(shù)代替同一個表多次的關聯(lián)。規(guī)則1.7.4原則上不要使用動態(tài)SQL，如果非得使用動態(tài)SQL，建議使用綁定變量。規(guī)則1.7.5一定要及時關閉和釋放游標規(guī)則1.7.6建議在異常處理中，把收集到錯誤信息記入錯誤日志表，以備查詢和分析。CREATE OR REPLACE PROCEDURE sp_increament_xxx/*名稱: sp_increament_xxx功能描述：xxx表/模塊數(shù)據(jù)增量更新，錯誤原因分析通過tbl_task_table日志表修訂記錄：版本號編輯時間編輯人修改描述1.0.02010-05-01John1、創(chuàng)建此存儲過程1.0.12010-06-01Sandy2、更新xxx字段在xxx處入?yún)⒊鰠⒚枋觯篘/A返回值描述：(主要針對函數(shù))N/A*/ASv_err_num NUMBER;v_err_msg VARCHAR2(100);v_begin_date DATE;v_end_date DATE;BEGINv_err_num:=0;v_err_msg:=;-某表增量更新步驟BEGINSAVEPOINT spt_xxx;-從任務表中獲取更新初始時間SELECT lasttime INTO v_begin_date FROM tbl_task_table TWHERE id=sp_increament_xxx;-從源數(shù)據(jù)表中獲取更新最后時間SELECT MAX(oper_date) INTO v_end_date FROM tbl_table_source;-為提高執(zhí)行效率，將增量數(shù)據(jù)寫入臨時表中INSERT INTO tmp_tbl_table_source(fieldname1,fieldname2,fieldname3,fieldname4,fieldname5,fieldname6)SELECTfieldname1,fieldname2,fieldname3,fieldname4,fieldname5,fieldname6FROM tbl_table_source sourcetableWHERE sourcetable.create_date v_begin_dateAND sourcetable.create_date = v_end_date;-再講增量數(shù)據(jù)從臨時表更新到最終目標表MERGE INTO tbl_table_original_dest desttableUSING tmp_tbl_table_source tmptableON (desttable.primarykey = tmptable.primarykey) -匹配判斷標準，根據(jù)主鍵判斷WHEN MATCHED THEN-如果已存在，更新原紀錄UPDATE SET desttable.fieldname1 = tmptable.fieldname1,desttable.fieldname1 = tmptable.fieldname1,desttable.fieldnamem = tmptable.fieldnamem,desttable.fieldnamen = tmptable.fieldnamenWHEN NOT MATCHED THEN-如果不存在，插入新紀錄INSERT (fieldname1,fieldname2,fieldname3,fieldname4,fieldname5,fieldname6)VALUES (tmptable.fieldname1,tmptable.fieldname2,tmptable.fieldname3,tmptable.fieldname4,tmptable.fieldname5,tmptable.fieldname6);-更新任務表相應的狀態(tài)、時間UPDATE tbl_task_tableSET lasttime_=v_end_date,status=SUCCESSWHERE id=sp_increament_xxx;COMMIT;-異常處理，把錯誤記入相關日志表，可以及時找到錯誤原因并進行分析。EXCEPTIONWHEN OTHERS THENROL