SQL 随心所欲的存储过程（PROCEDURE）

本节要点

存储过程简介
创建存储过程
调用存储过程
删除存储过程
存储过程的优缺点

存储过程简介

前面我们讲解的，都是一条条单个的，多个SQL语句之间没有逻辑关键的SQL语句。可很多时候，我们可能需要根据一定的业务逻辑，写出有前后关系的SQL语句，并且顺序执行。

比如：现需要向学生表中插入新的学生数据。但在插入学生数据的时，需要同时检查老师表里的数据。如果插入学生的老师不在老师表里，则先向老师表中插入一条老师数据，再向学生表中插入学生数据。

处理流程如下图所示：

那么，我们在书写SQL的时候，大概就需要这样三个步骤：

步骤1、使用SELECT语句查询新学生的老师是否存在于老师表中；
如果不存在 ，则继续执行第2步；
如果存在，则直接跳到第3步；
步骤2、向老师表中插入老师数据；
步骤3、向学生表中插入学生数据；

我们根据这三个步骤，可以写出几个SQL，来完成我们上面的处理逻辑。但是，如果我们需要向学生表中插入1万条记录，是不是这些SQL就需要重复书写1万次呢？

这很明显不符合我们程序员“偷懒”的爱好啊。

于是，SQL语言提供了一类对象来将上面几个SQL包装在一起，在向学生表中插入数据时，直接调用这个对象就可以。

这个对象，就是存储过程。

官方一点的定义，就是存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，然后通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

创建存储过程

存储过程的创建：使用关键字CREATE PROCEDURE。

比如，本课程开始向学生表中插入数据的需求，可以编写存储过程如下：

CREATE PROCEDURE sp_add_student(
    IN i_student_id VARCHAR(50), IN i_student_name VARCHAR(100),
    IN i_gender VARCHAR(10), IN i_birth_day DATE,
    IN i_age INT, IN i_class_id VARCHAR(50),
    IN i_score DECIMAL(18,2), IN i_teacher_id VARCHAR(20)
)
BEGIN
    IF NOT EXISTS(SELECT 1 FROM teacher WHERE teacher_id = 'i_teacher_id') THEN
        INSERT INTO teacher(teacher_id) VALUES (i_teacher_id);
    END IF;
 
    INSERT INTO student VALUES(
        i_student_id,i_student_name,i_gender,i_birth_day,
        i_age, i_class_id, i_score, i_teacher_id
    );
END;

CREATE PROCEDURE关键字表示，正在创建一个存储过程。

sp_add_student是将要创建的存储过程名。当然，数据库中的存储过程名是不可以重复的。

紧跟着后面的括号中的很多个IN，代表了存储过程的入参。也就是在调用存储过程时，可以接收的参数。IN后面对应的是参数名称和参数类型。

再向下的BEGIN与END关键字的中间一段代码，表示的是存储过程的主体。我们将要实现的逻辑操作，都写在这里。

调用存储过程

存储过程创建后，使用CALL关键字来调用。

比如，可以使用下面的语句调用存储过程sp_add_student。

CALL sp_add_student(
    'S20170091', '杨艳', '女', '2003-04-09',
    15, 'G0206', 89.23, 'T0021'
);

在调用存储过程时，必须要指定对应的参数值。其中参数的个数以及参数值的类型，都必须与定义存储过程时一致。否则就会报错。

当然了，存储过程也可以没有参数，那么在调用时，就不需要给参数赋值。

执行上面调用存储过程的语句后，程序会将杨艳同学的数据插入学生表，并同时检查老师表中有没有一个编号为T0021的老师。如果没有，就会向老师表中插入一个编号为T0021的老师数据。

删除存储过程

删除存储过程，使用关键字DROP PROCEDURE。语法如下：

DROP PROCEDURE sp_add_student;

其中，sp_add_student是要删除的存储过程名。

存储过程的优缺点

通过上面的例子，我们发现，按照指定的逻辑编写完存储过程，并且调用起来就比较方便了。即使调用一万次，也只是存储过程的参数赋值不同而已。起到了模块化封装代码，以及代码复用的作用。

而且，在存储过程中，还可以写一些功能更强大，灵活性更高的语句，让我们能够更轻松的实现复杂的业务逻辑。

但是，存储过程也存在一定的缺点。

首先，存储过程的开发和调试比较困难。一方面，需要一定的学习成本，另一方面，在执行报错时，调试起来也比较麻烦。

其次，就是可移植性差。不同的数据库，存储过程的语法差别很大。将一个数据库的存储过程迁移到另一个数据库，差不多跟重新开发是一样的工作量，甚至还要大于重新开发的工作量。