• 存在性檢查

--正確姿勢：
SELECT COUNT(*) FROM dual WHERE EXISTS (SELECT 1 FROM yxxc_gzb);
--不正確姿勢：
SELECT COUNT(*) FROM yxxc_gzb;

• 提防 DDL 提交事務(wù)

DDL 語句的第一步就是 COMMIT,然后才是執(zhí)行 DDL 本身物蝙，無任命令是否執(zhí)行成功院仿，
都已經(jīng)提交。所以不要在事務(wù)中使用 DDL 語句

• 減少對 sysdate 的調(diào)用

sysdate 函數(shù)在經(jīng)常會被使用，但它是函數(shù)熟吏，一定要注意將 sysdate 移出循環(huán)拐揭，先賦予
變量，然后在循環(huán)中引用變量火脉。

• 把靜態(tài)表達(dá)式遷出循環(huán)和 SQL 語句

PROCEDURE show_customers(prefix_in IN VARCHAR2, state_in IN VARCHAR2) IS 
c_state CONSTANT mfe_customers.state%TYPE := upper(state_in);
c_output_prefix CONSTANT VARCHAR2(32767) := to_char(SYSDATE, 'Mon DD, YYYY') || ' ' ||
                                            upper(prefix_in) || ' ' ||
                                            c_state;
CURSOR customers_cur IS
  SELECT last_name, first_name, city
    FROM mfe_customers
   WHERE state = c_state;
BEGIN
  FOR customer_rec IN customers_cur LOOP
    dbms_output.put_line(c_output_prefix || ' ' || customer_rec.first_name || ' ' ||
                         customer_rec.last_name);
  END LOOP;
END show_customers;

• 與NULL值進(jìn)行比較或邏輯運(yùn)算的時候千萬不要使用“=”牵舵，“<>”操作符，要用IS NULL操作符
  （NULL值并不等于其它的所有值倦挂，甚至不等于另外的一個NULL值畸颅，程序代碼應(yīng)該顯式的處理NULL值）

--假設(shè)業(yè)務(wù)：兩個為空的字符相等
DECLARE
  v_first_name  VARCHAR2(100);
  v_second_name VARCHAR2(100);
BEGIN
  v_first_name  := '';
  v_second_name := '';
  --不正確姿勢
  IF v_first_name = v_second_name THEN
    dbms_output.put_line('v_first_name和v_second_name相等');
  ELSE
    dbms_output.put_line('v_first_name和v_second_name不相等');
  END IF;
  --正確姿勢
  IF v_first_name = v_second_name THEN
    dbms_output.put_line('v_first_name和v_second_name相等');
  ELSIF v_first_name IS NULL AND v_second_name IS NULL THEN
    dbms_output.put_line('v_first_name和v_second_name相等');
  ELSE
    dbms_output.put_line('v_first_name和v_second_name不相等');
  END IF;
END;

--IF模版
DECLARE
 v_action VARCHAR2(100);
BEGIN
 IF v_action = 'ADD' THEN
   --增加操作
 ELSIF v_action = 'UPDATE' THEN
   --修改操作
 ELSIF v_action IS NULL THEN
   --處理NULL值
 ELSE 
   --處理其它情況
 ENF IF;
END;

• 避免在聲明部分指定默認(rèn)值，特別這些值是函數(shù)調(diào)用返回的值

塊的異常部分只會處理塊的執(zhí)行部分拋出的異常方援，假如聲明塊中拋出異常没炒，是不會被異常處理部分捕獲的，而會往調(diào)用方拋出犯戏。

• 清理PLSQL塊中的數(shù)據(jù)

比如：動態(tài)游標(biāo)使用完記得關(guān)閉送火，很多情況是出現(xiàn)異常沒關(guān)閉。

• 最好不要在包說明中定義變量先匪，每個對包有執(zhí)行權(quán)限的人都可以看到甚至修改

