04.03 存儲過程的創建,以及遊標在存儲過程的使用方法

遊標的作用：

1. 使用遊標可以執行多個不相關的操作.如果希望當產生了結果集後,對結果集中的數據進行多種不相關的數據操作

2. 使用遊標可以提供腳本的可讀性

3. 使用遊標可以建立命令字符串,使用遊標可以傳送表名,或者把變量傳送到參數中,以便建立可以執行的命令字 符串.

注意事項：但是個人認為遊標操作效率不太高，並且使用時要特別小心，使用完後要及時關閉

存儲過程優缺點：

優點：

1. 存儲過程增強了SQL語言的功能和靈活性。存儲過程可以用流控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。

2. 可保證數據的安全性和完整性。

3． 通過存儲過程可以使沒有權限的用戶在控制之下間接地存取數據庫，從而保證數據的安全。

通過存儲過程可以使相關的動作在一起發生，從而可以維護數據庫的完整性。

3. 再運行存儲過程前，數據庫已對其進行了語法和句法分析，並給出了優化執行方案。這種已經編譯好的過程可極大地改善SQL語句的性能。 由於執行SQL語句的大部分工作已經完成，所以存儲過程能以極快的速度執行。

4. 可以降低網絡的通信量, 不需要通過網絡來傳送很多sql語句到數據庫服務器了

5. 使體現企業規則的運算程序放入數據庫服務器中，以便集中控制

當企業規則發生變化時在服務器中改變存儲過程即可，無須修改任何應用程序。企業規則的特點是要經常變化，如果把體現企業規則的運算程序放入應用程序中，則當企業規則發生變化時，就需要修改應用程序工作量非常之大（修改、發行和安裝應用程序）。如果把體現企業規則的 運算放入存儲過程中，則當企業規則發生變化時，只要修改存儲過程就可以了，應用程序無須任何變化。

缺點：

1. 可移植性差

2. 佔用服務器端多的資源，對服務器造成很大的壓力

3. 可讀性和可維護性不好

使用說明：

Create [or replace] procedure 過程名字（參數 …）as

vs_ym_sn_end CHAR(6); --同期終止月份

CURSOR cur_1 IS --定義遊標(簡單的說就是一個可以遍歷的結果集)

SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,SUM(usd_amt)/10000 usd_amt_sn

FROM BGD_AREA_CM_M_BASE_T

WHERE ym >= vs_ym_sn_beg

AND ym <= vs_ym_sn_end

GROUP BY area_code,CMCODE;

BEGIN

--用輸入參數給變量賦初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS TO_DATE 等很常用的函數。

vs_ym_beg := SUBSTR(is_ym,1,6);

vs_ym_end := SUBSTR(is_ym,7,6);

vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,"yyyymm"), -12),"yyyymm");

vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,"yyyymm"), -12),"yyyymm");

--先刪除表中特定條件的數據。

DELETE FROM xxxxxxxxxxx_T WHERE ym = is_ym;

--然後用內置的DBMS_OUTPUT對象的put_line方法打印出影響的記錄行數，其中用到一個系統變量SQL%rowcount

DBMS_OUTPUT.put_line("del上月記錄="||SQL%rowcount||"條");

INSERT INTO xxxxxxxxxxx_T(area_code,ym,CMCODE,rmb_amt,usd_amt)

SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000

FROM BGD_AREA_CM_M_BASE_T

WHERE ym >= vs_ym_beg

AND ym <= vs_ym_end

GROUP BY area_code,CMCODE;

DBMS_OUTPUT.put_line("ins當月記錄="||SQL%rowcount||"條");

--遍歷遊標處理後更新到表。遍歷遊標有幾種方法，用for語句是其中比較直觀的一種。

FOR rec IN cur_1 LOOP

UPDATE xxxxxxxxxxx_T

SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn

WHERE area_code = rec.area_code

AND CMCODE = rec.CMCODE

AND ym = is_ym;

END LOOP;

COMMIT;

--錯誤處理部分。OTHERS表示除了聲明外的任意錯誤。SQLERRM是系統內置變量保存了當前錯誤的詳細信息。

EXCEPTION

WHEN OTHERS THEN

vs_msg := "ERROR IN xxxxxxxxxxx_p("||is_ym||"):"||SUBSTR(SQLERRM,1,500);

ROLLBACK;

--把當前錯誤記錄進日誌表。

INSERT INTO LOG_INFO(proc_name,error_info,op_date)

VALUES("xxxxxxxxxxx_p",vs_msg,SYSDATE);

COMMIT;

RETURN;

END;