数据库原理与应用 第 15 章 · 第八节
课程 › 第 15 章 › 第八节 创建函数调用与维护游标提取与关闭 版式
第 15 章　存储过程与函数　·　第八节

自定义函数与游标

创建、调用和维护自定义函数，游标的声明、打开、提取和关闭
本 节 知 识 点
  1. 30创建函数
  2. 31调用与维护函数
  3. 32游标的概念与声明
  4. 33打开、提取与关闭游标
30
第 15 章 · 第八节 · 课件第 72、73、74、75 页合并重做

创建函数

RETURNS 声明类型，函数体中用 RETURN 返回值。

15.5 自定义函数课件第 72、73、74、75 页合并

创建自定义函数

CREATE FUNCTION 函数名([参数名 类型[, …]]) RETURNS 类型 [特征 …] BEGIN 函数体 END
与存储过程的不同
  • 参数只能是输入参数，不写 IN
  • 用 RETURNS 声明返回值的类型
  • 函数体中至少要有一条 RETURN 值;
  • 要建在其他数据库中，写成 数据库名.函数名
MySQL 8.0 默认开启二进制日志，函数必须声明 DETERMINISTIC、NO SQL、READS SQL DATA 三者之一，否则报错 1418。
例：判断素数
DELIMITER $$
CREATE FUNCTION isprime(m INT)
RETURNS BOOL
NO SQL
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 ;

实　验

实验 5-1　不声明特征

实　验

实验 5-3　isprime

31
第 15 章 · 第八节 · 课件第 76、77、78、79、80 页合并重做

调用与维护函数

像内置函数一样调用，查看、修改、删除与过程相仿。

15.5 自定义函数课件第 76、77、78、79、80 页合并

调用与维护函数

调用
  • 与内置函数的用法相同：函数名(实参)
  • 可以用在 SELECT、WHERE 等表达式中，不用 CALL
SELECT cal_depts();
SELECT isprime(7), isprime(9);
查看、修改、删除
操作语句
查看状态SHOW FUNCTION STATUS [LIKE '模式'];
查看代码SHOW CREATE FUNCTION 函数名;
修改特征ALTER FUNCTION 函数名 特征 …;
删除DROP FUNCTION [IF EXISTS] 函数名;
  • ALTER FUNCTION 也只能改特征，要改代码，删除后重建
ALTER FUNCTION isprime SQL SECURITY INVOKER;
DROP FUNCTION IF EXISTS cal_depts;

实　验

实验 5-2　cal_depts

实　验

实验 5-4　在查询中使用函数

32
第 15 章 · 第八节 · 课件第 81、82、83 页合并重做

游标的概念与声明

游标让程序逐行处理查询结果。

15.6 游标的使用课件第 81、82、83 页合并

游标的概念与声明

游标是从包含多条记录的结果集中逐条访问记录的机制，由结果集和游标位置两部分组成：结果集是定义游标的 SELECT 语句的结果，游标位置是指向其中某一行的指针。
使用游标的四个步骤
步骤语句
1 声明DECLARE 游标名 CURSOR FOR SELECT 语句;
2 打开OPEN 游标名;
3 提取FETCH 游标名 INTO 变量[, …];
4 关闭CLOSE 游标名;
要点
  • 游标只能用在存储过程和函数中
  • 利用游标，可以从前向后逐条处理结果集中的记录
  • 声明游标时 SELECT 并不执行，还没有结果集
DECLARE s_cursor CURSOR FOR SELECT sno, sn FROM s;

实　验

实验 5-5　游标的四个步骤

33
第 15 章 · 第八节 · 课件第 84、85、86 页合并重做

打开、提取与关闭游标

OPEN、FETCH、CLOSE，取完由处理程序结束循环。

15.6 游标的使用课件第 84、85、86 页合并

打开、提取与关闭游标

语句作用
OPEN执行 SELECT，结果集存放在服务器内存中，游标指向第一条记录
FETCH取出游标指向的记录存入变量，游标移到下一条。一次只取一条
CLOSE释放结果集占用的内存
取完最后一条再 FETCH，报错 1329。要先声明 NOT FOUND 处理程序，再用循环逐条提取。
  • 声明的顺序：变量、游标、处理程序
例：连接一个院系的学生姓名
DECLARE done INT DEFAULT 0;
DECLARE x VARCHAR(20);
DECLARE c CURSOR FOR
  SELECT sn FROM s WHERE dept = dp;
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;

试一试游标逐行提取

游标 c 的结果集：信息学院学生的姓名
执行
行sn

实　验

实验 5-6　游标与循环

保山学院·人工智能教研室·曹鼎鼎