实验报告四 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 页 共 7 页

②创建一存储过程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 ;

第 2 页 共 7 页

CALL insert_student('900104','里斯','男',21,'2010-09-12')

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查看存储过程或函数的定义。

第 3 页 共 7 页

SHOW CREATE PROCEDURE delete_student_by_snoG

SHOW CREATE FUNCTION get_student_by_snoG

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');

第 4 页 共 7 页

5、光标的使用

①创建一存储过程update_student_borthyear，在学生表中添加字段”borthyear”

（出生年份），在存储过错中利用光标，通过学生年龄计算出出生年份并修改表中对应

字段。

DELIMITER $$

CREATE PROCEDURE update_student_borthyear(IN snox CHAR(10))

BEGIN

DECLARE Sagex VARCHAR(10);

DECLARE borthyearx INT;

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=;

SET oldsno=;

IF newsno<>oldsno THEN

UPDATE sc SET sno=newsno WHERE sno=oldsno;

END IF;

END$$

第 5 页 共 7 页

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

BEGIN

DECLARE oldsno CHAR(10);

SET oldsno=;

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=;

SET ssex=;

IF (sage>15 AND sage<41) AND (ssex='男' OR ssex='女') THEN

SET @choice=1;

ELSE

第 6 页 共 7 页