课程 › 第 15 章 › 实验四
第 15 章 · 实验四 · 在第七节之后

存储过程

建立和调用例 15-10、例 15-11，再分别使用 IN、OUT、INOUT 参数，最后查看、修改和删除存储过程。

环　境

MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。

执　行

Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。

注　意

输入法切到英文。表名、字段名不加引号，字符串值才用单引号。

本次实验的顺序　后一个实验用到前一个的结果

  1. 实验 4-1无参存储过程
  2. 实验 4-2选课门数
  3. 实验 4-3IN 参数
  4. 实验 4-4OUT 参数
  5. 实验 4-5INOUT 参数
  6. 实验 4-6查看、修改与删除
第一步

数据准备（必做）

重建 teaching 库的五张表和教材中的数据，并删除本章实验建立的存储过程和函数。

整段复制，全部执行下载 .sql
-- 第 15 章实验数据：教材 teaching 库的五张表和教材中的数据
-- 整段执行一次即可。五次实验共用，想从头再做时重新执行。
CREATE DATABASE IF NOT EXISTS teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 删除本章实验建立的存储过程和函数
DROP PROCEDURE IF EXISTS s_count;
DROP PROCEDURE IF EXISTS info_count;
DROP PROCEDURE IF EXISTS pass_or_fail;
DROP PROCEDURE IF EXISTS sum100;
DROP PROCEDURE IF EXISTS max_i;
DROP PROCEDURE IF EXISTS odd_sum;
DROP PROCEDURE IF EXISTS showstudentsnums;
DROP PROCEDURE IF EXISTS cal_courses;
DROP PROCEDURE IF EXISTS dept_students;
DROP PROCEDURE IF EXISTS cal_students;
DROP PROCEDURE IF EXISTS double_it;
DROP PROCEDURE IF EXISTS fetch3;
DROP PROCEDURE IF EXISTS dept_names;
DROP FUNCTION IF EXISTS cal_depts;
DROP FUNCTION IF EXISTS isprime;
DROP FUNCTION IF EXISTS get_level;

-- 删除旧的表。先暂时关闭外键检查，库里有别的表引用它们时也能删除
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS sc, tc, avgsal, classroom, s, c, t;
SET FOREIGN_KEY_CHECKS = 1;

