什么是存儲過程？

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

存儲過程的優點？

• 執行效率很高，因為存儲過程是預編譯的，即創建時編譯，而SQL語句是執行一次，編譯一次。調用存儲過程可以大大減少同數據庫的交互次數。
• 降低網絡通信量，因為存儲過程執行的時候，只需要call存儲過程名，不需要傳遞大量的SQL語句。 
• 有利于復用。

存儲過程的缺點？

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

存儲過程的用途？

• 造測試數據：可以使用存儲過程，往表里造幾百萬條數據。
• 數據同步：兩個表之間按照一定的業務邏輯進行數據同步。
• 數據挖掘。

存儲過程注意事項？

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

如何寫存儲過程？

Sql代碼  

1. --創建或者更新存儲過程update_user_p  
2. create or replace procedure update_user_p(param1 in varchar2) is  
3.   v_taskName VARCHAR2(20); --定義變量，Oracle類型。  
4.   v_i        number(12);  
5.   --將User_Advisor_Log表的結果集賦給cur  
6.   CURSOR cur IS  
7.     SELECT * FROM User_Advisor_Log;  
8.   --sql開始標記，以上是定義變量，以下才寫程序  
9. begin  
10.   DBMS_OUTPUT.PUT_LINE(param1);  
11.   v_i := 0;  
12.   DBMS_OUTPUT.PUT_LINE('start!');  
13.   --遍歷結果集  
14.   for cur_result in cur LOOP  
15.     
16.     begin  
17.       v_taskName := cur_result.TASK_NAME; --將結果集賦給變量v_creator，一個語句結束需要分號結尾。  
18.       
19.       --if語句開始  
20.       if v_taskName > 0 then  
21.         begin  
22.           NULL; --NULL 語句表明什么事都不做，這句不能刪去，因為PL/SQL體中至少需要有一句;  
23.         end;  
24.       end if;  
25.       
26.       --while循環  
27.       while v_taskName > 0 LOOP  
28.         begin  
29.           NULL;  
30.         end;  
31.       end LOOP;  
32.       
33.       --建議每循環一萬次提交一下  
34.       v_i := v_i + 1;  
35.       if mod(v_i, 10000) = 0 then  
36.         commit;  
37.       end if;  
38.       
39.       --有異常輸出，或者在這里回滾  
40.     exception  
41.       when others then  
42.         DBMS_OUTPUT.PUT_LINE('update_user_p has error!');  
43.     end;  
44.   end LOOP; --循環結束  
45.   commit;  
46.   DBMS_OUTPUT.PUT_LINE('end and commit!');  
47. end update_user_p;  

一個簡單的造數據存儲過程

Sql代碼  

1. --往表里造40萬數據。  
2. create or replace procedure vas_create_acookie_data_p is  
3.   v_i number(12);  
4.   
5. begin  
6.   v_i := 0;  
7.   while v_i < 400000 LOOP  
8.     begin  
9.       insert into TableName (GMT_CREATED,  
10.          CREATOR,  
11.          GMT_MODIFIED,  
12.          MODIFIER,  
13.          MEMBER_ID)  
14.       values  
15.         (sysdate, 'sys', sysdate, 'sys', v_i);  
16.       v_i := v_i + 1;  
17.       
18.     end;  
19.   end LOOP;  
20.   commit;  
21. 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帳號解鎖）。