mysql在存储过程中确保外键值存在及不重复插入数据

mysql在存储过程中插入数据，如果有外键，确保外键存在才插入；如果没有重复的主键，才插入。

以student表为例，在存储过程中的代码如下，参数是(IN `stu_id` int,IN `stu_name` varchar(30),IN `age` int,IN `sex` varchar(10),IN `school_year` int,IN `school` varchar(20),IN `major` varchar(20),IN `dor_id` int,IN `pwd` varchar(15),IN `phone` varchar(11))。

BEGIN
DECLARE iRest INT;
SELECT COUNT(*) INTO iRest FROM dormitory WHERE dormitory.dormitory_id = dor_id;
IF iRest > 0 THEN
INSERT INTO student (student.student_no,student.`name`,student.age,student.sex,student.school_year,student.school,student.major,student.dormitory_id,student.`password`,student.phone) SELECT stu_id,stu_name,age,sex,school_year,school,major,dor_id,pwd,phone FROM DUAL WHERE NOT EXISTS(SELECT student.student_no FROM student WHERE student.student_no = stu_id);
END IF;
END