计算机科学系实验报告
课程名称：MYSQL数据库技术 实验指导教师：钟志宏 姓 名 实验名称 实验日期 吴位吕 学 号 2010081245 班 级 B10计信 MYSQL存储过程与触发器 2012/10 实验地点 3506 实验成绩 实验目的 １．理解存储过程在数据库中的作用，能够针对MYSQL编写所需的存储过程。 ２．理解触发器在数据库中的作用，能够针对MYSQL编写所需的触发器。 １．掌握MYSQL中存储过程编写的方法并完成指导书上要求的相关实验。 实验内容： ２．掌握MYSQL中触发器编写的方法并完成级联更新、删除、及CHECK功能。 \\实验环境 实验要求：
安装MYSQL数据库的PC （在导入的教学管理STM数据库中完成）：
1、 基本储存过程的创建
①创建一存储过程get_student_num，利用输出参数形式获取学生人数信息。并利
用CALL调用该存储过程查看结果。
DELIMITER $$
CREATE PROCEDURE `stm`.`get_student_num`(OUT count_num CHAR(10)) BEGIN
SELECT COUNT(sno) INTO count_num FROM student; END$$ DELIMITER ;
CALL get_student_num(@count_num) SELECT @count_num
第 1 页 共 6 页
②创建一存储过程get_student_by_sno，通过输入学生编号作为参数，获得该学生的记录信息。并利用CALL调用该存储过程查看结果。
DELIMITER $$
CREATE PROCEDURE `stm`.`get_student_by_sno`(IN sno_in CHAR(10)) BEGIN
SELECT *FROM student WHERE sno = sno_in; END$$ DELIMITER ;
CALL get_student_by_sno('900262')
③创建一存储过程update_sage_by_sno，通过输入学生编号、年龄作为参数，将指定学生的年龄更改为指定的年龄。并利用CALL调用该存储过程查看结果。
DELIMITER $$
CREATE PROCEDURE `stm`.`update_sage_by_sno`(IN sno_in CHAR(13) ,sage_in INT) BEGIN
UPDATE student SET sage=sage_in WHERE sno=sno_in; END$$ DELIMITER ;
CALL update_sage_by_sno('900125',20)
④创建一存储过程delete_student_by_sno，通过输入学生编号作为参数，删除该学生记录。并利用CALL调用该存储过程查看结果。
DELIMITER $$
CREATE PROCEDURE `stm`.`delete_student_by_sno`(IN sno_in CHAR(10)) BEGIN
DELETE FROM student WHERE sno=sno_in; END$$ DELIMITER ;
CALL delete_student_by_sno('900106')
⑤创建一存储过程insert_student，通过输入相关信息作为参数，向学生表中添加一学生记录。并利用CALL调用该存储过程查看结果。
DELIMITER $$
CREATE PROCEDURE `stm`.`insert_student`(IN snox CHAR(10),snamex VARCHAR(10),ssexx VARCHAR(1),sagex SMALLINT(5),enterdatex DATETIME) BEGIN
INSERT INTO student (sno,sname,ssex,sage,enterdate) VALUES (snox,snamex,ssexx,sagex,enterdatex); END$$ DELIMITER ;
CALL insert_student('900104','里斯','男',21,'2010-09-12')
第 2 页 共 6 页
2、基本函数过程的创建
①创建一存储函数get_student_num，利用输出参数形式获取学生人数信息。并利用select调用该存储函数查看结果。
DELIMITER $$
CREATE FUNCTION `stm`.`get_student_num`() RETURNS INT BEGIN
DECLARE num INT;
SELECT COUNT(*) INTO num FROM student; RETURN num; END$$ DELIMITER ;
SELECT get_student_num()
②创建一存储函数get_student_by_sno，通过输入学生编号作为参数，获得该学生的记录信息。并利用select调用该存储函数查看结果。
DELIMITER $$
CREATE FUNCTION `stm`.`get_student_by_sno`(snox CHAR(10)) RETURNS CHAR(100) BEGIN
DECLARE snamex VARCHAR(10); DECLARE ssexx VARCHAR(1); DECLARE sagex SMALLINT; DECLARE enterdatex DATETIME; DECLARE record VARCHAR(100);
SELECT sname,ssex,sage,enterdate INTO snamex,ssexx,sagex,enterdatex FROM student
WHERE sno=snox;
SET record=CONCAT_WS(',',snox,snamex,ssexx,sagex,enterdatex);
RETURN record; END$$ DELIMITER ;
SELECT get_student_by_sno('900106')
3、利用SHOW CREATE {PROCEDURE | FUNCTION} sp_name查看存储过程或函数的定义。
SHOW CREATE PROCEDURE delete_student_by_sno\\G SHOW CREATE FUNCTION get_student_by_sno\\G
第 3 页 共 6 页
4、定义条件和处理
①创建一存储过程insert_student_condition，利用条件定义，当主键重复时结束 存储过程，并提示“学生主键重复”。
DELIMITER $$
CREATE PROCEDURE insert_student_condition(snox CHAR(10),snamex VARCHAR(20),ssexx VARCHAR(2),sagex SMALLINT,enterdatex DATETIME)
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '23000'LECT '学生主键重复'; INSERT INTO student(sno,sname,ssex,sage,enterdate) VALUES(snox,snamex,ssexx,sagex,enterdatex); END$$ DELIMITER ;
CALL insert_student_condition('900106','毕琴','女',20,'2010-09-15');
②创建一存储过程insert_student_condition_user，利用条件定义，当插入学生 的性别不为“男”或“女”时结束存储过程，并提示“学生性别不正确”。
DELIMITER $$
CREATE PROCEDURE insert_student_condition_user(IN snox CHAR(10),snamex VARCHAR(20),ssexx CHAR(2),sagex SMALLINT,enterDatex DATETIME) BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'SELECT '学生性别不正确'; IF ssexx='男'OR ssexx='女' THEN
INSERT INTO student(sno,sname,ssex,sage,enterDate) VALUES (snox,snamex,ssexx,sagex,enterDatex) ; ELSE
INSERT INTO xx VALUES('xxxxx'); END IF; END$$ DELIMITER;
CALL insert_student_condition_user('900134','向阳','中',20,'2010-09-12'); CALL insert_student_condition_user('900134','向阳','女',20,'2010-09-12');
5、光标的使用
①创建一存储过程update_student_borthyear，在学生表中添加字段”borthyear” （出生年份），在存储过错中利用光标，通过学生年龄计算出出生年份并修改表中对应 字段。
DELIMITER $$
CREATE PROCEDURE update_student_borthyear(IN snox CHAR(10)) BEGIN
DECLARE Sagex VARCHAR(10); DECLARE borthyearx INT;
第 4 页 共 6 页
DECLARE cur1 CURSOR FOR SELECT sage,borthyear FROM student; DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur1; OPEN cur1; REPEAT
FETCH cur1 INTO Sagex, borthyearx;
UPDATE student SET borthyear=2012-Sage WHERE sno=snox; UNTIL 0 END REPEAT; CLOSE cur1; END$$ DELIMITER ;
CALL update_student_borthyear('900104');
6、触发器
①创建一更新前触发器trigger_student_sc_update，实现在更新学生表的sno时，级联更新对于sc中的相关记录的sno值。
DELIMITER $$
CREATE TRIGGER `stm`.`trigger_student_sc_update` AFTER UPDATE ON student FOR EACH ROW BEGIN
DECLARE newsno CHAR(10); DECLARE oldsno CHAR(10); SET newsno=new.sno; SET oldsno=old.sno; IF newsno<>oldsno THEN
UPDATE sc SET sno=newsno WHERE sno=oldsno; END IF; END$$ DELIMITER ;
UPDATE student SET sno='900176' WHERE sname='楼飞';
②创建一删除前触发器trigger_student_sc_delete，实现在删除一名学生时，级联删除sc中的相关记录。
DELIMITER $$
CREATE TRIGGER `stm`.`trigger_student_sc_delete` BEFORE DELETE ON student FOR EACH ROW
第 5 页 共 6 页
BEGIN
DECLARE oldsno CHAR(10); SET oldsno=old.sno;
DELETE FROM sc WHERE sno=oldsno; END$$ DELIMITER ;
DELETE FROM student WHERE sno='900176';
③创建一插入前触发器trigger_student_insert，实现检查学生的性别、年龄是满足要求才插入该学生信息。 DELIMITER $$ CREATE
TRIGGER `stm`.`trigger_student_insert` BEFORE INSERT ON student FOR EACH ROW BEGIN
DECLARE ssex CHAR(5); DECLARE sage SMALLINT; SET sage=new.sage; SET ssex=new.ssex;
IF (sage>15 AND sage<41) AND (ssex='男' OR ssex='女') THEN SET @choice=1; ELSE
SET @choice=0;
INSERT INTO xxxx VALUES (1); END IF; END$$ DELIMITER ;
INSERT INTO student(sno,sname,ssex,sage,enterdate) VALUES('900178','飞华','男',12,'2010-9-12');
总结：
第 6 页 共 6 页