【MySQL数据库编程 存储过程&触发器 示例】

1．利用不同的分支语句实现输入学生成绩，输出成绩等级。（对于百分制成绩，60分以下为不及格，大于60分且小于或等于70分为及格，大于70分且小于或等于80分为中，大于80分且小于或等于90分为良，大于90分且小于或等于100分为优秀。）

USE stumanbd;

DELIMITER $$
CREATE PROCEDURE getstuname(IN score INT(10))
BEGIN
IF score<60 THEN
SELECT '不及格';
ELSEIF 60<score AND score<=70 THEN
SELECT '及格';
ELSEIF 70<score AND score<=80 THEN
SELECT '中';
ELSEIF 80<score AND score<=90 THEN
SELECT '良';
ELSEIF 90<score AND score<=100 THEN
SELECT '优秀';
END IF;
END $$
DELIMITER ;

CALL getstuname(55);/*呼叫存储过程 结果不及格*/
CALL getstuname(95);/*呼叫存储过程 结果优秀*/

2、利用不同的流程控制语句输出1～200的和。

USE stumanbd;

DELIMITER $$
CREATE PROCEDURE sumadd(IN a INT(10), IN b INT(10))  
BEGIN  
    DECLARE beg INT(10) DEFAULT a;  
    DECLARE ed INT(10) DEFAULT b;  
    DECLARE SUM INT(10) DEFAULT 0;  
    WHILE beg <= ed DO  
        SET SUM = SUM + beg;  
        SET beg = beg + 1;  
    END WHILE;  
    SELECT SUM;  
END $$
DELIMITER ;
CALL sumadd(0,200);

3、创建存储过程onlineproc1，查看菜品编号为“m222005059”的菜品名称，并显示价格。

USE onlineordsysdb;

DELIMITER $$
CREATE PROCEDURE onlineproc1(IN menuid VARCHAR(20))
BEGIN
SELECT menuname AS '菜品名',menuprice AS '菜品价格' ,menuprice*menudicount AS '折扣后价格' FROM menu WHERE menuno=menuid;
END $$
DELIMITER ;

CALL onlineproc1('m222005059');

4、创建带输入参数的存储过程onlineproc2，在订单详情表orderdetails中，输入订单编号，输出订单总额。

USE onlineordsysdb;

DELIMITER $$
CREATE PROCEDURE onlineproc2(IN dataid VARCHAR(20))
BEGIN
DECLARE num FLOAT(20) DEFAULT 0;
DECLARE num2 FLOAT(20) DEFAULT 0;
SELECT SUM(o.detailscount*m.menuprice) INTO num FROM orderdetails AS o,menu AS m WHERE o.ordersno=dataid AND o.menuno=m.menuno;
SELECT SUM(o.detailscount*m.menuprice*m.menudicount)INTO num2 FROM orderdetails AS o,menu AS m WHERE o.ordersno=dataid AND o.menuno=m.menuno;
SELECT num AS '订单总额',num2 AS '折扣后订单总额';
END $$
DELIMITER ;

CALL onlineproc2('081300001');

5、创建带输入和输出参数的存储过程onlineproc3，输入送餐员工编号，输出送餐员工姓名。

USE onlineordsysdb;

DELIMITER $$
CREATE PROCEDURE onlineproc3(IN staffid VARCHAR(20))
BEGIN
SELECT staffname FROM staff WHERE staffno=staffid;
END $$
DELIMITER ;

CALL onlineproc3('S1000001');

6、通过SHOW CREATE语句查看存储过程onlineproc1。

SHOW CREATE PROCEDURE onlineproc1;

7、删除存储过程onlineproc1。

DROP PROCEDURE IF EXISTS onlineproc1;

8、创建自定义函数getmenu，从用户表users中根据指定的用户编号获取用户姓名。

USE stumanbd;

DELIMITER $$
CREATE FUNCTION getmenu(userid VARCHAR(20))
RETURNS VARCHAR(50) CHARSET utf8 
BEGIN
DECLARE usname VARCHAR(50);
SELECT username INTO usname FROM users AS u WHERE u.userno=userid;
RETURN usname;
END $$
DELIMITER ;

SELECT getmenu('u100001');

9、创建user_insert触发器，在向用户表users中插入一条记录时，输出“记录插入成功”，创建成功后，验证触发器的执行结果。

USE onlineordsysdb;

CREATE TABLE userslog(
LOG VARCHAR(100)
);
DELIMITER $$
CREATE TRIGGER user_insert
AFTER INSERT ON users FOR EACH ROW
BEGIN  
INSERT INTO userslog VALUES('记录插入成功');
END $$
DELIMITER ;
SELECT * FROM userslog;
INSERT INTO users VALUES('u800001','z4','h5','男','2020-2-13','13812344321','河北正定','普通');
SELECT * FROM userslog;

