條款六十五，試著讓 package 只有少量的內容，只在裡面保存少量的 procedure 與 function。 因為 package 會在第一次叫用時被載入進記憶體中，所以為了最佳化記憶體的耗費，與載入的時間，我們需要盡可能的將 package 切割乾淨，職責劃分乾淨，確保 package 內只有適當且少量的內容。

條款五十八，總是使用字串變數去執行 Dynamic SQL。 像是下面這段程式直接將要執行的字串帶入執行 Dynamic SQL 就不被建議。 DECLARE l_empno emp.empno%TYPE := 4711; BEGIN EXECUTE IMMEDIATE 'DELETE FROM emp WHERE epno = :p_empno' USING l_empno; END; 建議的做法是用字串變數儲存要執行的 Dynamic SQL 語法，在將字串變數帶入動態運行。這樣的做法在串接複雜語句時會比較容易，在例外處理上若有需要也可以直接將變數拿來使用。 DECLARE l_empno emp.empno%TYPE := 4711; l_sql VARCHAR2(32767); BEGIN l_sql := 'DELETE FROM emp WHERE epno = :p_empno'; EXECUTE IMMEDIATE l_sql USING l_empno; EXCEPTION WHEN others THEN DBMS_OUTPUT.PUT_LINE(l_sql); END;

條款五十七，避免使用 Oracle 預先定義好的例外。 像是下面這段程式，在程式中主動拋出了 Oracle 預先定義好的例外就不是建議的做法。 BEGIN RAISE NO_DATA_FOUND; END; 應該要避免直接使用 Oracle 預先定義好的例外。 DECLARE my_exception EXCEPTION; BEGIN RAISE my_exception; END;

條款五十六，避免未處理的例外。 像是下面這段程式，當沒資料或是資料過多時 Select into 就會丟出例外。 CREATE OR REPLACE PACKAGE BODY department_api IS FUNCTION name_by_id (in_id IN departments.department_id%TYPE) RETURN departments.department_name%TYPE IS l_department_name departments.department_name%TYPE; BEGIN SELECT department_name INTO l_department_name FROM departments WHERE department_id = in_id; RETURN l_department_name; END name_by_id; END department_api; 建議是要確保程式不會有未處理的例外，像是上面這樣的程式就要加處理 NO_DATA_FOUND 與 TOO_MANY_ROWS。 CREATE OR REPLACE PACKAGE BODY department_api IS FUNCTION name_by_id (in_id IN departments.department_id%TYPE) RETURN departments.department_name%TYPE IS l_department_name departments.department_name%TYPE; BEGIN SELECT department_name INTO l_department_name FROM departments WHERE department_id = in_id; RETURN l_department_name; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN TOO_MANY_ROWS THEN RAISE; END name_by_id; END department_api;

條款五十五，避免直接使用 RAISE_APPLICATION_ERROR hard code 帶入 error number 與 error message 拋出錯誤。 像是下面這樣的程式，程式中直接使用 RAISE_APPLICATION_ERROR hard code 帶入 error number 與 error message 拋出錯誤，這樣 error number 與 error message 會散在許多地方，且會有重複撰寫的可能性，並不是建議採用。 BEGIN raise_application_error(-20501,'Invalid employee_id'); END; 建議將之封裝，集中撰寫，不僅修改方便，維護起來也方便。 BEGIN err_up.raise(in_error => err.co_invalid_employee_id); END;

條款五十三，避免單獨使用 WHEN OTHERS 去處理例外。 像是下面這樣的程式，使用 WHEN OTHERS 搭配 IF 條件式與 SQLCODE 去處理例外，就是不建議的作法。 ... EXCEPTION WHEN OTHERS THEN IF SQLCODE = -1 THEN update_instead (...); ELSE err.log; RAISE; END IF; ... 如果明確使用例外的名稱去處理例外，可以免去不必要的 IF 判斷，及 SQLCODE 的使用。 ... EXCEPTION WHEN DUP_VAL_ON_INDEX THEN update_instead (...); WHEN OTHERS THEN err.log; RAISE; ...

