存儲過程（特定功能的SQL語句集） 一組為了完成特定功能的SQL語句集，存儲在資料庫中，經過第一次編譯後再次調用不需要編譯，用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是資料庫中一個重要對象。 1.創建存儲過程： create procedure 存儲過程名稱 ( ...
存儲過程（特定功能的SQL語句集）
一組為了完成特定功能的SQL語句集，存儲在資料庫中，經過第一次編譯後再次調用不需要編譯，用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是資料庫中一個重要對象。
1.創建存儲過程：
create procedure 存儲過程名稱 ([存儲過程參數列表]) [存儲過程特性] SQL代碼內容
1 例：delimiter //
2 create procedure proc()
3 begin
4 select * from userinfo;
5 end //
6 delimiter;
2.創建存儲函數：
create function 存儲函數名稱 ([存儲過程參數列表]) returns type [存儲函數特性] SQL代碼內容
1 例：delimiter //
2 create function NameByZip()
3 returns char(50)
4 return (select username from userinfo where userage='23');
5 //
6 delimiter;
3.變數的使用
1）定義變數：declare 局部變數名稱,...數據類型 [default value];
例：declare myparam int default 100;
2）為變數賦值：set 局部變數名稱=expr,var_name=expr,...;
例：declare var1,var2 int;
set var1=10,var2=10;
4.定義條件和處理程式
1）定義條件：declare 條件名稱 condition for sqlstate 'xxxx';
declare 條件名稱 condition for xxxx;
2）定義處理程式：捕獲 sqlstate_value: delcare continue handler for sqlstate 'xxxx' set @info='no_such_table';
捕獲 mysql_error_code: declare continue handler for xxxx set @info='no_such_table';
5.游標的使用
1）聲明游標：declare 游標名稱 cursor for SQL語句;
2）打開游標：open 游標名稱;
3）使用游標：fetch 游標名稱 into 欄位名,...;
4）關閉游標：close 游標名稱;
6.流程式控制制語句
if語句：if val is null then select 'val is null';
else select 'val is not null';
end if;
case語句：case val
when 1 then select 'val is 1';
when 2 then select 'val is 2';
else select 'val is not 1 or 2';
end case;
loop語句：declare id int default 0;
add_loop:loop;
set id = id + 1;
if id>=10 then leave add_loop;
end if;
end loop add_loop;
leave語句：退出迴圈語句
iterate語句：重新執行迴圈
repeat語句：declare id int default 0;
repeat
set id = id + 1;
until id >= 10; ##先執行後判斷
end repeat;
while語句：declare id int default 0;
while id >=10 do ##先判斷後執行
set id = id+1;
end while;
7.調用存儲過程和調用存儲函數
call 存儲過程名稱（存儲過程定義的參數）
select 存儲函數名稱（存儲過程定義的參數）
8.查看存儲過程和函數
show {procedure | function} status like 'x%' 語句查看存儲過程和函數的狀態
show create {procedure | function} 存儲函數名稱 語句查看存儲過程和函數的定義
9.修改存儲過程和函數
alter {procedure | function}存儲函數名稱 （存儲過程特性）
10.刪除存儲過程和函數
drop {procedure | function} [if exists] 存儲函數名稱
存儲過程優缺點：
優點：
1）存儲過程因為SQL語句已經預編譯過了，因此運行的速度比較快；
2）存儲過程在伺服器端運行，減少了客戶端的壓力。
3）允許模塊化程式設計，就是說只需要創建一次過程，以後在程式中就可以調用該過程任意次，類似方法的復用；
4）減少網路流量，客戶端調用存儲過程只需要傳存儲過程名和相關參數即可，與傳輸SQL語句相比自然數據量少了很多；
5）增強了使用的安全性，充分利用系統管理員可以對執行的某一個存儲過程進行許可權限制，從而能夠實現對某些數據訪問的限制，避免非授權用戶對數據的訪問，保證數據的安全。程式員直接調用存儲過程根本不知道表結構是什麼，有什麼欄位，沒有直接暴露表名以及欄位名給程式員。
缺點：
調試麻煩（至少沒有像開發程式那樣容易），可移植性不靈活（因為存儲過程是依賴於具體的資料庫）。
存儲過程優化思路：
1）儘量利用一些SQL語句來代替一些小迴圈，例如聚合函數、求平均函數等。
2）中間結果存放於臨時表，加索引；
3）少使用游標。SQL是一個集合語言，對於集合運算具有較高的性能。而cursors是過程運算，比如對一個100萬行數據進行查詢，游標需要讀表100萬次，而不是使用游標則只需要少量幾次讀取。
4）事務越短越好，SQL server支持併發操作，如果事務過多過長或隔離級別過高，都會造成併發阻塞、死鎖。導致查詢極慢，CPU占用率極低。
5）使用try...catch處理異常；
6）查詢語句儘量不要放在迴圈內；
