MySQL数据库高级SQL语句之MySQL存储过程

一：存储过程简介

• MySQL 5.0 版本开始支持存储过程。

• 存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

• 存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

• 存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

二：存储过程的优缺点

2.1：存储过程的优点

• 存储过程可封装，并隐藏复杂的商业逻辑。
• 存储过程可以回传值，并可以接受参数。
• 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
• 存储过程可以用在数据检验，强制实行商业逻辑等。

2.2：存储过程的缺点

• 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
• 存储过程的性能调校与撰写，受限于各种数据库系统。

2.3：创建存储过程的语法结构

• 使用CREATE PROCEDURE语句创建存储过程

• 创建存储过程的语法结构
  示例：

CREATE PROCEDURE<过程名>([过程参数[…..]])<过程体>
[过程参数[.-..]]格式
IN| ouT|lNouTj参数名><类型>

2.4：存储参数的类型

• 输入参数：IN
• 输出参数：OUT
• 输入/输出参数：INOUT

2.5：创建存储过程

• 存储过程主题部分，被称为过程体

• 以BEGIN开始，以END结束，若只有一条SQL语句，则可以省略BEGIN-END

• 以DELIMITER开始和结束

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;　　#将语句的结束符号恢复为分号

2.6：存储结构的语法结构

1. 定义存储过程

delimiter $$
create procedure存储过程名(in 参数名参数类型)
begin

2. 定义变量

declare变量名变量类型
#变量赋值
set变量名=值
sql语句1;
sql语句2;
…
sql语句n;
end$
delimiter;

3. 调用存储过程

call存储过程名(实际参数);

4. 查询存储过程

show procedure status where db='数据库';

5. 删除存储过程

drop procedure存储过程名;

示例：

• 创建存储过程

mysql> delimiter $$     #将语句的结束符从分号开始；临时改为两个$$
mysql> create procedure proc()
    -> begin
    -> select name,score from info;
    -> end$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;       #将语句的结束符恢复为分号

• 删除存储过程

语法结构：
drop procedure + 存储过程的名字
mysql> drop procedure proc;
Query OK, 0 rows affected (0.00 sec)

• 调用存储过程

mysql> call proc();
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan | 88.00 |
| lisi     | 77.00 |
| wangwu   | 90.00 |
| tianqi   | 66.00 |
| zhaoliu  | 81.00 |
+----------+-------+
5 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)