條款五十二，不要使用自定義錯誤去重新定義 Oracle 預先定義的錯誤。 像是下面這樣的程式，使用自定義錯誤重新定義了 no_data_found 錯誤，就是不建議的作法。 DECLARE no_data_found EXCEPTION; … BEGIN ... EXCEPTION WHEN no_data_found THEN sys.dbms_output.put_line(co_no_data_found); END; 比較好的作法應該是反思是否需要重新定義 Oracle 預先定義的錯誤、是否應該定義的是不同的錯誤。 DECLARE empty_value EXCEPTION; ... BEGIN ... EXCEPTION WHEN empty_value THEN sys.dbms_output.put_line(co_empty_value); WHEN no_data_found THEN sys.dbms_output.put_line(co_no_data_found); END;

條款五十一，不要使用 Error Number 去處理 Unnamed Exceptions。 像是下面這樣的程式，直接使用 Error Number -2291 去處理 Unnamed Exception 就不是建議的作法。 BEGIN ... EXCEPTION WHEN OTHERS THEN IF SQLCODE = -2291 THEN ... END IF; END; 比較好的作法是透過 pragma exception_init 將未命名的內部錯誤做個命名，然後直接用這個命名去攔截對應的錯誤做對應的處理。 DECLARE e_parent_missing EXCEPTION; PRAGMA EXCEPTION_INIT(e_parent_missing,-2291); ... BEGIN ... EXCEPTION WHEN e_parent_missing THEN ... END;

條款五十，避免 hard-codeed FOR loop 的上下邊界值。 像是下面這段程式 hard-coded 了 FOR loop 的上下邊界值，這樣的撰寫預期上下邊界值是不會被變動的，且後續再看這段程式可能也會不理解為何上下邊界值會帶這樣的值，程式的維護性上會比較差。 BEGIN <<for_loop>> FOR i IN 1..5 LOOP sys.dbms_output.put_line(i); END LOOP for_loop; END; 比較好的做法是不要將上下邊界值 hard-coded，可能用常數設定其值，並給予較具意義的常數名稱，讓程式的可讀性與可維護性更好。 DECLARE co_lower_bound CONSTANT SIMPLE_INTEGER := 1; co_upper_bound CONSTANT SIMPLE_INTEGER := 5; BEGIN <<for_loop>> FOR i IN co_lower_bound..co_upper_bound LOOP sys.dbms_output.put_line(i); END LOOP for_loop; END;

條款四十九，避免未使用的 FOR loop 索引。 像是下面這樣的程式使用了 numeric FOR loop，卻未使用 FOR loop 的索引，導致程式碼變得更為複雜沒有效率。 DECLARE ... BEGIN l_row := co_lower_bound; l_value := co_first_value; <<for_loop>> FOR i IN co_lower_bound .. co_upper_bound LOOP sys.dbms_output.put_line(l_row || co_delimiter || l_value); l_row := l_row + co_row_incr; l_value := l_value + co_value_incr; END LOOP for_loop; END; 比較好的作法是要看看是否能透過索引撰寫出更適合的程式，如果不行則要反思這邊使用 numeric FOR loop 是否合適。 DECLARE ... BEGIN <<for_loop>> FOR i IN co_lower_bound .. co_upper_bound LOOP sys.dbms_output.put_line(i || co_delimiter || to_char(co_first_value + i * co_value_incr)); END LOOP for_loop; END;

條款四十八，不要遍巡 CURSOR 去確認是否含有資料。 像是下面這段程式片巡走訪 CURSOR 以確認是否含有資料，這樣並不是很好的寫法。 DECLARE l_employee_found BOOLEAN := FALSE; … BEGIN <<check_employees>> FOR r_employee IN c_employee LOOP l_employee_found := TRUE; END LOOP check_employees; END; 比較好的作法應該像是下面這段程式這樣，將 CURSOR 開啟，嘗試 Fetch 一筆資料，利用 CURSOR 的 %FOUND 去判斷是否含有資料，最後將 CURSOR 關閉。 DECLARE l_employee_found BOOLEAN := FALSE; … BEGIN OPEN c_employee; FETCH c_employee INTO r_employee; l_employee_found := c_employee%FOUND; CLOSE c_emplyoee; END;

