【头歌系统数据库实验】实验14 数据库编程-2

2024-01-02 19:31:20

目录

第1关:定义一个名为PROC_UPDATEGRADE的存储过程

第2关:使用游标定义一个名为PROC_JGRADE无参存储过程

第3关:使用游标定义一个名为PROC_NUMGRADE有参存储过程


如果对你有帮助的话,不妨点赞收藏评论一下吧,爱你么么哒😘??????

第1关:定义一个名为PROC_UPDATEGRADE的存储过程

任务描述

定义一个名为PROC_UPDATEGRADE的存储过程,将学生选课成绩从百分制改为等级制(即A、B、C、D、E),其中A为[90,100],B为[80,90),C为[70,80),D为[60,70),E为[0,60)。

相关知识

1、学生选课表SC由学号(Sno)、课程号(Cno)、成绩(Grade)组成。 SC表如下图:

,

现已构建SC表,结构信息如下:

,

创建SC表的Mysql代码为:

CREATE TABLE SC( Sno CHAR(9), Cno CHAR(4), Grade INT, PRIMARY KEY(Sno, Cno) );

INSERT INTO SC VALUES('200215121','1',92); INSERT INTO SC VALUES('200215121','2',85); INSERT INTO SC VALUES('200215121','3',88); INSERT INTO SC VALUES('200215122','2',90); INSERT INTO SC VALUES('200215122','3',80); INSERT INTO SC VALUES('200215123','1',60); INSERT INTO SC VALUES('200215123','2',75); INSERT INTO SC VALUES('200215124','1',52); INSERT INTO SC VALUES('200215124','2',63); INSERT INTO SC VALUES('200215125','1',100); INSERT INTO SC VALUES('200215125','2',100); INSERT INTO SC VALUES('200215126','2',34); INSERT INTO SC VALUES('200215126','3',45); INSERT INTO SC VALUES('200215127','2',86); INSERT INTO SC VALUES('200215127','3',88);

2、存储过程定义 定义一个存储过程语句的语法格式如下:

  1. CREATE [OR REPLACE ] PROCEDURE <模式名.存储过程名> [WITH ENCRYPTION]
  2. [(<参数名> <参数模式> <参数数据类型> [<默认值表达式>]
  3. {,<参数名> <参数模式> <参数数据类型> [<默认值表达式>] })]
  4. AS | IS
  5. [<说明语句端段>]
  6. BEGIN
  7. <执行语句段>
  8. [Exception
  9. <异常处理语句段>]
  10. END;

其中: (1)<模式名.存储过程名>:指明被创建的存储过程的名称。 (2)<参数名>:指明存储过程参数的名称。 (3) WITH ENCRYPTION:为可选项,如果指定 WITH ENCRYPTION 选项,则对 BEGIN 到 END 之间的语句块进行加密,防止非法用户查看其具体内容,加密后的存储过程或存储函数的定义可在 SYS.SYSTEXTS 系统表中查询。 (4)<参数模式>:指明存储过程参数的输入/输出方式。参数模式可设置为 IN、OUT 或 IN OUT(OUT IN),默认为 IN 类,IN 表示向存储过程传递参数,OUT 表示从存储过程返回参数。而 IN OUT 表示传递参数和返回参数。 (5)<参数数据类型>:指明存储过程参数的数据类型。 (6)<说明语句端段>:由变量、游标和子程序等对象的申明构成。 (7)<执行语句段>:由 SQL 语句和过程控制语句构成的执行代码。 (8)<异常处理语句段>:各种异常的处理程序,存储过程执行异常时调用,可默认。 注意:使用该语句的用户必须是 DBA 或该存储过程的拥有者且具有 CREATE PROCEDURE 数据库权限的用户;参数的数据类型只能指定变量类型,不能指定长度。

相关操作① DECLARE定义变量 DECLARE用于定义变量,在存储过程和函数中通过declare定义变量在BEGIN...END中,且在语句之前,定义变量语法为:

  1. DECLARE 变量名 变量类型 [DEFAULT 初始化值];

例如:

  1. DECLARE a, b INT DEFAULT 5;

② SET定义用户变量 SET语句可用于向系统变量或用户变量赋值,用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效,针对用户变量的定义如下:

  1. SET @var_name = expr [, @var_name = expr] ...

