实验五 MySQL过程式数据库对象

原创 于 2024-05-28 20:32:51 发布 · 858 阅读 · 6 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

1.（1）创建存储过程，实现按学生姓名查询计算该学生所获得的总学分，当学生所修课程成绩大于等于60分时才能获得该门课程的学分，根据将查询结果用输出参数传递给主程序。

DELIMITER //
CREATE PROCEDURE creditresearch (
    IN studentName VARCHAR(255),
    OUT totalCredits INT
)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE currentCourseNo VARCHAR(255);
DECLARE currentCredit INT;
    DECLARE courseCursor CURSOR FOR 
        SELECT Course.Cno, Course.Credit
        FROM Course
        JOIN SC ON Course.Cno = SC.Cno
        JOIN Student ON SC.Sno = Student.Sno
        WHERE Student.Sname = studentName AND SC.Grade >= 60;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    SET totalCredits = 0;
    OPEN courseCursor;
    read_loop: LOOP
        FETCH courseCursor INTO currentCourseNo, currentCredit;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET totalCredits = totalCredits + currentCredit;
    END LOOP;
    CLOSE courseCursor;
END //
DELIMITER ;

（2）调用存储过程查询马晓旭所获得的总学分并输出显示。

CALL creditresearch('马晓旭', @tc);
SELECT @tc AS TotalCredits;

2.（1）创建存储过程，计算每个学生的学分成绩，学分成绩计算公式为：
学分成绩=该学生所有课程的成绩与对应课程学分的乘积和再除以该学生所有课程学分的加和
根据学分成绩输出该学生的专业奖学金等级，专业奖学金评定方法为：

  • 学分成绩等于或高于90为一等专业奖学金；
  • 学分成绩等于或高于85并且低于90为二等专业奖学金；
  • 学分成绩等于或高于80并且低于85为三等专业奖学金；
  • 低于80分未获得专业奖学金。
DELIMITER //
CREATE PROCEDURE Calculatecholarship()
BEGIN
    DECLARE student_id INT;
    DECLARE total_credit DECIMAL(5,2);
    DECLARE total_grade_credit DECIMAL(8,2);
    DECLARE average_score DECIMAL(5,2);
    DECLARE scholarship_level VARCHAR(20);
    DECLARE cur CURSOR FOR 
        SELECT Sno
        FROM Student;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO student_id;
				IF student_id NOT IN (SELECT Sno FROM SC ) THEN
            ITERATE read_loop;
        ELSE
            SELECT SUM(Credit) INTO total_credit
            FROM Course
            WHERE Cno IN (SELECT Cno FROM SC WHERE Sno = student_id);
            SELECT SUM(Grade * Credit) INTO total_grade_credit
            FROM SC
            JOIN Course ON SC.Cno = Course.Cno
            WHERE SC.Sno = student_id;
            SET average_score = total_grade_credit / total_credit;
        END IF;
				IF average_score >= 90 THEN
       		 SET scholarship_level = '一等专业奖学金';
				ELSEIF average_score >= 85 AND average_score < 90 THEN
   				 SET scholarship_level = '二等专业奖学金';
				ELSEIF average_score >= 80 AND average_score < 85 THEN
				   SET scholarship_level = '三等专业奖学金';
				ELSE
   				 SET scholarship_level = '未获得专业奖学金';
				END IF;
				SELECT CONCAT('学生 ', (SELECT Sname FROM Student WHERE Sno = student_id), '平均成绩为：', average_score, '奖学金等级：', scholarship_level) AS Result ;
    END LOOP;
    CLOSE cur;
END//
DELIMITER ;

（2）对创建的存储过程进行调用。

CALL Calculatecholarship();

3.（1）创建存储过程，实现将某个供应商供应给某个工程的某个零件增加指定的供应量，供应商号、零件号、工程号以及要增加的供应量在调用存储过程时通过参数传递给过程体。

DELIMITER //
CREATE PROCEDURE creditresearch (
    IN SNO1 VARCHAR(10),
    IN PNO1 VARCHAR(10),
    IN JNO1 VARCHAR(10),
    IN NUM INT)
BEGIN
    UPDATE spj
    SET qty = qty + NUM
    WHERE sno = SNO1 AND pno = PNO1 AND jno = JNO1;
END //
DELIMITER ;

（2）调用存储过程将spj表中第一行供应信息的供应量增加100。

call creditresearch('s1','p2','j2',100);

4． （1）使用alter table语句在SC关系中增加新属性列GPA，此列用来记录课程成绩的绩点值。

ALTER TABLE SC
ADD COLUMN GPA DECIMAL(3, 1);
UPDATE SC
SET GPA = 
    CASE 
        WHEN Grade >= 90 THEN 4.0
        WHEN Grade >= 85 AND Grade < 90 THEN 3.7
        WHEN Grade >= 82 AND Grade < 85 THEN 3.3
        WHEN Grade >= 78 AND Grade < 82 THEN 3.0
        WHEN Grade >= 75 AND Grade < 78 THEN 2.7
        WHEN Grade >= 71 AND Grade < 75 THEN 2.3
        WHEN Grade >= 68 AND Grade < 71 THEN 2.0
        WHEN Grade >= 65 AND Grade < 68 THEN 1.7
        WHEN Grade >= 62 AND Grade < 65 THEN 1.3
        WHEN Grade >= 60 AND Grade < 62 THEN 1.0
        ELSE 0