條款四十七，嘗試將 EXIT WHEN 搭配 label 使用。 像是下面這樣的程式，雖然使用了 EXIT WHEN 跳離迴圈，但是未搭配 label 使用，因此在巢狀迴圈下得一層一層的跳離。 BEGIN ... <<outerloop>> LOOP ... <<innerloop>> LOOP ... EXIT WHEN l_innerlp = co_exit_value; END LOOP innerloop; EXIT WHEN l_innerlp = co_exit_value; END LOOP outerloop; END; 如果將 EXIT WHEN 搭配 label 使用可以直接在巢狀迴圈下跳離，這樣程式會比較簡短、清晰、且易於維護。 BEGIN ... <<outerloop>> LOOP ... <<innerloop>> LOOP ... EXIT outerloop WHEN l_innerlp = 3; END LOOP innerloop; END LOOP outerloop; END;

條款四十六，總是使用 EXIT WHILE loop 去跳離迴圈，不要使用 IF…EXIT。 像是下面這樣的程式使用 IF 判斷要跳離迴圈的條件是否成立，成立的話使用 EXIT 跳離迴圈。如果 IF 判斷只是單純的用來處裡跳離迴圈的條件判斷，那這樣的撰寫方式就不是那麼洽當，程式碼變得比較冗餘，且不好維護。 BEGIN <<process_employees>> LOOP ... IF ... THEN EXIT process_employees; END IF; ... END LOOP process_employees; END; 比較好的做法是用 EXIT WHEN，將跳離迴圈的判斷直接寫在 EXIT WHEN 後面，這樣程式會比較簡短、清晰、且易於維護。 BEGIN <<process_employees>> LOOP ... EXIT process_employees WHEN (...); END LOOP process_employees; END;

條款四十五，避免使用 EXIT 去跳離迴圈，除非使用的是 basic loop。 像是下面這樣的程式，使用了 EXIT 去跳離迴圈，但是非 basic loop 都有迴圈的邊界條件可以設定，可以做到一樣的事情，所以這樣的寫法並不是很好。 ... i := co_min_value; <<while_loop>> WHILE (i <= co_max_value) LOOP i := i + co_increment; EXIT while_loop WHEN i > co_max_value; END LOOP while_loop; <<for_loop>> FOR i IN co_min_value..co_max_value LOOP ... EXIT for_loop WHEN i = co_max_value; END LOOP for_loop; <<process_employees>> FOR r_employee IN (SELECT last_name FROM employees) LOOP ... EXIT process_employees; END LOOP process_employees; ... 若使用非 basic loop，建議使用邊界條件來跳離迴圈。而 basic loop 因為沒有邊界條件的設定，因此也只能使用 EXIT 來跳離迴圈。

條款四十四，總是使用 WHILE loop 去處理 loose array。 像是下面這樣的程式用 FOR loop 去遍巡處理 loose array，這不是被建議使用的寫法，雖然大部分的狀況下可以正常運行，但是當 loose array 的內容被刪除時，這樣的處理會因為 null 元素而運行錯誤。 DECLARE ... l_index PLS_INTEGER; BEGIN ... IF t_employees IS NOT NULL THEN <<process_employees>> FOR i IN 1..t_employees.COUNT() LOOP ... END LOOP process_employees; END IF; END; 建議使用 WHILE loop 遍巡處理 loose array，才不會因為 Null 元素導致錯誤。 DECLARE ... l_index PLS_INTEGER; BEGIN l_index := t_employees.FIRST(); <<process_employees>> WHILE l_index IS NOT NULL LOOP ... l_index := t_employees.NEXT(l_index); END LOOP process_employees; END;

