--最近在项目中总是用到MySQL的触发器和存储过程，为了加深印象，总结下。
1、触发器
触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
语法格式：（语法格式一定要写对，不然就报错，很坑）
delimiter //
create trigger triggerName
after/before insert/update/delete on 表名
for each row #这句话在mysql是固定的
begin
sql语句;
END //
delimiter;接下来让我们创建一个触发器吧： 以insert为例
需求是：当在users中插入一条数据，就会在logs中生成一条日志信息。
1)users表
CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
`add_time` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`(250)) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1;
insert into `users` (name,add_time) values('小敏',20190702),
('小李',20190702),('小明',20190702), ('小王',20190702);2)log表
CREATE TABLE `logs` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`log` varchar(255) DEFAULT NULL COMMENT '日志说明',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日志表';
3）创建触发器
DELIMITER //
CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROW
BEGIN
DECLARE s1 VARCHAR(40)character set utf8;
DECLARE s2 VARCHAR(20) character set utf8;
SET s2 = " create a log in ";
SET s1 = CONCAT(CONCAT(NEW.name,s2),NEW.add_time);
INSERT INTO logs(log) values(s1);
END //
DELIMITER ;在navicat中可以看到我们创建的触发器：
4)测试
insert into `users` (name,add_time) values('小钱',20190723)在logs表中就可以看到我们加入的数据了
2、存储过程
存储过程（procedure）：存储过程是存储在数据库目录中的一段声明性SQL语句。 触发器，其他存储过程以及Java，Python，PHP等应用程序可以调用存储过程。自身的存储过程称为递归存储过程。大多数数据库管理系统支持递归存储过程。
语法格式：
DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
sql语句;
END //
DELIMITER ;
call GetAllProducts();接下来让我们创建一个简单的存储过程吧：
需要：提取学生表的信息 ，经判断后插入好学生表中
1）学生表：
create table student(
s_id int unsigned not null PRIMARY key,
s_name varchar(10),
s_score int,
s_class varchar(6)
)ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1;
insert into student values(1,'小王',70,'14_2'),(2,'小李',90,'14_3'),(3,'小明',30,'14_1'),
(4,'小花',60,'14_2'),(5,'张三',96,'14_4'),(6,'李四',73,'14_3'),
(7,'王五',92,'14_2'),(8,'赵六',88,'14_1');2)好学生表
create table good_student(
s_id int unsigned not null PRIMARY key,
s_name varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
s_class varchar(6),
rank int
)ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1;3)存储过程
DELIMITER //
CREATE PROCEDURE zy_data_p_auto()
BEGIN
DECLARE t_v_id bigint;
DECLARE t_v_name VARCHAR(20);
DECLARE t_v_class varchar(10);
DECLARE t_v_score int;
DECLARE i int default 0;
set @counts = (select count(1) from student) ; #获取student表的记录
set i = 1; #定义初始变量
WHILE i<=@counts do #循环条件
set @rank=0;
select s_id,s_name,s_class,s_score
into t_v_id
,t_v_name
,t_v_class
,t_v_score
from (
select @rownum:=@rownum+1 as rank
,t.*
from (select @rownum:=0) r , student t
order by t.s_score desc
) t
where rank = i ; #有顺序的取出student表的数据，并排序
IF t_v_score>=90 then #判断成绩>90的学生，加入好学生表中
insert into good_student values(t_v_id,t_v_name,t_v_class,i);
ELSE
select '成绩不够';
END IF;
set i = i+1;
END WHILE;
END //
DELIMITER ;4）查看结果：
3、存储过程和触发器联合使用
需求：往student插入一条数据时，更新logs表和good_student表
student ,logs,good_student表和上面表结构一样
1)创建触发器
DELIMITER //
CREATE TRIGGER tri_student AFTER INSERT ON student FOR EACH ROW
BEGIN
call insert_othertab_auto(new.s_id,new.s_name,new.s_class,new.s_score);
END //
DELIMITER ;2)创建存储过程
DELIMITER //
CREATE PROCEDURE tri_student ( in t_v_id bigint,in t_v_name VARCHAR(20),in t_v_class varchar(10),in t_v_score int)
BEGIN
DECLARE i int default 0;
DECLARE str VARCHAR(20) character set utf8;
set i = (select max(rank) from good_student); #定义初始变量
set str = concat (t_v_name,'is load data');
insert into logs values(t_v_id,str);
IF t_v_score>=90 then #判断成绩>90的学生，加入好学生表中
insert into good_student values(t_v_id,t_v_name,t_v_class,i+1);
ELSE
select '成绩不够';
END IF;
END //
DELIMITER ;3)测试结果
insert into student values(9,'测试者','14_6',95);
4、存储过程高级用法 (游标的使用)
CREATE DEFINER=`root`@`%` PROCEDURE `isc_p_make_streagt_auto`(IN V_TARGET_ID VARCHAR(200),IN v_TARGET_NAME VARCHAR(300),IN TIMES TIMESTAMP )
BEGIN
DECLARE v_STRATEGY_sub_ID bigint(20);
DECLARE v_STRATEGY_ID bigint(20);
DECLARE v_BEFORE_PATH varchar(100);
DECLARE v_CURRENT_PATH varchar(100);
DECLARE v_JSON_SCRIPT longtext;
DECLARE v_JSON_DESCRIBE longtext;
DECLARE v_SPLIT_STATE smallint(1);
DECLARE v_SYSTEM_SCORE decimal(10,2);
DECLARE v_EXPERIENCE_SCORE decimal(10,2);
DECLARE v_PREFERENCE_SCORE decimal(10,2);
DECLARE v_MAX_SCORE_STRATEGY_SUB_DETAIL_ID bigint(20);
DECLARE v_TRANSFER_CLASSIC_ID smallint(2);
DECLARE v_CREATE_USER_ID bigint(20);
DECLARE v_CREATE_TIME timestamp;
DECLARE v_UPDATE_USER_ID varchar(30);
DECLARE v_UPDATE_TIME timestamp;
DECLARE v_TENANT_ID varchar(20);
declare no_more_departments integer DEFAULT 0;
DECLARE cursor_employee CURSOR FOR
select STRATEGY_sub_id
,STRATEGY_ID
,BEFORE_PATH
,CURRENT_PATH
,JSON_SCRIPT
,JSON_DESCRIBE
,SPLIT_STATE
,SYSTEM_SCORE
,EXPERIENCE_SCORE
,PREFERENCE_SCORE
,MAX_SCORE_STRATEGY_SUB_DETAIL_ID
,TRANSFER_CLASSIC_ID
,CREATE_USER_ID
,CREATE_TIME
,UPDATE_USER_ID
,UPDATE_TIME
,TENANT_ID
from isc_strategy_sub
where source = 3 and stage = '1' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_departments = 1;
OPEN cursor_employee;
FETCH cursor_employee INTO v_STRATEGY_sub_ID,v_STRATEGY_ID,v_BEFORE_PATH,v_CURRENT_PATH,v_JSON_SCRIPT,v_JSON_DESCRIBE,v_SPLIT_STATE,v_SYSTEM_SCORE,v_EXPERIENCE_SCORE,v_PREFERENCE_SCORE,v_MAX_SCORE_STRATEGY_SUB_DETAIL_ID
,v_TRANSFER_CLASSIC_ID,v_CREATE_USER_ID,v_CREATE_TIME,v_UPDATE_USER_ID,v_UPDATE_TIME,v_TENANT_ID;
WHILE !no_more_departments DO
set @STRATEGY_SN = CONCAT('S',concat(date_format(current_timestamp(3),'%Y%m%d%H%i%s%S'),FLOOR(1000 + (RAND() * 9000))));
set @STRATEGY_NAME =(select STRATEGY_NAME from isc_strategy where STRATEGY_ID =v_STRATEGY_ID) ;
set @STRATEGY_DESCRIBE = '';
set @SOURCE = 0 ;
set @UPDATE_USER_ID=null;
INSERT INTO isc_strategy(strategy_sn,target_id,strategy_name,strategy_describe,source,create_user_id,
create_time,update_user_id,update_time,tenant_id )
VALUES (@STRATEGY_SN
,v_TARGET_ID
,@STRATEGY_NAME
,@STRATEGY_DESCRIBE
,@SOURCE
,v_CREATE_USER_ID
,v_CREATE_TIME
,v_UPDATE_USER_ID
,v_UPDATE_TIME
,v_TENANT_ID ) ;
set @STRATEGY_SUB_SN= CONCAT('SB',concat(date_format(current_timestamp(3),'%Y%m%d%H%i%s%S'),FLOOR(1000 + (RAND() * 9000))));
set @STRATEGY_ID = (select STRATEGY_ID from isc_strategy where STRATEGY_SN =@STRATEGY_SN );
set @STAGE = '1' ;
set @SOURCE = 0 ;
insert into isc_strategy_sub(
STRATEGY_SUB_SN,STRATEGY_ID,TARGET_ID,BEFORE_PATH,CURRENT_PATH ,STAGE,SOURCE,JSON_SCRIPT,JSON_DESCRIBE,SPLIT_STATE
,SYSTEM_SCORE,EXPERIENCE_SCORE,PREFERENCE_SCORE,MAX_SCORE_STRATEGY_SUB_DETAIL_ID,TRANSFER_CLASSIC_ID,CREATE_USER_ID
,CREATE_TIME,UPDATE_USER_ID,UPDATE_TIME,TENANT_ID
)
values(@STRATEGY_SUB_SN,@STRATEGY_ID,v_TARGET_ID,v_BEFORE_PATH,v_CURRENT_PATH,@STAGE,@SOURCE,v_JSON_SCRIPT,v_JSON_DESCRIBE,v_SPLIT_STATE
,v_SYSTEM_SCORE,v_EXPERIENCE_SCORE,v_PREFERENCE_SCORE,v_MAX_SCORE_STRATEGY_SUB_DETAIL_ID,v_TRANSFER_CLASSIC_ID,v_CREATE_USER_ID
,v_CREATE_TIME,v_UPDATE_USER_ID,v_UPDATE_TIME,v_TENANT_ID
) ;
set @STRATEGY_SUB_ID = (select STRATEGY_SUB_ID from isc_strategy_sub where STRATEGY_SUB_SN = @STRATEGY_SUB_SN) ;
insert into isc_strategy_key_rule(STRATEGY_SUB_ID,TAG_LIB_ID,KEY_RULE,KEY_RULE_DESCRIBE,MAPPING_RULE,MAPPING_DESCRIBE,ORIGINAL_COLUMN
,MAPPING_COLUMN,MAPPING_COLUMN_CN,MAPPING_TAG_LIB_ID,CREATE_USER_ID,CREATE_TIME,TENANT_ID)
select @STRATEGY_sub_ID
,TAG_LIB_ID
,KEY_RULE
,KEY_RULE_DESCRIBE
,MAPPING_RULE
,MAPPING_DESCRIBE
,ORIGINAL_COLUMN
,MAPPING_COLUMN
,MAPPING_COLUMN_CN
,MAPPING_TAG_LIB_ID
,CREATE_USER_ID
,CREATE_TIME
,TENANT_ID
from isc_strategy_key_rule
where STRATEGY_SUB_ID = v_STRATEGY_sub_ID
;
FETCH cursor_employee INTO v_STRATEGY_sub_ID,v_STRATEGY_ID,v_BEFORE_PATH,v_CURRENT_PATH,v_JSON_SCRIPT,v_JSON_DESCRIBE,v_SPLIT_STATE,v_SYSTEM_SCORE,v_EXPERIENCE_SCORE,v_PREFERENCE_SCORE,v_MAX_SCORE_STRATEGY_SUB_DETAIL_ID
,v_TRANSFER_CLASSIC_ID,v_CREATE_USER_ID,v_CREATE_TIME,v_UPDATE_USER_ID,v_UPDATE_TIME,v_TENANT_ID;
END WHILE;
CLOSE cursor_employee;
END
扫一扫
5444
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
