数据库实验6（答案） 存储过程 MySQL

数据库实验6 存储过程

前言

本次实验主要是编写存储过程SQL语句，个人认为比较麻烦的点的可能是11,12用游标创建存储过程的情况。

参考资料

关于存储过程这一块的，除了题目本身的相关知识。还可以看看下面这个菜鸟教程的。

MySQL 存储过程 | 菜鸟教程 (runoob.com)

1

DELIMITER $
CREATE PROCEDURE PROC_COUNT()
BEGIN
    SELECT COUNT(*) FROM J WHERE JNAME LIKE '%厂';
END ;$
DELIMITER ;
CALL PROC_COUNT();

2

DELIMITER $
CREATE PROCEDURE PROC_JNAME(IN city char(10))
BEGIN
    SELECT JNAME FROM J WHERE J.CITY = city;
END; $
DELIMITER ;

3

DELIMITER $
CREATE PROCEDURE PROC_JINFO(IN city char(10))
BEGIN
    SELECT J.JNAME, P.PNAME, SUM(QTY) AS SUM_QTY FROM J NATURAL JOIN SPJ NATURAL JOIN P
        WHERE J.CITY = city GROUP BY JNAME, PNAME ORDER BY JNAME, PNAME;
END; $
DELIMITER ;

4

DELIMITER $
CREATE PROCEDURE PROC_AVGGRADE(IN cname char(9))
BEGIN
    SELECT AVG(Grade) AS AVG_Grade FROM Course NATURAL JOIN SC
        WHERE Course.Cname = cname GROUP BY Course.Cname;
end; $
DELIMITER ;

5

DELIMITER $
CREATE PROCEDURE PROC_SINFO(IN sname char(10))
BEGIN
    SELECT JNAME, PNAME, SUM(QTY) AS SUM_QTY
    FROM S NATURAL JOIN SPJ JOIN J ON J.JNO = SPJ.JNO
        NATURAL JOIN P
        WHERE S.SNAME = sname GROUP BY JNAME, PNAME ORDER BY JNAME, PNAME;
end; $
DELIMITER ;

6

DELIMITER $
CREATE PROCEDURE PROC_JSEARCH(IN _jno char(2))
BEGIN
    SELECT SNAME, PNAME, JNAME
    FROM S natural join SPJ join J on SPJ.JNO = J.JNO NATURAL JOIN P
        where J.JNO = _jno
    group by SNAME, PNAME, JNAME
    order by SNAME, PNAME;
END ;$
DELIMITER ;

7

drop procedure PROC_COUNT;

DELIMITER $
CREATE PROCEDURE PROC_COUNT()
BEGIN
    SELECT COUNT(*) FROM J WHERE JNAME LIKE '%厂';
END ;$
DELIMITER ;
CALL PROC_COUNT();

8

DELIMITER $
CREATE PROCEDURE PROC_AVGWEIGHT (IN _sno char(2), IN _jno char(2), OUT AVG_WEIGHT INTEGER)
BEGIN
    SELECT AVG(WEIGHT) INTO AVG_WEIGHT
    from SPJ natural join P
        where SPJ.SNO = _sno and JNO = _jno;
end; $
DELIMITER ;

SET @AVG_WEIGHT = 0;
CALL PROC_AVGWEIGHT('S2', 'J1', @AVG_WEIGHT);
SELECT @AVG_WEIGHT;

9

DELIMITER $
CREATE PROCEDURE PROC_JGRADE(IN _jno char(2), OUT JTYPE char(1))
BEGIN
    DECLARE sqt INT;
    SELECT SUM(QTY) INTO sqt FROM SPJ WHERE JNO = _jno;
    IF sqt < 1000 THEN
        SET JTYPE = 'S';
    ELSEIF sqt <= 2000 THEN
        SET JTYPE = 'M';
    ELSE
        SET JTYPE = 'L';
    end if;
END; $
DELIMITER ;

10

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 < 60;
END; $
DELIMITER ;

CALL PROC_UPDATEGRADE();

11–游标，创建临时表

DELIMITER $
CREATE PROCEDURE PROC_JGRADE()
BEGIN
    DECLARE SJNO, JTYPE varchar(2);
    DECLARE SQTY INT;
    DECLARE DONE INT DEFAULT 0;

    DECLARE CUR CURSOR FOR
        SELECT JNO, SUM(QTY) FROM SPJ GROUP BY SPJ.JNO ORDER BY SPJ.JNO;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;

    DROP TEMPORARY TABLE if exists temp;       # 删除

    # 创建临时表
    CREATE TEMPORARY TABLE temp
    (SJNO VARCHAR(2), SQTY INT, JTYPE VARCHAR(2));

    OPEN CUR;
    WHILE DONE != 1 DO
        FETCH CUR INTO SJNO, SQTY;
        IF SQTY < 1000 THEN
            SET JTYPE = 'S';
        ELSEIF SQTY <= 2000 THEN
            SET JTYPE = 'M';
        ELSE
            SET JTYPE = 'L';
        end if ;
        IF DONE != 1 THEN
            INSERT INTO temp values (SJNO, SQTY, JTYPE);
        end if ;
        end while ;
    CLOSE CUR;
    SELECT t.SJNO, t.SQTY, t.JTYPE FROM temp t order by SJNO;
END; $
DELIMITER ;

12

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DpkDvbov-1669821049901)(C:\Users\86152\AppData\Roaming\Typora\typora-user-images\image-20221130230913429.png)]

统计数量，然后按序插入即可。

DELIMITER $
CREATE PROCEDURE PROC_NUMGRADE(IN _canme char(10))
BEGIN
    DECLARE grade, num1, num2, num3, num4, num5, num6 int;
    DECLARE DONE INT DEFAULT 0;

    declare cur cursor for select SC.Grade from SC natural join Course
        where Cname = _canme order by Grade desc;
    declare continue handler for not found set DONE = 1;
    open cur;
    set num1 = 0, num2 = 0, num3 = 0, num4 = 0, num5 = 0, num6 = 0;
    create temporary table temp(Type char(10), Num INT);
    while DONE != 1 DO
        fetch cur into grade;
        if grade = 100 and DONE != 1 then
            set num1 = num1 + 1;
        elseif grade >= 90 and DONE != 1 then
            set num2 = num2 + 1;
        elseif grade >= 80 and DONE != 1 then
            set num3 = num3 + 1;
        elseif grade >= 70 and DONE != 1 then
            set num4 = num4 + 1;
        elseif grade >= 60 and DONE != 1 then
            set num5 = num5 + 1;
        elseif DONE != 1 then
        set num6 = num6 + 1;
        end if ;
    end while ;
    insert into temp values ('100', num1),
                            ('[90,100)', num2),
                            ('[80,90)', num3),
                            ('[70,80)', num4),
                            ('[60,70)', num5),
                            ('[0,60)', num6);

    select t.Type, t.Num from temp t;
    drop temporary table if exists temp;
end; $
DELIMITER ;