HNU_数据库_实验6（2021级）-存储过程实验

实验六 存储过程实验

0.LAB6-SQL语句汇总

# 无参数的存储过程
# 定义一个存储过程，查询选课表。
DELIMITER $
CREATE PROCEDURE select_sc()
BEGIN 
	SELECT * FROM sc;
END$
DELIMITER ;
call select_sc();

# 有参数的存储过程
# 定义一个存储过程，查询学号为202103001的选课记录
DELIMITER $
CREATE PROCEDURE select_sc_s(IN Sno varchar(9))
BEGIN 
    SELECT * FROM sc WHERE sc.Sno=Sno;
END$
DELIMITER ;
DROP PROCEDURE select_sc_s;
call select_sc_s('202103001');

# 有输出参数的存储过程
# 定义一个存储过程，查询某个学生的某门课程的成绩
DELIMITER $
CREATE PROCEDURE select_sc_g(IN Sno varchar(9),IN Cno varchar(4),OUT Grade int)
BEGIN 
    SELECT sc.Grade INTO Grade
		FROM sc WHERE sc.Sno=Sno AND sc.Cno=Cno;
END$
DELIMITER ;
call select_sc_g('202103001','7',@Grade);
select @Grade;

# 有局部变量的存储过程
# 定义一个存储过程，比较一个学生的两门课的成绩大小：
DELIMITER $
CREATE PROCEDURE compare_grade(IN Sno varchar(9),IN Cno1 varchar(4),IN Cno2 varchar(4),OUT result varchar(45))
BEGIN
	DECLARE Grade1 INT;
    DECLARE Grade2 INT;
    
    SELECT sc.Grade INTO Grade1
		FROM sc WHERE sc.Sno=Sno AND sc.Cno=Cno1;
	SELECT sc.Grade INTO Grade2
		FROM sc WHERE sc.Sno=Sno AND sc.Cno=Cno2;
        
	IF Grade1 > Grade2 THEN SET result="前者成绩更高";
    ELSEIF Grade1 < Grade2 THEN SET result="后者成绩更高";
    ELSE SET result="二者成绩相同";
    END IF;
END$
DELIMITER ;
# DROP PROCEDURE compare_grade;
ALTER PROCEDURE compare_grade COMMENT "比较一个学生的两门课的成绩大小";
call compare_grade('200215122','2','3',@res);
SELECT @res;

# 无参数的自定义函数
# 定义一个函数，查询选课表的记录条数。
DELIMITER $
CREATE FUNCTION F_select_sc() RETURNS INT
READS SQL DATA
BEGIN 
	DECLARE n INT DEFAULT 0;
	SELECT COUNT(*) INTO n FROM sc;
    RETURN n;
END$
DELIMITER ;
select F_select_sc();

# 有参数的自定义函数
# 定义一个函数， 查询学号为200215122的选课记录条数：
DELIMITER $
CREATE FUNCTION F_select_sc_s(Sno varchar(9)) RETURNS INT
READS SQL DATA
BEGIN 
	DECLARE n INT DEFAULT 0;
	SELECT COUNT(*) INTO n FROM sc WHERE sc.Sno=Sno;
    RETURN n;
END$
DELIMITER ;
DROP FUNCTION F_select_sc_s;
/*添加注释*/
ALTER FUNCTION F_select_sc COMMENT "查询选课表的记录条数";
select F_select_sc_s('200215122');

# 普通游标
# 使用游标功能，查找满18岁的学生数量：
DELIMITER $
CREATE PROCEDURE age_18()
BEGIN
    DECLARE c INT DEFAULT 0;
    DECLARE total INT DEFAULT 0;
    
    -- 创建接收游标数据的变量
    DECLARE done INT DEFAULT FALSE;
    -- 创建结束标志变量
    DECLARE cur CURSOR FOR SELECT Sage FROM student WHERE Sage >= 18;
    -- 指定游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- 设置初始值
    SET total = 0;
    -- 打开游标
    OPEN cur;
    read_loop: LOOP
        -- 根据游标当前指向的一条数据
        FETCH cur INTO c;
        -- 判断循环是否结束
        IF done THEN
            -- 跳出游标循环
            LEAVE read_loop; 
        END IF;
        SET total = total + 1; -- 获取一条数据将count值进行累加操作
    END LOOP; -- 结束游标循环
    CLOSE cur; -- 关闭游标
    SELECT total; -- 输出结果
END $
DELIMITER ;
call age_18();


-- 创建带有OUT参数的存储过程
DELIMITER $
CREATE PROCEDURE GetStudentEnrollments(IN sno INT, OUT sName VARCHAR(255), OUT courseList VARCHAR(1024))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE courseName VARCHAR(255);    
    -- 为学生的注册声明游标
    DECLARE cur CURSOR FOR 
        SELECT c.cname FROM sc JOIN course c ON sc.cno = c.cno WHERE sc.sno= sno;    
    -- 为未找到声明处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- 获取学生姓名
    SELECT student.sname INTO sName FROM student WHERE student.sno = sno;    
    -- 初始化courseList
    SET courseList = '';    
    -- 打开游标
    OPEN cur;    
    -- 遍历结果
    read_loop: LOOP
        FETCH cur INTO courseName;
        IF done THEN
            LEAVE read_loop;
        END IF;        
        -- 连接课程名称
        SET courseList = CONCAT(courseList, courseName, ', ');
    END LOOP;    
    -- 关闭游标
    CLOSE cur;    
    -- 移除末尾的逗号和空格
    SET courseList = SUBSTRING(courseList, 1, LENGTH(courseList) - 2);