條款四十三，遍巡 dense array 時，建議使用 1 當做 lower boundary，使用 COUNT() 當做 upper boundary。 像是下面這樣的程式，使用了 FIRST() 與 LAST() 做為遍巡走訪的條件，dense array 不為空時可以正常運作，但當 dense array 為空時則會發生錯誤。 DECLARE t_employees t_employee_type := t_employee_type(); BEGIN <<process_employees>> FOR i IN t_employees.FIRST()..t_employees.LAST() LOOP … END LOOP process_employees; END; 簡單的解決方式可以加判斷 dense array 是否為空。 DECLARE t_employees t_employee_type := t_employee_type(); BEGIN <<process_employees>> IF t_employees IS NOT EMPTY THEN FOR i IN t_employees.FIRST()..t_employees.LAST() LOOP … END LOOP process_employees; END IF; END; 但建議的方式是改以 1 與 COUNT() 做為遍巡走訪的條件。

條款四十二，總是使用 NUMERIC FOR loop 去處理 dense array。 像是下面這樣的程式： ... BEGIN <<process_employees>> LOOP EXIT process_employees WHEN i > t_employees.COUNT(); … i := i + 1; END LOOP process_employees; END; 可以像下面這樣改寫，程式碼的維護性會比較好。 ... BEGIN <<process_employees>> FOR i IN 1..t_employees.COUNT() LOOP … END LOOP process_employees; END;

條款四十一，總是使用 CURSOR for loop，除非使用 Bulk operations。 像是下面這樣的程式： ... BEGIN <<read_employees>> LOOP FETCH c_employees INTO r_employee; EXIT read_employees WHEN c_employees%NOTFOUND; … END LOOP read_employees; CLOSE c_employees; END; 可以像下面這樣改寫，程式碼的維護性會比較好。 ... BEGIN <<read_employees>> FOR r_employee IN c_employee LOOP … END LOOP read_employees; END;

條款四十，如果程式中有 loop，嘗試使用 label 讓他的區塊範圍更為清楚。 像是下面這樣的程式： BEGIN FOR r_employee IN (SELECT * FROM emp) LOOP … END LOOP; END; 可以像下面這樣改寫，在 loop 的前面加上 Label，然後在 End 後加上 Label Name。 BEGIN <<process_employees>> FOR r_employee IN (SELECT * FROM emp) LOOP … END LOOP process_employees; END; 這樣程式碼的區塊範圍清楚許多。

條款三十八，如果 NVL 第二或第三個參數是 function 呼叫或是 select 語句，使用 CASE 取代 NVL2。 這是因為 NVL2 會先把每個可能的結果都先取出，儘管是根本不會出的結果，所以這樣會有不必要的 overhead。 SELECT NVL2(dummy, function_call(), function_call()) FROM dual; 建議的做法是用 CASE 取代 NVL2，以避開這樣的問題。 SELECT CASE WHEN dummy IS NULL THEN function_call() ELSE function_call() END FROM dual;

條款三十七，如果 NVL 第二個參數是 function 呼叫或是 select 語句，使用 COALESCE 取代 NVL。 這是因為 NVL 會先把每個可能的結果都先取出，儘管是根本不會出的結果，所以這樣會有不必要的 overhead。 SELECT NVL(dummy, function_call()) FROM dual; 建議的做法是用 COALESCE 取代 NVL，以避開這樣的問題。 SELECT COALESCE(dummy, function_call()) FROM dual;

條款三十六，嘗試使用 CASE 而不要用 DECODE。 用 DECODE 的可讀性較低，不易閱讀。 BEGIN SELECT DECODE(dummy, 'A', 1 , 'B', 2 , 'C', 3 , 'D', 4 , 'E', 5 , 'F', 6 , 7) INTO l_result FROM dual; ... 改用 CASE 撰寫雖然程式變多，但閱讀起來相對會比較清楚。 BEGIN l_result := CASE dummy WHEN 'A' THEN 1 WHEN 'B' THEN 2 WHEN 'C' THEN 3 WHEN 'D' THEN 4 WHEN 'E' THEN 5 WHEN 'F' THEN 6 ELSE 7 END; ...

