课程 › 第 15 章 › 实验五
第 15 章 · 实验五 · 在第八节之后

自定义函数与游标

先看不声明特征时函数为什么建不起来，再建立 cal_depts、isprime 和一个求等级的函数，最后用游标逐行处理查询结果。

环　境

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

执　行

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

注　意

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

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

  1. 实验 5-1不声明特征
  2. 实验 5-2cal_depts
  3. 实验 5-3isprime
  4. 实验 5-4在查询中使用函数
  5. 实验 5-5游标的四个步骤
  6. 实验 5-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');
5-1

不声明特征

讲义 · 创建函数
目的
建立一个统计院系个数的函数，不写任何特征，看 MySQL 8.0 的反应。
前提
已执行数据准备。
  1. 建立 cal_depts，返回院系的个数，不写特征

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE FUNCTION cal_depts() RETURNS INT
BEGIN
  DECLARE n INT;
  SELECT COUNT(DISTINCT dept) INTO n FROM s;
  RETURN n;
END $$
DELIMITER ;

语句解释

RETURNS INT声明返回值的类型，注意是 RETURNS
RETURN n返回一个值，函数体中必须有
报错 1418MySQL 8.0 默认开启二进制日志，函数必须声明 DETERMINISTIC、NO SQL 或 READS SQL DATA 之一
执行后看：报错 1418，函数没有建立。
5-2

cal_depts

讲义 · 创建函数讲义 · 调用与维护函数
目的
给函数加上 READS SQL DATA 重新建立，调用它，再把它用在查询条件中。
前提
已完成实验 5-1。
  1. 建立 cal_depts，声明 READS SQL DATA
  2. 调用 cal_depts()
  3. 在 WHERE 中使用函数

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE FUNCTION cal_depts() RETURNS INT
READS SQL DATA
BEGIN
  DECLARE n INT;
  SELECT COUNT(DISTINCT dept) INTO n FROM s;
  RETURN n;
END $$
DELIMITER ;
SELECT cal_depts();
SELECT cno, cn FROM c WHERE ct / 16 > cal_depts();

语句解释

READS SQL DATA函数中有读数据的语句，但不修改数据
SELECT cal_depts()函数与内置函数一样用在表达式中，不用 CALL
ct / 16 > cal_depts()学时除以 16 大于院系个数 3 的课程
执行后看：cal_depts() 为 3。第二个查询得到 c4 数据结构、c5 数据库系统、c7 高等数学。
5-3

isprime

讲义 · 创建函数
目的
建立判断素数的函数 isprime，分别判断 1、7、9、97。
前提
已完成实验 5-2。
  1. 建立 isprime，参数 m，返回 BOOL，声明 NO SQL
  2. 用 WHILE 从 2 试除到 m-1

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE FUNCTION isprime(m INT) RETURNS BOOL
NO SQL
COMMENT '判断一个数是否为素数，是返回 true'
BEGIN
  DECLARE i INT DEFAULT 2;
  IF m <= 1 THEN
    RETURN FALSE;
  END IF;
  WHILE i <= m - 1 DO
    IF m MOD i = 0 THEN
      RETURN FALSE;
    END IF;
    SET i = i + 1;
  END WHILE;
  RETURN TRUE;
END $$
DELIMITER ;
SELECT isprime(1), isprime(7), isprime(9), isprime(97);

语句解释

m INT函数的参数只能是输入参数，不写 IN
BOOL就是 TINYINT(1)，TRUE 为 1，FALSE 为 0
NO SQL函数体中没有读写数据的语句
循环中的 RETURN执行到 RETURN，函数立即结束
执行后看：得到 0、1、0、1。
5-4

在查询中使用函数

讲义 · 调用与维护函数
目的
把实验 2-4 中按分数段求等级的 CASE 写成函数 get_level，在查询选课表时使用。
前提
已完成实验 5-3。
  1. 建立 get_level，参数为成绩，返回等级
  2. 查询 s2、s4 的成绩和等级

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE FUNCTION get_level(g DECIMAL(5,2)) RETURNS VARCHAR(4)
DETERMINISTIC
BEGIN
  RETURN CASE
    WHEN g IS NULL THEN '未考'
    WHEN g < 60 THEN '不及格'
    WHEN g < 70 THEN '及格'
    WHEN g < 90 THEN '良好'
    ELSE '优秀'
  END;
END $$
DELIMITER ;
SELECT sno, cno, score, get_level(score) AS 等级
FROM sc WHERE sno IN ('s2', 's4');

语句解释

DETERMINISTIC同样的参数总是得到同样的结果
RETURN CASE … END直接返回 CASE 表达式的值
好处等级的规则写一次，多处查询都能用
执行后看：8 行，结果与实验 2-4 的第二个查询相同。
5-5

游标的四个步骤

讲义 · 游标的概念与声明讲义 · 打开、提取与关闭游标
目的
用游标逐行取出理学院学生的姓名。理学院只有 2 人，故意取 3 次，看第 3 次会怎样。
前提
已完成实验 5-4。
  1. 声明游标，结果集为理学院学生的姓名
  2. 打开游标，取 3 次，每次输出取到的姓名
  3. 关闭游标

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE PROCEDURE fetch3()
BEGIN
  DECLARE x VARCHAR(20);
  DECLARE c CURSOR FOR SELECT sn FROM s WHERE dept = '理学院';
  OPEN c;
  FETCH c INTO x;
  SELECT x;
  FETCH c INTO x;
  SELECT x;
  -- 已经没有下一行，报错 1329
  FETCH c INTO x;
  SELECT x;
  CLOSE c;
END $$
DELIMITER ;
CALL fetch3();

语句解释

DECLARE … CURSOR FOR声明游标，这时查询还没有执行
OPEN执行查询，游标指向第一行
FETCH … INTO取出当前行存入变量，游标移到下一行
报错 1329No data，结果集已经取完。过程在这里中止，后面的语句没有执行
执行后看：先后输出 魏立 和 赵琳琳，然后报错 1329。
5-6

游标与循环

讲义 · 打开、提取与关闭游标
目的
用循环和游标把某个院系所有学生的姓名连成一个字符串，取完时由处理程序结束循环。
前提
已完成实验 5-5。
  1. 建立 dept_names，IN 参数为院系，OUT 参数带回姓名串
  2. 声明 NOT FOUND 处理程序
  3. 调用，查看信息学院的结果

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE PROCEDURE dept_names(IN dp VARCHAR(30), OUT name_list VARCHAR(200))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE x VARCHAR(20);
  DECLARE c CURSOR FOR SELECT sn FROM s WHERE dept = dp ORDER BY sno;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN c;
  lp: LOOP
    FETCH c INTO x;
    IF done = 1 THEN
      LEAVE lp;
    END IF;
    SET name_list = CONCAT_WS(',', name_list, x);
  END LOOP lp;
  CLOSE c;
END $$
DELIMITER ;
CALL dept_names('信息学院', @names);
SELECT @names;

语句解释

声明的顺序先变量，再游标，最后处理程序
CONTINUE HANDLER FOR NOT FOUND取不到数据时不报错，执行 SET done = 1 后继续
IF done = 1取完就用 LEAVE 结束循环
CONCAT_WS用逗号把不为 NULL 的值连起来。OUT 参数开始时为 NULL，所以第一个姓名前面没有逗号
执行后看：@names 为 王彤,苏乐,林欣怡,何欣荣。
保山学院·人工智能教研室·曹鼎鼎