如果有需要种吸，建議在包體定義變量，并且通過set/get控制對包數(shù)據(jù)的訪問（意味著在過程或函數(shù)中胚鸯，不要直接引用變量骨稿，而是通過get方法）

• 在復(fù)雜的IF ELSIF ELSE 語句中，可以建議使用case語句來替代（不要忘記else語句），增強(qiáng)可讀性坦冠。

• PL/SQL的異常處理機(jī)制

image

• 避免在異常部分中嵌入應(yīng)用程序邏輯
• 避免通過錯誤編碼引用異常形耗，若Oracle沒給該異常命名，自己給它命一個

  • 前：

    
    
    image

  • 后：

    
    
    image
• PL/SQL查詢
  • 把查詢的值寫入記錄中辙浑，即使用%rowtype
  • 只有當(dāng)需要檢索多行數(shù)據(jù)時激涤，才使用游標(biāo)for循環(huán)
• PL/SQL修改
  • 要始終顯式列出INSERT語句中所使用的數(shù)據(jù)庫表的列
  • SQL%屬性總是會話中最近執(zhí)行的隱式游標(biāo)

注意：當(dāng)更新或者刪除那些“本應(yīng)該”存在的數(shù)據(jù)時，要檢查SQL%ROWCOUNT屬性判呕，以驗證所完成動作的正確性倦踢。

SQL%ROWCOUNT：受DML語句影響的數(shù)據(jù)行的數(shù)量
SQL%FOUND：該語句影響至少一行數(shù)據(jù)時，返回TRUE
SQL%NOTFOUND：該語句影響至少一行數(shù)據(jù)時侠草，返回FALSE

• 動態(tài)SQL

  • 把動態(tài)SQL字符串賦給一個變量辱挥，需要時盡可能用占位符，即綁定變量
  • 避免SQL串聯(lián)边涕，防止SQL注入
  • 對于存在動態(tài)SQL語句的程序（模式級別或者包內(nèi)過程）來說晤碘，最好使用AUTHID CURRENT_USER定義為調(diào)用者模式，可最大程度降低風(fēng)險

• 函數(shù)的要點就是能夠返回一個值（標(biāo)量功蜓、集合园爷、記錄），如果通過使用OUT式撼、IN OUT參數(shù)列表返回值的話童社，則函數(shù)的功能用途顯得不明顯了，此時可以考慮使用過程著隆。

如果需要同時返回多項信息扰楼，可采用如下方式：
1、返回一個記錄或集合
2美浦、將函數(shù)變成過程
3灭抑、將獨(dú)立的函數(shù)分解成復(fù)合函數(shù)，并全部返回標(biāo)量值抵代？？

• 對于子程序而言忘嫉，參數(shù)列表應(yīng)該明確地描述程序?qū)崿F(xiàn)其功能時需要的參數(shù)荤牍，以及有可能返回的值。

當(dāng)在一個主過程包含子過程或子函數(shù)時庆冕，如果子過程等需要的參數(shù)包括主過程傳遞進(jìn)來的某些參數(shù)時康吵，
最好在子過程的參數(shù)中也進(jìn)行定義下，而不要說直接拿主過程的參數(shù)访递，
之前寫代碼時一直在考慮是否要定義形參晦嵌，猶豫不決，導(dǎo)致有些子過程有定義，有些則沒有惭载，不太規(guī)范旱函。

• 如果需要往已有的程序增加參數(shù)時，要考慮原有調(diào)用程序能夠不受影響描滔，可使用以下方式：

1棒妨、確保新增的IN參數(shù)都有默認(rèn)值，當(dāng)然含长，如果參數(shù)是OUT或IN OUY的話券腔，則不行
2、增加重載函數(shù)/過程拘泞，這時要注意纷纫，千萬不要將原有函數(shù)/過程的代碼COPY過來，而應(yīng)創(chuàng)建另一個“核心”程序供其調(diào)用