例如:

  1. SET @name = 'abc', @weight = 20;

③ 存储过程调用:CALL命令 过程体调用CALL sp_name[(传参)]; ④ 查看存储过程:SHOW PROCEDURE STATUS命令 SHOW PROCEDURE STATUS where db='数据库名'; 命令查看对应数据库中存在哪些存储过程 ⑤ 存储过程修改:ALTER PROCEDURE命令 MySQL 中修改存储过程的语法格式如下: ALTER PROCEDURE 存储过程名 [ 特征 ... ] 注意:ALTER PROCEDURE 语句用于修改存储过程的某些特征。如果要修改存储过程的内容,可以先删除原存储过程,再以相同的命名创建新的存储过程;如果要修改存储过程的名称,可以先删除原存储过程,再以不同的命名创建新的存储过程。 ⑥ 存储过程删除:DROP PROCEDURE命令 存储过程被创建后,就会一直保存在数据库服务器上,直至被删除。当 MySQL 数据库中存在废弃的存储过程时,我们需要将它从数据库中删除。 MySQL 中使用 DROP PROCEDURE 语句来删除数据库中已经存在的存储过程。语法格式如下: DROP PROCEDURE [ IF EXISTS ] <过程名> 语法说明如下: ? 过程名:指定要删除的存储过程的名称。 ? IF EXISTS:指定这个关键字,用于防止因删除不存在的存储过程而引发的错误。 注意:存储过程名称后面没有参数列表,也没有括号,在删除之前,必须确认该存储过程没有任何依赖关系,否则会导致其他与之关联的存储过程无法运行。 ⑦ OUT参数输出:SELECT @变量名 例:存储过程create procedure out_param(out p_out int);调用后输出OUT参数方法如下: CALL out_param(@pp_out); SELECT @pp_out; 注意:pp_out可与 p_out同名 ⑧ 修改命令结束符:DELIMITER命令

,

⑨ 使用SELECT …INTO语句为变量赋值 在MySQL存储过程中,可以使用SELECT …INTO语句对变量进行赋值,该语句在数据库中进行查询,并将得到的结果赋值给变量。SELECT …INTO语句的语法格式如下:

  1. SELECT col_name[,...] INTO var_name[,...] table_expr

col_name:要从数据库中查询的列字段名; var_name:变量名,列字段名按照在列清单和变量清单中的位置对应,将查询得到的值赋给对应位置的变量; table_expr:SELECT语句其余部分,包括可选的FROM子句和WHERE子句。 需要注意的是,在使用SELECT …INTO语句时,变量名不能和数据表中的字段名不能相同,否则会出错。 例如:

,

⑩ 将变量值返回给调用者 在存储过程中定义的变量,经过一系列的处理之后,结果值可能需要返回给存储过程调用者。那么如何返回呢?方便的做法是使用SELECT语句将变量作为结果集返回,例如:

,

创建存储过程样例:创建一个存储过程,查询某用户ID在T表中的用户名

,


开始你的任务吧,祝你成功!

USE myschool;
#增加一列等级列
ALTER TABLE SC ADD `rank` CHAR(2);
#请在此处添加实现代码
########## Begin ##########
DELIMITER $
CREATE PROCEDURE PROC_UPDATEGRADE()
Begin
    update SC set rank = 'A' where Grade >= 90 and Grade <= 100;
	update SC set rank = 'B' where Grade >= 80 and Grade < 90;
	update SC set rank = 'C' where Grade >= 70 and Grade < 80;
	update SC set rank = 'D' where Grade >= 60 and Grade < 70;
	update SC set rank = 'E' where Grade >= 0 and Grade < 60;
END;$
DELIMITER ;
CALL PROC_UPDATEGRADE();
########## End ##########
#以下代码禁止删除
SELECT * FROM SC;

第2关:使用游标定义一个名为PROC_JGRADE无参存储过程

任务描述

使用游标定义一个名为PROC_JGRADE无参存储过程,统计各工程使用零件数量的分布情况,按照S档<1000,M档1000-2000,L档>2000分段统计,即输出工程JNO、每个工程使用零件个数、每个工程使用零件个数的分档,结果按工程号JNO升序排序。

相关知识

