預存程序(Stored Procedure):一組可程式化的函數,是為了完成特定功能的SQL語句集,經編譯建立並保存在資料庫中,使用者可透過指定預存程序的名字並給定參數(需要時)來調用執行。
推薦課程:MySQL教學。
優點(為什麼要用預存程序?):
①將重複性很高的一些操作,封裝到一個預存程序中,簡化了對這些SQL的呼叫
②批量處理:SQL 循環,減少流量,也就是「跑批」
③統一接口,確保資料的安全性
相對於oracle資料庫來說,MySQL的預存程序相對功能較弱,使用較少。
一、預存程序的建立與呼叫
>預存程序就是具有名字的一段程式碼,用來完成一個特定的功能。
>建立的預存程序保存在資料庫的資料字典中。
1、建立預存程序
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: Valid SQL routine statement [begin_label:] BEGIN [statement_list] …… END [end_label]
#建立資料庫,備份資料表用於範例操作
mysql> create database db1; mysql> use db1; mysql> create table PLAYERS as select * from TENNIS.PLAYERS; mysql> create table MATCHES as select * from TENNIS.MATCHES;
範例:建立一個儲存過程,刪除給定球員參加的所有比賽
mysql> delimiter $$ #将语句的结束符号从分号;临时改为两个$$(可以是自定义) mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) -> BEGIN -> DELETE FROM MATCHES -> WHERE playerno = p_playerno; -> END$$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; #将语句的结束符号恢复为分号
解析:
預設情況下,預存程序和預設資料庫相關聯,如果想指定預存程序建立在某個特定的資料庫下,那麼在過程名前面加資料庫名做前綴;
在定義過程時,使用DELIMITER $$ 指令將語句的結束符號從分號; 暫時改為兩個$$,使得過程體中使用的分號被直接傳遞到伺服器,而不會被客戶端(如mysql)解釋。
二、預存程序的參數
預存程序可以有0個或多個參數,用於預存程序的定義。
3種參數類型:
IN輸入參數:表示呼叫者向過程傳入值(傳入值可以是字面量或變數)
OUT輸出參數:表示過程向呼叫者傳出值(可以傳回多個值)(傳出值只能是變數)
INOUT輸入輸出參數:既表示呼叫者向過程傳入值,又表示過程向調用者傳出值(值只能是變數)
1、in輸入參數
mysql> delimiter $$ mysql> create procedure in_param(in p_in int) -> begin -> select p_in; -> set p_in=2; -> select P_in; -> end$$ mysql> delimiter ; mysql> set @p_in=1; mysql> call in_param(@p_in);+------+ | p_in | +------+ | 1 | +------+ +------+ | P_in | +------+ | 2 | +------+mysql> select @p_in;+-------+ | @p_in | +-------+ | 1 | +-------+
#以上可以看出,p_in在儲存過程中被修改,但不影響@p_id的值,因為前者為局部變數、後者為全域變數。
2、out輸出參數
mysql> delimiter //mysql> create procedure out_param(out p_out int) -> begin -> select p_out; -> set p_out=2; -> select p_out; -> end -> //mysql> delimiter ; mysql> set @p_out=1; mysql> call out_param(@p_out);+-------+ | p_out | +-------+ | NULL | +-------+ #因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null+-------+ | p_out | +-------+ | 2 | +-------+mysql> select @p_out;+--------+ | @p_out | +--------+ | 2 | +--------+ #调用了out_param存储过程,输出参数,改变了p_out变量的值
3、inout輸入參數
mysql> delimiter $$ mysql> create procedure inout_param(inout p_inout int) -> begin -> select p_inout; -> set p_inout=2; -> select p_inout; -> end -> $$ mysql> delimiter ; mysql> set @p_inout=1; mysql> call inout_param(@p_inout);+---------+ | p_inout | +---------+ | 1 | +---------+ +---------+ | p_inout | +---------+ | 2 | +---------+mysql> select @p_inout;+----------+ | @p_inout | +----------+ | 2 | +----------+
#呼叫了inout_param儲存過程,接受了輸入的參數,也輸出參數,改變了變數
注意:
①如果過程沒有參數,也必須在過程名稱後面寫上小括號
例:CREATE PROCEDURE sp_name ([proc_parameter[,...] ]) ……
②確保參數的名字不等於列的名字,否則在過程體中,參數名稱被當作列名來處理
強烈建議:
>輸入值使用in參數;
>傳回值使用out參數;
>inout參數就盡量的少用。
以上是mysql如何建立預存程序的詳細內容。更多資訊請關注PHP中文網其他相關文章!