• 當(dāng)一個過程實現(xiàn)邏輯比較復(fù)雜時陪腌，最好對其進(jìn)行功能拆分（逐步求精法辱魁、自頂向下設(shè)計法），使用本地子程序偷厦，使主程序執(zhí)行部分可進(jìn)行調(diào)用商叹，使得邏輯更清晰，把實現(xiàn)細(xì)節(jié)進(jìn)行封裝只泼。

寫代碼時剖笙，可以先進(jìn)行程序接口設(shè)計，將實現(xiàn)邏輯進(jìn)行拆分请唱，跟JAVA的接口設(shè)計類似弥咪，
最后再對其具體實現(xiàn)詳細(xì)編碼，讓編碼者或者他人看到程序執(zhí)行部分時十绑，能夠很好的理解其實現(xiàn)意圖

• 盡可能把更多的業(yè)務(wù)規(guī)則封裝聚至、隱藏在函數(shù)中，供其它過程調(diào)用本橙。

所有軟件項目的一個特性是：永遠(yuǎn)不會變化的是事物一直在變化扳躬，如業(yè)務(wù)需求、數(shù)據(jù)結(jié)構(gòu)或用戶界面等等甚亭。
我們最好能夠編寫出能夠容易適應(yīng)這些變化的代碼贷币。

• 不要在函數(shù)中出現(xiàn)多個出口，遵循“一個入口只能有一個出口”亏狰，另外役纹，在異常處理部分也要保留RETURN語句。
• 返回布爾值的函數(shù)絕對不能返回NULL（確保返回TRUE或者FALSE）暇唾，這點在進(jìn)行條件判斷的時候尤其要注意促脉。

-- 是否包含指定數(shù)字（6或者8）

--不好的函數(shù)：
CREATE OR REPLACE FUNCTION is_contains_special_number(i_str IN VARCHAR2)
  RETURN BOOLEAN IS
BEGIN
  RETURN instr(i_str, '6') > 0 OR instr(i_str, '6') > 0;
END is_contains_special_number;
--當(dāng)傳入NULL時辰斋，輸出contains special number，明顯有問題
BEGIN
  IF NOT is_contains_special_number('') THEN
    dbms_output.put_line('not contains special number');
  ELSE
    dbms_output.put_line('contains special number');
  END IF;
END;
--姿勢換一下瘸味，調(diào)用方作處理宫仗，但治標(biāo)不治本，不能確保所有調(diào)用方都這么處理：
BEGIN
  IF NOT NVL(is_contains_special_number(''),FALSE) THEN
    dbms_output.put_line('not contains special number');
  ELSE
    dbms_output.put_line('contains special number');
  END IF;
END;
--修改函數(shù)（確保返回TRUE或者FALSE）：
CREATE OR REPLACE FUNCTION is_contains_special_number(i_str IN VARCHAR2)
  RETURN BOOLEAN IS
  v_result BOOLEAN;
BEGIN
  IF instr(i_str, '6') > 0 OR instr(i_str, '6') > 0 THEN
    v_result:=TRUE;
  ELSE --當(dāng)i_str為空時硫戈，也返回FALSE
    v_result:=FALSE;
  END IF;
  RETURN v_result;
END is_contains_special_number;

• 包的最佳實踐：

  • 使用程序包組合功能相關(guān)的程序锰什，避免編寫模式別的過程或函數(shù)

  總是從程序包開始，即時此時包中僅僅只有一個程序丁逝，將來也很有可能會有很多程序
  

  • 讓程序包的關(guān)注面范圍小而窄