條款三十五，嘗試使用 CASE 語句取代多行 ELSIF 語句。 像是下面這樣的語法，使用了過多的 ELSIF，可讀性沒有那麼好。 IF l_color = 'red' THEN ... ELSIF l_color = 'blue' THEN ... ELSIF l_color = 'black' THEN ... 若換用 CASE 來撰寫，可讀性會好很多。 CASE l_color WHEN 'red' THEN ... WHEN 'blue' THEN ... WHEN 'black' THEN ... END

條款三十四，避免在 SQL 語句運行與 implicit cursor 中間使用 procedure 或是 function。 像是下面的例子，這邊先刪除了一些資料，後續要用 SQL%ROWCOUNT 去判斷刪除的筆數，但中間卻調用了其它 function，以致於 SQL%ROWCOUNT 的值不如我們的預期。 CREATE PROCEDURE remove_emp_and_process (in_id IN emp.empno%TYPE) AS BEGIN DELETE FROM emp WHERE empno = in_id RETURNING deptno INTO l_deptno; process_department (...); IF SQL%ROWCOUNT > 1 THEN -- Too many rows deleted! Rollback and recover... ROLLBACK; END IF; END remove_emp_and_process; 所以我們必須避免在 SQL 語句運行與 implicit cursor 中間使用 procedure 或是 function。

條款三十三，總是關閉開啟的游標。 像是下面這樣將游標開啟後並未揪游標關閉，使用的資源不會自動釋放。 CREATE PROCEDURE not_close_cursor (out_count OUT INTEGER) AS CURSOR c1 IS SELECT COUNT (*) FROM all_users; BEGIN out_count := 0; OPEN c1; FETCH c1 INTO out_count; END not_close_cursor; ... 應該要自己將游標在適當的時機點關閉，使用的資源才會被釋放。 CREATE PROCEDURE close_cursor (out_count OUT INTEGER) AS CURSOR c1 IS SELECT COUNT (*) FROM all_users; BEGIN out_count := 0; OPEN c1; FETCH c1 INTO out_count; CLOSE c1 END close_cursor;

條款三十二，當使用 BULK 與 LIMIT 操作時，避免直接在後面用 %NOTFOUND 判斷是否有資料處理，應改用 COUNT() 判斷。 像是下面這樣的撰寫方式，迴圈內每次會處理 5 筆資料，假設今天總資料量為 14，那第三次處理時因為剩餘的筆數 4 小於 LIMIT 設定的筆數 5，%NOTFOUND 會為 true 跳離，剩餘的資料就都沒有跑到。 -- This example will only show 10 of 14 employees DECLARE TYPE t_employee_type IS TABLE OF emp%ROWTYPE; t_employees t_employee_type; CURSOR c_emp IS SELECT * FROM emp ORDER BY empno; BEGIN OPEN c_emp; <<process_emp>> LOOP FETCH c_emp BULK COLLECT INTO t_employees LIMIT 5; EXIT process_emp WHEN c_emp%NOTFOUND; <<display_emp>> FOR i IN 1.

條款三十一，Always use %NOTFOUND instead of NOT %FOUND to check whether a cursor was successful。 不要用 Not %FOUND 去撰寫判斷邏輯。 LOOP FETCH c_employees INTO r_employee; EXIT WHEN NOT c_employees%FOUND; ... END LOOP; 取而代之的是要用 %NOTFOUND 去撰寫判斷邏輯。 LOOP FETCH c_employees INTO r_employee; EXIT WHEN c_employees%NOTFOUND; ... END LOOP; 這樣可讀性會比較好。

