I. Table
Student table
Create table 't_ student '(
'Stunum' int (11) not null auto_increment,
'Stamp' varchar (20) default NULL,
'Birthday' date default NULL,
Primary key ('stunum ')
) ENGINE = InnoDB default charset = utf8
Student Score Table
Create table 't_ stu_score '(
'Id' int (11) not null auto_increment,
'Stunum' int (11) default NULL,
'Score 'decimal (6, 2) default NULL,
Primary key ('id '),
KEY 'fk _ t_stu_score '('stunum '),
CONSTRAINT 'fk _ t_stu_score 'foreign key ('stunum') REFERENCES't _ student' ('stunum ')
) ENGINE = InnoDB default charset = utf8
Student details
Create table 't_ stu_detail '(
'Id' int (11) not null auto_increment,
'Stamp' varchar (20) default NULL,
'Score 'decimal (6, 2) default NULL,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8
Insert data that meets certain conditions in the t_Student and t_stu_score tables to t_stu_detail.
Ii. Process
DELIMITER &&
Create procedure proc_AddStuDetail (IN p_score DECIMAL (6, 2 ))
BEGIN
DECLARE vstuNum INT;
DECLARE vstuName VARCHAR (20 );
DECLARE vbirthday DATE;
DECLARE vscore DECIMAL (6, 2 );
DECLARE done INT;
-- Define a cursor
DECLARE stuCursor CURSOR
FOR
SELECT stuNum, stuName, birthday FROM t_Student;
-- Define end mark
Declare continue handler for not found set done = 1;
-- Open the cursor
OPEN stuCursor;
-- Loop
StuLoop: LOOP
-- Retrieve data from the cursor
FETCH stuCursor INTO vstuNum, vstuName, vbirthday;
IF done = 1 THEN
LEAVE stuLoop;
End if;
If date (vbirthday)> = '2017-03-01 'THEN
SELECT score INTO vscore FROM t_stu_score WHERE stuNum = vstuNum;
IF vscore> = p_score THEN
Insert into t_stu_detail VALUES (NULL, vstuNum, vscore );
End if;
End if;
End loop stuLoop;
-- Close the cursor
CLOSE stuCursor;
END
&&
DELIMITER;
Iii. Call Process
CALL proc_AddStuDetail (86 );