• 盡可能使用bull colect和forall進(jìn)行批量提取數(shù)據(jù)汁胆，減少上下文切換

  • 關(guān)于bull colect的建議

    • 更多內(nèi)存，空間換時間

    注意：
    PL/SQL集合消耗的內(nèi)存來自于PGA霜幼，而不是SGA嫩码，即內(nèi)存消耗是以每個會話為基礎(chǔ)的。
    假如程序中bull collect需要耗費(fèi)5M內(nèi)存罪既，而并發(fā)連接有1000個铸题，則你的應(yīng)用程序需要耗費(fèi)5G內(nèi)存，這不容忽視琢感。
    

    • LIMIT子句

    當(dāng)使用bull colect時丢间，應(yīng)該要同時使用LIMIT子句，生產(chǎn)環(huán)境的數(shù)據(jù)量可能會大幅度增長驹针。
    可嘗試不同的LIMIT值烘挫，以平衡優(yōu)化的性能和內(nèi)存消耗的關(guān)系。
    

    • 沒有游標(biāo)屬性

    當(dāng)與顯式游標(biāo)和循環(huán)一起使用LIMIT子句時柬甥，不要使用SQL%NOTFOUND和NO_DATA_FOUND等來終止循環(huán)饮六。
    而應(yīng)該要檢查bull colect填充的集合，假如沒有數(shù)據(jù)返回了苛蒲，集合數(shù)量為0卤橄。
    

    • 非字符串索引的集合，集合索引值始終從1開始

  • 關(guān)于forall的建議

    • 異常臂外，考慮添加save exception

    默認(rèn)情況下窟扑，當(dāng)循環(huán)中紅的DML語句出現(xiàn)問題時導(dǎo)致SQL引擎拋出異常時，
    會把這個異常傳回PL/SQL引擎漏健，此時會終止程序辜膝，
    可給forall頭部增加save exception子句，保存執(zhí)行過程中拋出的異常漾肮，并在結(jié)束時返回所有的異常。
    

    • 每個forall語句中茎毁，只可以執(zhí)行一條DML語句

• 緩存實踐（盡可能在最快的內(nèi)存位置克懊，緩存靜態(tài)數(shù)據(jù)）

  備注：
  SGA：數(shù)據(jù)庫實例保留一份忱辅，所有會話均可使用
  PGA：每個會話保留一份
  

  • 包緩存，保存在PGA谭溉，消耗內(nèi)存大

  1墙懂、一般后臺應(yīng)用程序會使用連接池技術(shù)，需要確保的是：緩沖池中每個連接都包含相同的被緩沖數(shù)據(jù)扮念，
  避免前端應(yīng)用程序在不同的會話間切換時损搬，操作不同的數(shù)據(jù)集。
  
  2柜与、應(yīng)該在包體內(nèi)部聲明數(shù)據(jù)結(jié)構(gòu)（私有巧勤，只能在包內(nèi)使用），以便于管理其內(nèi)容弄匕，保證完整性颅悉。
  
  CREATE OR REPLACE PACKAGE BODY emplu2
  IS
     TYPE tab_tabtype IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER;
     loadtab tab_tabtype;
  
     FUNCTION onerow (
        employee_id_in IN employee.employee_id%TYPE
        )
     RETURN employee%ROWTYPE
     IS
     BEGIN
        RETURN loadtab (employee_id_in);
     EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
           RETURN emplu1.onerow (employee_id_in);
     END;
  
  BEGIN
     FOR rec IN (SELECT * FROM employee)
     LOOP
        loadtab (rec.employee_id) := rec;
     END LOOP;
  END;
  

  • 結(jié)果緩存，保存在SGA迁匠，消耗內(nèi)存小

• 管道函數(shù)

• 當(dāng)集合和記錄是OUT或IN OUT參數(shù)時剩瓶，可以考慮使用NOCOPY降低負(fù)載

函數(shù)或過程參數(shù)模式：
1、IN：讀（傳引用）
2城丧、OUT：寫延曙，初始值默認(rèn)null（傳值）
3、IN OUT：讀寫（傳值）

傳值：復(fù)制進(jìn)亡哄，復(fù)制出枝缔。參數(shù)的值會被復(fù)制到應(yīng)用程序的數(shù)據(jù)結(jié)構(gòu)，
傳引用：程序中參數(shù)變量指向值所在內(nèi)存區(qū)域磺平。