数据库试验六,数据库规划与完成

数据库 2025-01-07 5

依据查找成果，数据库试验六的内容首要触及存储进程和触发器的创立和运用。以下是几个具体试验的介绍和意图：

试验六：存储进程和触发器的创立和运用

试验意图1. 了解触发器的概念2. 把握创立触发器的办法3. 把握检查、删去触发器信息的办法4. 了解存储进程的概念5. 把握创立、履行存储进程的办法6. 了解检查、修正和删去存储进程的办法

试验内容1. 无参数的存储进程界说一个存储进程，更新开销表中的教材总费用。示例代码： ```sql DELIMITER $ CREATE PROCEDURE proc_calTextBookExpense BEGIN UPDATE expenditure SET amount = FROM textbook_order qwe2 WHERE expenditure_type = textbook; END$ DELIMITER ; ``` 履行存储进程： ```sql CALL proc_calTextBookExpense; ```

2. 有参数的存储进程界说一个存储进程，更新特定课程的容量。示例代码： ```sql DELIMITER $ CREATE PROCEDURE proc_updateCourseCapacity, newCapacity SMALLINTqwe2 BEGIN UPDATE sc_summary SET capacity = newCapacity WHERE cno = sc_summary.cno; END$ DELIMITER ; ``` 履行存储进程： ```sql call proc_updateCourseCapacity; ```

3. 有局部变量的存储进程界说一个存储进程，更新某个学生的成果信息。示例代码： ```sql /成果表，记载成果信息/ create table grade PRIMARY KEY, avg_grade FLOAT, avg_weight_grade FLOAT qwe2; /存储进程/ DELIMITER $ CREATE PROCEDURE proc_updateGradeqwe2 BEGIN DECLARE sno CHAR; DECLARE avg_grade SMALLINT; DECLARE avg_weight_grade FLOAT; SELECT student.sno INTO sno FROM student WHERE student.sname = sname; SELECT SUM / COUNT INTO avg_grade FROM SC WHERE SC.sno = sno; SELECT SUM / SUM INTO avg_weight_grade FROM SC,course WHERE SC.sno = sno AND SC.cno = course.cno; INSERT INTO grade VALUES ON DUPLICATE KEY UPDATE grade.avg_grade = avg_grade, grade.avg_weight_grade = avg_weight_grade; END$ DELIMITER ; ``` 履行存储进程： ```sql call proc_updateGrade; ``` 检查履行成果： ```sql SELECT FROM grade; ```

4. 有输出参数的存储进程界说一个存储进程，更新某个学生的加权均匀成果信息并回来该成果。示例代码： ```sql DELIMITER $ CREATE PROCEDURE proc_updateAvgWGrade, out result REALqwe2 BEGIN DECLARE sno CHAR; DECLARE avg_weight_grade FLOAT; SELECT student.sno INTO sno FROM student WHERE student.sname = sname; SELECT SUM / SUM INTO avg_weight_grade FROM SC,course WHERE SC.sno = sno AND SC.cno = course.cno; SELECT avg_weight_grade INTO result; INSERT INTO grade VALUES ON DUPLICATE KEY UPDATE grade.avg_grade = avg_grade, grade.avg_weight_grade = avg_weight_grade; END$ DELIMITER ; ``` 履行存储进程并得到回来成果： ```sql set @result = 0; call proc_updateAvgWGrade; select @result; ```

5. 修正存储进程示例代码： ```sql DELIMITER $ CREATE PROCEDURE proc_calTextBookExpense BEGIN UPDATE expenditure SET amount = FROM textbook_order qwe2 WHERE expenditure_type = textbook; END$ DELIMITER ; ``` 履行存储进程： ```sql CALL proc_calTextBookExpense; ```

更多具体内容能够参阅以下