MySQL--存储过程--自定义函数--触发器基础语法汇总

简单的存储过程：

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()

▄█▀█●各位同仁，如果我的代码对你有帮助，请给我一个赞吧，为了下次方便找到，也可关注加收藏呀
如果有什么意见或建议，也可留言区讨论