MySQL存儲過程.png
一、存儲過程
1.定義
存儲過程是SQL語句和控制語句的預編譯集合，以一個名稱存儲并作為一個單元處理
2.存儲過程的優(yōu)點
- 增強SQL語句的功能和靈活性
- 實現(xiàn)較快的執(zhí)行速度
- 減少網(wǎng)絡流量
3.創(chuàng)建存儲過程
CREATE
[DEFINER = {user|CURRENT_USER}]
PROCEDURE sp_name([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[IN|OUT|INOUT] param_name type
4.參數(shù)
- IN，表示該參數(shù)的值必須在調用存儲過程時指定
- OUT，表示該參數(shù)的值可以被存儲過程改變，并且可以返回
- INOUT，表示該參數(shù)在調用時指定，并且可以被改變和返回
5.特性
COMMENT 'string'
|{CONTAINS SQL| NO SQL| READS SQL DATA| MODIFIES SQL DATA
|SQL SECURITY{DEFINER| INVOKER}}
- COMMENT：注釋
- CONTAINS SQL：包含SQL語句，但不包含讀或寫數(shù)據(jù)的語句
- NO SQL：不包含SQL語句
- READS SQL DATA：包含讀數(shù)據(jù)的語句
- MODIFIES SQL DATA：包含寫數(shù)據(jù)的語句
- SQL SECURITY{DEFINER| INVOKER}指明誰有權限來執(zhí)行
6.過程體
- 過程體由合法SQL語句構成
- 過程體可以是任意SQL語句
- 過程體如果為復合結構則使用BEGIN...END語句
- 復合結構可以包含聲明，循環(huán)，控制結構
二、MySQL創(chuàng)建存儲過程
1.MySQL創(chuàng)建不帶參數(shù)的存儲過程
創(chuàng)建
example:
CREATE PROCEDURE sp1() SELECT VERSION();
調用存儲過程
CALL sp_name([parameter[,...]])
CALL sp_name[()]
example:
CALL sp1;
/*Result
+------------+
| VERSION() |
+------------+
| 5.7.17-log |
+------------+
*/
2.MySQL創(chuàng)建帶有IN參數(shù)的存儲過程
example:
DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
END
//
DELIMITER ;
CALL removeUserById(3);
SELECT * FROM users;
/* Result
+----+------+----------------------------------+------+------+
| id | name | password | age | sex |
+----+------+----------------------------------+------+------+
| 1 | A | 202cb962ac59075b964b07152d234b70 | 25 | 1 |
| 2 | B | 250cf8b51c773f3f8dc8b4be867a9a02 | 24 | 1 |
| 4 | D | 38b3eff8baf56627478ec76a704e9b52 | 25 | 1 |
| 5 | E | ec8956637a99787bd197eacd77acce5e | 27 | 0 |
| 6 | F | 6974ce5ac660610b44d9b9fed0ff9548 | 21 | 1 |
| 7 | G | c9e1074f5b3f9fc8ea15d152add07294 | 20 | 0 |
| 8 | H | 65b9eea6e1cc6bb9f0cd2a47751a186f | 23 | 1 |
| 9 | J | f0935e4cd5920aa6c7c996a5ee53a70f | 18 | 1 |
| 10 | K | a97da629b098b75c294dffdc3e463904 | 30 | 0 |
| 11 | L | a3c65c2974270fd093ee8a9bf8ae7d0b | 8 | 0 |
| 12 | M | 2723d092b63885e0d7c260cc007e8b9d | 12 | 1 |
| 13 | N | 3644a684f98ea8fe223c713b77189a77 | 27 | 1 |
| 14 | P | 94f6d7e04a4d452035300f18b984988c | 26 | 0 |
| 15 | Q | 18d8042386b79e2c279fd162df0205c8 | 25 | 0 |
| 16 | R | cee631121c2ec9232f3a2f028ad5c89b | 23 | 0 |
| 17 | S | d490d7b4576290fa60eb31b5fc917ad1 | 25 | 1 |
+----+------+----------------------------------+------+------+
*/
3.MySQL創(chuàng)建帶有IN和OUT類型參數(shù)的存儲過程
example:
DELIMITER //
CREATE PROCEDURE removeUserANDReturnUserNums(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
SELECT count(id) FROM users INTO userNums;
END
//
DELIMITER ;
CALL removeUserANDReturnUserNums(17, @nums);
SELECT @nums;
/*Result
+-------+
| @nums |
+-------+
| 15 |
+-------+
*/
4.創(chuàng)建帶有多個OUT類型參數(shù)的存儲過程
example:
DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED, OUT deleteUsers SMALLINT UNSIGNED, OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM users INTO userCounts;
END
//
DELIMITER ;
CAll removeUserByAgeAndReturnInfos(20, @a, @b);
SELECT @a, @b;
/*Result
+------+------+
| @a | @b |
+------+------+
| 1 | 14 |
+------+------+
*/
5.MySQL存儲過程與自定義函數(shù)的區(qū)別
- 存儲過程實現(xiàn)的功能要復雜一些；而函數(shù)的針對性更強
- 存儲過程可以返回多個值；函數(shù)只能有一個返回值
- 存儲過程一般獨立的來執(zhí)行；而函數(shù)可以作為其他SQL語句的組成部分來出現(xiàn)。
6.修改存儲過程
ALTER PROCEDURE sp_name [characteristic ...]
COMMENT 'string'
|{CONTAINS SQL| NO SQL| READS SQL DATA| MODIFIES SQL DATA
|SQL SECURITY{DEFINER| INVOKER}}
7.刪除存儲過程
DROP PROCEDURE [IF EXISTS] sp_name;
三、小結
- 存儲過程：是SQL語句和控制語句的預編譯集合，以一個名稱存儲并作為一個單元處理。
- 參數(shù)：輸入類型 輸出類型 輸入&&輸出
- 創(chuàng)建：CREATE ...... PROCEDURE ......
- 注意事項
- 創(chuàng)建存儲過程或者自定義函數(shù)時需要通過DELIMITER語句修改界定符。
- 如果函數(shù)體或過程體有多個語句，需要包含在BEGIN......END語句塊中。
- 存儲過程通過call來調用。
