MySQL-11-預存程序，mysql預存程序

MySQL-11-預存程序，mysql預存程序

預存程序

我們常用的操作資料庫語言SQL語句在執行的時候需要要先編譯，然後執行，而預存程序（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯後儲存在資料庫中，使用者通過指定預存程序的名字並給定參數（如果該預存程序帶有參數）來調用執行它。

一個預存程序是一個可程式化的函數，它在資料庫中建立並儲存。它可以有SQL語句和一些特殊的控制結構組成。當希望在不同的應用程式或平台上執行相同的函數，或者封裝特定功能時，預存程序是非常有用的。資料庫中的預存程序可以看做是對編程中物件導向方法的類比。它允許控制資料的訪問方式。

預存程序的優點：

(1).預存程序增強了SQL語言的功能和靈活性。預存程序可以用流量控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。

(2).預存程序允許標準組件是編程。預存程序被建立後，可以在程式中被多次調用，而不必重新編寫該預存程序的SQL語句。而且資料庫專業人員可以隨時對預存程序進行修改，對應用程式原始碼毫無影響。

(3).預存程序能實現較快的執行速度。如果某一操作包含大量的Transaction-SQL代碼或分別被多次執行，那麼預存程序要比批處理的執行速度快很多。因為預存程序是先行編譯的。在首次運行一個預存程序時查詢，最佳化器對其進行分析最佳化，並且給出最終被儲存在系統資料表中的執行計畫。而批處理的Transaction-SQL語句在每次運行時都要進行編譯和最佳化，速度相對要慢一些。

(4).預存程序能過減少網路流量。針對同一個資料庫物件的操作（如查詢、修改），如果這一操作所涉及的Transaction-SQL語句被組織程預存程序，那麼當在客戶電腦上調用該預存程序時，網路中傳送的只是該調用語句，從而大大增加了網路流量並降低了網路負載。

(5).預存程序可被作為一種安全機制來充分利用。系統管理員通過執行某一預存程序的許可權進行限制，能夠實現對相應的資料的存取權限的限制，避免了非授權使用者對資料的訪問，保證了資料的安全。

 

1.預存程序的建立

a) 文法：

CREATE PROCEDURE 過程名 ([過程參數[,...]])

[特性 ...] 

過程體

b) 說明

預存程序可能會有輸入、輸出、輸入輸出參數，多個參數使用,分隔開。

IN 輸入參數： 表示該參數的值必須在調用預存程序時指定，在預存程序中修改該參數的值不能被返回

OUT 輸出參數: 該值可以在預存程序內部被改變，並可返回

INOUT 輸入輸出參數： 調用時指定，並且可以被改變和返回

c) 例子：

例一：

delimiter $$

create procedure proc1(IN p_in int)

begin

select p_in;

set p_in=2;

select p_in;

end

$$

delimiter ;

調用： set @p_in=1;

CALL proc2(@p_in);

執行完後再使用 select @p_in; 發現結果任然為1，表示輸入參數雖然在預存程序中被改變，但是不影響原來的值。

 

例二：

delimiter $$

create procedure proc2(OUT p_out int)

begin

select p_out;

set p_out=2;

select p_out;

end

$$

 

delimiter ;

 

調用： set @p_out=1;

CALL proc2(@p_out);

執行完後再使用 select @p_out; 發現結果變為2，表示輸出參數雖然在預存程序中被改變，影響原來的值。

 

例三：

INOUT 參數樣本。略。

 

d) 函數體中詳細說明

聲明變數、變數賦值、注釋等都與mysql函數編程一致！

 

 

 

 

 

2.調用預存程序

使用 CALL 預存程序 來調用，見上面例子。

 

3.查詢預存程序

查看某個資料庫下面的預存程序和函數

select name from mysql.proc where db=’資料庫名’;

或者

select routine_name from information_schema.routines where routine_schema='資料庫名';

或者

show procedure status where db='資料庫名';

進行查詢。

查看某個預存程序的詳細

SHOW CREATE PROCEDURE 資料庫.預存程序名;

就可以查看當前預存程序的詳細。

 

4. MySQL預存程序的修改

ALTER PROCEDURE

更改用CREATE PROCEDURE 建立的預先指定的預存程序，其不會影響相關預存程序或儲存功能。

 

5. MySQL預存程序的刪除

刪除一個預存程序比較簡單，和刪除表一樣：

DROP PROCEDURE

從MySQL的表格中刪除一個或多個預存程序。

 

6. MySQL預存程序的控制語句

 (1). 條件陳述式

Ⅰ. if-then -else語句

Ⅱ. case語句： 

DELIMITER $$  

CREATE PROCEDURE proc3 (in parameter int)  

begin 

declare var int;  

set var=parameter+1;  

case var  

when 0 then   

insert into t values(17);  

when 1 then   

insert into t values(18);  

else   

insert into t values(19);  

end case;  

end;  

$$

DELIMITER ;  

(3). 迴圈語句

Ⅰ. while ···· end while：

Ⅱ. repeat···· end repeat：

它在執行操作後檢查結果，而while則是執行前進行檢查。

DELIMITER //  

CREATE PROCEDURE proc5 ()  

begin   

declare v int default 0;  

repeat  

insert into t values(v);  

set v=v+1;  

until v>=5  

end repeat;  

end;  

//  

DELIMITER ;   

 

Ⅲ. loop ·····end loop:

loop迴圈不需要初始條件，這點和while 迴圈相似，同時和repeat迴圈一樣不需要結束條件, leave語句的意義是離開迴圈。

DELIMITER $$  

CREATE PROCEDURE proc6 ()  

begin 

declare v int;  

set v=0;  

LOOP_LABLE:loop  

insert into t values(v);  

set v=v+1;  

if v >=5 then 

leave LOOP_LABLE;  

end if;  

end loop;  

end;  

$$ 

DELIMITER ;  

  

Ⅳ. LABLES 標號：

標號可以用在begin repeat while 或者loop 語句前，語句標號只能在合法的語句前面使用。可以跳出迴圈，使運行指令達到複合陳述式的最後一步。

(4). ITERATE迭代

通過引用複合陳述式的標號,來從新開始複合陳述式

DELIMITER $$  

CREATE PROCEDURE proc10 ()  

begin 

declare v int;  

set v=0;  

LOOP_LABLE:loop  

if v=3 then   

set v=v+1;  

ITERATE LOOP_LABLE;  

end if;  

 

insert into t values(v);  

set v=v+1;  

if v>=5 then 

leave LOOP_LABLE;  

end if;  

end loop;  

end;  

$$ 

DELIMITER ;