Analysis of triggers and stored procedure instances of MySQL database

1. Experiment purpose

• 1. Master the SQL programming language and programming specifications supported by a mainstream DBMS, and standardize the design of stored procedures;

• 2. Be able to understand the functions and execution principles of different types of triggers, and verify the effectiveness of triggers;

• 3. Cultivate students' systems thinking and improve the skills required to solve complex engineering problems. programming ability.

2. Experimental requirements

Master the SQL programming language of a mainstream DBMS, and define BEFORE (for) triggers and AFTER triggers based on the database created earlier ; Master the database stored procedure definition, stored procedure operation, stored procedure renaming, stored procedure deletion, and stored procedure parameter transfer.

Warm reminder: The following content has been tested, but there will inevitably be omissions, but most of the ideas and implementation of the code are correct after testing.

3. Implementation content and steps

1. Create a simple stored procedure without parameters

• (1) Create the stored procedure sp_avggrade and implement The function of querying the average score of students in each course;

delimiter//
CREATE PROCEDURE sp_avggrade()
COMMENT '查询每门课程学生的平均成绩的功能'
BEGIN
SELECT cno as 课程号,avg(grade)as 平均成绩
FROM sc
GROUP BY cno;
end//
delimiter;

(2) Call the stored procedure to implement the query.

call sp_avggrade();

2. Create a stored procedure with input parameters

(1) Create the stored procedure sp_course_avggrade to query the course average of the specified course number by inputting the course number parameter Grade function;

delimiter//
CREATE PROCEDURE sp_course_avggrade(IN c_no CHAR(2))
COMMENT '通过输入课程编号参数查询指定课程编号的课程平均成绩的功能'
BEGIN
SELECT cno,AVG(grade)
FROM sc
WHERE cno=c_no;
END//
delimiter;

(2) Call this stored procedure to obtain the average grade of the specified course.

3. Create a stored procedure with input and output parameters

(1) Create the stored procedure sp_sdept _student to find the students of the college based on the department number parameter entered by the user. The number of students and output it in the form of a variable;

delimiter//
CREATE PROCEDURE sp_sdept_student2(IN _sdept CHAR(10),OUT num int)
BEGIN
SELECT COUNT(sno) INTO num
FROM student
WHERE _sdept = sdept;
END//
delimiter;

(2) Call this stored procedure to obtain the number of students in the corresponding department in the form of a returned variable.

SET @num=10;
CALL sp_sdept_student2('计科',@num);
SELECT @num AS '人数';

4. Creation and use of triggers

(1) Create a trigger on the student table to realize that when a student record is deleted from the student table, the level of the student's score record in the score table Joint deletion;

delimiter//
CREATE TRIGGER delete_stu
BEFORE DELETE
ON student
FOR EACH ROW
BEGIN
DELETE
FROM sc
WHERE sc.sno=old.sno;
END//
delimiter;

(2) Create a trigger on the course selection table. If the entered score is greater than 100 points and less than 0 points, the record will be refused to be inserted and displayed;

delimiter//
CREATE TRIGGER scgrade
BEFORE INSERT ON sc
FOR EACH ROW
BEGIN
IF new.grade>100 or new.grade < 0 THEN
SIGNAL SQLSTATE &#39;45000&#39;
SET message_text=&#39;录入成绩不符合规定，拒绝插入&#39;;
END IF;
END//
delimiter;

(3) Verify the triggers in (1) and (2)

Verify (1)

DELETE
FROM student
WHERE sno='201513';

Verify (2)

INSERT INTO sc(sno,cno,grade)
VALUES('2002720','1',200);

The above is the detailed content of Analysis of triggers and stored procedure instances of MySQL database. For more information, please follow other related articles on the PHP Chinese website!