mysql預存程序的參數類型有三種:1、輸入參數,用「IN」關鍵字標識,可以傳遞給一個預存程序;2、輸出參數,用「OUT」關鍵字標識,用於預存程序需傳回一個操作結果的情形;3、輸入輸出參數,以「INOUT」關鍵字標示。
本教學操作環境:windows7系統、mysql8版本、Dell G3電腦。
預存程序(Stored Procedure):
一組可程式化的函數,是為了完成特定功能的SQL語句集,經編譯建立並儲存在資料庫中,使用者可透過指定預存程序的名字並給定參數(需要時)來呼叫執行。
優點(為什麼要用預存程序?):
①將重複性很高的一些操作,封裝到一個預存程序中,簡化了對這些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)解釋。
2、呼叫預存程序:call sp_name[(傳參)];
mysql> select * from MATCHES; +---------+--------+----------+-----+------+ | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | +---------+--------+----------+-----+------+ | 1 | 1 | 6 | 3 | 1 | | 7 | 1 | 57 | 3 | 0 | | 8 | 1 | 8 | 0 | 3 | | 9 | 2 | 27 | 3 | 2 | | 11 | 2 | 112 | 2 | 3 | +---------+--------+----------+-----+------+ 5 rows in set (0.00 sec) mysql> call delete_matches(57); Query OK, 1 row affected (0.03 sec) mysql> select * from MATCHES; +---------+--------+----------+-----+------+ | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | +---------+--------+----------+-----+------+ | 1 | 1 | 6 | 3 | 1 | | 8 | 1 | 8 | 0 | 3 | | 9 | 2 | 27 | 3 | 2 | | 11 | 2 | 112 | 2 | 3 | +---------+--------+----------+-----+------+ 4 rows in set (0.00 sec)
解析:
在預存程序中設定了需要傳參的變數p_playerno,呼叫預存程序的時候,透過傳參將57賦值給p_playerno,然後進行預存程序裡的SQL操作。
3、預存程序體
>儲存程序體包含了在程序呼叫時必須執行的語句,例如:dml、ddl語句,if-then- else和while-do語句、宣告變數的declare語句等
>過程體格式:以begin開始,以end結束(可嵌套)
BEGIN BEGIN BEGIN statements; END END END
注意:每個嵌套區塊及其中的每個語句,必須以分號結束,表示過程體結束的begin-end區塊(又叫做複合語句compound statement),則不需要分號。
4、為語句區塊貼標籤
[begin_label:] BEGIN [statement_list] END [end_label]
例如:
label1: BEGIN label2: BEGIN label3: BEGIN statements; END label3 ; END label2; END label1
標籤有兩個作用:
①增強程式碼的可讀性
②在某些語句(例如:leave和iterate語句),需要用到標籤
##二、預存程序的參數
預存程序可以有0個或多個參數,用於預存程序的定義。MySQL 預存程序支援三種類型的參數,即輸入參數、輸出參數和輸入/輸出參數,分別以 IN、OUT 和 INOUT 三個關鍵字標識。 其中,輸入參數可以傳遞給一個預存程序,輸出參數用於預存程序需要傳回一個操作結果的情形,而輸入/輸出參數既可以充當輸入參數也可以充當輸出參數。
3種參數類型:
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存储过程,接受了输入的参数,也输出参数,改变了变量
mysql影片教學】
以上是mysql預存程序的參數類型有哪些的詳細內容。更多資訊請關注PHP中文網其他相關文章!