1、供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成,标识某供应商 供应某种零件 给某工程项目的数量为QTY。 SPJ表如下图:

,

现已构建SPJ表,结构信息如下:

,

2、游标 游标的操作 ① 游标的定义

  1. DECLARE 光标名称 CURSOR FOR 查询语法

例:

  1. DECLARE CURSOR_NAME CURSOR FOR SELECT_STATEMENT;

② 打开游标

  1. OPEN 光标名称

例:

  1. OPEN CURSOR_NAME;

③ 取游标中的数据

  1. FETCH 光标名称 INFO VAR_NAME [,VAR_NAME ].....

例:

  1. FETCH CURSOR_NAME INFO VAR_NAME;

④ 关闭游标

  1. CLOSE 光标名称

例:

  1. CLOSE CURSO_NAME;

⑤ 释放游标

  1. DEALLOCATE 光标名称

例:

  1. DEALLOCATE CURSOR_NAME;

游标使用示例: 使用游标创建一个存储过程,统计STUDENT表年龄大于19的记录的数量,如下为STUDENT表的创建代码:

  1. CREATE TABLE STUDENT(
  2. STUID INT PRIMARY KEY AUTO_INCREMENT,
  3. STUNAME VARCHAR(20),
  4. STUSEX VARCHAR(2),
  5. STUAGE INT
  6. );
  7. INSERT INTO STUDENT(STUNAME,STUSEX,STUAGE) VALUES
  8. ('小明','男',20),
  9. ('小花','女',19),
  10. ('大赤','男',20),
  11. ('可乐','男',19),
  12. ('莹莹','女',19);

使用游标创建存储过程代码:

  1. DELIMITER $
  2. CREATE PROCEDURE PROC_STAT()
  3. BEGIN
  4. # 创建 用于接收游标值的变量
  5. DECLARE ID,AGE,TOTAL INT;
  6. DECLARE NAME,SEX CHAR(10);
  7. # 游标结束的标志
  8. DECLARE DONE INT DEFAULT 0;
  9. # 声明游标
  10. DECLARE CUR CURSOR FOR SELECT STUID,STUNAME,STUSEX,STUAGE FROM STUDENT WHERE STUAGE > 19;
  11. # 指定游标循环结束时的返回值
  12. DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;
  13. # 打开游标
  14. OPEN CUR;
  15. # 初始化变量
  16. SET TOTAL = 0;
  17. # WHILE 循环
  18. WHILE DONE != 1 DO
  19. FETCH CUR INTO ID,NAME,SEX,AGE;
  20. IF DONE != 1 THEN
  21. SET TOTAL = TOTAL + 1;
  22. END IF;
  23. END WHILE;
  24. # 关闭游标
  25. CLOSE CUR;
  26. # 输出累计的结果
  27. SELECT TOTAL;
  28. END$
  29. DELIMITER ;
  30. # 调用存储过程
  31. CALL PROC_STAT();

开始你的任务吧,祝你成功!

USE mydata;
#请在此处添加实现代码
########## Begin ##########
DELIMITER $
CREATE PROCEDURE PROC_JGRADE()
BEGIN
    # 创建 用于接收游标值的变量
    DECLARE PTYPE CHAR(10);
    DECLARE NJNO CHAR(10);
    DECLARE QTYTEMP INT DEFAULT 0;
    # 游标结束的标志
    DECLARE JNOTEMP INT DEFAULT 0;
    # 声明游标
    DECLARE TEMP CURSOR FOR 
    SELECT JNO,SUM(QTY)
    FROM SPJ 
    GROUP BY JNO 
    ORDER BY JNO ASC;
    # 打开游标
    OPEN TEMP;
    # 初始化变量
    CREATE TEMPORARY TABLE temp_table(
    SJNO CHAR(10) NOT NULL,
    SQTY CHAR(10) NOT NULL,
    JTYPE CHAR(10) NOT NULL
    );
    # REPEAT 循环
    REPEAT 
    FETCH TEMP INTO NJNO,QTYTEMP;
    IF(QTYTEMP<1000)
    THEN
    SET PTYPE='S';
    ELSEIF(1000<=QTYTEMP AND QTYTEMP <=2000)
    THEN
    SET PTYPE='M';
    ELSEIF(QTYTEMP>2000)
    THEN
    SET PTYPE='L';
    END IF;
    INSERT INTO temp_table VALUES(NJNO,QTYTEMP,PTYPE);
    SET JNOTEMP =JNOTEMP+1;
    UNTIL JNOTEMP>=5
    END REPEAT;
    # 关闭游标
    CLOSE TEMP;
    # 输出累计的结果
    SELECT*FROM temp_table;