條款二十九，Try to use anchored records as targets for your cursors。 像是下面這邊的程式就宣告了幾個變數，開啟 Cursor 後遍巡，將資料塞到變數後再進一步處理。 DECLARE CURSOR c_user IS SELECT user_id, firstname, lastname FROM user; l_user_id user.user_id%TYPE; l_firstname user.firstname%TYPE; l_lastname user.lastname%TYPE; BEGIN OPEN c_user; FETCH c_user INTO l_user_id, l_firstname, l_lastname; WHILE c_user%FOUND LOOP FETCH c_user INTO l_user_id, l_firstname, l_lastname; END LOOP; CLOSE c_user; END; 如果改用 cursor-anchored records 去實作，就可以省去一些不必要的變數宣告。 DECLARE CURSOR c_user IS SELECT user_id, firstname, lastname FROM user; r_user c_user%ROWTYPE; BEGIN OPEN c_user; FETCH c_user INTO r_user; <<process_user>> WHILE c_user%FOUND LOOP FETCH c_user INTO r_user; END LOOP process_user; CLOSE c_user; END;

條款二十八，如果 Oracle 版本支援，請使用 ANSI-Join。 因為 ANSI-Join 使用上比較沒有那麼多限制，支援 outer join，且能將 join 條件與 query 條件分開。 使用上會像是下面這樣： SELECT a.pid ,a.name ,a.birthday ,b.country FROM person a JOIN country b ON (a.cid = b.cid) WHERE …

條款二十七，當使用多個來源做搜尋時，總是使用資料表別名。 像是下面這樣，資料從兩個資料表來，如果不給資料表取別名我們會無法輕易識別資料是從哪個資料表來的。 SELECT a.pid ,a.name ,a.birthday ,b.country FROM person a JOIN country b ON (a.cid = b.cid) WHERE …

條款二十六，運行插入命令時總是指定塞入的欄位。 像是下面這樣不指定要塞入欄位的寫法，塞入的動作會對資料表欄位的順序有所依賴，當欄位順序一有變動就會產生不如預期的結果，而且問題發生時不是很容易可以被偵測出來。 INSERT INTO messages VALUES (l_mess_no ,l_mess_typ ,l_mess_text ); 如果在塞入時明確的指定塞入的欄位，就能避開這樣的問題。 INSERT INTO messages (mess_no ,mess_typ ,mess_text ) VALUES (l_mess_no ,l_mess_typ ,l_mess_text );

條款二十五，避免使用 LONG 與 LONG RAW 型態。 LONG 與 LONG RAW 型態已經過時，留著只是為了向前相容，不建議拿來使用，應考慮用 NCLOB、BLOB、BFILE 去替。

條款二十四，當值只有兩種狀態時，試著使用 Boolean 型態。 像是如果要表達的是是否為較大的數值，如果沒有特別的理由，那就不該使用數值表示，因為數值代表的意義沒有 Boolean 型態來的明確。 DECLARE v_IsBigger number(1) := 1; BEGIN DBMS_OUTPUT.PUT_LINE(CASE WHEN v_IsBigger = 1 THEN 'True' ELSE 'False' END); END; 若用 Boolean 型態表達會比較清楚。 DECLARE v_IsBigger BOOLEAN := true; BEGIN DBMS_OUTPUT.PUT_LINE(CASE WHEN v_IsBigger THEN 'True' ELSE 'False' END); END;

條款二十三，使用 Varchar2 時總是定義 Char Semantic。 預設未定義時有可能指定的是 Byte，也有可能是 Char，端看 NLS_LENGTH_SEMANTICS 參數的設定，預設是 Byte。 DECLARE v_str varchar(200); BEGIN ... END; 但通常我們指定字串應該看的是字元數，建議是將其指定用 Char。 DECLARE v_str varchar(200 char); BEGIN ... END;

條款二十二，不要用空字串去代替 Null 值。 在 Oracle 這邊不論是對的 Varchar 或是 Varchar2 型態賦予空字串，Oracle 都會將其視為 Null，所以這邊不建議將其設為空字串。 DECLARE v_str varchar2(4000) := ''; BEGIN DBMS_OUTPUT.PUT_LINE(CASE WHEN v_str is null THEN 'null' ELSE 'not null' END); END; 而是應該像下面，沒特別塞值時預設就是 Null。 DECLARE v_str varchar2(4000); BEGIN DBMS_OUTPUT.PUT_LINE(CASE WHEN v_str is null THEN 'null' ELSE 'not null' END); END;