END $
DELIMITER ;
CALL GetStudentEnrollments('200215122', @studentName, @courseList);
SELECT @studentName AS studentName, @courseList AS courseList;


# 带参数的游标
#  检索指定学生所选课程的信息。
DELIMITER $
CREATE PROCEDURE P(IN sno VARCHAR(9))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE courseID varchar(4);
    DECLARE courseName VARCHAR(255);    
    -- 创建带参数的游标
    DECLARE cur CURSOR FOR 
        SELECT sc.cno, c.cname FROM sc JOIN course c ON sc.cno = c.cno WHERE sc.sno= sno;    
    -- 声明未找到处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;    
    -- 打开游标
    OPEN cur;    
    -- 输出学生ID和名称
    SELECT student.sno, student.sname FROM student WHERE student.sno = sno;    
    -- 遍历结果
    read_loop: LOOP
        FETCH cur INTO courseID, courseName;
        IF done THEN
            LEAVE read_loop;
        END IF;        
        -- 输出课程信息
        SELECT courseID, courseName;
    END LOOP;    
    -- 关闭游标
    CLOSE cur;    
END $
DELIMITER ;
DROP PROCEDURE P;
CALL P('200215121');

实验 6.1 存储过程实验

1.实验目的

掌握数据库PL/SQL编程语言，以及数据库存储过程的设计和使用方法。

2.实验内容和要求

存储过程定义，存储过程运行，存储过程更名，存储过程删除，存储过程的参数传递。掌握PL/SQL编程语言和编程规范，规范设计存储过程。

3.实验重点和难点

实验重点：存储过程定义和运行。

实验难点：存储过程的参数传递方法。

4.实验过程

(1)无参数的存储过程

定义一个存储过程，查询选课表。

(2)有参数的存储过程

定义一个存储过程，查询学号为202103001的选课记录：

in 表示传入的参数，可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。

out 表示从存储过程内部传值给调用者。

(3)有输出参数的存储过程

定义一个存储过程，查询某个学生的某门课程的成绩：

输出参数为out Grade;返回值放在@Grade中，可以查看@Grade 的值。

(4)有局部变量的存储过程

定义一个存储过程，比较一个学生的两门课的成绩大小：

(5)修改存储过程

在MYSQL中，ALTER PROCEDURE 语句用于修改存储过程的某些特征。

如果要修改存储过程的内容，可以先删除原存储过程，再以相同的命名创建新的存储过程；

如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程。

MySQL 中修改存储过程的语法格式如下:

ALTER PROCEDURE 存储过程名 [ 特征 … ]
​
• 特征指定了存储过程的特性，可能的取值有：
CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
NO SQL 表示子程序中不包含 SQL 语句。
READS SQL DATA 表示子程序中包含读数据的语句。
MODIFIES SQL DATA 表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
   DEFINER 表示定义者有对存储过程中对象的访问权限，调用者就能够执行。
   INVOKER 表示调用者必须拥有存储过程中的对象的访问权限，才能执行。
COMMENT ‘string’ 表示注释信息。

举例：为compare_grade添加注释：

验证：

(6)删除存储过程

删除存储过程compare_grade。

5.实验总结

用户自定义函数和存储过程可以可以带有IN, OUT, INOUT参数。在MYSQL中，获取OUT值需要先声明一个变量，然后执行函数或存储过程，获取结果，不能传入NULL获取结果集。存储过程不可以修改名称或内容，只可以修改其特征。用户自定义函数只能通过SELECT调用，而存储过程只能通过call调用。

6.思考题

(1))试总结几种调试存储过程的方法

可以通过在存储过程中添加RAISE语句进行调试，也可以通过OUT参数测试存储过程中的中间结果是否正确。

(2)存储过程中的SELECT语句与普通的SELECT语句格式有何不同？执行方法有何不同？

存储过程中的SELECT语句和普通的SELECT语句的不同在于存储过程中的SELECT语句将值通过INTO子句赋值给变量，而普通的SELECT语句一般直接返回结果集。

实验 6.2 自定义函数实验

1.实验目的

掌握数据库PL/SQL编程语言以及数据库自定义函数的设计和使用方法。

2.实验内容和要求

自定义函数定义，自定义函数运行，自定义函数更名，自定义函数删除，自定义函数的参数传递。掌握PL/SQL和编程规范，规范设计自定义函数。

3.实验重点和难点

实验重点：自定义函数的定义和运行。

实验难点：自定义函数的参数传递方法。

4.实验过程

