範圍和簡介

簡介

本規範是針對關系型資料庫ORACLE的相關特性，拟定的用于指導和規範相關開發過程的規範，其旨在通過該規範的限制和建議，使開發人員可以在他們所編寫的代碼中保持統一正确的風格，提供代碼的可讀性以及減少出現錯誤的幾率。

範圍

本規範僅适用于關系型資料庫系統ORACLE以及其對應的PL/SQL腳本語言。

關鍵詞

程式設計規範、資料庫、ORACLE、PL/SQL

術語和定義

本規範采用以下的術語描述：

★ 規則：程式設計時強制必須遵守的原則。

★ 建議：程式設計時必須加以考慮的原則。

★ 說明：對此規則或建議進行必要的解釋。

★ 示例：對此規則或建議從正、反兩個方面給出。

資料庫整體設計規範（必讀）

設計

應用裡面，多個資料庫之間請不要通過DBLINK通路。
請不要采用觸發器。
請不要使用視圖和物化視圖。
請不要使用外鍵限制，如果資料存在外鍵關系，請在程式層面實作。
請盡量不要使用 job，如果不得已必須使用，Job 的設計必須是可重複執行的。
請盡量不要采用存儲過程，業務邏輯請在程式層面實作。
應用必須具有自動重連的機制。但是又要避免每執行一條 SQL 語句就檢查一下 DB 的可用性。

命名

命名應使用富有意義的英文詞彙，多個單詞組成的，中間以下劃線分割。
命名隻能使用英文字母，數字和下劃線。
命名避免使用 Oracle 保留字和系統關鍵字。
命名長度以不超過 15 個字元為宜(避免超過 20)。
命名全部采用小寫，并且名稱前後不能加引号。

資料庫對象設計規範

表

設計

在設計時盡量包含兩個日期字段:gmt_created(建立日期),gmt_modified(修改日期)且

非空, 對表的記錄進行更新的時候，必須包含對 gmt_modified 字段的更新。

盡可能使用簡單資料類型，不要使用類似數組或者嵌套表這種複雜類型。
必須要有主鍵，且盡量不要使用有實際意義的字段做主鍵。
需要 join 的字段，資料類型保持絕對一緻。
當表的字段數非常多時，可以将表分成兩張表，一張作為條件查詢表，一張作為詳細内容表（主要是為了性能考慮）。
當字段的類型為枚舉型或布爾型時，建議使用 char(1)類型。

命名

同一個子產品的表盡可能使用相同的字首，表名盡可能表達含義，例如：

CRM_SAL_FUND_ITEM。

字段命名應盡可能使用表達實際含義的英文單詞或縮寫，不要使用類似“VALUE1”這種無意義的字段名。
布爾值類型的字段命名為 is+描述。如 member 表上表示是否為 enabled 的會員的字段命名為 IsEnabled。

字段類型

注意：能用數值類型就用數值類型，如：IP位址也可以使用數值類型來存放。

類型	規範
NUMBER(p,s)	固定精度數字類型
NUMBER	不固定精度數字類型，當不确定數字的精度時使用，PK 通常使用此類型
DATE	當僅需精确到秒時，選擇 DATE 而不是 TIMESTAMP 類型
TIMESTAMP	擴充日期類型，不建議使用
VARCHAR2	變長字元串，最長 4000 個位元組
CHAR	定長字元串，除非是CHAR(1),否則不要使用
CLOB	當超過 4000 位元組時使用，但是要求這個字段必須單獨建立到一張表中，然後有 PK 與主表關聯。此類型應該盡量控制使用

字段注釋

标準字段注釋由一組"@"開頭的标簽+空格+文本組成。

MD_USER 表的部分字段為例：

Name	Type	Comments
PARTY_ID	VARCHAR2(20)	@desc 主鍵 ID
CORPORATION_ID	VARCHAR2(20)	@desc 使用者所在公司 ID
@fk md_corporation.party_id
STATUS	VARCHAR2(20)	@desc 狀态
@values disable\|enable: 未激活狀态\|激活狀态
IS_PRI_ACCOUNT	CHAR(1)	@desc 是否為主賬号。背景生成UK 時使用 @values y\|n:是帳号，非主帳号 @logic 一個公司内部，有且僅有一個主賬号存在

注釋标簽說明

标簽名	中文含義	必填	備注
@desc	字段中文描述	Yes
@fk	字段對應的外鍵字段
@values	取值範圍說明。多個值以"\|"分隔	如此字段的值由系統自動生成，可忽略不書寫。
@sample	資料範本	對于複雜資料格式，最好給一個資料範本。
@formula	計算公式	寫明該字段由哪些字段以何種公式計算得到。
@logic	資料邏輯	簡要寫明該字段的資料是在何種業務規則下，如何變化的。
@redu	辨別此字段備援
@depr	辨別此字段已廢棄	簡要寫明：廢棄人廢棄日期廢棄原因

索引

設計

Bitmap 索引通常不适合我們的環境。
索引根據實際 SQL，由 DBA 建立。
不要建立帶限制的索引，所有的限制效果都通過顯示建立限制然後再 using index 一個已經建立好的普通索引來實作。

命名

<table_name>_<column_name>_ind,各部分以下劃線（_）分割。
多單詞組成的 column name，取前幾個單詞首字母，加末單詞組成 column_name。如： sample 表 member_id 上的索引：sample_mid_ind。

限制

設計

主鍵最好是無意義的，由 Sequence 産生的 ID 字段，類型為 number，不建議使用組合主鍵。
若要達到唯一性限制的效果，不要建立 unique index，必須顯式建立普通索引和限制（pk 或 uk），即先建立一個以限制名命名的普通索引，然後建立一個限制，用 using index ...指定索引。
當删除限制的時候，為了確定不影響到 index，最好加上 keep index 參數。
主鍵的内容不能被修改。
外鍵限制一般不在資料庫上建立，隻表達一個邏輯的概念，由程式控制。
當萬不得已必須使用外健的話，必須在外健列建立 INDEX。

命名

主鍵限制： _pk 結尾，<table_name>_pk；
unique 限制：_uk 結尾，<table_name>_<column_name>_uk；
check 限制： _ck 結尾，<table_name>_<column_name>_ck；
外鍵限制： _fk 結尾，以 pri 連接配接本表與主表，<table_name>_pri_<table_name>_fk；

SEQUENCE

命名

seq_<table_name>

觸發器

命名

<table_name>_A(After)B(Before)I(Insert)U(Update)D(Delete)_trg。
若是用于同步的觸發器以 sync 作為字首：sync_<table_name>_trg。

過程、函數、包

命名

