实验报告四 MYSQL存储过程与触发器

计算机科学系实验报告

课程名称：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 页

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说教育文库实验报告四 MYSQL存储过程与触发器在线全文阅读。