END$
DELIMITER ;
########## End ########## 
# 以下代码禁止删除
# 调用存储过程
CALL PROC_JGRADE();

第3关:使用游标定义一个名为PROC_NUMGRADE有参存储过程

任务描述

使用游标定义一个名为PROC_NUMGRADE有参存储过程,统计某课程的成绩分布情况,即按照各分数段统计人数,按照100,[90,100),[80,90),[70,80),[60,70),[0,60)分段统计。

相关知识

1、课程表Course由课程号(Cno)、课程名(Cname)、先行课(Cpno)、学分(Ccredit)组成。 Course表如下图:

,

现已构建Course表,结构信息如下:

,

创建Course表的Mysql代码为:

CREATE TABLE Course( Cno CHAR(4) PRIMARY KEY, Cname CHAR(9), Cpno CHAR(4), Ccredit INT );

INSERT INTO Course VALUES('1','数据库','5',4); INSERT INTO Course VALUES('2','离散数学','',2); INSERT INTO Course VALUES('3','信息系统','1',4); INSERT INTO Course VALUES('4','操作系统','6',3); INSERT INTO Course VALUES('5','数据结构','7',4); INSERT INTO Course VALUES('6','数据处理','',2); INSERT INTO Course VALUES('7','PaSCal语言','6',4);

2、学生选课表SC由学号(Sno)、课程号(Cno)、成绩(Grade)组成。 SC表如下图:

,

现已构建SC表,结构信息如下:

,

创建SC表的Mysql代码为:

CREATE TABLE SC( Sno CHAR(9), Cno CHAR(4), Grade INT, PRIMARY KEY(Sno, Cno) );

INSERT INTO SC VALUES('200215121','1',92); INSERT INTO SC VALUES('200215121','2',85); INSERT INTO SC VALUES('200215121','3',88); INSERT INTO SC VALUES('200215122','2',90); INSERT INTO SC VALUES('200215122','3',80); INSERT INTO SC VALUES('200215123','1',60); INSERT INTO SC VALUES('200215123','2',75); INSERT INTO SC VALUES('200215124','1',52); INSERT INTO SC VALUES('200215124','2',63); INSERT INTO SC VALUES('200215125','1',100); INSERT INTO SC VALUES('200215125','2',100); INSERT INTO SC VALUES('200215126','2',34); INSERT INTO SC VALUES('200215126','3',45); INSERT INTO SC VALUES('200215127','2',86); INSERT INTO SC VALUES('200215127','3',88);

3、游标 游标的操作 ① 游标的定义

  1. DECLARE 光标名称 CURSOR FOR 查询语法

例:

  1. DECLARE CURSOR_NAME CURSOR FOR SELECT_STATEMENT;

② 打开游标

  1. OPEN 光标名称

例:

  1. OPEN CURSOR_NAME;

③ 取游标中的数据

  1. FETCH 光标名称 INFO VAR_NAME [,VAR_NAME ].....

例:

  1. FETCH CURSOR_NAME INFO VAR_NAME;

④ 关闭游标

  1. CLOSE 光标名称

例:

  1. CLOSE CURSO_NAME;

⑤ 释放游标

  1. DEALLOCATE 光标名称

例:

  1. DEALLOCATE CURSOR_NAME;

游标使用示例: 使用游标创建一个存储过程,统计STUDENT表年龄大于19的记录的数量,如下为STUDENT表的创建代码:

  1. CREATE TABLE STUDENT(
  2. STUID INT PRIMARY KEY AUTO_INCREMENT,
  3. STUNAME VARCHAR(20),
  4. STUSEX VARCHAR(2),
  5. STUAGE INT
  6. );
  7. INSERT INTO STUDENT(STUNAME,STUSEX,STUAGE) VALUES
  8. ('小明','男',20),
  9. ('小花','女',19),
  10. ('大赤','男',20),
  11. ('可乐','男',19),
  12. ('莹莹','女',19);

