Mysql存储过程、触发器、事件调度器使用入门指南- Starterknow

六、游标七、自定义函数八、触发器九、事件

存储过程（Stored Procedure）是一种在数据库中存储复杂程序的数据库对象。为了完成特定功能的SQL语句集，经过编译创建并保存在数据库中。

一、存储过程的简单使用

创建存储过程

create procedure test()

begin

select * from users;

end;调用存储过程

call test();

二、存储过程中的变量

create procedure test()

begin

— 使用 declare语句声明一个变量

declare username varchar(32) default ”;

— 使用set语句给变量赋值

set username=’xiaoxiao’;

— 将users表中id=1的名称赋值给变量username

select name into username from users where id=1;

— 返回变量

select username;

end;
注意：

三、变量的作用域

存储过程的作用域在begin和end块之间，变量声明在begin之外，可以作为全局变量使用：

create procedure test()

begin

   declare userscount int default 0; — 用户表中的数量

   begin

         select count(*) into userscount from users;

         select userscount; — 返回用户表中的数量

   end;

   begin

      declare maxmoney int default 0; — 最大金额

      select max(money) into maxmoney from orders;

      select userscount,maxmoney; — 返回用户表中的数量、最大金额

   end;

end;

四、存储过程参数

create procedure 名称([IN|OUT|INOUT] 参数名参数数据类型 )

begin

……

endIN: 传入参数（不指定时，默认就是IN类型）

create procedure test(userId int)

begin

      declare username varchar(32) default ”;

      select name into username from users where id=userId;

      select username;

end;OUT：传出参数

create procedure test(in userId int,out username varchar(32))

begin

   select name into username from users where id=userId;

end;INOUT: 既是传入又是传出参数

create procedure test6(inout userId int,inout username varchar(32))

begin

set userId=2;

set username=”;

select id,name into userId,username from users where id=userId;

end;

五、逻辑控制语句

1、条件语句

if() then…

elseif() then…

else …

end if;create procedure test(in userid int)

begin

declare my_status int default 0;

select status into my_status from users where id=userid;

if(my_status=1)

then

   update users set score=score+10 where id=userid;

elseif(my_status=2)

then

   update users set score=score+20 where id=userid;

else

   update users set score=score+30 where id=userid;

end if;

end;

2、循环语句

（1）while

while(表达式) do

……

end while;create procedure test()

begin

  declare i int default 0;

  while(i<10) do

   begin

      select i;

      set i=i+1;

      insert into test1(id) values(i);

   end;

  end while;

end;（2）repeat

repeat…until…end repeat;只有当until为真是才跳出循环：

create procedure test()

begin

declare i int default 0;

repeat

   begin

      select i;

      set i=i+1;

      insert into test1(id) values(i);

   end;

until i>=10 — 如果i>=10,则跳出循环

end repeat;

end;

3、case分支

case …

when … then….

when…. then….

else …

end case; create procedure testcate(userid int)

begin

declare my_status int default 0;

select status into my_status from users where id=userid;

      case my_status

         when 1 then update users set score=10 where id=userid;

         when 2 then update users set score=20 where id=userid;

         when 3 then update users set score=30 where id=userid;

         else update users set score=40 where id=userid;

      end case;

end;

六、游标

游标保存了查询结果的临时区域

declare 变量名 cursor … — 创建一个游标变量

close 变量名; — 关闭游标create procedure test()

begin

      declare stopflag int default 0;

      declare username VARCHAR(32);

      declare username_cur cursor for select name from users where id%2=0;

      — 游标变量username_cur保存了查询的临时结果，即结果集

      — 在游标变量中数据的结尾，将变量stopflag设置为1，用于循环中判断是否结束

      declare continue handler for not found set stopflag=1;

      open username_cur; — 打卡游标

      fetch username_cur into username; — 游标向前走一步，取出一条记录放到变量username中

      while(stopflag=0) do — 如果游标还没有结尾，就继续

         begin

            — 在用户名前门拼接 ‘_cur’ 字符串

            update users set name=CONCAT(username,’_cur’) where name=username;

            fetch username_cur into username;– 游标向前走一步，取出一条记录放到变量username中

         end;

      end while; — 结束循环

      close username_cur; — 关闭游标

end;

七、自定义函数

— 创建函数

create functiоn 函数名(参数) returns 返回类型；

— 函数体

begin …… end；

— 指定函数的返回值

returns

–函数调用

select 函数名()。create functiоn getusername(userid int) returns varchar(32)

reads sql data  — 从数据库中读取数据，但不修改数据

begin

      declare username varchar(32) default ”;

      select name into username from users where id=userid;

      return username;

end;

八、触发器

触发器也是一种数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

创建触发器：create trigger 触发器名

after、before：在对表操作之前（before）或者之后（after）触发动作。

操作事件：insert,update,delete等修改操作

影响的范围：for each row

1、需求：出于审计目的，当有人往表users插入一条记录时，把插入的userid,username,插入动作和操作时间记录下来。

create trigger tr_users_insert after insert on users

for each row

begin

      insert into oplog(userid,username,action,optime)

      values(NEW.userid,NEW.name,’insert’,now());

end;2、需求：出于审计目的，当删除users表时，记录删除前该记录的主要字段值

create trigger tr_users_delete before delete on users

for each row

begin

      insert into oplog(userid,username,action,optime)

      values(OLD.id,OLD.name,’delete’,now());

end;

九、事件

触发器只是针对某个表产生的事件执行一些语句,而事件调度器则是在某一个(间隔)时间执行一些语句。

在使用这个功能之前必须确保事件调度器event_scheduler已开启：

SET GLOBAL event_scheduler = 1;

— 或者

SET GLOBAL event_scheduler = on;

–查看开启情况

show variables like ‘%event_scheduler%’;create event[IF NOT EXISTS]event_name — 创建使用create event

ON SCHEDULE schedule — on schedule 什么时候来执行

[ON COMPLETION [NOT] PRESERVE] — 调度计划执行完成后是否还保留

[ENABLE | DISABLE] — 是否开启事件，默认开启

[COMMENT ‘comment’] — 事件的注释

DO sql_statement; — 这个调度计划要做什么？需求：设计一个福彩的开奖过程，每3分钟开奖一次

— 存储过程

create procedure test()

      begin

         insert into lottery(num1,num2,num3,ctime)

         select FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,now();

      end;– 事件

create event if not exists test_event — 创建一个事件

      on schedule every  3 minute — on schedule 每三分钟执行一次

      on completion preserve

      do call test;  –调用存储过程参考文章：mysql存储过程学习笔记

到此这篇关于Mysql存储过程、触发器、事件调度器使用入门的文章就介绍到这了,更多相关Mysql存储过程、触发器、事件调度器内容请搜索共生网络以前的文章或继续浏览下面的相关文章希望大家以后多多支持共生网络！

原创文章，作者：starterknow，如若转载，请注明出处：https://www.starterknow.com/117984.html

Mysql存储过程、触发器、事件调度器使用入门指南

相关文章：

相关推荐

请登录

联系我们