MySQL 5.0 版本开始支持存储过程.
存储过程 (Stored Procedure) 是一种在数据库中存储复杂程序, 以便外部程序调用的一种数据库对象.
存储过程是为了完成特定功能的 SQL 语句集, 经编译创建并保存在数据库中, 用户可通过指定存储过程的名字并给定参数 (需要时) 来调用执行.
存储过程思想上很简单, 就是数据库 SQL 语言层面的代码封装与重用.
优点
存储过程可封装, 并隐藏复杂的商业逻辑.
存储过程可以回传值, 并可以接受参数.
存储过程无法使用 SELECT 指令来运行, 因为它是子程序, 与查看表, 数据表或用户定义函数不同.
存储过程可以用在数据检验, 强制实行商业逻辑等.
缺点
存储过程, 往往定制化于特定的数据库上, 因为支持的编程语言不同. 当切换到其他厂商的数据库系统时, 需要重写原有的存储过程.
存储过程的性能调校与撰写, 受限于各种数据库系统.
存储过程的创建和调用
创建存储过程
- 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 存储过程中的关键语法
1. 声明语句结束符, 可以自定义:
DELIMITER $$
或
DELIMITER //
2. 声明存储过程:
CREATE PROCEDURE demo_in_parameter(IN p_in int)
3. 存储过程开始和结束符号:
BEGIN .... END
4. 变量赋值:
SET @p_in=1
5. 变量定义:
DECLARE l_int int unsigned default 4000000;
6. 创建 MySQL 存储过程, 存储函数:
create procedure 存储过程名(参数)
7. 存储过程体:
create function 存储函数名(参数)
实例
1. 创建数据库, 备份数据表用于示例操作:
- 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;
2. 下面是存储过程的例子, 删除给定球员参加的所有比赛:
- 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) 解释.
调用存储过程:
- 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 操作.
存储过程体
存储过程体包含了在过程调用时必须执行的语句, 例如: dml,ddl 语句, if-then-else 和 while-do 语句, 声明变量的 declare 语句等
过程体格式: 以 begin 开始, 以 end 结束(可嵌套)
- BEGIN
- BEGIN
- BEGIN
- statements;
- END
- END
- END
注意: 每个嵌套块及其中的每条语句, 必须以分号结束, 表示过程体结束的 begin-end 块(又叫做复合语句 compound statement), 则不需要分号.
- [begin_label:] BEGIN
- [statement_list]
- END [end_label]
- label1: BEGIN
- label2: BEGIN
- label3: BEGIN
- statements;
- END label3 ;
- END label2;
- END label1
来源: http://database.51cto.com/art/201811/586254.htm