MySQL存儲過程（Stored Procedure）是經過編譯並存儲在數據庫中的一段SQL語句集，可通過指定存儲過程的名字和參數調用執行。
1、創建存儲過程
語法：
<code>CREATE
[DEFINER = 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]/<code>
實例：
<code>mysql> delimiter //
mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
BEGIN
SELECT COUNT(*) INTO cities FROM world.city
WHERE CountryCode = country;
END//
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> CALL citycount('JPN', @cities); -- cities in Japan
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @cities;
+---------+
| @cities |
+---------+
| 248 |
+---------+
1 row in set (0.00 sec)
mysql> CALL citycount('FRA', @cities); -- cities in France
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @cities;
+---------+
| @cities |
+---------+
| 40 |
+---------+
1 row in set (0.00 sec)/<code>
2、MySQL刪除存儲過程
刪除存儲過程語法：
<code>DROP PROCEDURE [IF EXISTS] sp_name/<code>
3、MySQL查看存儲過程
- 查看存儲過程的狀態
<code>show procedure status [like ‘pattern’];/<code>
- 查看存儲過程的定義
<code>show create procedure sp_name/<code>
- 通過系統表查看存儲過程信息
<code>select * from mysql.proc where db='test';
select * from information_schema.routines where routine_schema='test';/<code>
分享到:
閱讀更多 數據庫的那些事 的文章
