简单的存储过程:
delimiter $$ ----用命令行操作需要修改语句结束标志
drop procedure if exists duan; ----如果存在duan则删除
create procedure duan() ----创建 存储过程
begin ----存储过程代码块开始标志
declare my_name varchar(32) default ''; ----定义变量,一个declare只能定义一个变量
set my_name='gao'; ----修改变量的值需要用set,也可通过select into 的方式
select context into my_name from t_monitor_object where username='shaomeng'; ---- 可以通过 select into 方式为变量赋值
select my_name; ----可以通过select 将变量进行返回
end; ----存储过程的结束标志
$$ ----语句的结束标志
delimiter ;
存储过程的参数(传入参数–IN,传出参数–OUT类型,可变参数INOUT):
1. 传入参数IN:一般只作为传入,不作为修改和返回,如果不显示指定参数类型,默认为IN类型
(功能:读取外部变量值,但有效范围仅限于存储过程的内部)
drop procedure if exists proc1;
create procedure proc1(IN my_uid int) ----IN可省略 不显示指定参数类型,默认为IN类型
begin
declare my_uname varchar(32) default '';
select username into my_uname from t_monitor_object where id=my_uid;
select my_uname;
select my_uid;
end;
call proc1(32) ----调用时所传参数为常量
select my_uid; ----因为IN类型的参数不用于返回,在存储过程之外不能进行查询
2. 传出参数OUT:传入的为变量,不能传入具体值;在调用的过程中可以修改和返回
(功能:不读取外部变量的值,在存储过程执行完毕之后保留新值)
create procedure tt1(OUT i int) ----存储过程的参数类型需定义在变量名称之前
begin
select i; ----不管是第一次调用还是第二次调用存储过程,该输出均为null,因为OUT类型参数并不会带入参数值
set i=2;
select i;
end;
set @aa=1; ----设置全局变量@aa的值为1,可通过select @aa;调用查看内容
call tt1(@aa)
call tt1(@a) ----OUT类型参数为变量,OUT类型参数前必须加@
select @a ----可以在存储过程之外调用OUT类型参数,输出为2
3. 可变变量INOUT:传入的为变量;调用时可传入值,并且可以修改、返回值。
(功能:读取外部变量值,在存储过程完毕后保留新值)
create procedure p1(INOUT i int)
begin
select i; ----输出为 1
set i = 10;
select i; ----输出为 10
end;
----下面代码依次执行
set @a = 1;
select @a; ----输出为 1
call p1(@a);
select @a; ----输出为 10
存储过程的条件语句:
- 条件语句的基本结构if() then …elseif() then … else … end if;【注意elseif之间没有空格】
- if判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式(可以为SQL)
create procedure proc1(IN my_uid int)
begin
declare my_uname varchar(32) default '';
if(my_uid%2 =0)
then
select username into my_uname from t_monitor_object where id = my_uid;
elseif(my_uid = 31)
then set my_uname = 'gaoshaomeng';
else set my_uname = 'aaa';
end if;
select my_uname;
select my_uid;
end;
存储过程的循环语句:
- while循环:
- while语句的基本结构为:while() do begin … end; end while;
- while判断返回逻辑真或者假,可以是任意返回boolean值的表达式(可以是包含SQL的复杂混合表达式)
- repeat循环语句:
- repeat语句的基本结构:repeat begin … end; until … end repeat;
- until 判断返回逻辑真或者假,当为真时,循环结束
------while循环
create procedure proc1(IN id_start int)
begin
declare flag int default 0;
while(flag<=10)
do
begin
insert into testa(id) values(id_start+flag);
set flag = flag+1;
end;
end while;
end;
call proc1(50)
------repeat循环
create procedure proc1(IN id_start int)
begin
declare flag int default 0;
delete from testa;
repeat
begin
insert into testa(id) values(flag+id_start);
set flag = flag +1;
end;
until flag>50
end repeat;
end;
call proc1(20)
函数的基本语法
drop function if exists func1; ----如果函数存在就删除
create function getuname(uid int) returns varchar(32)
reads sql data ----函数的高级特性(可去掉),包含只读的sql
begin
declare my_uname varchar(32) default '';
select username into my_uname from t_monitor_object where id =uid;
return my_uname;
end;
select getuname(47)
1. 创建函数使用create function 函数名(参数) returns 返回类型
游标
- 存放cursor中单个值的变量应该比cursor先定义
(demo中的//1 应该在//2 之前)
create procedure proc1()
begin
declare stopflag int default 0;
declare my_id int; //1
declare id_cur cursor for select id from t_monitor_object where id%2=0; //2
declare continue handler for not found set stopflag = 1;
----定义类型为continue的handler处理程序,当状态值(condition_value)为not found时设置stopflag值为 1
open id_cur; -----需要打开游标
fetch id_cur into my_id; -----抓取游标中的第一个值
while(stopflag =0)
do
begin
update t_monitor_object set username = concat(username,'_test') where id=my_id;
fetch id_cur into my_id; -----继续依次抓取游标中的值
end;
end while;
Close id_cur; -----游标需要手动关闭
end;
call proc1();
触发器:
-
创建触发器使用create trigger 触发器名
after insert on t_monitor_object
在对表t_monitor_object 执行插入语句之后触发
after—可选–>before、afger
insert—可选—>insert、update、delete -
影响的范围 for each row
-
应用案例:删除某个表中某行数据时,将重要字段进行复制
-
当触发器操作的表格操作失败时,触发触发器的表格操作也会失败
create trigger my_tri after insert on t_monitor_object
for each row
begin
insert into users(uname) values(new.username);
end;
case分支:
create procedure proce(my_id int)
begin
case
when my_id<50 then update t_monitor_object set id = id*5 where id=my_id;
-----then后面可 执行sql语句
when my_id<60 then update t_monitor_object set id = id*10 where id=my_id;
when my_id<70 then update t_monitor_object set id = id*20 where id=my_id;
else update t_monitor_object set id = id*20 where id=my_id;
end case;
end;
call proce(49)
declare处理程序与条件值示例
- Declare continue handler for not found sert stopflag=1;在SQLSTATE的条件值为not found时,执行continue(handler_type)类型的handler处理程序
- 处理程序handler_type的选项
- Continue 在代码块中继续,在循环中继续下一次循环
- Exit 退出begin…end的代码块
- Undo(暂不支持)
- Declare continue handler for not found sert stopflag=1;或者为:declare continue handler for SQLSTATE ‘23000’ set stopFlag=2;
- 条件值condition_value(红色)
- SQLSTATE [VALUE]
- SQLWARNING 是对所有以01开头的SQLSTATE代码的速记
- NOT FOUNT 是对所有以02开头的SQLSTATE代码的速记
- SQLEXCEPTION 是对所有没有被SQLWARNING或者NOT FOUND捕获的SQLSTATE代码的速记
示例:
(continue可改为exit,insert的表字段id不可重复,插入多个1会报错)
drop procedure if exists test_handler1;
create procedure test_handler1()
begin
declare stopFlag int default 1;
declare continue handler for SQLSTATE '23000' set stopFlag=2;
select stopFlag;
insert into testa(id) values(1);
set stopFlag=2;
select stopFlag;
insert into testa(id) values(1);
set stopFlag=3;
select stopFlag;
end;
call test_handler1()
▄█▀█●各位同仁,如果我的代码对你有帮助,请给我一个赞吧,为了下次方便找到,也可关注加收藏呀
如果有什么意见或建议,也可留言区讨论