什么是存儲(chǔ)過程？

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

存儲(chǔ)過程的優(yōu)點(diǎn)？

• 執(zhí)行效率很高，因?yàn)榇鎯?chǔ)過程是預(yù)編譯的，即創(chuàng)建時(shí)編譯，而SQL語句是執(zhí)行一次，編譯一次。調(diào)用存儲(chǔ)過程可以大大減少同數(shù)據(jù)庫的交互次數(shù)。
• 降低網(wǎng)絡(luò)通信量，因?yàn)榇鎯?chǔ)過程執(zhí)行的時(shí)候，只需要call存儲(chǔ)過程名，不需要傳遞大量的SQL語句。 
• 有利于復(fù)用。

存儲(chǔ)過程的缺點(diǎn)？

• 移植性非常差，如果在oracle上寫的存儲(chǔ)過程，移植到mysql需要修改。
• 代碼可讀性差，實(shí)現(xiàn)一個(gè)簡單的邏輯，代碼會(huì)非常長。

存儲(chǔ)過程的用途？

• 造測試數(shù)據(jù)：可以使用存儲(chǔ)過程，往表里造幾百萬條數(shù)據(jù)。
• 數(shù)據(jù)同步：兩個(gè)表之間按照一定的業(yè)務(wù)邏輯進(jìn)行數(shù)據(jù)同步。
• 數(shù)據(jù)挖掘。

存儲(chǔ)過程注意事項(xiàng)？

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

如何寫存儲(chǔ)過程？

Sql代碼  

1. --創(chuàng)建或者更新存儲(chǔ)過程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表的結(jié)果集賦給cur  
6.   CURSOR cur IS  
7.     SELECT * FROM User_Advisor_Log;  
8.   --sql開始標(biāo)記，以上是定義變量，以下才寫程序  
9. begin  
10.   DBMS_OUTPUT.PUT_LINE(param1);  
11.   v_i := 0;  
12.   DBMS_OUTPUT.PUT_LINE('start!');  
13.   --遍歷結(jié)果集  
14.   for cur_result in cur LOOP  
15.     
16.     begin  
17.       v_taskName := cur_result.TASK_NAME; --將結(jié)果集賦給變量v_creator，一個(gè)語句結(jié)束需要分號(hào)結(jié)尾。  
18.       
19.       --if語句開始  
20.       if v_taskName > 0 then  
21.         begin  
22.           NULL; --NULL 語句表明什么事都不做，這句不能刪去，因?yàn)镻L/SQL體中至少需要有一句;  
23.         end;  
24.       end if;  
25.       
26.       --while循環(huán)  
27.       while v_taskName > 0 LOOP  
28.         begin  
29.           NULL;  
30.         end;  
31.       end LOOP;  
32.       
33.       --建議每循環(huán)一萬次提交一下  
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; --循環(huán)結(jié)束  
45.   commit;  
46.   DBMS_OUTPUT.PUT_LINE('end and commit!');  
47. end update_user_p;  

一個(gè)簡單的造數(shù)據(jù)存儲(chǔ)過程

Sql代碼  

1. --往表里造40萬數(shù)據(jù)。  
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;  

如何執(zhí)行存儲(chǔ)過程？

執(zhí)行存儲(chǔ)過程：call update_user_p('this is param')。在output 里可以看見DBMS_OUTPUT.PUT_LINE的輸出。

如何調(diào)試存儲(chǔ)過程？

在plsql里編輯存儲(chǔ)過程，點(diǎn)擊執(zhí)行，系統(tǒng)會(huì)告訴你，錯(cuò)誤的行數(shù)和原因。并能顯示代碼結(jié)構(gòu)。

另外可以使用DBMS_OUTPUT.PUT_LINE打印異常，注意打印異常時(shí)，輸出上下文（如錯(cuò)誤的taskName）。 

性能測試

•  用存儲(chǔ)過程插入40萬數(shù)據(jù)用了10秒。
•  遍歷并判斷40萬條數(shù)據(jù)用了25秒。
•  80萬次SQL判斷+40萬次SQL插入=25秒。

其他問題

•  存儲(chǔ)過程執(zhí)行非常慢，有可能是更新語句引起了死鎖，也有可能是語句執(zhí)行慢（需要建索引）。
•  存儲(chǔ)過程編譯非常慢，有可能是當(dāng)前存儲(chǔ)過程正在執(zhí)行，被鎖住了。（使用DBA帳號(hào)解鎖）。