MySQL存储过程初探

什么是存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数来执行它。存储过程是数据库中的一个重要对象。（百度百科）

简单来说，存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合。

---

存储过程优缺点

优点：

• 简化复杂操作和对变动的管理
• 提高性能
• 保证数据的安全性

缺点：

• 用户可能没有创建存储过程的权限
• 维护、移植比较麻烦

---

使用存储过程

1. 创建、执行与删除

一张成绩表

mysql> select * from grade;
+-------+-------+-------+
| name  | grade | score |
+-------+-------+-------+
| li    |    90 |     5 |
| niu   |    60 |     2 |
| z     |    89 |     3 |
| zhang |    70 |     4 |
+-------+-------+-------+
4 rows in set (0.00 sec)

小例子

mysql> delimiter //
mysql> create procedure gradeavg() begin select Avg(grade) AS gradeaverage from grade;end//
Query OK, 0 rows affected (0.10 sec)

mysql> delimiter ;
mysql> call gradeavg();
+--------------+
| gradeaverage |
+--------------+
|      77.2500 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure gradeavg;
Query OK, 0 rows affected (0.03 sec)

2. 检查存储过程

显示用来创建一个存储过程的CREATE语句

    SHOW CREATE PROCEDURE 存储过程名;

若想获取详细信息，可以使用SHOW CREATE PROCEDURE STATUS 。

3. 使用参数

mysql> delimiter //
mysql> create procedure showOneTotal(
    -> IN dest varchar(10),
    -> IN flag BOOLEAN,
    -> OUT total DECIMAL(8,2)
    -> )
    -> BEGIN
    ->    DECLARE per DECIMAL(8,2) DEFAULT 0.7;
    ->    DECLARE gra DECIMAL(8,2);
    ->    DECLARE sco DECIMAL(8,2);
    ->    set gra = (SELECT grade FROM grade Where name = dest);
    ->    set sco = (SELECT score FROM grade Where name = dest);
    ->    IF flag THEN
    ->       SELECT (gra*per)+(sco*20*(1-per)) INTO total;
    ->    ELSE
    ->       SELECT gra INTO total;
    ->    END IF;
    -> END //
Query OK, 0 rows affected (0.10 sec)

mysql> delimiter ;
mysql> call showOneTotal('li',0,@total);
Query OK, 1 row affected (0.00 sec)

mysql> select @total;
+--------+
| @total |
+--------+
|  90.00 |
+--------+
1 row in set (0.00 sec)

mysql> call showOneTotal('li',1,@total);
Query OK, 1 row affected (0.00 sec)

mysql> select @total;
+--------+
| @total |
+--------+
|  93.00 |
+--------+
1 row in set (0.00 sec)

MySQL支持以下类型的参数：
- IN：传递给存储过程
- OUT：从存储过程传出
- INOUT：对存储过程传入和传出

存储过程的代码位于BEGIN和END语句内

4. 定义变量与赋值

4.1定义变量

• １.以 DECLARE 关键字声明的变量，只能在存储过程中使用，称为存储过程变量，例如：DECLARE a INT DEFAULT 0;主要用在存储过程中，或者是给存储过程传参数中。

• ２.使用set或select直接赋值，变量名以@开头,例如:set @ａ=1;可以在一个会话的任何地方声明，作用域是整个会话，称为会话变量。

两者的区别是：

以DECLARE声明的变量相当于局部变量，在调用存储过程时，都会被初始化为 NULL。set或select声明的变量就相当于这个会话内的全局变量，不会被再初始化，在一个会话内，只初始化一次，之后在会话内都是对上一次计算的结果。在存储过程中，使用动态语句，预处理时，动态内容必须赋给一个会话变量。

4.2 给变量赋值

create procedure test1()
begin
    set @num = (SELECT grade FROM grade Where name = 'li');
    select @num;
end
定义变量
create procedure test2()
begin
    DECLARE a DECIMAL(8,2);
    SELECT grade into a FROM grade Where name = 'li';
    select a;
end

create procedure test3()
begin
    DECLARE a DECIMAL(8,2);
    set a =(SELECT grade FROM grade Where name = 'li');
    select a;
end

后两种方式如果声明的变量名跟要查询的列名一样，那么结果会为NULL，一定要注意。（PS：MySQL默认对数据库名和表名区分大小写，对列名及列内容不区分大小写）

5高级操作

5.1 条件判断

IF 条件1 THEN
    do sth
ELSEIF 条件2 THEN
    do sth
ELSE
    do sth
END IF;

5.2 循环

方法一：while

WHILE 循环条件 DO
    循环体
END WHILE;

方法二：repeat

REPEAT
    循环体
    UNTIL 循环结束条件
END REPEAT;

方法三：loop

loop_label:LOOP
    循环体
    if ... then（循环结束条件）
        leave loop_label;
    end if;
END LOOP

GoTo不是标准的，在这里就不提了，有兴趣可以了解。

---

小结

以前总是听老师讲存储过程怎么怎么，今天真正上手写感觉还是蛮有意思的。触发过程就是一组为了完成特定功能的SQL语句集，类似于其他高级语言中的函数，需要什么的时候只要去调用即可。写的过程中虽然遇到了一些小问题，但是也都解决了，这次初探只有一些基本的操作，可能还不够深入，如果对这感兴趣可以自己找找资料再多了解一些。