mysql 存储过程 触发器 初探

什么是存储过程?

即是可以编写逻辑的sql,如判断,循环等,,

有什么好处?

设想我们要插入100条数据,在存储过程中循环和批量操作相比,肯定存储过程效率高咯…另外存储过程的运行速度是很快的,你发送几条sql到数据库和发送一条执行命令到数据库,肯定执行命令的速度快多啦!!

坏处

移植性,,,不好吧。。。

我测试用的数据库结构

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50717
Source Host           : localhost:3306
Source Database       : pro_test

Target Server Type    : MYSQL
Target Server Version : 50717
File Encoding         : 65001

Date: 2018-02-04 20:59:23
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_interest
-- ----------------------------
DROP TABLE IF EXISTS `t_interest`;
CREATE TABLE `t_interest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(11) DEFAULT NULL COMMENT '学生id',
  `name` varchar(32) DEFAULT NULL COMMENT '兴趣名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for t_log
-- ----------------------------
DROP TABLE IF EXISTS `t_log`;
CREATE TABLE `t_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(32) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for t_student
-- ----------------------------
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Procedure structure for ifAndWhileTest
-- ----------------------------
DROP PROCEDURE IF EXISTS `ifAndWhileTest`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ifAndWhileTest`(in c CHAR, in num int, out result int)
begin 
declare i int default 0;
declare sum int default 0;
IF c = '1'
THEN
while i <= num DO
set sum = sum + i;
set i = i + 1;
END WHILE;
set result = sum; 
ELSE
set result = 0;
END IF;
end
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for inTest
-- ----------------------------
DROP PROCEDURE IF EXISTS `inTest`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `inTest`(in a int)
begin 
set a = 6;
select a;
end
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for outTest
-- ----------------------------
DROP PROCEDURE IF EXISTS `outTest`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `outTest`(out a int)
begin 
set a = 6;
select a;
end
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for saveStudent
-- ----------------------------
DROP PROCEDURE IF EXISTS `saveStudent`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `saveStudent`(in stuName varchar(32), in interestName varchar(32))
begin 
declare stuId int;
insert into t_student(name) values(stuName);
select max(id) into stuId from t_student;
insert into t_interest(s_id, name) values(stuId, interestName);  
end
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `toStudentAdd`;
DELIMITER ;;
CREATE TRIGGER `toStudentAdd` AFTER INSERT ON `t_student` FOR EACH ROW begin 
insert into t_log(content, create_time) values(concat('学生表插入了一条记录,id是', new.id), now());
end
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `toStudentUpdate`;
DELIMITER ;;
CREATE TRIGGER `toStudentUpdate` AFTER UPDATE ON `t_student` FOR EACH ROW begin 
insert into t_log(content, create_time) values(concat('学生表更新了一条记录,原id是', old.id, '更新后的名字是', new.name), now());
end
;;
DELIMITER ;

定义

delimiter (你自定义的符号)
create procedure 存储过程名称(参数) 
begin 
..............过程
end (你自定义的符号)

关于参数

  1. in参数,只能作为接受的值,无法修改
  2. out参数,可以修改,不能接受
  3. inout参数,结合两者。。

    eg:

delimiter //
create procedure inTest(in a int) 
begin 
set a = 6;
select a;
end //

-- 以下是调用
set  @s = 1;
call inTest(@s);
select @s;

-- ------------------上面的存储过程中修改a的值为6并输出后,我们再次查询 @s 的值发现还是1,并没有变

delimiter //
create procedure outTest(out a int) 
begin 
set a = 6;
select a;
end //

set  @f = 1;
call outTest(@f);
select @f;
-- ------------------上面的存储过程中修改a的值为6并输出后,我们再次查询 @f 的值发现变成6

关于选择结构和循环

直接看代码吧:

-- 输入一个字母和一个数字,如果字母是a,计算0-这个数字的和并返回,否则返回0
delimiter //
create procedure ifAndWhileTest(in c CHAR, in num int, out result int) 
begin 
declare i int default 0;
declare sum int default 0;
-- declare是定义局部变量,貌似只能定义在代码最前端。。我试的时候是这样
IF c = '1'
THEN
while i <= num DO
set sum = sum + i;
set i = i + 1;
END WHILE;
set result = sum; 
ELSE
set result = 0;
END IF;
end //

call ifAndWhileTest('a', 100, @r);
select @r;

实例

-- 实例,一个存储过程,接受学生姓名和爱好,进行插入(两张表的操作)

delimiter //
create procedure saveStudent(in stuName varchar(32), in interestName varchar(32))
begin 
declare stuId int;
insert into t_student(name) values(stuName);
select max(id) into stuId from t_student;
insert into t_interest(s_id, name) values(stuId, interestName);  
end //

call saveStudent('小肥', '晒太阳')

触发器

就是在执行某个操作后我们希望执行操作.比如在操作学生表时记录日志

-- 实现触发器,对学生进行操作时进行记录(语法就是下面这样)
create trigger toStudentAdd after insert on t_student for each row 
begin 
insert into t_log(content, create_time) values(concat('学生表插入了一条记录,id是', new.id), now());
end

-- new代表新的数据,old代表旧的数据
create trigger toStudentUpdate after update on t_student for each row 
begin 
insert into t_log(content, create_time) values(concat('学生表更新了一条记录,原id是', old.id, '更新后的名字是', new.name), now());
end

update t_student set name = '小发' where id = 4
阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页