-- 课程表
CREATE TABLE c (
  cno CHAR(4) NOT NULL,
  cn VARCHAR(30) NOT NULL,
  ct INT,
  PRIMARY KEY (cno)
);
-- 学生表
CREATE TABLE s (
  sno CHAR(4) NOT NULL,
  sn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  maj VARCHAR(30),
  dept VARCHAR(30),
  PRIMARY KEY (sno)
);
-- 教师表
CREATE TABLE t (
  tno CHAR(4) NOT NULL,
  tn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  prof VARCHAR(20),
  maj VARCHAR(30),
  dept VARCHAR(30),
  sal DECIMAL(8,2),
  PRIMARY KEY (tno)
);
-- 选课表
CREATE TABLE sc (
  sno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  score DECIMAL(5,2),
  PRIMARY KEY (sno, cno),
  FOREIGN KEY (sno) REFERENCES s (sno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);
-- 授课表
CREATE TABLE tc (
  tno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  tcdate DATE,
  PRIMARY KEY (tno, cno),
  FOREIGN KEY (tno) REFERENCES t (tno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);

INSERT INTO c VALUES
('c1','Java程序设计',40), ('c2','程序设计基础',48), ('c3','线性代数',48),
('c4','数据结构',64), ('c5','数据库系统',56), ('c6','数据挖掘',32),
('c7','高等数学',60), ('c8','控制理论',32);

INSERT INTO s VALUES
('s1','王彤','女',18,'计算机','信息学院'),
('s2','苏乐','男',20,'信息','信息学院'),
('s3','林欣怡','女',19,'信息','信息学院'),
('s4','陶然','男',19,'自动化','工学院'),
('s5','魏立','男',17,'数学','理学院'),
('s6','何欣荣','女',21,'计算机','信息学院'),
('s7','赵琳琳','女',19,'数学','理学院'),
('s8','李轩','男',18,'自动化','工学院');

INSERT INTO t VALUES
('t1','刘杨','男',38,'教授','计算机','信息学院',3800.00),
('t2','石丽','女',28,'讲师','计算机','信息学院',2800.00),
('t3','顾伟','男',35,'副教授','计算机','信息学院',3200.00),
('t4','赵礼','男',45,'教授','自动化','工学院',4267.90),
('t5','赵希希','女',33,'副教授','数学','理学院',3332.67),
('t6','张刚','男',30,'讲师','计算机','工学院',2600.00);

INSERT INTO sc VALUES
('s1','c1',90.50), ('s1','c2',88.00),
('s2','c4',70.00), ('s2','c5',57.00), ('s2','c6',81.50), ('s2','c7',NULL),
('s3','c1',75.00), ('s3','c2',76.50), ('s3','c4',85.00),
('s4','c1',93.00), ('s4','c2',82.00), ('s4','c6',NULL), ('s4','c7',68.00),
('s5','c2',79.00), ('s5','c7',84.00),
('s7','c3',77.00), ('s7','c5',62.00), ('s7','c7',100.00),
('s8','c2',66.00), ('s8','c3',96.00);

INSERT INTO tc VALUES
('t1','c1','2021-09-03'), ('t1','c2','2021-09-04'),
('t2','c5','2021-03-06'), ('t2','c6','2021-09-06'),
('t3','c2','2021-03-08'), ('t3','c4','2021-09-08'),
('t4','c3','2021-03-10'),
('t5','c7','2021-09-12'), ('t5','c8','2021-03-15');
4-1

无参存储过程

讲义 · 两个例子与调用
目的
完成例 15-10：建立不带参数的存储过程 showstudentsnums，查询学生人数，然后调用它。
前提
已执行数据准备。
  1. 建立 showstudentsnums
  2. 用 CALL 调用两次，一次带括号，一次不带

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE PROCEDURE showstudentsnums()
BEGIN
  SELECT COUNT(*) AS 学生人数 FROM s;
END $$
DELIMITER ;
CALL showstudentsnums();
CALL showstudentsnums;

语句解释

()没有参数，定义时括号也不能省
CALL 过程名调用无参过程时括号可以省略
存放位置存储过程属于当前数据库 teaching
执行后看：两次调用都得到 8。
4-2

选课门数

讲义 · 两个例子与调用
目的
完成例 15-11：建立 cal_courses，列出所有同学的学号、姓名和选课门数，没有选课的同学显示为 NULL。
前提
已完成实验 4-1。
  1. 用学生表左连接按学号分组的选课统计
  2. 调用 cal_courses

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE PROCEDURE cal_courses()
BEGIN
  SELECT s.sno, s.sn, courses.num AS 选课门数
  FROM s LEFT JOIN
       (SELECT sno, COUNT(*) AS num FROM sc GROUP BY sno) AS courses
  ON s.sno = courses.sno;
END $$
DELIMITER ;
CALL cal_courses();

语句解释

子查询 courses先按学号统计每人的选课门数
LEFT JOIN学生表中的行全部保留，没有选课的同学在 courses 中找不到对应行，num 为 NULL
要显示 0可以写成 IFNULL(courses.num, 0)
执行后看：8 行，s6 何欣荣的选课门数为 NULL，s2 和 s4 为 4。
4-3

IN 参数

讲义 · 三种参数
目的
建立带输入参数的存储过程，按院系名称查询学生。
前提
已完成实验 4-2。
  1. 建立 dept_students，参数 dp 为院系名称
  2. 分别查理学院和工学院

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE PROCEDURE dept_students(IN dp VARCHAR(30))
BEGIN
  SELECT sno, sn, maj FROM s WHERE dept = dp;
END $$
DELIMITER ;
CALL dept_students('理学院');
CALL dept_students('工学院');

语句解释

IN dp VARCHAR(30)输入参数，写明名称和类型，IN 可以省略
调用时实参的个数和类型要与形参一致
参数名不要与字段名相同，否则 WHERE 中分不清
执行后看：理学院 2 人：魏立、赵琳琳。工学院 2 人：陶然、李轩。
4-4

OUT 参数

讲义 · 三种参数讲义 · 实参与调用
目的
建立 cal_students：按性别和院系统计人数，由 OUT 参数带回。再用常量作 OUT 参数的实参试一试。
前提
已完成实验 4-3。
  1. 建立 cal_students，两个 IN 参数、一个 OUT 参数
  2. 用 @n 接收结果并查询
  3. 把第三个实参写成 5

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE PROCEDURE cal_students(IN sst ENUM('男', '女'), IN dp VARCHAR(45), OUT num INT)
BEGIN
  DECLARE people INT DEFAULT 0;
  SELECT COUNT(*) INTO people FROM s WHERE sex = sst AND dept = dp;
  SET num = people;
END $$
DELIMITER ;
CALL cal_students('女', '信息学院', @n);
SELECT @n;
-- OUT 参数的实参必须是变量，报错 1414
CALL cal_students('女', '信息学院', 5);

语句解释

ENUM参数只能取 男 或 女
OUT num INT输出参数，过程中给它赋值，调用结束后值传给实参
@n用用户会话变量接收输出参数的值
报错 1414常量不能接收输出的值
执行后看：@n 为 3。最后一句报错 1414。
4-5

INOUT 参数

讲义 · 三种参数
目的
建立一个把参数翻倍的存储过程，参数既传入又传出，连续调用两次。
前提
已完成实验 4-4。
  1. 建立 double_it，参数为 INOUT
  2. 给 @v 赋值 5，调用两次后查询 @v

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE PROCEDURE double_it(INOUT x INT)
BEGIN
  SET x = x * 2;
END $$
DELIMITER ;
SET @v = 5;
CALL double_it(@v);
CALL double_it(@v);
SELECT @v;

语句解释

INOUT调用时要传入一个有值的变量，过程结束后把新值带回
两次调用第一次 5 变 10，第二次 10 变 20
一行过程体只有一条语句时也可以不写 BEGIN … END
执行后看：@v 为 20。
4-6

查看、修改与删除

讲义 · 查看存储过程讲义 · 修改与删除
目的
查看 cal_courses 的定义和状态，修改它的特征，最后删除 showstudentsnums。
前提
已完成实验 4-5。
  1. 用 SHOW CREATE PROCEDURE 查看定义
  2. 修改特征：READS SQL DATA、INVOKER、注释
  3. 查看状态，确认修改
  4. 删除 showstudentsnums，再删除一次

参考答案

可以直接复制到 Workbench 执行
SHOW CREATE PROCEDURE cal_courses;
ALTER PROCEDURE cal_courses
  READS SQL DATA
  SQL SECURITY INVOKER
  COMMENT '显示学生选课门数';
SHOW PROCEDURE STATUS LIKE 'cal%';
DROP PROCEDURE IF EXISTS showstudentsnums;
-- 已经删除，再删报错 1305
DROP PROCEDURE showstudentsnums;

语句解释

SHOW CREATE PROCEDURE查看过程的定义，Create Procedure 一列是完整的代码
ALTER PROCEDURE只能修改特征，不能修改过程体。要改代码，先删除再重建
READS SQL DATAcal_courses 只读数据，所以声明 READS SQL DATA
报错 1305PROCEDURE does not exist，加上 IF EXISTS 就不会报错
执行后看：状态中 cal_courses 的 Security_type 为 INVOKER，Comment 为 显示学生选课门数。最后一句报错 1305。
保山学院·人工智能教研室·曹鼎鼎