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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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无参存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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
    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有参存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
USE myschool;
#请在此处添加实现代码
########## BEGIN ##########
DELIMITER $
CREATE PROCEDURE proc_numgrade(IN sumtemp CHAR(9))
BEGIN
    DECLARE a INT;
    DECLARE b INT default 0;
    DECLARE c cursor for
    SELECT Grade
    FROM Course,SC
    WHERE Cname = sumtemp AND Course.Cno = SC.Cno;
    DECLARE contINue handler for not found SET b = 1;
    CREATE temporary table TMEP_TABLE(
        Type CHAR(30),
        Num INT
    );
    INSERT INTO TMEP_TABLE VALUES('100', 0);
    INSERT INTO TMEP_TABLE VALUES('[90,100)', 0);
    INSERT INTO TMEP_TABLE VALUES('[80,90)', 0);
    INSERT INTO TMEP_TABLE VALUES('[70,80)', 0);
    INSERT INTO TMEP_TABLE VALUES('[60,70)', 0);
    INSERT INTO TMEP_TABLE VALUES('[0,60)', 0);
    OPEN c;
    WHILE b != 1 do
        FETCH c INTO a;
        IF b != 1 THEN
            IF a=100 THEN UPDATE TMEP_TABLE SET Num=Num+1 WHERE Type='100';
            ELSEIF a>=90 AND a<100 THEN UPDATE TMEP_TABLE SET Num=Num+1 WHERE Type='[90,100)';
            ELSEIF a>=80 AND a<90 THEN UPDATE TMEP_TABLE SET Num=Num+1 WHERE Type='[80,90)';
            ELSEIF a>=70 AND a<80 THEN UPDATE TMEP_TABLE SET Num=Num+1 WHERE Type='[70,80)';
            ELSEIF a>=60 AND a<70 THEN UPDATE TMEP_TABLE SET Num=Num+1 WHERE Type='[60,70)';
            ELSEIF a>=0 AND a<60 THEN UPDATE TMEP_TABLE SET Num=Num+1 WHERE Type='[0,60)';
            END IF;  
        END IF;
    END WHILE;
    CLOSE c;
    SELECT * FROM TMEP_TABLE;
END;$
DELIMITER ;
########## END ##########
# 以下代码禁止删除
CALL PROC_NUMGRADE('离散数学');