自定义函数 (user-defined function UDF是一种对MySQL扩展的途径，其用法和内置函数相同。 自定义函数的两个必要条件：

• 参数

• 返回值（必须有）。函数可以返回任意类型的值。

语法如下：

CREATE FUNCTION function_name(parameter_nametype,[parameter_name type,...])
RETURNS {
STRING|INTEGER|REAL}
runtime_body

(1)无参数的自定义函数

定义一个函数，查询选课表的记录条数。

①报错：

②原因：

报错与MySQL存储函数的二进制日志记录要求有关。在MySQL中，当创建存储函数时，它需要声明为DETERMINISTIC、NO SQL或READS SQL DATA之一。这对于二进制日志记录的一致性和可预测性非常重要。

③修改：

要解决此问题，可以修改函数声明以包括其中的一个特性。这里函数正在从表中读取数据（使用SELECT COUNT(*) INTO n FROM sc），可以使用READS SQL DATA。

(2)有参数的自定义函数

定义一个函数， 查询学号为200215122的选课记录条数：

注：这里的参数就不用再写IN了。

(3)有局部变量的自定义函数

见上面的（1）、（2）

(4)修改自定义函数

类似于存储过程，修改自定义函数只能修改其特性，不能修改名称或函数体。

验证：

(5)删除自定义函数

删除F_select_sc_s函数。

5.实验总结

MYSQL中，自定义函数需要在定义时声明返回值的类型，并在函数结束后显示使用RETURN语句返回值。

MYSQL的FUNCTION语句只能通过RETURN返回值，不能通过OUT参数返回多个值，且只能通过SELECT语句调用，不能使用call语句调用。

6.思考题

(1)试分析自定义函数与存储过程的区别与联系。

自定义函数和存储过程都用于执行一些SQL语句，完成计算值等操作。

二者的区别在于：

1. 存储过程只能单独使用CALL函数调用，而函数在SELECT语句中，像内置函数一样调用。

2. 存储过程可以有IN, OUT参数，返回多个值。而函数只能返回一个值。

3. 存储过程可以调用函数，函数不能使用存储过程。

(2)如何使得自定义函数可以返回多个值？如何利用？

MYSQL中，自定义函数不可以使用OUT或INOUT参数，因此只能返回一个值。

其他数据库管理系统例如POSTGRELSQL，可以指定OUT参数返回多个值，并且返回值的类型可以是TABLE，即可以返回一个结果集。对于OUT参数，可以先定义一个变量，然后调用函数并传入该变量，得到返回值结果。

实验6.3 游标实验

1.实验目的

掌握PL/SQL游标的设计、定义和使用方法，理解PL/SQL游标按行操作和SQL按结果集操作的区别和联系。

2.实验内容和要求

游标定义、游标使用。掌握各种类型游标的特点、区别与联系。

3.实验重点和难点

实验重点：游标定义和使用。

实验难点：游标类型。

4.实验过程

关于游标：

游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。
游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
尽管游标能遍历结果中的所有行，但一次只指向一行。
游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。
​
​
游标的三个属性：
1.不敏感（Asensitive）：数据库可以选择不复制结果集
2.只读（Read only）
3.不滚动（Nonscrollable）：游标只能向一个方向前进，并且不可以跳过任何一行数据。
​
​
游标的处理过程
1. 声明游标 declare：没有检索数据，只是定义要使用的 select 语句
DECLARE cursor_name CURSOR FOR select_statement  
​
2. 打开游标 open：打开游标以供使用，用上一步定义的 select 语句把数据实际检索出来
OPEN cursor_name;
​
3. 检索游标 fetch：对于填有数据的游标，根据需要取出(检索)各行
FETCH cursor_name INTO var_name [, var_name]...
​
4. 关闭游标 close：在结束游标使用时，必须关闭游标。
CLOSE cursor_name; // 关闭游标
DEALLOCATE cursor_name; //释放游标

(1)普通游标

使用游标功能，查找满18岁的学生数量：

(2)REFCURSOR类型游标

REF CURSOR是动态游标，一般的CURSOR是静态游标。

一般的CURSOR在定义时结果集的类型就是确定的，不可以再更改，而动态游标可以打开任何形式的结果集，取出任何类型的记录数据。

在MYSQL中只能使用静态游标，定义时的SELECT语句必须确定，不可以再更改。

但是，您可以通过使用存储过程和OUT参数来实现类似的功能。以下是使用三个基本表：students（学生表），courses（课程表）和enrollments（选课表）的示例。此示例检索学生信息以及他们所注册的课程：

(3)带参数的游标

检索指定学生所选课程的信息。

5.实验总结

游标对数据库记录逐条处理，而不是对结果集一起处理。声明游标和游标结束处理的异常需要按顺序声明。REF CURSOR是动态游标，可以在打开该游标时指定SELECT语句产生游标的结果集，游标的结果可以使用RECORD类型保存。在MYSQL中不支持动态游标，也没有RECORD类型，游标也不可以使用参数，只能声明变量完成处理。

6.思考题 

试 试分析说明REFCURSOR类型游标的优点。

REFCURSOR类型的游标使用灵活，可以多次打开不同的SELECT结果集，实现对不同类型数据的逐条处理。如果使用一般的游标，只能提前将所有需要处理的结果集的SELECT语句都声明一个游标使用。