创建:
create procedure 存储过程名称(参数列表)
begin
--sql语句
end;
调用:
call 名称(参数列表)
查询某个数据库的存储过程:
select * from information_schema.routines where routine_schema='数据库名称'
查询某个存储过程的创建语句:
show create procedure p1
删除存储过程
drop procedure if exists p1;
注:在命令行中编写存储过程时因为sql 语句需要用到;进行结尾,但是end后面也需要;导致出现错误,我们可以使用delimiter关键字对结束符号进行自定义
delimiter 字符;
之后在end后加上我们自定义的结束符即可。
变量:
系统变量:
是mysql服务器提供的,不是用户去自己定义的,属于服务器层面,分为全局(global),会话变量(session)
查看系统变量:
show variables like 'auto%'(默认是session)模糊匹配
show global variables like 'auto%'(指定是global)模糊匹配
show @@系统变量名称 --指定名称的系统变量
用户自定义变量:
用户根据需要自己定义的变量,用户变量不用提前声明,在用的时候直接用@变量名使用就可以其作用域是当前连接
用户自定义变量赋值:
set @变量名=值;
set @变量名:=值;建议使用因为=也是比较运算符用以混乱
set @变量名=值,@变量名:=值,......;
使用用户变量:
select @变量名,...;
select count(*)into @变量名 from 表名:查询表的所有数据行数赋值给变量
如果变量没有赋值则会获取到null
局部变量:
局部变量是根据需要定义在局部生效的变量,访问之前,需要用到关键字declare 进行声明,可以作为存储过程的局部变量和输入参数,局部变量范围是其内部声明的begin。。。。end块。
声明:
declare变量名 变量类型 (default 默认值);
赋值:
set 变量名=值;
set 白能量明明:=值;
其他语法:
if:
fi 逻辑表达式 then
满足if执行的表达式;
elseif 逻辑表达式 then
满足elseif执行的表达式;
...
else
执行的表达式;
end if;
举例:
create PROCEDURE p2()
begin
declare sorce int default 30;
declare result varchar(20);
if sorce>20 then
set result :='及格';
elseif sorce>15 then
set result :='中等';
else
set result :='不及格';
end if;
select result;
end;
参数:
用法:
create procedure 存储过程名称([in/out/inout 参数名 参数类型])
begin
...
end;
例子:
create PROCEDURE p3(inout sorce int, out result varchar(20))
begin
if sorce>20 then
set result :='及格';
elseif sorce>15 then
set result :='中等';
else
set result :='不及格';
end if;
end;
drop PROCEDURE p3
set @sorce=2(不赋值默认为null)
call p3(@sorce,@result)(@result 是接收函数的返回值)
select @result
select @sorce
存储过程case
语法:
如果when_value1=case_calue1执行when_value1后面then的语句如果case_calue1=when_value2则执行when_value2后面then的语句,都不满足则执行else后的语句
如果search_condition1为true 则执行后面的then,若search_condition2为true 则执then后面的语句,否则执行else后面的语句
例子:
create PROCEDURE p6(in months int)
begin
DECLARE result VARCHAR(20);
CASE
WHEN months>1 and months<=3 THEN
set result='第一季度';
WHEN months>3 and months<=6 THEN
set result='第二季度';
WHEN months>6 and months<=9 THEN
set result='第三季度';
WHEN months>9 and months<=12 THEN
set result='第四季度';
ELSE
set result='输入错误';
END CASE;
select concat('输入月份为:',months,',季度为',result);
end;
call p6(19)
存储过程:while循环
例子:
create PROCEDURE p7(in n int )
begin
DECLARE sum int;
set sum=0;
while n>0 do
set sum:=n+sum;
set n:=n-1;
END WHILE;
select sum;
end;
call p7(100)
存储过程repeat循环(类似dowhile)
例子:
create PROCEDURE p8(in n int )
begin
DECLARE sum int;
set sum=0;
REPEAT
set sum:=n+sum;
set n:=n-1;
UNTIL n<=0
END REPEAT;
select sum;
end;
call p8(100);
存储过程loop
例子:
create PROCEDURE p11( in n int )
begin
DECLARE sum int DEFAULT 0;
loop1: LOOP
set sum:= n+sum;
set n=n-1;
IF n<=0 THEN
LEAVE loop1;
END IF;
IF n%2=0 THEN
ITERATE loop1;
set n=n-1;
END IF;
set n=n-1;
END LOOP loop1;
select sum;
end;
drop PROCEDURE p11;
call p11(100)
条件处理程序
declare exit handler for sqlstate'02000' close u_cursor;
(如果状态码是02000时会关闭游标u_cursor 并且退出程序)
存储函数:
例子:
create FUNCTION f1(n int)
RETURNS int no sql
begin
DECLARE sum int DEFAULT 0;
WHILE n>0 DO
set sum:=sum+n;
set n:=n-1;
END WHILE;
RETURN sum;
END;
select f1(100)
存储过程游标:
例子:
create procedure p01(in uage int)
begin
declare uname varchar(20);
declare uid int;
declare u_cursor CURSOR for select id,name from user where age>uage;
declare exit handler for not found close u_cursor;
drop table if exists tb_user;
create table if not EXISTS tb_user (
id int PRIMARY key,
name varchar(20)
);
open u_cursor;
WHILE true DO
FETCH u_cursor into uid,uname;
INSERT into tb_user values(uid,uname);
END WHILE;
close u_cursor;
end;
drop PROCEDURE p01
call p01(1)