過程以 proc_開頭，函數以 func_開頭，包以 pkg_開頭。
變量命名約定：本地變量以 v_為字首，參數以 p_為字首，可以帶_I(輸入)，O(輸出)、_IO(輸入輸出）表示參數的輸入輸出類型。

書寫規範

大小寫風格

規則：資料庫腳本中涉及到的各種保留字、關鍵字、操作符、資料類型、辨別符、對象、字段、變量、常量等的大小寫風格必須遵循以下規範：

大小寫規範表

文字類型	大小寫	示例
ORACLE保留字	大寫	IDENTIFIED、INITIAL、LONG、MAXEXTENTS、RENAME、ROWNUM、SYSDATE、VARCHAR2
ORACLE關鍵字	大寫	ANALYZE、AUTOEXTEND、COMPILE、DBA、EXTENT、INSTANCE、MAXTRANS、SAVEPOINT、TABLESPACE
ANSI SQL保留字	大寫	ADD、BETWEEN、CREATE、DISTINCT、DROP、FROM、SESSION、SELECT、TABLE、UPDATE、VARCHAR
ANSI SQL關鍵字	大寫	BEGIN、COMMIT、CONSTRAINT、COUNT、CURSOR、DECLARE、EXECUTE、PRIMARY、SUM、TRANSACTION
PL/SQL保留字	大寫	ARRAY、BOOLEAN、DATABASE、DO、EXIT、RAISE、SAVEPOINT、RUN、WHILE、XOR
操作符	大寫	ALL、AND、ANY、ESCAPE、EXISTS、OR、UNION
資料類型	大寫	INT、NUMBER、DATE
系統資料庫對象（如：視圖、函數、存儲過程、包等）	大寫	USER$、DBA_JOBS、DECODE、SUBSTR、DBMS_OUTPUT、PUT_LINE、NO_DATA_FOUND
自定義資料庫對象	大寫	T_PUB_TABLE_NAME、F_PUB_GETID、P_KBS_GET_SERVICEINFO
字段名	大寫	USER_NAME、STAFFNO
常量	大寫	SECONDS_OF_DAY
變量	邏輯首字母大寫	v_BeginDate、v_FaxNumber、c_StaffAuth

說明：

ORACLE的關鍵字和保留字請參見視圖V$RESERVED_WORDS；
上表中需要大寫的關鍵字/保留字，可以錄入UltraEdit的wordfile.txt，在使用UltraEdit開發時輸入這些關鍵字保留字後會自動變為大寫。

縮進風格

規則：腳本代碼塊采用縮進風格書寫，保證代碼清晰易讀，風格一緻。縮進格數統一為2個空格，但需要關鍵字右對齊或者參數/字段向上對齊的時候例外。

示例1：

CREATE SEQUENCE SQ_CSP_TABLENAME

MINVALUE 1

START WITH 1

INCREMENT BY 1;

示例2：

IF c_CursorName%ISOPEN THEN

CLOSE c_CursorName;

END IF;

規則：縮進必須使用空格鍵，不允許使用TAB鍵。

規則：同一條語句占用多于一行時，每行的第一個關鍵字應當右對齊，但INSERT...SELECT語句例外。

示例1：

FETCH c_Cursor

INTO v_Field1, v_Field2, ...

↑

關鍵字右對齊

示例2：

SELECT FIELD1, FIELD2, ...

FROM T_CSP_TABLENAME

WHERE FIELD1 > 1

AND (FIELD2 < SYSDATE OR FIELD2 > SYSDATE + 3)

AND FIELD3 LIKE 'HUAWEI%'

GROUP BY FIELD1, FIELD2

ORDER BY FIELD1, FIELD2;

↑

關鍵字右對齊

規則：在INSERT...SELECT語句中，如果需要換行時，應使INSERT INTO部分的字段與SELECT部分的字段一一對應，以增強可讀性。

示例：

錯誤的寫法：

BEGIN

INSERT INTO T_DEST_TABLENAME (DESTFIELDNAME1, DESTFIELDNAME2,

DESTFIELDNAME3, DESTFIELDNAME4, DESTFIELDNAME5,

DESTFIELDNAME6, DESTFIELDNAME7)

SELECT SRCFIELDNAME1, SRCFIELDNAME2, SRCFIELDNAME3,

SRCFIELDNAME4, SRCFIELDNAME5, SRCFIELDNAME6,

SRCFIELDNAME7

FROM T_SRC_TABLENAME;

END;

正确的寫法A：

BEGIN

INSERT INTO T_DEST_TABLE_NAME (DESTFIELDNAME1, DESTFIELDNAME2,

DESTFIELDNAME3, DESTFIELDNAME4, DESTFIELDNAME5,

DESTFIELDNAME6, DESTFIELDNAME7)

SELECT SRCFIELDNAME1, SRCFIELDNAME2,

SRCFIELDNAME3, SRCFIELDNAME4, SRCFIELDNAME5,

SRCFIELDNAME6, SRCFIELDNAME7

FROM T_SRC_TABLE_NAME;

END;

正确的寫法B：

BEGIN

INSERT INTO T_DEST_TABLE_NAME (DESTFIELDNAME1, -- 注釋

DESTFIELDNAME2, -- 注釋

DESTFIELDNAME3, -- 注釋

DESTFIELDNAME4, -- 注釋

DESTFIELDNAME5, -- 注釋

DESTFIELDNAME6, -- 注釋

DESTFIELDNAME7) -- 注釋

SELECT SRCFIELDNAME1, -- 注釋

SRCFIELDNAME2, -- 注釋

SRCFIELDNAME3, -- 注釋

SRCFIELDNAME4, -- 注釋

SRCFIELDNAME5, -- 注釋

SRCFIELDNAME6, -- 注釋

SRCFIELDNAME7 -- 注釋

FROM T_SRC_TABLE_NAME;

END;

正确的寫法C：

BEGIN

INSERT INTO T_DEST_TABLE_NAME (

DESTFIELDNAME1, -- 注釋

DESTFIELDNAME2, -- 注釋

DESTFIELDNAME3, -- 注釋

DESTFIELDNAME4, -- 注釋

DESTFIELDNAME5, -- 注釋

DESTFIELDNAME6, -- 注釋

DESTFIELDNAME7) -- 注釋

SELECT SRCFIELDNAME1, -- 注釋

SRCFIELDNAME2, -- 注釋

SRCFIELDNAME3, -- 注釋

SRCFIELDNAME4, -- 注釋

SRCFIELDNAME5, -- 注釋

SRCFIELDNAME6, -- 注釋

SRCFIELDNAME7 -- 注釋

FROM T_SRC_TABLE_NAME;

END;

說明：

在錯誤的寫法中，雖然SELECT語句部分的SRCFIELDNAME1, SRCFIELDNAME2, SRCFIELDNAME3可以寫在一行中，但由于INSERT INTO語句部分中DESTFIELDNAME1, DESTFIELDNAME2寫在一行中，而DESTFIELDNAME3在下一行，是以SELECT語句中每行的字段應與INSERT INTO語句中的字段一一對應（如正确的寫法A）；
INSERT INTO語句中的各個字段折行後，應縮進并與上一字段左對齊（如正确的寫法B），或者與INTO關鍵字左對齊（如正确的寫法C）；
SELECT語句中折行後的第一個字段名應縮進并與上一行的第一個字段名左對齊。

規則：INSERT INTO語句中，如果需要對每個字段增加注釋，應将每個字段單獨列為一行，并在行尾增加注釋。

示例1：

INSERT INTO T_DEST_TABLE_NAME (DESTFIELDNAME1, -- 注釋1

DESTFIELDNAME2, -- 注釋2

DESTFIELDNAME3) -- 注釋3

VALUES (FieldValue1,

FieldValue2,

FieldValue3);

或者

INSERT INTO T_DEST_TABLE_NAME (

DESTFIELDNAME1, -- 注釋1

DESTFIELDNAME2, -- 注釋2

DESTFIELDNAME3) -- 注釋3

VALUES (

FieldValue1,

FieldValue2,

FieldValue3);

或者

INSERT INTO T_DEST_TABLE_NAME

(

DESTFIELDNAME1, -- 注釋1

DESTFIELDNAME2, -- 注釋2

DESTFIELDNAME3 -- 注釋3

)

VALUES

(

FieldValue1,

FieldValue2,

FieldValue3

);

其他說明：

在規範的代碼模闆中詳細給出了各種情況下的範例寫法，請編碼過程參考；
在資料庫腳本的編碼過程中，請嚴格按照代碼模闆進行書寫；
對于模闆中未涉及的情況，應在保證符合上述規範原則，同時和代碼模闆中的風格保持一緻的前提下，根據實際情況靈活處理。

空格及換行

規則：不允許把多個短語句寫在一行中，即一行隻寫一條語句。

示例：

v_Variable1 := 1; v_Variable2 := 'abc';

應寫成：

v_Variable1 := 1;

v_Variable2 := 'abc';

說明：兩個指派語句不能寫在一行中，必須分兩行寫。

規則：相對獨立的程式塊之間、變量說明之後必須加空行。

示例：

v_Variable1 := 1;

IF v_BeginDate IS NULL THEN

v_BeginDate := SYSDATE - 15;

END IF;

應寫成：

-- 初始化局部變量

v_Variable1 := 1;

-- 判斷開始時間

IF v_BeginDate IS NULL THEN

v_BeginDate := SYSDATE - 15;

END IF;

說明：兩個程式塊在邏輯上相對獨立，應用空行加以分隔，同時增加注釋。

建議：建議對超過120字元的語句要分行書寫，長表達式應在低優先級操作符處換行，操作符或關鍵字放在新行之首。劃分出的新行應适當地縮進，使排版整齊、語句可讀。是否分行應根據實際情況而定，原則是保證代碼整齊、語句可讀。

分行示例：

120字元

↓

…… (a * b * c * d) + (e * f) + ……

應寫成：

…… (a * b * c * d)

+ (e * f) + ……

說明：

加法的優先級低于乘法，是以應在加号處換行；
兩組乘法雖然在邏輯上會先于加法執行，但顯式加上括号使可讀性更強。

不分行示例：

具體内容請參見代碼模闆“建立初始化資料模闆”部分，有時初始化資料的語句不分行情況下可讀性更強。

規則：調用函數或過程時，如果參數清單超過120字元，應根據邏輯内容進行換行，或者每個參數占用一行。

示例：

錯誤的寫法：

120字元

↓

P_PROCEDURE(i_Param1, i_Param2, i_Param3, o_Param1, oParam2, ...);

正确的寫法A：

P_PROCEDURE(i_Param1, i_Param2, i_Param3,

o_Param1, o_Param2, ...);

正确的寫法B：

P_PROCEDURE(i_Param1, -- 注釋

i_Param2, -- 注釋

i_Param3, -- 注釋

o_Param1, -- 注釋

o_Param2, -- 注釋

...) -- 注釋

說明：

因為前三個為輸入參數，後兩個為輸出參數，是以在第3、4參數之間換行比較清晰（如正确的寫法A）；
有時為了增加注釋，可以使每個參數皆占用一行（如正确的寫法B）；
參數換行以後與上一行的第一個參數對齊。

規則：雙目運算符、操作符前後應以空格分隔，間隔符之後應以空格分隔。

示例：

v_DateVar:=TO_DATE('2001-01-01 01:30:00','YYYY-MM-DD HH24:MI:SS');

v_IntegerVar1:=v_IntegerVar2+v_IntegerVar3;

IF v_Number>0 THEN

應寫成：

v_DateVar := TO_DATE('2001-01-01 01:30:00', 'YYYY-MM-DD HH24:MI:SS');

↑ ↑

指派符前後加空格逗号後面加空格

v_IntegerVar1 := v_IntegerVar2 + v_IntegerVar3;

↑ ↑

指派符前後加空格加号前後加空格

IF v_Number > 0 THEN

↑

比較符号前後加空格

建議：不同優先級的操作符混合使用時，建議使用括号進行隔離。

示例：

…… a * b + c ……

應寫成：

…… (a * b) + c ……

說明：使用括号使代碼的優先級更加清晰，而且可以避免犯錯。

建議：SQL語句中存在不同優先級的關系運算符時，建議使用括号。

示例：

WHERE FIELD1 > 1 AND FIELD2 < SYSDATE OR FIELD3 > SYSDATE + 3

應寫成：

WHERE (FIELD1 > 1 AND FIELD2 < SYSDATE) OR (FIELD3 > SYSDATE + 3)

說明：如果SQL語句的條件複雜，很容易出現問題，是以增加括号可以提高可閱讀性。

建立表

規則：腳本中的字段名縮進為2個空格。

規則：腳本中字段名稱、字段類型、DEFAULT關鍵字應左對齊，NULL/NOT NULL應右對齊（也可以認為就是NULL關鍵字左對齊）。

示例：

CREATE TABLE T_WF_TABLE_NAME

(

INTFIELD INT NOT NULL,

FLOATFIELD NUMBER(4, 2) DEFAULT 1.23 NOT NULL,

DATEFIELD DATE DEFAULT SYSDATE NULL,

VARCHAR2FIELD VARCHAR2(200) NULL

)

TABLESPACE SERVICE_MAIN_DAT;

說明：至于每行中的每個項目之間使用多少個空格沒有明确要求，隻要求從縱向上各個字段的名稱、資料類型、預設值、是否為空等對齊美觀即可。

建立過程/函數/包

規則：腳本中的參數縮進為2個空格。

規則：腳本中的參數名稱、資料類型、注釋資訊應左對齊，IN/OUT/IN OUT關鍵字左對齊右對齊皆可。

示例：

CREATE OR REPLACE PROCEDURE P_CSP_PROCEDURENAME

(

i_DateParam DATE, -- 注釋

o_IntParam IN OUT INT, -- 注釋

o_VarcharParam OUT VARCHAR2, -- 注釋

o_RetCode OUT INT -- 注釋

)

或者

CREATE OR REPLACE PROCEDURE P_CSP_PROCEDURENAME

(

i_DateParam DATE, -- 注釋

o_IntParam IN OUT INT, -- 注釋

o_VarcharParam OUT VARCHAR2, -- 注釋

o_RetCode OUT INT -- 注釋

)

說明：至于每行中的每個項目之間使用多少個空格沒有明确要求，隻要求從縱向上各個參數的名稱、資料類型、IN/OUT關鍵字等對齊美觀即可。

SQL 開發規範

編碼規範

在代碼中不允許出現任何 DDL 語句

DDL 語句一律由 DBA 編寫并統一執行

寫 SQL 的時侯一定要使用綁定變量

對于極少數情況下不使用綁定變量提高性能，使用之前一定要和 DBA 溝通

寫 SQL 的時候一定要給每個字段指定表名做字首

比如 select a.id,a.name from test a; 好處是一來帶來性能的提升，二來可以避免一些錯誤的發生。

在 sqlmap 中的變量，要用#号，而不要用$符号

如#appid#。因為$name$ 是字面意義的替換，這種形式會有 SQL 注入的漏洞，而 #name# 是帶類型的替換，不存在 SQL 注入的風險。

請不要寫 select * 這樣的代碼，指定需要的字段名

避免在 where 子句中對字段施加函數

通常，不允許在字段上添加函數或者表達式，這樣将導緻索引失效，如：

錯誤的寫法：

select *

from iw_account_log

where to_char(trans_dt, 'yyyy-mm-dd') = '2007-04-04';

select qty from product where p_id + 12 = 168;

正确的寫法：

select *

from iw_account_log

where trans_dt >= to_date('2007-04-04', 'yyyy-mm-dd')

and trans_dt < to_date('2007-04-05', 'yyyy-mm-dd');

select qty from product where p_id = 168 - 12;

如果是業務要求的除外，但需要在編寫時咨詢DBA特别注意，當表連接配接時，用于連接配接的兩個表的字段如果資料類型不一緻，則必須在

一邊加上類型轉換的函數，錯誤的寫法（a.id 是 number 類型，而 b.operator_number 是 char 類型）：

select count

from adm_user a, adm_action_log b

where a.id = b.operator_number

and a.username = '小钗';

正确的寫法：

select count

from adm_user a, adm_action_log b

where to_char(a.id) = b.operator_number

and a.username = '小钗';

select count

from adm_user a, adm_action_log b

where a.id = to_number(b.operator_number)

and a.username = '小钗';

上面兩種寫法哪個正确？遇到這種情況時必須咨詢 DBA！

嚴格要求使用正确類型的變量，杜絕 oracle 做隐式類型轉換的情況

推薦在 sqlmap 的變量中指定變量的資料類型，如：select * from iw_user where iw_user_id = #userid:VARCHAR#其中，對于時間類型的字段，必須使用 TO_DATE 進行指派（目前時間可直接用 sysdate 表示），不允許下列這些錯誤用法：

錯誤的寫法（使用 date 類型的變量）：

select *

from iw_account_log

where trans_account = #transaccount :varchar#

and trans_dt >= #dateBegin :date#

and trans_dt < #dateEnd :date#

錯誤的寫法（将 to_date 函數和數字進行算術運算）：

select *

from iw_account_log

where trans_account = #transaccount

:varchar#

and trans_dt >= to_date(#dateBegin :varchar#, 'yyyy-mm-dd hh24:mi:ss')

and trans_dt <

to_date(#dateBegin :varchar#, 'yyyy-mm-dd hh24:mi:ss') + 1

正确的寫法：

select *

from iw_account_log

where trans_account = #transaccount

:varchar#

and trans_dt >= to_date(#dateBegin :varchar#, 'yyyy-mm-dd hh24:mi:ss')

and trans_dt < to_date(#dateEnd :varchar#, 'yyyy-mm-dd hh24:mi:ss') /* 或 trans_dt < sysdate */

對于變量資料類型錯誤導緻 SQL 嚴重性能問題的，按嚴重的編碼錯誤 Bug 處理！

全模糊查詢無法使用 INDEX，應當盡可能避免

比如：select * from table where name like '%jacky%';

外連接配接的寫法

不推薦使用 ANSI 連接配接，如 inner join、left join、right join、full outer join，而推薦使用（+）來表示外連接配接

不推薦的寫法：

select a.*, b.goods_title

from iw_account_log a

left join beyond_trade_base b

on a.TRANS_OUT_ORDER_NO = b.trade_no

where a.trans_code = '6003'

and a.trans_account = #transacnt :varchar#

and a.trans_dt > to_date(.. .)

推薦的寫法：

select a.*, b.goods_title

from iw_account_log a, beyond_trade_base b

where a.TRANS_OUT_ORDER_NO = b.trade_no(+)

and a.trans_code = '6003'

and a.trans_account = #transacnt :varchar#

and a.trans_dt > to_date(.. .)

表連接配接分頁查詢的使用

包含排序邏輯的分頁查詢寫法，必須是三層 select 嵌套：

錯誤的寫法：

SELECT t1.*

FROM (SELECT t.*, ROWNUM rnum

FROM beyond_trade_base t

WHERE seller_account = :1

AND gmt_create >= TO_DATE(:2, 'yyyy-mm-dd')

AND gmt_create < TO_DATE(:3, 'yyyy-mm-dd')

ORDER BY gmt_create DESC) t1

WHERE rnum >= :4

AND rnum < :5

正确的寫法：

SELECT t2.*

FROM (SELECT t1.*, ROWNUM rnum

FROM (SELECT t.*

FROM beyond_trade_base t

WHERE seller_account = :1

AND gmt_create >= TO_DATE(:2, 'yyyy-mm-dd')

AND gmt_create < TO_DATE(:3, 'yyyy-mm-dd')

ORDER BY gmt_create DESC) t1

WHERE ROWNUM <= :4) t2

WHERE rnum >= :5

不包含排序邏輯的分頁查詢寫法，則是兩層 select 嵌套，但對 rownum 的範圍指定仍然必須在不同的查詢層次指定：

錯誤的寫法：

SELECT t1.*

FROM (SELECT t.*, ROWNUM rnum

FROM beyond_trade_base t

WHERE seller_account = :1

AND gmt_create >= TO_DATE(:2, 'yyyy-mm-dd')

AND gmt_create < TO_DATE(:3, 'yyyy-mm-dd')) t1

WHERE rnum >= :4

AND rnum <= :5

正确的寫法：

SELECT t1.*

FROM (SELECT t.*, ROWNUM rnum

FROM beyond_trade_base t

WHERE seller_account = :1

AND gmt_create >= TO_DATE(:2, 'yyyy-mm-dd')

AND gmt_create < TO_DATE(:3, 'yyyy-mm-dd')

AND ROWNUM <= :4) t1

WHERE rnum >= :5

注意下面兩種寫法的邏輯含義是不同的：

按建立時間排序（倒序），然後再取前 10 條：

SELECT t2.*

FROM (SELECT t1.*, ROWNUM rnum

FROM (SELECT t.*

FROM sell_offer t

WHERE owner_member_id = :1

AND gmt_create >= TO_DATE(:2, 'yyyy-mm-dd')

AND gmt_create < TO_DATE(:3, 'yyyy-mm-dd')

ORDER BY gmt_create DESC) t1

WHERE ROWNUM <= 10) t2

WHERE rnum >= 1

随機取 10 條，然後在這 10 條中按照交易建立時間排序（倒序）：

SELECT t1.*

FROM (SELECT t.*, ROWNUM rnum

FROM beyond_trade_base t

WHERE seller_account = :1

AND gmt_create >= TO_DATE(:2, 'yyyy-mm-dd')

AND gmt_create < TO_DATE(:3, 'yyyy-mm-dd')

AND ROWNUM <= 10

ORDER BY gmt_create DESC) t1

WHERE rnum >= 1

先連接配接後分頁與先分頁後連接配接性能較差：

SELECT t2.*

FROM (SELECT t1.*, ROWNUM rnum

FROM (SELECT a.*, b.receive_fee

FROM beyond_trade_base a, beyond_trade_process b

WHERE a.trade_no = b.trade_no

AND a.seller_account = :1

AND a.gmt_create >= TO_DATE(:2, 'yyyy-mm-dd')

AND a.gmt_create < TO_DATE(:3, 'yyyy-mm-dd')

ORDER BY a.gmt_create DESC) t1

WHERE ROWNUM <= :4) t2

WHERE rnum >= :5

性能較好：

SELECT /*+ ordered use_nl(a,b) */

a.*, b.receive_fee

FROM (SELECT t2.*

FROM (SELECT t1.*, ROWNUM rnum

FROM (SELECT t.*

FROM beyond_trade_base t

WHERE seller_account = :1

AND gmt_create >= TO_DATE(:2, 'yyyy-mm-dd')

AND gmt_create < TO_DATE(:3, 'yyyy-mm-dd')

ORDER BY gmt_create DESC) t1

WHERE ROWNUM <= :4) t2

WHERE rnum >= :5) a,

beyond_trade_process b

WHERE a.trade_no = b.trade_no

後面這種寫法的适用情況：

where 子句中的查詢條件都是針對 beyond_trade_base 表的（否則得到的結果将不相同）關聯 beyond_trade_process 表時，用的是該表的主鍵或者唯一鍵字段（否則将改變結果集的條數）

Hint 的使用

sql 中的/*+ ordered use_nl(member offer)*/是 hint，用來确定 SQL 的執行計劃，請在

DBA 确認後使用。

"<>"、"!="、"not in"、"exsits"和"not exists"的使用規範

原則上一般禁止使用"<>"、"!="和"not in"，而應該轉換成相應的"="和"in"查詢條件

錯誤的寫法：

select a.id, a.subject, a.create_type

from product

where status <> 'new'

and owner_member_id = :1

正确的寫法：

select a.id, a.subject, a.create_type

from product

where status in ('auditing',

'modified',

'service-delete',

'tbd',

'user-delete',

'wait-for-audit')

and owner_member_id = :1

錯誤的寫法：

select a.id, a.subject, a.create_type

from product

where create_type not in ('new_order', 'vip_add')

and owner_member_id = :1

正确的寫法：

select a.id,a.subject,a.create_type

from product

where create_type = 'cust_add'

and owner_member_id = :1

原則上不允許使用"exsits"和"not exists"查詢，應轉換成相應的"等連接配接"和外連接配接來查

詢

錯誤的寫法：

select a.id

from company a

where not exsits (select 1 from av_info_new b where a.id = b.company_id)

正确的寫法：

select a.id

from company a, av_info_draft b

where a.id = b.company_id

and b.company_id is null

錯誤的寫法：

select count

from company a

where exsits (select 1 from av_info_new b where a.id = b.company_id)

正确的寫法：

select count from company a, av_info_draft b where a.id = b.company_id

注：在通過等連接配接替換 exsits 的時候有一點需要注意，隻有在一對一的時候兩者才能較容易替換，如果是一對多的關系，直接替換後兩者的結果會出現不一緻情況。因為 exsits 是實作是否存在，不管存在一條還是多條，而等連接配接時傳回所關聯上的所有資料。

如有特殊需要無法完成相應的轉換，必須在 DBA 允許的情況下使用"<>"、"!="、"not in"、"exsits"和"not exists"

其它編寫規範

對表的記錄進行更新的時候，必須包含對 gmt_modified 字段的更新，并且不要使用 dynamic 标記，如：

錯誤的寫法：

update BD_CONTACTINFO <dynamic prepend="set">

......

<isNotNull prepend="," property="gmtModified"> GMT_MODIFIED = #gmtModified:TIMESTAMP# </isNotNull>

</dynamic> where ID = #id#

正确的寫法（當然，這裡更推薦直接更新為 sysdate）：

update BD_CONTACTINFO

set GMT_MODIFIED =

#gmtModified:TIMESTAMP# <dynamic>

......

</dynamic>

where ID = #id#

不允許在 where 後添加 1=1 這樣的無用條件，where 可以寫在 prepend 屬性裡，如：錯誤的寫法：

select count from BD_CONTRACT t where 1=1

......

</dynamic>

正确的寫法：

select count from BD_CONTRACT

......

</dynamic>

對大表進行查詢時，在 SQLMAP 中需要加上對空條件的判斷語句，具體可在遇到時咨詢 DBA，如：

性能上不保險的寫法：

select count from iw_user usr

usr.iw_user_id = #userId:varchar#

</isNotEmpty>

usr.email = #email:varchar#

</isNotEmpty>

usr.cert_type = #certType:varchar#

</isNotEmpty>

usr.cert_no = #certNo:varchar#

</isNotEmpty>

</dynamic>

性能上較保險的寫法（防止那些能保證查詢性能的關鍵條件都為空）：

select count from iw_user usr

usr.iw_user_id = #userId:varchar#

</isNotEmpty>

usr.email = #email:varchar#

</isNotEmpty>

usr.cert_type = #certType:varchar#

</isNotEmpty>

usr.cert_no = #certNo:varchar#

</isNotEmpty>

query not allowed

</isEmpty>

</dynamic>

另外，對查詢表單的查詢控制建議使用 web 層進行控制而不是用戶端腳本

（JAVASCRIPT/VBSCRIPT）

聚合函數常見問題

不要使用 count(1)代替 count（*）count(column_name)計算該列不為 NULL 的記錄條數count(distinct column_name)計算該列不為 NULL 的不重複值數量count()函數不會傳回 NULL，但 sum()函數可能傳回 NULL，可以使用nvl(sum(qty),0)來避免傳回 NULL

NULL 的使用

了解 NULL 的含義，是"不确定"，而不是"空"查詢時，使用 is null 或者 is not null更新時，使用等于号，如：update tablename set column_name = null

STR2NUMLIST、STR2VARLIST 函數的使用：

适用情況：使用唯一值（或者接近唯一值）批量取資料時編寫規範：a 表必須放在 from list 的第一位，并且必須在 select 後加上下面的 hint

正确的寫法：

select /+ ordered use_nl(a,b) */ b.

from TABLE(CAST(str2varlist(:1) as vartabletype)) a, beyond_trade_base b where a.column_value = b.trade_no;

格式規範

注釋說明

本注釋說明主要用于 PL/SQL 程式及其它 SQL 檔案，其它可作參考；
SQLPLUS 接受的注釋有三種：

這兒是注釋

／＊這兒是注釋＊／

REM 這兒是注釋

開始注釋，類似JAVAK 中的開始注釋，主要列出檔案名，編寫日期，版權說明，程式功能以及修改記錄：

REM

REM $Header: filename, version, created date,auther

REM

REM Copyright

REM

REM FUNCTION

REM function explanation

REM NOTES

REM

REM MODIFIED （yy/mm/dd）

REM who when - for what, recently goes first

塊注釋，如表注釋，PROCEDURE 注釋等，同JAVA:

*This table is for TrustPass

*mainly store the information

*of TrustPass members

注意: 在“/*”後應當另起一行，或與其後描述有間隔，否則在 SQLPLUS 中會有問題。

單行注釋，如列注釋：

login_id VARCHAR2(32) NOT NULL, -- 會員标

縮進

低級别語句在進階别語句後的，一般縮進 4 個空格：

DECLARE

v_MemberId VARCHAR2(32),

BEGIN

SELECT admin_member_id INTO v_MemberId

FROM company

WHERE id = 10;

DBMS_OUTPUT.PUT_LINE(v_MemberId);

END;

同一語句不同部分的縮進，如果為 sub statement，則通常為 2 個空格，如果與上一句某部分有密切聯系的，則縮至與其對齊：

BEGIN -- sub statemen

FOR v_TmpRec IN (SELECT login_id,

gmt_created, -- here indented as column above

satus

FROM member

WHERE site = 'china'

AND country = 'cn') LOOP

NULL;

END LOOP;

END;

斷行

一行最長不能超過 80 字元
同一語句不同字句之間
逗号以後空格
其他分割符前空格

SELECT offer_name

||','

||offer_count as offer_category,

FROM category

WHERE super_category_id_1 = 0;

常用文法

變量聲明

規則：代碼中聲明與表的字段相對應的變量時，應保證變量名和字段名相同。

說明：這樣要求的目的是增強可讀性。

示例：

DECLARE

v_DateField T_TABLENAME.DATEFIELD%TYPE;

BEGIN

SELECT DATEFIELD INTO v_DateField

FROM T_TABLENAME

WHERE ROWNUM = 1;

END;

規則：代碼中聲明與表的字段相對應的變量時，對類型的定義需要使用%TYPE方式。

說明：這樣表結構的變動不會影響存儲過程，避免表結構變更後出現變量長度或類型不一緻的問題。

傳回值

規則：函數的傳回值定義應遵從下列規範：成功出口傳回0，失敗出口傳回大于0整數。過程中定義的用于傳回錯誤碼的OUTPUT參數，其定義應遵從下列規範：成功出口傳回0，失敗出口傳回非0整數。

符号*

規則：腳本中不允許出現“*”的用法，必須用實際的字段名代替，INSERT語句必須指定要插入的字段名。

示例：

1、遊标定義

錯誤用法：

CURSOR c_CursorName IS

SELECT * FROM TABLENAME ...

正确用法：

CURSOR c_CursorName IS

SELECT FIELD1, FIELD2, ... FROM TABLENAME ...

2、INSERT 語句

錯誤用法：

INSERT INTO TABLENAME VALUES ...

正确用法：

INSERT INTO TABLENAME (FIELD1, FIELD2, ...) VALUES ...

包的使用

建議：ORACLE的包類似C++中的Class，有標頭和包體兩部分組成；包能夠把相關的功能封裝性在一個包中，包裡面裡可以有函數和過程。PACKAGE與PROCEDURE和FUNCTION的優點是封裝性比較好；在開發過程中，可以把功能點緊密相關的PROCEDURE、FUNCTION封裝在一個包裡面。在對包體進行編譯時，調用該包的存儲過程不會失效。

綁定變量

規則：使用綁定變量的SQL語句，能使用綁定變量方式的業務邏輯下不允許使用綁定常量實作。

說明：在存儲過程拼SQL語句作為動态SQL執行時，尤其需要注意。

示例：

錯誤用法：

v_SQL := 'DELETE FROM TABLENAME WHERE FIELD1 > SYSDATE AND FIELD2 = 102';

EXECUTE IMMEDIATE v_SQL;

正确用法：

v_ID := 102;

v_SQL := 'DELETE FROM TABLENAME WHERE FIELD1 > :1 AND FIELD2 = :2';

EXECUTE IMMEDIATE v_SQL USING SYSDATE, v_ID;

規則：為提升性能，減少循環的開銷，可以使用批量綁定。

說明：下面示例中的UPDATE語句可以一次将多條記錄進行更新，而避免了通過使用循環同一條SQL執行多次。

示例：

CREATE OR REPLACE PROCEDURE P_CSP_TEST

Description : 根據輸入的多個工單流水号，更新對應的SERVICECLASSID值

Author : 姓名工号

Date : YYYY-MM-DD

Version : 版本

Caller : 調用者

Callee : 被調用者

Comments : 注釋資訊

History :

1. Date : 修改日期，格式為YYYY-MM-DD

Author : 修改人姓名與工号

Modification : 修改說明

2. ......

(

i_SerialNos VARCHAR2, -- 一次輸入多個工單流水号以'~'分隔'

i_ServiceClassID VARCHAR2,

o_Ret OUT VARCHAR2 -- 成功傳回0，失敗傳回1

)

TYPE typ_StrArray IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;

v_ArrSerial typ_StrArray;

BEGIN

FOR i IN 1..9999 LOOP

-- 把輸入參數的值取出存放在數組v_ArrSerial中

EXIT WHEN GetParamStr(i_SerialNos, '~', i, v_ArrSerial(i)) <> 0;

END LOOP;

-- 根據數組中的工單流水号批量更新

FORALL i IN 1..v_ArrSerial.COUNT

UPDATE T_WF_SERVICEINFO t

SET t.SERVICECLASSID = i_ServiceClassID

WHERE SerialNo = v_ArrSerial(i);

COMMIT;

o_Ret := 0;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

o_Ret := 1;

END;

異常處理

規則：函數/過程中應該有異常處理的代碼，除非需要将任何可能的異常都向上抛出。

說明：不論代碼邏輯是否簡單，隻要有可能會抛出異常，函數/過程塊就應該包括異常處理代碼。詳見代碼模闆。

規則：如果需要的話，可以在異常處理部分将異常繼續向上抛出給調用者。

說明：如果有些異常是預料可能産生，而且腳本需要根據是否有異常來做不同的邏輯處理，這種情況可以在異常處理部分将該異常進行處理，但可能有些意料之外的異常，需要繼續向上抛出，便于調用者了解腳本執行是否成功，以便于調用者作下一步的處理如寫錯誤日志等操作。

規則：如果需要自定義異常，必須在異常處理塊中對其進行處理。

說明：如果自定義了異常，卻沒有相應的代碼進行處理，那麼就應該去掉該異常的定義。

規則：在對容錯性要求比較高的情況下，對異常塊中的代碼還需要判斷是否可能觸發異常，必要的時候應使用嵌套的異常。

說明：在異常處理部分中的寫日志之前，如果有事務，一定要先ROLLBACK；異常塊中寫日志、向表中插入一條初始化記錄等語句也可能失敗，對這些代碼必要時也應該增加異常保護，即使用嵌套的異常處理。

事務控制

規則：在任何出口之前，隻要存在事務未結束，必須送出或者復原，除非有特殊設計考慮。

說明：存儲過程在每個出口前，如果啟動了事務必須結束所有事務，以送出（COMMIT）或復原（ROLLBACK）來結束事務，否則可能會導緻表鎖等嚴重問題；如果在存儲過程中，沒有啟動事務，就不必執行COMMIT或ROLLBACK，否則多餘的ROLLBACK或COMMIT操作将增加資料庫的額外開銷。

規則：ORACLE的事務是串行的，嵌套調用中内層存儲過程的送出會導緻外層的存儲過程事務被一并送出；諸如生成流水号這類存儲過程應使用獨立事務，否則在産生流水号時就将之前的業務邏輯操作送出，而後續的操作不能保證在一個事務中。例如現有系統中一個工單循環派給多個部門時，循環體内産生流水号，如最後一個派單失敗時復原操作根本就不能回撤所有操作，導緻事務一緻性被破壞。

建議：盡量分解大事務，事務的大小應視系統的性能和應用的具體情況而定，過多過小的事務造成重做日志同步的等待，比如要往一個表插入100萬條記錄，如果每條記錄送出一次，則事務太小，可以做一個計數器，設定1萬或事務5萬條作一次送出。

遊标使用

規則：原則上避免使用遊标，尤其是動态遊标。

說明：

遊标的效率較低，如果在代碼中可以通過不同的條件分支實作的邏輯，盡量不要使用遊标。
動态遊标通常都可以轉化為若幹個靜态遊标，是以除非必要，盡量使用靜态遊标代替動态遊标。

建議：推薦使用CURSOR FOR LOOP語句，可以隐式打開關閉遊标；否則，顯式打開遊标，就必須在任何出口之前顯式關閉遊标。下面的示例通過 FOR IN LOOP取資料，不需要顯示打開和關閉遊标。

示例：

DECALRE

CURSOR c_Dept IS SELECT DEPTNO, DNAME FROM DEPT ORDER BY DEPTNO;

v_Tot_Salary EMP.SALARY%TYPE;

BEGIN

FOR r_Dept IN c_Dept LOOP

DBMS_OUTPUT.PUT_LINE('Department:' || r_Dept.DEPTNO);

v_Tot_Salary := 0;

END LOOP;

END;

規則：如果存儲過程傳回遊标類型參數，必須在任何出口前打開遊标。

說明：如果存儲過程有遊标類型的輸出參數，而在某個分支中未打開，其調用者使用該遊标擷取資料時會報錯。

示例：

CREATE OR REPLACE PROCEDURE P_TMP_TESTSYSCURSOR(

o_Cur OUT SYS_REFCURSOR -- 注釋

)

BEGIN

IF v_Flag > 0 THEN

OPEN o_Cur FOR SELECT FIELD1 FROM T_WF_TABLE;

RETURN;

ELSE

RETURN;

END IF;

END;

應寫成：

CREATE OR REPLACE PROCEDURE P_TMP_TESTSYSCURSOR(

o_Cur OUT SYS_REFCURSOR -- 注釋

)

BEGIN

IF v_Flag > 0 THEN

OPEN o_Cur FOR SELECT 0 AS RET, FIELD1 AS MSG FROM T_WF_TABLE;

RETURN;

ELSE

OPEN o_Cur FOR SELECT -1 AS RET, ’ErrorMsg’ AS MSG FROM DUAL;

RETURN;

END IF;

END;

代碼規模

建議：建議每個存儲過程和函數的規模不宜超過500行。

說明：存儲過程和函數的邏輯不宜太過複雜，應當注意對于同樣的計算，存儲過程執行效率遠低于其他應用程式，是以要避免将大量業務邏輯都放到存儲過程實作。

SQL建議與限制

建議

建議：在開發過程中，在SQL語句中，盡量不要指定索引。

說明：指定索引後，在以後的優化過程中，無法通過建立和優化索引，使對應的SQL語句用到其他更好的索引，對指定的索引删除、重建改名後，可能導緻該SQL語句用不到索引。

建議：在不使用DISTINCT、UNION、ORDER BY、GROUP BY情況下，也能實作業務功能的情況，一定不要使用這些功能。使用這些功能會導緻對應的SQL語句排序，增加系統的開銷。

示例：

錯誤的用法：

SELECT COUNT(*)

FROM (

SELECT SERIALNO

FROM T_PUB_COMMONINFO

WHERE A.PARTID >= '0127'

AND A.PARTID <= '1227'

ORDER BY ACCEPTBEGINTIME DESC -- 沒有用的ORDER BY

)

建議：建立組合索引時，要注意組合索引的順序和字段的選擇性，把經常出現在WHERE條件中同時選擇性比較好的字段放在複合索引的第一個位置。

說明：SQL語句在使用複合索引時，與該複合索引字段的組合順序有關，當索引的第一個字段出現在WHERE條件中，這時候對該表的查詢能夠用到該索引，當該索引的第一個字段沒有出現在SQL語句的查詢條件中，但該索引的其他的字段出現在WHERE條件中時，在基于規則的優化模式中，SQL引擎會用全表掃描方式查詢，在基于成本的優化模式中的（First Rows）方式查詢，SQL引擎會對該索引全索引掃描方式查詢。

示例：

錯誤的用法：

ALTER TABLE T_WF_SERVICEINFOHIS

ADD CONSTRAINT PK_WF_SERVICEINFOHIS PRIMARY KEY (MONTHDAY, SERIALNO, SERVICECLASSID);

說明：在業務邏輯的查詢中，許多時候之間按SERIALNO, SERVICECLASSID查時，在基于規則的優化模式中，用不到索引。

正确的用法：

ALTER TABLE T_WF_SERVICEINFOHIS

ADD CONSTRAINT PK_WF_SERVICEINFOHIS PRIMARY KEY (SERIALNO, SERVICECLASSID, MONTHDAY);

建議：建立表時資料和索引建議在不同的表空間。

建議：在WHERE條件表達式中，盡可能避免在要使用到索引的字段上使用函數，如果要使用函數建議建立相應的函數索引。

示例：

錯誤用法：

SELECT FIELD

FROM TABLENAME

WHERE SUBSTRB(FIELD, 1, 4) = '5378'

正确用法：

SELECT FIELD

FROM TABLENAME

WHERE FIELD LIKE '5378%'

建議：當查詢條件選擇性很低時使用索引反而降低效率，這種情況下，應該用特殊的方法屏蔽該索引，如果字段為數值型的就在表達式的字段名後+ 0，為字元型的就并上空串。

示例：

SELECT NUM_FIELD

FROM TABLENAME

WHERE NUM_FIELD + 0 > 30

SELECT STRING_FIELD

FROM TABLENAME

WHERE STRING_FIELD || '' = 'EXAMPLE'

建議：在SQL語句中，盡可能使用變量綁定，少用常量綁定。

建議：如果業務邏輯允許的情況下，盡量用UNION ALL代替UNION，用UNION ALL代替OR。

示例：

錯誤的用法：

SELECT SERIALNO

FROM T_WF_DISPOSALSTATUSHIS

WHERE (ACCEPTPHONE = :B4 OR CALLERNO = :B3 OR USERPHONE1 = :B2)

AND ACCEPTTIME > SYSDATE - :B1

正确的用法：

SELECT SERIALNO

FROM T_WF_DISPOSALSTATUSHIS

WHERE (ACCEPTPHONE = :B4)

AND ACCEPTTIME > SYSDATE - :B1

UNION ALL

SELECT SERIALNO

FROM T_WF_DISPOSALSTATUSHIS

WHERE (CALLERNO = :B3)

AND ACCEPTTIME > SYSDATE - :B1

UNION ALL

SELECT SERIALNO

FROM T_WF_DISPOSALSTATUSHIS A

WHERE (USERPHONE1 = :B2)

AND ACCEPTTIME > SYSDATE - :B1

建議：如果要對整個表或分區的資料删除，建議使用TRUNCATE替代DELETE。

建議：為了提高系統的并發性，盡可能的使事務的時間縮短。

建議：建立分區表的索引時，必須建立本地（LOCAL）索引。

說明：如果建立的是全局索引，在對分區表的某個分區TRUNCATE時，導緻該索引失效；需要對該全局索引進行重新編譯。

建議：避免通過DUAL表指派。

說明：過多的對DUAL表的通路，導緻調用該表的等待時間事件比較長。比如取系統時間之類的操作，往一個表插入記錄等。

示例：

錯誤的用法：

SELECT SYSDATE INTO v_Date

FROM DUAL

正确的用法：

v_Date := SYSDATE

錯誤的用法：

INSERT INTO TABLENAME(FIELD1, FIELD2, FIELD3)

SELECT '2', SYSDATE, SUSBTR(v_Name, 1, 30)

FROM DUAL;

正确的用法：

INSERT INTO TABLENAME(FIELD1, FIELD2, FIELD3)

VALUES('2', SYSDATE, SUSBTR(v_Name, 1, 30));

禁止

規則：

嚴禁用系統表空間(SYSTEM/SYSAUX/USER)作為使用者預設表空間；
嚴禁在系統表空間(SYSTEM/SYSAUX/USER)上建立使用者資料庫對象；
嚴禁在SYSTEM/SYS等系統使用者下，建立使用者資料庫對象。

規則：SQL語句的WHERE子句中應盡可能将字段放在等式左邊，将計算操作放在等式的右邊，除非是要屏蔽該字段的的索引，否則禁止字段參與表達式運算。

說明：任何對字段的操作都将造成此字段上的索引被屏蔽，導緻全表掃描，這裡所謂的操作包括資料庫函數、計算表達式等等。

示例：

錯誤的用法：

SELECT SOME_FIELD

FROM TABLENAME

WHERE NUM_FIELD / 30 < 1000

正确的用法：

SELECT SOME_FIELD

FROM TABLENAME

WHERE NUM_FIELD < 1000 * 30

錯誤的用法：

SELECT SOME_FIELD

FROM TABLENAME

WHERE TO_CHAR(LOGDATE, 'YYYYMMDD') = '19991201'

正确的用法：

SELECT SOME_FIELD

FROM TABLENAME

WHERE LOGDATE >= TO_DATE('19991201', 'YYYYMMDD')

AND LOGDATE < TO_DATE('19991202', 'YYYYMMDD')

規則：SQL語句的WHERE子句中每個條件的操作符兩邊類型應相同，禁止潛在的資料類型轉換。

說明：潛在的字段資料類型轉換将造成索引被屏蔽，導緻全表掃描。例如将字元型資料與數值型資料比較，ORACLE會自動将字元類型字段用TO_NUMBER函數進行轉換。

示例：

錯誤的用法：

表TABLENAME中的列STRING_FIELD是字元型（VARCHAR），則以下語句存在類型轉換：

SELECT SOMEFIELD

FROM TABLENAME

WHERE STRING_FIELD > 10

正确的用法：

SELECT SOMEFIELD

FROM TABLENAME

WHERE STRING_FIELD > '10'

規則：SQL語句的WHERE子句中避免使用IN操作，嚴禁使用NOT IN操作。

說明：在SQL語句中，能用表連接配接盡量使用表連接配接，不能使用表連接配接則使用EXISTS，嚴禁使用IN。

示例：

錯誤的用法：

SELECT SOME_FIELD

FROM TABLE1

WHERE FIELD1 IN (

SELECT FIELD2

FROM TABLE2

)

正确的用法：

SELECT T1.SOME_FIELD

FROM TABLE1 T1, TABLE2 T2

WHERE T1.FIELD1 = T2.FIELD2

錯誤的用法：

SELECT SOME_FIELD

FROM TABLE1

WHERE FIELD1 NOT IN (

SELECT FIELD2

FROM TABLE2

)

正确的用法：

SELECT SOME_FIELD

FROM TABLE1 T1

WHERE NOT EXISTS (

SELECT 1

FROM TABLE2 T2

WHERE T2.FIELD2 = T1.FIELD1

)

規則：禁止對VARCHAR(2000)之類的大字段值進行ORDER BY、DISTINCT、GROUP BY、UNION之類的操作。

說明：此類操作将消耗大量的CPU和記憶體資源。

規則：禁止在沒有事務的存儲過程和代碼中，随意使用COMMIT和ROLLBACK。

說明：過多多餘的ROLLBACK和COMMIT容易引起資料庫的同步日志等待事件，對系統的性能有影響，下面語句中的COMMIT和ROLLBACK就是多餘的。

示例：

錯誤的用法：

CREATE OR REPLACE PROCEDURE P_MS_QUERYBYSERIALNO

(

i_SerialNo VARCHAR2, -- 工單流水号

rCursor OUT PACK_SERVICE.t_RetDataSet -- 傳回結果集

)

BEGIN

OPEN rCursor

FOR SELECT b.STAFFNO AS STAFFNO,

DECODE(b.COMMITRESULT, 0, '成功', '失敗') AS COMMITRESULT

FROM T_MS_SENDLOGHIS a, T_MS_INTERFACECALLED b

WHERE b.SERIALNO = i_SerialNo;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

正确的用法：

CREATE OR REPLACE PROCEDURE P_MS_QUERYBYSERIALNO

(

i_SerialNo VARCHAR2, -- 工單流水号

rCursor OUT PACK_SERVICE.t_RetDataSet -- 傳回結果集

)

BEGIN

OPEN rCursor

FOR SELECT b.STAFFNO AS STAFFNO,

DECODE(b.COMMITRESULT, 0, '成功', '失敗') AS COMMITRESULT

FROM T_MS_INTERFACECALLED b

WHERE b.SERIALNO = i_SerialNo;

EXCEPTION

WHEN OTHERS THEN

OPEN rCursor

FOR SELECT '成功' AS STAFFNO,

'成功' AS COMMITRESULT

FROM DUAL

WHERE 1 = 2;

END;

規則：禁止利用SQL語句做一些業務邏輯的判斷或操作。

示例：

錯誤的用法：

SELECT STAFFNO, STAFFNAME

FROM T_PUB_STAFF

WHERE (i_StaffNo IS NULL OR STAFFNO = i_StaffNo)

AND (i_StaffName IS NULL OR STAFFNAME LIKE '%' || i_StaffName || '%')

錯誤分析：上面的SQL語句中，利用SQL引擎對變量的值進行判斷，導緻在使用過程中，對該表進行全表掃描。

正确的用法：通過代碼中對變量的值進行判斷然後決定執行對應的SQL語句。

規則：禁止在生産系統，直接用PL/SQL Developer或TOAD之類的工具中直接調試存儲過程。

規則：禁止在PL/SQL Developer中執行SELECT * FROM TABLE_NAME FOR UPDATE，然後點選鎖圖示方式，再編輯資料的方式進行更新資料庫中的資料。

開發工具

UltraEdit-32

全稱：UltraEdit-32 Professional Text/HEX Editor

公司：IDM Computer Solutions, Inc.

網址：http://www.ultraedit.com

簡介：UltraEdit是目前比較流行的文本編輯器，它可以将文本中的字元串按自己所期望的字型及格式顯示出來，對顯示代碼中關鍵字、字元串、系統函數等尤為有用，并且可以在編輯過程中自動按照自己所定義關鍵字清單（WORDFILE.TXT檔案）校正大小寫。

PL/SQL Developer

全稱：PL/SQL Developer

公司：Allround Automations

網址：http://www.allroundautomations.nl

簡介：PL/SQL Developer是能支援斷點單步調試ORACLE腳本的工具，通過它可以友善的檢視修改ORACLE的資料庫對象及其屬性、執行各種SQL語句，調試測試存儲過程函數等，是開發ORACLE腳本和現場解決ORACLE腳本問題的好工具。

TOAD

全稱：TOOLS FOR ORACLE APPLICATION DEVELOPER

公司：Quest Software

網址：http://www.quest.com

簡介：TOAD能支援斷點單步調試ORACLE腳本，同時還有較強的DBA管理功能。

PowerDesigner

全稱：PowerDesigner

公司：Sybase

網址：http://www.sybase.com

簡介：PowerDesigner 是一個獨具特色的模組化工具集，它融合了以下幾種标準模組化技術：使用 UML 的應用程式模組化、業務流程模組化和傳統資料庫模組化。我們主要利用該工具完成資料庫模組化，PowerDesigner能以ER圖的方式展現各實體之間的關系，尤其适用于項目設計階段的表結構設計。

資料庫設計開發規範

範圍和簡介

簡介

範圍

關鍵詞

術語和定義

資料庫整體設計規範（必讀）

設計

命名

資料庫對象設計規範

表

設計

命名

字段類型

字段注釋

索引

設計

命名

限制

設計

命名

SEQUENCE

命名

觸發器

命名

過程、函數、包

命名

書寫規範

大小寫風格

規則：資料庫腳本中涉及到的各種保留字、關鍵字、操作符、資料類型、辨別符、對象、字段、變量、常量等的大小寫風格必須遵循以下規範：

縮進風格

規則：腳本代碼塊采用縮進風格書寫，保證代碼清晰易讀，風格一緻。縮進格數統一為2個空格，但需要關鍵字右對齊或者參數/字段向上對齊的時候例外。

規則：縮進必須使用空格鍵，不允許使用TAB鍵。

規則：同一條語句占用多于一行時，每行的第一個關鍵字應當右對齊，但INSERT...SELECT語句例外。

規則：在INSERT...SELECT語句中，如果需要換行時，應使INSERT INTO部分的字段與SELECT部分的字段一一對應，以增強可讀性。

規則：INSERT INTO語句中，如果需要對每個字段增加注釋，應将每個字段單獨列為一行，并在行尾增加注釋。

空格及換行

規則：不允許把多個短語句寫在一行中，即一行隻寫一條語句。

規則：相對獨立的程式塊之間、變量說明之後必須加空行。

規則：調用函數或過程時，如果參數清單超過120字元，應根據邏輯内容進行換行，或者每個參數占用一行。

規則：雙目運算符、操作符前後應以空格分隔，間隔符之後應以空格分隔。

建議：不同優先級的操作符混合使用時，建議使用括号進行隔離。

建議：SQL語句中存在不同優先級的關系運算符時，建議使用括号。

建立表

規則：腳本中的字段名縮進為2個空格。

規則：腳本中字段名稱、字段類型、DEFAULT關鍵字應左對齊，NULL/NOT NULL應右對齊（也可以認為就是NULL關鍵字左對齊）。

建立過程/函數/包

規則：腳本中的參數縮進為2個空格。

規則：腳本中的參數名稱、資料類型、注釋資訊應左對齊，IN/OUT/IN OUT關鍵字左對齊右對齊皆可。

SQL 開發規範

編碼規範

在代碼中不允許出現任何 DDL 語句

寫 SQL 的時侯一定要使用綁定變量

寫 SQL 的時候一定要給每個字段指定表名做字首

在 sqlmap 中的變量，要用#号，而不要用$符号

請不要寫 select * 這樣的代碼，指定需要的字段名

避免在 where 子句中對字段施加函數

嚴格要求使用正确類型的變量，杜絕 oracle 做隐式類型轉換的情況

全模糊查詢無法使用 INDEX，應當盡可能避免

外連接配接的寫法

表連接配接分頁查詢的使用

Hint 的使用

"<>"、"!="、"not in"、"exsits"和"not exists"的使用規範

其它編寫規範

格式規範

注釋說明

縮進

斷行

常用文法

變量聲明

傳回值

符号*

包的使用

綁定變量

異常處理

事務控制

遊标使用

代碼規模

SQL建議與限制

建議