第23课:随心所欲的存储过程

本节要点

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

存储过程简介

前面我们讲解的,都是一条条单个的,多个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是要删除的存储过程名。

存储过程的优缺点

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

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

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

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

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

课后习题:

1、对于第6课课后习题中的论坛注册用户表、第15课课后习题中的文章表、用户访问的文章记录表,请编写SQL完成以下查询。

(1)、设计一张文章访问量统计表,表结构如下:

统计日期 DATE
文章编号 VARCHAR(50)
文章标题 VARCHAR(200)
访问人数 INTEGER
访问次数 INTEGER

然后创建一个存储过程,传入一个日期,统计传入日期当天所有文章的访问人数及访问次数,并将结果插入文章访问量统计表。

【版权说明:仅允许非商业转载且请注明出处:Mac私塾 网址:http://macsishu.com】

发表评论

邮箱地址不会被公开。

Captcha Code