條款二十一，避免使用 Varchar 型態。 Varchar 型態在工業標準上是可以儲存空字串的，但在 Oracle 這邊並未遵循這樣的標準，當將空字串存放至 Varchar 型態，Oracle 這邊會將空字串變為 null 值，就跟 Varchar2 型態是一樣的，但難保哪天會改回遵循工業標準。 DECLARE v_str varchar(4000); BEGIN … END; 因此在 Oracle 這邊建議不要使用 Varchar 型態，建議使用 Varchar2 型態。 DECLARE v_str varchar2(4000); BEGIN … END;

條款二十，避免使用 Char。 Oracle 的 Char 型態在宣告時需指定大小，宣告多大就佔多大。像是下面這邊筆者宣告了 4000 的 char，則該變數即佔了 4000，儘管這邊只賦予了短短的幾個字元進去。 DECLARE v_str char(4000) := 'test'; BEGIN DBMS_OUTPUT.PUT_LINE(Length(v_str)); END; 取而代之的是應該改用 varchar2 型態。 DECLARE v_str varchar2(4000) := 'test'; BEGIN DBMS_OUTPUT.PUT_LINE(Length(v_str)); END;

條款十九，用 PLS_INTEGER 去表示整數。 如果要宣告整數，不要用 Number 型態去宣告。 DECLARE v_number number(38, 0); BEGIN ... END; 建議使用 PLS_INTEGER，因為使用 PLS_INTEGER 型態使用的記憶體會比較少，且效能上會快上三倍。 DECLARE v_number PLS_INTEGER; BEGIN ... END;

條款十八，避免在宣告 Number 型態的變數或是 SubType 時未設定整數位數。 像是下面這樣，變數宣告時未指定整數位數，預設精確度為 38 位。 DECLARE v_number number; BEGIN ... END; 如果使用上已經知道明確位數，那建議在宣告時還是明確的指定。 DECLARE v_number number(9, 2); BEGIN ... END;

條款十五是說在變數宣告時，變數的名稱不要加上雙引號。 像是下面這樣，宣告的變數加上雙引號是合法的。 DECLARE "v_str" VARCHAR2(30) ; BEGIN … END main; 但不建議這樣宣告，建議還是不要加上雙引號。 DECLARE v_str VARCHAR2(30) ; BEGIN … END main;

條款十四是說不要去覆寫變數。 像是下面這樣外層與內層宣告了一樣名稱的變數，是不建議的寫法。 <<main>> DECLARE v_str VARCHAR2(30); BEGIN <<sub>> DECLARE v_str VARCHAR2(4000) ; BEGIN … END sub; END main; 建議使用上還是應該要將變數名稱錯開。

條款十三是說要避免在變數宣告的同時呼叫 function 去初始變數。 DECLARE l_company_name VARCHAR2(30) := util_pck.get_company_name(in_id => 47); BEGIN … END; 因為在變數宣告的地方呼叫 function 去初始變數，function 發生例外時是無法攔截處理的。 因此要像下面這樣將宣告與初始拆開處理。 DECLARE v_str VARCHAR2(30); BEGIN <<init>> BEGIN v_str := util_pck.get_company_name(inId => 47); EXCEPTION WHEN VALUE_ERROR THEN ... END init; END;

條款十二是在說當判斷變數是否為 null 時，不要像下面這樣使用 = 去判斷。 DECLARE v_str VARCHAR2(30); BEGIN if v_str = null then … end if; END; 因為 null 不等於任何東西，即使是 null 也不等於 null。 {% img /images/posts/PLSQLCopRule12/1.png %} 正確的方式應該是用 is 或 is not 去做判斷。 DECLARE v_str VARCHAR2(30); BEGIN if v_str is null then … end if; END;

