當PHP遇到MySQL預存程序

標籤：php   mysql   預存程序   

1、MySQL預存程序

我們常用的操作資料庫語言SQL語句在執行的時候需要要先編譯，然後執行，而預存程序（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯後儲存在資料庫中，使用者通過指定預存程序的名字並給定參數（如果該預存程序帶有參數）來調用執行它。
一個預存程序是一個可程式化的函數，它在資料庫中建立並儲存。它可以有SQL語句和一些特殊的控制結構組成。當希望在不同的應用程式或平台上執行相同的函數，或者封裝特定功能時，預存程序是非常有用的。資料庫中的預存程序可以看做是對編程中物件導向方法的類比。它允許控制資料的訪問方式。 

2、預存程序通常有以下優點：

(1)預存程序增強了SQL語言的功能和靈活性。預存程序可以用流量控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。
(2)預存程序允許標準組件是編程。預存程序被建立後，可以在程式中被多次調用，而不必重新編寫該預存程序的SQL語句。而且資料庫專業人員可以隨時對預存程序進行修改，對應用程式原始碼毫無影響。
(3)預存程序能實現較快的執行速度。如果某一操作包含大量的Transaction-SQL代碼或分別被多次執行，那麼預存程序要比批處理的執行速度快很多。因為預存程序是先行編譯的。在首次運行一個預存程序時查詢，最佳化器對其進行分析最佳化，並且給出最終被儲存在系統資料表中的執行計畫。而批處理的Transaction-SQL語句在每次運行時都要進行編譯和最佳化，速度相對要慢一些。 
(4)預存程序能過減少網路流量。針對同一個資料庫物件的操作（如查詢、修改），如果這一操作所涉及的Transaction-SQL語句被組織程預存程序，那麼當在客戶電腦上調用該預存程序時，網路中傳送的只是該調用語句，從而大大增加了網路流量並降低了網路負載。
(5)預存程序可被作為一種安全機制來充分利用。系統管理員通過執行某一預存程序的許可權進行限制，能夠實現對相應的資料的存取權限的限制，避免了非授權使用者對資料的訪問，保證了資料的安全。

3、PHP調用MySQL案例

執行個體一：無參的預存程序

$conn = mysql_connect('localhost','root','root') or die ("資料連線錯誤!!!");mysql_select_db('test',$conn);$sql = "create procedure myproce()beginINSERT INTO user (id, username, sex) VALUES (NULL, 's', '0');end; ";mysql_query($sql);//建立一個myproce的預存程序$sql = "call test.myproce();";mysql_query($sql);//調用myproce的預存程序，則資料庫中將增加一條新記錄。

執行個體二：傳入參數的預存程序

$sql = "create procedure myproce2(in score int)beginif score >= 60 thenselect 'pass';elseselect 'no';end if;end; ";mysql_query($sql);//建立一個myproce2的預存程序$sql = "call test.myproce2(70);";mysql_query($sql);//調用myproce2的預存程序,看不到效果，可以在cmd下看到結果。

執行個體三：傳出參數的預存程序

$sql = "create procedure myproce3(out score int)beginset score=100;end; ";mysql_query($sql);//建立一個myproce3的預存程序$sql = "call test.myproce3(@score);";mysql_query($sql);//調用myproce3的預存程序$result = mysql_query('select @score;');$array = mysql_fetch_array($result);echo '<pre>';print_r($array);

執行個體四：傳出參數的inout預存程序

$sql = "create procedure myproce4(inout sexflag int)beginSELECT * FROM user WHERE sex = sexflag;end; ";mysql_query($sql);//建立一個myproce4的預存程序$sql = "set @sexflag = 1";mysql_query($sql);//設定性別參數為1$sql = "call test.myproce4(@sexflag);";mysql_query($sql);//調用myproce4的預存程序,在cmd下面看效果

執行個體五：使用變數的預存程序 

$sql = "create procedure myproce5(in a int,in b int)begindeclare s int default 0;set s=a+b;select s;end; ";mysql_query($sql);//建立一個myproce5的預存程序$sql = "call test.myproce5(4,6);";mysql_query($sql);//調用myproce5的預存程序,在cmd下面看效果

執行個體六：case文法

$sql = "create procedure myproce6(in score int)begincase scorewhen 60 then select '及格';when 80 then select '及良好';when 100 then select '優秀';else select '未知分數';end case;end; ";mysql_query($sql);//建立一個myproce6的預存程序$sql = "call test.myproce6(100);";mysql_query($sql);//調用myproce6的預存程序,在cmd下面看效果

執行個體七：迴圈語句

$sql = "create procedure myproce7()begindeclare i int default 0;declare j int default 0;while i<10 doset j=j+i;set i=i+1;end while;select j;end; ";mysql_query($sql);//建立一個myproce7的預存程序$sql = "call test.myproce7();";mysql_query($sql);//調用myproce7的預存程序,在cmd下面看效果

執行個體八：repeat語句

$sql = " create procedure myproce8()begindeclare i int default 0;declare j int default 0;repeatset j=j+i;set i=i+1;until j>=10end repeat;select j;end; ";mysql_query($sql);//建立一個myproce8的預存程序$sql = "call test.myproce8();";mysql_query($sql);//調用myproce8的預存程序,在cmd下面看效果

執行個體九：loop語句

$sql = "create procedure myproce9()begindeclare i int default 0;declare s int default 0;loop_label:loopset s=s+i;set i=i+1;if i>=5 thenleave loop_label;end if;end loop;select s;end; ";mysql_query($sql);//建立一個myproce9的預存程序$sql = "call test.myproce9();";mysql_query($sql);//調用myproce9的預存程序,在cmd下面看效果

執行個體十：刪除預存程序

mysql_query("drop procedure if exists myproce");//刪除test的預存程序

當PHP遇到MySQL預存程序