Oracle的預存程序編程

什麼是預存程序。

 

是一個可以用編程的方式來操作SQL的集合。

 

預存程序的優點。 執行效率很高，因為預存程序是先行編譯的，即建立時編譯，而SQL語句是執行一次，編譯一次。調用預存程序可以大大減少同資料庫的互動次數。 降低網路通訊量，因為預存程序執行的時候，只需要call預存程序名，不需要傳遞大量的SQL語句。  有利於複用。

預存程序的缺點。 移植性非常差，如果在oracle上寫的預存程序，移植到mysql需要修改。 代碼可讀性差，實現一個簡單的邏輯，代碼會非常長。

預存程序的用途。 造測試資料：可以使用預存程序，往表裡造幾百萬條資料。 資料同步：兩個表之間按照一定的商務邏輯進行資料同步。 資料採礦。

預存程序注意事項。 資料量大的時候（10萬+），一定要做壓力測試，有些預存程序在大資料量的情況下才會出現問題。 如果插入或者更新的次數比較多，為了提高效率，可以執行一萬次，再commit一次。 如果先插入記錄，沒有commit，再對這條記錄進行更新，會引起死結。如果先後對同一筆記錄進行更新，又沒有commit，也會引起死結。因為後一條語句會等待前一條語句提交。如果出現這種情況，則需要一條條commit。 不要忘記在預存程序裡寫commit。

如何寫預存程序。

--建立或者更新預存程序update_user_pcreate or replace procedure update_user_p(param1 in varchar2) is  v_taskName VARCHAR2(20); --定義變數，Oracle類型。  v_i        number(12);  --將User_Advisor_Log表的結果集賦給cur  CURSOR cur IS    SELECT * FROM User_Advisor_Log;  --sql開始標記，以上是定義變數，以下才寫程式begin  DBMS_OUTPUT.PUT_LINE(param1);  v_i := 0;  DBMS_OUTPUT.PUT_LINE('start!');  --遍曆結果集  for cur_result in cur LOOP      begin      v_taskName := cur_result.TASK_NAME; --將結果集賦給變數v_creator，一個語句結束需要分號結尾。          --if語句開始      if v_taskName > 0 then        begin          NULL; --NULL 語句表明什麼事都不做，這句不能刪去，因為PL/SQL體中至少需要有一句;        end;      end if;          --while迴圈      while v_taskName > 0 LOOP        begin          NULL;        end;      end LOOP;          --建議每迴圈一萬次提交一下      v_i := v_i + 1;      if mod(v_i, 10000) = 0 then        commit;      end if;          --有異常輸出，或者在這裡復原    exception      when others then        DBMS_OUTPUT.PUT_LINE('update_user_p has error!');    end;  end LOOP; --迴圈結束  commit;  DBMS_OUTPUT.PUT_LINE('end and commit!');end update_user_p;

一個簡單的造資料存放區過程

--往表裡造40萬資料。create or replace procedure vas_create_acookie_data_p is  v_i number(12);begin  v_i := 0;  while v_i < 400000 LOOP    begin      insert into TableName (GMT_CREATED,         CREATOR,         GMT_MODIFIED,         MODIFIER,         MEMBER_ID)      values        (sysdate, 'sys', sysdate, 'sys', v_i);      v_i := v_i + 1;        end;  end LOOP;  commit;end vas_create_acookie_data_p;

如何執行預存程序。

執行預存程序：call update_user_p('this is param')。在output 裡可以看見DBMS_OUTPUT.PUT_LINE的輸出。

 

如何調試預存程序。

 

在plsql裡編輯預存程序，點擊執行，系統會告訴你，錯誤的行數和原因。並能顯示代碼結構。

另外可以使用DBMS_OUTPUT.PUT_LINE列印異常，注意列印異常時，輸出上下文（如錯誤的taskName）。 

 

 

 

效能測試  用預存程序插入40萬資料用了10秒。  遍曆並判斷40萬條資料用了25秒。  80萬次SQL判斷+40萬次SQL插入=25秒。

其他問題  預存程序執行非常慢，有可能是更新語句引起了死結，也有可能是語句執行慢（需要建索引）。  預存程序編譯非常慢，有可能是當前預存程序正在執行，被鎖住了。（使用DBA帳號解鎖）。