條款十一是說不要將變數初始為 NULL。 DECLARE v_str VARCHAR2(30) := null; BEGIN ... END; 因為預設就是初始為 Null。 DECLARE v_str VARCHAR2(30); BEGIN ... END;

條款十是在說如果有些常用的型態使用，建議將它設成 SubType，像是下面這邊 VARCHAR2(4000) 程式中如果常用的話，就可以將它設成 SubType。 DECLARE v_str VARCHAR2(4000); BEGIN … END; 這邊將它設成名為 STRING_MAX 的 SubType，後續可直接拿來宣告使用。 CREATE OR REPLACE PACKAGE PKG_SUBTYPE AS SUBTYPE STRING_MAX IS VARCHAR2(4000); END PKG_SUBTYPE; DECLARE v_str PKG_SUBTYPE.STRING_MAX; BEGIN ... END;

條款九主要是在描述型態的定義要集中一處放置，以 Oracle 來說型態的定義可以放置在 DB 的 Types 下。 {% img /images/posts/PLSQLCopRule9/1.png %} 也可以建造個 Package 集中放置，這邊建議是挑選一個地方放置即可，避免有些型態的宣告放置在 Types 下，而有的在 Package 下。 選用上要注意 Oracle 內的型態有的只能在 Database 下使用，無法在 Package 下使用，像是 Object 型態。

條款八，使用 anchored declarations。 像是下面這樣的程式： DECLARE v_empName VARCHAR2(10); BEGIN … END; 預期要帶入的是 emp table 的 ename，這在 emp table 的 schema 中是其實是已經定義好的。如果像上面這樣另行宣告可能會不小心設錯，或是 schema 修改時會很麻煩。 若是使用 anchored declarations，型態會直接參照 schema 的定義，免去像這樣的困擾。 DECLARE v_empName emp.ename%TYPE; BEGIN … END;

條款五，避免在程式中直接使用字串。 像是下面這樣的程式： SET SERVEROUTPUT ON Begin DBMS_OUTPUT.put_line('Root Account:' || 'Admin'); End; 可以像下面這樣改寫，建立一個 Package 統一存放常數字串，呼叫端改透過 Package 叫用。 SET SERVEROUTPUT ON CREATE OR REPLACE PACKAGE CONST IS ROOT_ACCOUNT CONSTANT varchar(50) := 'Admin'; END CONST; / Begin DBMS_OUTPUT.put_line('Root Account:' || CONST.ROOT_ACCOUNT); End; 這樣常數字串的宣告會集中在 Package 內，修改上也比較方便。

條款二，如果程式中有迴圈區塊，為其加上 label 讓他的區塊範圍更為清楚。 像是下面這樣的程式： SET SERVEROUTPUT ON DECLARE i PLS_INTEGER; Begin FOR i IN 1..10 LOOP DBMS_OUTPUT.put_line('i =' || i); End LOOP; End; 可以像下面這樣改寫，在 For…Loop 前面加上 Label，然後在 End 後加上 Label Name。 SET SERVEROUTPUT ON DECLARE i PLS_INTEGER; Begin <<Print1To10>> FOR i IN 1..10 LOOP DBMS_OUTPUT.put_line('i =' || i); End LOOP Print1To10; End; 改完程式碼的區塊範圍清楚了許多。

條款一，如果程式中有子 block，嘗試使用 label 讓他的區塊範圍更為清楚。 像是下面這樣的程式： SET SERVEROUTPUT ON Begin DBMS_OUTPUT.put_line('Start'); Begin DBMS_OUTPUT.put_line('Processing...'); End; DBMS_OUTPUT.put_line('End'); End; 可以像下面這樣改寫，在子 Block 的 Begin 前面加上 Label，然後在 End 後加上 Label Name。 SET SERVEROUTPUT ON Begin DBMS_OUTPUT.put_line('Start'); <<Processing>> Begin DBMS_OUTPUT.put_line('Processing...'); End Processing; DBMS_OUTPUT.put_line('End'); End; 改完程式碼的區塊範圍清楚了許多。