存储过程
创建调用删除
创建存储过程
-- 不带参数的过程
create procedure p_hello()
begin
select sysdate();
end;
-- 携带参数的过程
create procedure p_hello(in var_uid int)
begin
select * from suser s where s.sid=var_uid;
end;
调用存储过程
-- 不带参数的调用
call p_hello();
-- 携带参数的调用
call p_hello(200);
删除存储过程
drop procedure if exists p_hello;
变量定义与赋值
创建存储过程——含变量/使用
-- 创建含变量的过程,并复制使用
create procedure p_hello()
begin
declare v_num int; -- 定义变量
declare v_var varchar(255); -- 定义变量
set v_num = 1; -- 设置变量
set v_var = "amazing"; -- 设置变量
select v_num;
select v_var;
end;
选择语句
create procedure p_hello(in v_id int)
begin
if(v_id > 0) then
select '> 0';
elseif (v_id = 0) then
select '=0';
else
select '<0';
end if;
end;
循环语句
while循环
create procedure p_while()
begin
declare i int;
set i=1;
while i <=5 do
select concat('index:',i);
set i=i+1;
end while;
end;
loop循环
create procedure p_loop()
begin
declare i int;
set i=1;
loop_example : loop
select concat('index ->',1);
set i=i+1;
if i>10 then
leave loop_example;
end if;
end loop;
end;
静态游标
create procedure p_hello()
begin
declare sid integer;
declare sname varchar(255);
declare result varchar(4000) default '';
declare cursor_stu cursor for select s.sid,s.sname from student s;
declare continue handler for SQLATATE '02000' set sid = null;
open cursor_stu;
fetch cursor_stu into sid,sname;
while(sid is not null)do
set result = concat(result,'sid:',sid,',sname:',sname,';');
fetch cursor_stu into sid,sname;
end while
close cursor_stu;
select result;
end;
触发器
与数据表关系密切,主要用于保护表中的数据
有多个表具有一定的相互联系时,触发器能够让不同的表保持数据的一致性
只有执行INSERT、UPDATE和DELETE操作时才能激活触发器
优点:
执行时自动的,当对触发器相关表的数据做出相应的修改后立即执行
可以实施比FOREIGN KEY约束,CHECK约束更为复杂的检查和操作
可以实现表数据的级联更改,在一定程度上保障了数据的完整性
缺点:
触发器实现的业务逻辑在出问题时很难进行定位,特别是涉及多个触发器的情况。
大量使用触发器容易导致结构被打乱,增加程序的复杂性
需要变动的数据量较大时,触发器的执行效率会非常低
类型
INSERT触发器
在语句执行之前或之后响应的触发器
在INSERT触发器代码内,可引用一个名为NEW的虚拟表来访问被插入的行
在UPDATE触发器代码内,可引用一个名为NEW的虚拟表来访问更新的值
UPDATE触发器
在UPDATE触发器代码内,可引用一个NEW的虚拟表来访问更新的值
在UPDATE触发器代码内,可引用一个OLD的虚拟表来访问UPDATE语句执行前的值
在BEFORE UPDATE触发器中,NEW中的值可能也被更新,即允许更改将要用于UPDATE语句中的值。
OLD中的值全部时只读的,不能被更新
DELETE触发器
在DELETE触发器代码内,可以引用一个OLD的虚拟表来访问被删除的行。
OLD中的值全部都是只读,不能被更新。
对于事务性表,如果触发程序失败,以及由此导致的整个语句失败,该语句所执行的所有更改将回滚。
对于非事务性表,则不能执行此类回滚,即使语句失败,失败前所作的任何更改仍然有效。
使用语法
create trigger 触发器名 <before | after>
<insert | update | delete>
on 表名 for each row 触发器主体
创建before触发器
-- 创建触发器
-- 累计grade的总和
create trigger tr_grade
before insert on suser
for each row
set @sum=@sum+NEW.grade;
-- 使用触发器
set @sum=0
insert into 'suser' values('10','amazing','100');
select @sum;
创建after触发器
-- 创建一个suser备份表 suser_bak
create table suser_bak as select * from suser;
--向suser插入数据时,也会向备份表插入数据
create trigger tr_bak
after insert on suser
for each row
insert into suser_bak
values(NEW.sid,NEW.sname,concat('bak-',NEW.grade));
查看触发器
-- 简单查看
show triggers;
-- 指定查看触发器
select * from information_schema.triggers where trigger_name='tr_bak';
修改删除
修改可通过删除原触发器,在以相同名称创建新的触发器
删除一个表的同时,也会自动删除该表上的触发器
触发器不能更新或覆盖,为了修改一个触发器,必须先删除,在重新创建
-- 删除触发器
drop trigger [if exists] [数据库名] 触发器名
drop trigger if exists tr_bak
事件
在响应的时刻调用的过程式数据库对象。一个事件可调用一次,也可周期性调用,由一个特定的线程来管理,又称“事件调度器”。
事件取代了原先只能由操作系统的计划任务来执行工作
MySql的事件调度器可以精确到每秒钟执行一个任务
操作系统的计划任务只能精确到每分钟执行一次。
开启事件
查看是否开启
show variables likes '%event_scheduler%';
临时开启
set global event_scheduler=on;
长久开启
# 修改my.ini中[mysqld]数据
event_scheduler=ON
创建事件
一条create event创建一个事件
每个事件由两个主要部分组成
第一部分:事件调度,标识事件何时启动以及按什么频率启动;
第二部分:时间动作,事件启动时执行的代码
一个事件可以是活动的或停止的
活动状态:事件调度器检查事件动作是否必须调用
停止状态:事件的声明存储在目录中,但调度器不会检查它是否应该调用
语法规则:
CREATE
[DEFINER = { user | CURRENT_USER }] -- 定义谁去执行的
EVENT
[IF NOT EXISTS]
event_name -- 事件名
ON SCHEDULE schedule -- 任务调度计划
[ON COMPLETION [NOT] PRESERVE] -- 任务执行完成后是否保留(即是一次执行,还是多次执行)
[ENABLE | DISABLE | DISABLE ON SLAVE] -- 事件的状态:开启,停止
[COMMENT 'comment'] -- 事件的注解
DO event_body; -- 事件所执行的代码
schedule: -- 编写具体调度计划
AT timestamp [+ INTERVAL interval] ... -- 在具体时间
|
EVERY interval -- 周期性执行,设定开始和结束时间
[STARTS timestamp [+ INTERVAL interval]...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
实例演示
-- 立即启动事件
CREATE EVENT event_now
ON SCHEDULE
AT now()
DO insert into test_list values('event_time',now());
-- 10分钟后清空表
CREATE EVENT IF NOT EXISTS etruncate_test
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
DO TRUNCATE TABLE test;
-- 每分钟启动事件
create event test.event_minute
on schedule
every 1 minute
do insert into test_list values('event_time',now());
-- 5天后开启每天定时3秒向test2中插入数据,一个月后停止执行
CREATE EVENT IF NOT EXISTS e_truncate_test2
ON SCHEDULE
EVERY 3 SECOND
STARTS CURRENT_TIMESTAMP + INTERVAL 5 day
ENDS CURRENT_TIMESTAMP + INTERVAL 1 month
ON COMPLETION PRESERVE
DO insert into test2(sname,stime) values('1',now());
-- 每秒调用存储过程
CREATE DEFINER='root'@'localhost' EVENT 'eventUpdateStatus'
ON SCHEDULE
EVERY 1 SECOND
STARTS '2021-2-22 00:00:00'
ON COMPLETION RESERVE
ENABLE
DO call updateStatus();
启用禁用
-- 禁用事件
alter event event_name disable;
-- 启用事件
alter event event_name enable;
删除事件
drop event [if exists] event_name;