使用游标创建存储过程代码:

  1. DELIMITER $
  2. CREATE PROCEDURE PROC_STAT()
  3. BEGIN
  4. # 创建 用于接收游标值的变量
  5. DECLARE ID,AGE,TOTAL INT;
  6. DECLARE NAME,SEX CHAR(10);
  7. # 游标结束的标志
  8. DECLARE DONE INT DEFAULT 0;
  9. # 声明游标
  10. DECLARE CUR CURSOR FOR SELECT STUID,STUNAME,STUSEX,STUAGE FROM STUDENT WHERE STUAGE > 19;
  11. # 指定游标循环结束时的返回值
  12. DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;
  13. # 打开游标
  14. OPEN CUR;
  15. # 初始化变量
  16. SET TOTAL = 0;
  17. # WHILE 循环
  18. WHILE DONE != 1 DO
  19. FETCH CUR INTO ID,NAME,SEX,AGE;
  20. IF DONE != 1 THEN
  21. SET TOTAL = TOTAL + 1;
  22. END IF;
  23. END WHILE;
  24. # 关闭游标
  25. CLOSE CUR;
  26. # 输出累计的结果
  27. SELECT TOTAL;
  28. END$
  29. DELIMITER ;
  30. # 调用存储过程
  31. CALL PROC_STAT();

开始你的任务吧,祝你成功!

USE myschool;
#请在此处添加实现代码
########## Begin ##########
DELIMITER $
CREATE PROCEDURE PROC_NUMGRADE(IN TEMPCNAME CHAR(9))
BEGIN
    # 创建 用于接收游标值的变量
    DECLARE TEMPGRADE INT;
    # 游标结束的标志
    DECLARE DONE INT DEFAULT 0;
    # 声明游标
    DECLARE CUR CURSOR FOR
    SELECT Grade
    FROM Course,SC
    WHERE Cname = TEMPCNAME AND Course.Cno = SC.Cno;
    # 指定游标循环结束时的返回值 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;
    # 创建临时表
    CREATE TEMPORARY TABLE TEMP_TABLE(
        Type CHAR(30),
        Num INT
    );
    INSERT INTO TEMP_TABLE VALUES('100', 0);
    INSERT INTO TEMP_TABLE VALUES('[90,100)', 0);
    INSERT INTO TEMP_TABLE VALUES('[80,90)', 0);
    INSERT INTO TEMP_TABLE VALUES('[70,80)', 0);
    INSERT INTO TEMP_TABLE VALUES('[60,70)', 0);
    INSERT INTO TEMP_TABLE VALUES('[0,60)', 0);
    # 打开游标
    OPEN CUR;
    # WHILE 循环
    WHILE DONE != 1 DO
        FETCH CUR INTO TEMPGRADE;
        IF DONE != 1 THEN
            IF TEMPGRADE=100 THEN UPDATE TMEP_TABLE SET Num=Num+1 WHERE Type='100';
            ELSEIF TEMPGRADE>=90 AND TEMPGRADE<100 THEN UPDATE TEMP_TABLE SET Num=Num+1 WHERE Type='[90,100)';
            ELSEIF TEMPGRADE>=80 AND TEMPGRADE<90 THEN UPDATE TEMP_TABLE SET Num=Num+1 WHERE Type='[80,90)';
            ELSEIF TEMPGRADE>=70 AND TEMPGRADE<80 THEN UPDATE TEMP_TABLE SET Num=Num+1 WHERE Type='[70,80)';
            ELSEIF TEMPGRADE>=60 AND TEMPGRADE<70 THEN UPDATE TEMP_TABLE SET Num=Num+1 WHERE Type='[60,70)';
            ELSEIF TEMPGRADE>=0 AND TEMPGRADE<60 THEN UPDATE TEMP_TABLE SET Num=Num+1 WHERE Type='[0,60)';
            END IF;  
        END IF; 
    END WHILE;
    # 关闭游标
    CLOSE CUR;
    # 输出累计的结果
    SELECT * FROM TMEP_TABLE;
END;$
DELIMITER ;
########## End ########## 
# 以下代码禁止删除
CALL PROC_NUMGRADE('离散数学');

文章来源:https://blog.csdn.net/YK3020936216/article/details/135347221
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。