10、创建delete触发器，触发器名称为“order_delete”，当删除订单表orders中的订单信息时，订单详情表orderdetails中必须同时把该订单的详细信息删除。触发器创建成功后，删除订单编号为“08130002”的订单，查看订单详情表中的信息，验证触发器的执行结果。

USE onlineordsysdb;

DELIMITER $$
CREATE TRIGGER order_delete
AFTER DELETE ON orders FOR EACH ROW
BEGIN
DELETE FROM orderdetails WHERE ordersno = OLD.ordersno;
END $$
DELIMITER ;

DELETE FROM orders WHERE ordersno='08130002';

SELECT * FROM orders;

SELECT * FROM orderdetails;

11、删除触发器order_delete。

DROP TRIGGER IF EXISTS order_delete;

 附录 A stumanbd 数据库建表语句：

CREATE DATABASE /*!32312 IF NOT EXISTS*/`stumanbd` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `stumanbd`;

DROP TABLE IF EXISTS `t_class`;

CREATE TABLE `t_class` (
  `classno` CHAR(3) NOT NULL COMMENT '班级编号',
  `classname` VARCHAR(50) DEFAULT NULL COMMENT '班级名称',
  PRIMARY KEY (`classno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `t_class`(`classno`,`classname`) VALUES ('001','20软件技术1班'),('002','20软件技术2班'),('003','20软件技术3班'),('004','20软件技术4班'),('005','20软件技术5班'),('006','20软件技术6班'),('007','20软件技术7班'),('008','20软件技术2班');

DROP TABLE IF EXISTS `t_class_copy`;

CREATE TABLE `t_class_copy` (
  `classno` CHAR(3) NOT NULL COMMENT '班级编号',
  `classname` VARCHAR(50) DEFAULT NULL COMMENT '班级名称',
  PRIMARY KEY (`classno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `t_class_copy`(`classno`,`classname`) VALUES ('001','20软件技术1班'),('002','20软件技术2班'),('003','20软件技术3班'),('004','20软件技术4班'),('005','20软件技术5班'),('006','20软件技术6班'),('007','20软件技术7班'),('008','20软件技术2班');

DROP TABLE IF EXISTS `t_course`;

CREATE TABLE `t_course` (
  `courseno` VARCHAR(10) NOT NULL COMMENT '课程编号',
  `coursename` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
  `coursenature` ENUM('考试课','考查课') DEFAULT NULL COMMENT '课程性质',
  `coursescore` FLOAT NOT NULL COMMENT '课程学分',
  `coursehour` INT(10) UNSIGNED DEFAULT NULL COMMENT '课程学时',
  PRIMARY KEY (`courseno`),
  UNIQUE KEY `coursename` (`coursename`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `t_course`(`courseno`,`coursename`,`coursenature`,`coursescore`,`coursehour`) VALUES ('07081903','c语言','考试课',3,56),('07081904','框架编程技术','考查课',1.5,28),('07081906','色彩构成','考试课',3,48),('07081911','mysql','考试课',3,56),('07081917','网页设计','考试课',6.5,120),('07081918','图像处理','考查课',3,56),('07081920','java','考查课',4,72);

DROP TABLE IF EXISTS `t_dorm`;

CREATE TABLE `t_dorm` (
  `d_id` CHAR(4) NOT NULL COMMENT '宿舍号',
  `d_type` VARCHAR(20) NOT NULL COMMENT '宿舍类型',
  `d_buildnum` INT(4) NOT NULL COMMENT '楼号',
  `d_bednum` INT(4) NOT NULL COMMENT '床位号',
  `d_remark` VARCHAR(50) DEFAULT NULL COMMENT '备注信息',
  PRIMARY KEY (`d_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `t_dorm`(`d_id`,`d_type`,`d_buildnum`,`d_bednum`,`d_remark`) VALUES ('1001','标兵宿舍',1,6,'外省1人'),('1003','普通宿舍',1,7,NULL),('2002','文明宿舍',2,6,NULL),('2004','标兵宿舍',2,7,'回民1人'),('3005','文明宿舍',3,5,NULL),('4006','文明宿舍',4,5,NULL);

DROP TABLE IF EXISTS `t_profetitle`;

CREATE TABLE `t_profetitle` (
  `profetitleno` VARCHAR(6) NOT NULL COMMENT '职称编号',
  `profetitle` VARCHAR(30) NOT NULL COMMENT '职称名称',
  PRIMARY KEY (`profetitleno`),
  UNIQUE KEY `profetitle` (`profetitle`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `t_profetitle`(`profetitleno`,`profetitle`) VALUES ('Z003','副教授'),('Z001','助教'),('Z005','助理实验师'),('Z006','实验师'),('Z004','教授'),('Z002','讲师'),('Z007','高级实验师');

DROP TABLE IF EXISTS `t_score`;

CREATE TABLE `t_score` (
  `stuno` CHAR(11) NOT NULL COMMENT '学号',
  `courseno` VARCHAR(10) NOT NULL COMMENT '课程编号',
  `score` INT(11) DEFAULT NULL COMMENT '成绩',
  KEY `fk_id6` (`stuno`),
  KEY `fk_id7` (`courseno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `t_score`(`stuno`,`courseno`,`score`) VALUES ('35092001021','07081903',98),('35092001021','07081906',86),('35092002023','07081911',56),('35092002022','07081917',58),('35091903024','07081903',77),('35092001021','07081920',50),('35092005059','07081918',90),('35092001007','07081904',93),('35091903024','07081903',85);

DROP TABLE IF EXISTS `t_students`;

CREATE TABLE `t_students` (
  `stuno` CHAR(11) NOT NULL COMMENT '学号',
  `stuname` VARCHAR(30) NOT NULL COMMENT '姓名',
  `stugender` ENUM('男','女') DEFAULT '男',
  `stubirth` DATETIME NOT NULL COMMENT '出生日期',
  `classno` CHAR(3) NOT NULL COMMENT '班级编号',
  `di_id` CHAR(4) NOT NULL COMMENT '宿舍号',
  PRIMARY KEY (`stuno`),
  UNIQUE KEY `stuname` (`stuname`),
  KEY `fk_id1` (`classno`),
  KEY `fk_id2` (`di_id`),
  CONSTRAINT `fk_id` FOREIGN KEY (`classno`) REFERENCES `t_class` (`classno`),
  CONSTRAINT `fk_id2` FOREIGN KEY (`di_id`) REFERENCES `t_dorm` (`d_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `t_students`(`stuno`,`stuname`,`stugender`,`stubirth`,`classno`,`di_id`) VALUES ('35091903024','程学峰','男','2000-12-08 00:00:00','005','3005'),('35092001007','王小蒙','女','1999-12-10 00:00:00','008','1003'),('35092001021','张江涛','男','2002-11-03 21:48:20','001','1001'),('35092002010','刘婷婷','女','2001-02-11 00:00:00','002','2002'),('35092002022','李玉红','女','2001-02-23 00:00:00','004','2004'),('35092002023','林强','男','2001-10-10 00:00:00','003','1003'),('35092005021','刘梦瑶','女','2000-06-09 00:00:00','006','1003'),('35092005059','张浩','男','2001-11-10 00:00:00','007','2002');

DROP TABLE IF EXISTS `t_teachers`;

CREATE TABLE `t_teachers` (
  `teano` VARCHAR(12) NOT NULL COMMENT '教师编号',
  `teaname` VARCHAR(50) DEFAULT NULL COMMENT '姓名',
  `teagender` ENUM('男','女') DEFAULT '男' COMMENT '性别',
  `teabirth` DATETIME DEFAULT NULL COMMENT '出生日期',
  `profetitleno` VARCHAR(6) DEFAULT NULL COMMENT '职称编号',
  PRIMARY KEY (`teano`),
  KEY `fk_id3` (`profetitleno`),
  CONSTRAINT `fk_id3` FOREIGN KEY (`profetitleno`) REFERENCES `t_profetitle` (`profetitleno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `t_teachers`(`teano`,`teaname`,`teagender`,`teabirth`,`profetitleno`) VALUES ('1998032561','王龙飞','男','1972-09-06 00:00:00','Z004'),('2001030217','刘依然','女','1976-12-03 00:00:00','Z003'),('2001030277','李梦','女','1975-08-12 00:00:00',NULL),('2004090216','张鹏超','男','1983-08-10 00:00:00','Z002'),('2019482719','李晓霞','女','2001-01-08 00:00:00','Z001');

DROP TABLE IF EXISTS `t_tealecture`;

CREATE TABLE `t_tealecture` (
  `lectureno` VARCHAR(8) NOT NULL COMMENT '授课编号',
  `teano` VARCHAR(12) NOT NULL COMMENT '教师编号',
  `courseno` VARCHAR(10) NOT NULL COMMENT '课程编号',
  `courseterm` VARCHAR(4) NOT NULL COMMENT '开设编号',
  PRIMARY KEY (`lectureno`),
  KEY `fk_id4` (`teano`),
  KEY `fk_id5` (`courseno`),
  CONSTRAINT `fk_id4` FOREIGN KEY (`teano`) REFERENCES `t_teachers` (`teano`),
  CONSTRAINT `fk_id5` FOREIGN KEY (`courseno`) REFERENCES `t_course` (`courseno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `t_tealecture`(`lectureno`,`teano`,`courseno`,`courseterm`) VALUES ('LN001','2001030217','07081903','第一学期'),('LN002','2004090216','07081911','第二学期'),('LN003','1998032561','07081917','第一学期'),('LN004','2019482719','07081918','第二学期'),('LN005','2001030277','07081918','第三学期'),('LN006','2001030217','07081904','第三学期'),('LN007','2004090216','07081906','第四学期'),('LN008','1998032561','07081917','第四学期'),('LN009','1998032561','07081903','第五学期');

DROP TABLE IF EXISTS `t_test`;

CREATE TABLE `t_test` (
  `col1` CHAR(11) NOT NULL COMMENT '测试字段1',
  `col2` VARCHAR(30) DEFAULT NULL COMMENT '测试字段2'
) ENGINE=INNODB DEFAULT CHARSET=utf8;

附录 B onlineordsysdb 数据库建表语句：

CREATE DATABASE /*!32312 IF NOT EXISTS*/`onlineordsysdb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `onlineordsysdb`;

DROP TABLE IF EXISTS `menu`;

CREATE TABLE `menu` (
  `menuno` CHAR(12) NOT NULL COMMENT '菜单编号',
  `menuname` VARCHAR(50) NOT NULL COMMENT '菜品名称',
  `typesno` CHAR(6) NOT NULL COMMENT '菜品种类编号',
  `menuffavour` VARCHAR(50) NOT NULL COMMENT '风味',
  `menuprice` FLOAT NOT NULL COMMENT '单价',
  `menudicount` FLOAT NOT NULL COMMENT '折扣'
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `menu`(`menuno`,`menuname`,`typesno`,`menuffavour`,`menuprice`,`menudicount`) VALUES ('m000001001','小香槟','004','保定风味',4,0.8),('m111903024','剁辣椒炒鸡胗','002','保定风味',50,0.8),('m112001021','蒜香鸡翅','001','四川风味',34,0.9),('m112002010','滑蛋炒牛肉','001','保定风味',30,0.9),('m112002023','红烧鸡爪','001','山东风味',43,0.9),('m222001007','韭菜炒香干','001','山东风味',18,0.9),('m222002022','小米蒸排骨','001','四川风味',40,0.9),('m222005021','麻婆茄子','001','山东风味',15,0.9),('m222005059','香辣猪蹄','002','四川风味',48,0.8),('m333001001','米饭','003','保定风味',2,0.8),('m333001002','水饺','003','保定风味',10,0.9),('m333001003','葱香大饼','003','保定风味',8,0.9);

DROP TABLE IF EXISTS `orderdetails`;

CREATE TABLE `orderdetails` (
  `datailsffno` CHAR(8) NOT NULL COMMENT '订单序号',
  `ordersno` CHAR(10) NOT NULL COMMENT '订单编号',
  `menuno` CHAR(12) NOT NULL COMMENT '菜品编号',
  `detailscount` SMALLINT(6) NOT NULL COMMENT '菜品数量'
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `orderdetails`(`datailsffno`,`ordersno`,`menuno`,`detailscount`) VALUES ('D1000001','081300001','m112001021',2),('D1000002','081300001','m112002010',1),('D1000003','081300001','m112002023',1),('D2000001','08130002','m222002022',2),('D2000002','08130002','m222005021',1),('D3000002','08150001','m112002023',1),('D3000003','08150001','m333001003',2);

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
  `ordersno` CHAR(10) NOT NULL COMMENT '订单编号',
  `userno` CHAR(8) NOT NULL COMMENT '用户编号',
  `orderstime` DATETIME NOT NULL COMMENT '订餐时间',
  `orderspaidmode` VARCHAR(20) NOT NULL COMMENT '付款方式',
  `ordersphone` CHAR(20) NOT NULL COMMENT '联系电话',
  `ordersmoney` FLOAT NOT NULL COMMENT '金额',
  `staffno` CHAR(10) NOT NULL COMMENT '送餐员工编号',
  `orderscook` VARCHAR(30) DEFAULT NULL COMMENT '炒菜厨师'
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `orders`(`ordersno`,`userno`,`orderstime`,`orderspaidmode`,`ordersphone`,`ordersmoney`,`staffno`,`orderscook`) VALUES ('081300001','u100001','2021-04-03 11:20:30','微信','13112345000',96.3,'S1000001','刘勇'),('08130002','u100002','2021-03-03 12:03:10','支付宝','13012344000',85.5,'S1000002','张平'),('08140003','u200003','2021-03-13 11:30:23','微信','133312344111',84,'S1000003','萧寒力'),('08150001','u300004','2021-02-13 12:06:10','微信','13412344222',53.1,'S2000001','马少辉'),('08150002','u400001','2021-04-03 11:30:15','微信','1351244321',76.3,'S2000002','王召阳'),('08160002','u400002','2021-04-05 12:10:30','支付宝','13612344321',64.9,'S2000002','平瑞'),('08160003','u100003','2021-04-04 12:20:20','微信','13712344321',155,'S3000002','平玉明'),('08170001','u300001','2021-04-03 11:18:35','支付宝','13812344321',160.3,'S3000003','刘迪'),('08170002','u100001','2021-04-05 12:10:30','微信','13112345000',84,'S3000002','刘迪'),('08170003','u100002','2021-04-06 11:10:20','支付宝','13012344000',64.9,'S1000003','张平');

DROP TABLE IF EXISTS `staff`;

CREATE TABLE `staff` (
  `staffno` CHAR(10) NOT NULL COMMENT '送餐员工编号',
  `staffname` VARCHAR(20) NOT NULL COMMENT '送餐员工姓名',
  `staffarea` VARCHAR(100) NOT NULL COMMENT '负责区域',
  `staffphone` CHAR(20) NOT NULL COMMENT '联系电话',
  PRIMARY KEY (`staffno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `staff`(`staffno`,`staffname`,`staffarea`,`staffphone`) VALUES ('S1000001','小飞侠','玉龙区域','18132568963'),('S1000002','神速到达','阿尔卡迪亚区域','13013111311'),('S1000003','奥特曼','卓达区域','13013221322'),('S2000001','快乐之星','风河源区域','13013331333'),('S2000002','克塞号','城南春天区域','13013441344'),('S3000002','神剑','龙凤湖区域','13013551355'),('S3000003','飞之影','滨湖新城区域','13013661366');

DROP TABLE IF EXISTS `types`;

CREATE TABLE `types` (
  `typesno` CHAR(6) NOT NULL COMMENT '菜品种类编号',
  `typesname` ENUM('热菜','凉菜','主食','饮品') NOT NULL COMMENT '菜品种类名称',
  `typedescription` VARCHAR(100) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`typesno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `types`(`typesno`,`typesname`,`typedescription`) VALUES ('001','热菜','省略'),('002','凉菜','省略'),('003','主食','省略'),('004','饮品','省略');

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `userno` CHAR(8) NOT NULL COMMENT '用户编号',
  `username` VARCHAR(50) NOT NULL COMMENT '姓名',
  `usernickname` VARCHAR(50) NOT NULL COMMENT '昵称',
  `usergender` CHAR(1) NOT NULL COMMENT '性别',
  `userregistration` DATETIME NOT NULL COMMENT '注册时间',
  `userphone` CHAR(20) NOT NULL COMMENT '电话',
  `useraddress` VARCHAR(100) NOT NULL COMMENT '地址',
  `usergrade` ENUM('普通','超级','金钻') NOT NULL DEFAULT '普通' COMMENT '级别',
  PRIMARY KEY (`userno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT  INTO `users`(`userno`,`username`,`usernickname`,`usergender`,`userregistration`,`userphone`,`useraddress`,`usergrade`) VALUES ('u100001','张展','展展','男','2017-02-03 14:49:02','13112345432','河北石家庄裕华路88号','普通'),('u100002','刘梅','梅花','女','2018-03-03 14:50:53','13012344321','河北石家庄和平路5号','超级'),('u100003','高丽','丽丽','女','2017-02-13 14:57:20','13712344321','河北石家庄裕华路14号','普通'),('u200003','李武坡','阿仔','男','2017-05-13 14:52:06','13312344321','河北石家庄中山路60号','金钻'),('u300001','李乐淮','乐乐','女','2018-02-13 14:58:56','13812344321','河北省石家庄和平路9号','超级'),('u300004','高达','达人天下','男','2017-02-13 14:53:26','13412344321','河北石家庄裕华路4号','普通'),('u400001','高帅','帅帅','男','2019-12-03 14:54:52','13512344321','河北石家庄和平路16号','超级'),('u400002','李魁','小李子','男','2017-05-06 14:56:04','13612344321','河北石家庄中山路60号','金钻');