END;

（2）创建触发器，实现当在SC表中插入元组和修改grade列的值时，自动插入和修改该门课程成绩的绩点值，课程成绩转换为绩点分值规则如下表：

百分制成绩90-100 85-8982-8478-8175-7771-7468-7065-6762-6460-61<60
课程绩点4.03.73.332.72.321.71.31.0
DELIMITER //
CREATE TRIGGER calculate_gpa_insert
BEFORE INSERT ON SC
FOR EACH ROW
BEGIN
    DECLARE gpa DECIMAL(3, 1);
    IF NEW.Grade >= 90 THEN
        SET gpa = 4.0;
    ELSEIF NEW.Grade >= 85 AND NEW.Grade < 90 THEN
        SET gpa = 3.7;
    ELSEIF NEW.Grade >= 82 AND NEW.Grade < 85 THEN
        SET gpa = 3.3;
    ELSEIF NEW.Grade >= 78 AND NEW.Grade < 82 THEN
        SET gpa = 3.0;
    ELSEIF NEW.Grade >= 75 AND NEW.Grade < 78 THEN
        SET gpa = 2.7;
    ELSEIF NEW.Grade >= 71 AND NEW.Grade < 75 THEN
        SET gpa = 2.3;
    ELSEIF NEW.Grade >= 68 AND NEW.Grade < 71 THEN
        SET gpa = 2.0;
    ELSEIF NEW.Grade >= 65 AND NEW.Grade < 68 THEN
        SET gpa = 1.7;
    ELSEIF NEW.Grade >= 62 AND NEW.Grade < 65 THEN
        SET gpa = 1.3;
    ELSEIF NEW.Grade >= 60 AND NEW.Grade < 62 THEN
        SET gpa = 1.0;
    ELSE
        SET gpa = 0;
    END IF;
    SET NEW.GPA = gpa;
END;
//
CREATE TRIGGER calculate_gpa_update
BEFORE UPDATE ON SC
FOR EACH ROW
BEGIN
    DECLARE gpa DECIMAL(3, 1);
    IF NEW.Grade >= 90 THEN
        SET gpa = 4.0;
    ELSEIF NEW.Grade >= 85 AND NEW.Grade < 90 THEN
        SET gpa = 3.7;
    ELSEIF NEW.Grade >= 82 AND NEW.Grade < 85 THEN
        SET gpa = 3.3;
    ELSEIF NEW.Grade >= 78 AND NEW.Grade < 82 THEN
        SET gpa = 3.0;
    ELSEIF NEW.Grade >= 75 AND NEW.Grade < 78 THEN
        SET gpa = 2.7;
    ELSEIF NEW.Grade >= 71 AND NEW.Grade < 75 THEN
        SET gpa = 2.3;
    ELSEIF NEW.Grade >= 68 AND NEW.Grade < 71 THEN
        SET gpa = 2.0;
    ELSEIF NEW.Grade >= 65 AND NEW.Grade < 68 THEN
        SET gpa = 1.7;
    ELSEIF NEW.Grade >= 62 AND NEW.Grade < 65 THEN
        SET gpa = 1.3;
    ELSEIF NEW.Grade >= 60 AND NEW.Grade < 62 THEN
        SET gpa = 1.0;
    ELSE
        SET gpa = 0;
    END IF;
    SET NEW.GPA = gpa;
END;
//
DELIMITER ;

（3）在SC表中录入和修改数据对触发器进行验证。

  1. 在project数据库中用触发器完成如下功能。
    当对spj表的供应量qty属性值进行修改时，若供应量的变化范围超过10%（注意：变化有可能是增加，也有可能是减少），自动将修改的情况记录到另一个表spj_tra(id,sno,pno,jno,before_qty,after_qty，update_time)中。
    spj_tra表各属性列含义为：id属性为表的主键，要求设置为自增，值从1开始依次递增，sno、pno、jno是被修改的元组的供应商号、零件号和工程项目号，before_qty是修改前的供应量，after_qty是修改后的供应量,update_time是修改时间，此列值通过使用now()函数获取系统当前日期插入。
    （1）用create table语句创建spj_tra表。
CREATE TABLE IF NOT EXISTS spj_tra (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sno VARCHAR(10),
    pno VARCHAR(10),
    jno VARCHAR(10),
    before_qty INT,
    after_qty INT,
    update_time TIMESTAMP DEFAULT NOW()
);

（2）创建触发器实现如上要求的操作。

DELIMITER //
CREATE TRIGGER spj_update
BEFORE UPDATE ON spj
FOR EACH ROW
BEGIN
    DECLARE PERCENT DECIMAL(10,4);
		SET PERCENT = ABS((NEW.qty-OLD.qty)/OLD.qty);
    IF PERCENT >= 0.1 THEN
        INSERT INTO spj_tra (sno, pno, jno, before_qty, after_qty) 
        VALUES (NEW.sno, NEW.pno, NEW.jno, OLD.qty, NEW.qty);
    END IF;
END;
//
DELIMITER ;

（3）修改spj表中qty的值对触发器进行验证。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值