存储过程
| 类型 | 含义 |
|---|---|
| IN | 作为输入，调用时需要传入值 |
| OUT | 作为输出，该参数作为返回值 |
| INOUT | 既可以输入参数，也可以输出参数 |
语法：
创建
create procedure 存储过程名称(
in 参数名称1 参数数据类型，
out 参数名称2 参数数据类型,
inout 参数名称3 参数数据类型
)
begin
————SQL语句
end;
调用
call 存储过程名称(参数1，@参数名称2) # in和out
set @参数名称2 = X;
call 存储过程名称(@参数名称2); #inout
select @参数名称2;
查看
show create procedure 存储过程名称;
删除
drop procedure if exists 存储过程名称;
用户变量
定义：用户自定义的变量，即用户变量，用户变量不用提前声明，在使用的时候直接用@变量名使用即可。作用域为当前连接
创建用户变量
set @变量名 = 值
select 字段名 into @变量名 from 表名; @把字段的值赋给用户变量
使用用户变量
select @变量名;
局部变量
定义：局部变量是根据需要定义的在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin…end块
声明：
declare 变量名 变量类型 [default x]; #default为默认值，此时默认值为x
赋值：
set 变量名 = 值;
select 字段名 into 变量名 from 表名....;
eg：
create procedure p()
begin
declare stu_count int default 0;
select count(*) into stu_count from student;
select stu_count;
end;
call p(); #把count(*)的值赋给了stu_count
逻辑语句 if else
语法：
if 条件1 then
代码1
elseif 条件2 then #可选
代码2
else #可选
代码3
end if;
如果 符合条件1 则进入代码1，否则如果符合条件2则进入代码2，否则进入代码3
eg：
create procedure p(
in sc char(10),
in sc2 char(10)
)
begin
declare result varchar(10);
declare sum int;
select grade into sum from score where sno = sc and cno = sc2;
if sum >= 85 then
set result = '优秀';
elseif sum>=60 then
set result = '及格';
else
set result = '不及格';
end if;
select result;
end;
call p('2015001','c02');
分支函数 case
语法1：
case 表达式
when when_value1 then statement_list1
when when_value2 then statement_list3
....
else statement_list
语法2：
case
when 表达式1 then 代码1
when 表达式2 then 代码2
.....
else
代码3
end case;
eg:
create PROCEDURE p2(
in month int)
begin
declare result varchar(10);
case
when month>=1 and month<=3 then
set result = '第一季度';
when month>=4 and month<=6 then
set result = '第二季度';
when month>=7 and month<=9 then
set result = '第三季度';
when month>=10 and month<=12 then
set result = '第四季度';
else
set result = '非法参数';
end case;
select concat('您输入的月份为：',month,'所属的季度为：',result);
end;
call p2(1);
循环
while
满足条件后，再次执行循环体中的sql语句
# 先判断条件，如果条件为true，则执行逻辑，否则，不执行
while 条件 do
sql逻辑代码
end while;
eg:
#计算从1累加到n的值，n为传入的参数
create procedure p3(
in n int
)
begin
declare total int DEFAULT 0;
while n>0 do
set total = total+n;
set n = n-1;
end while;
select total;
end;
call p3(3);
repeat
# 先判断条件，当满足条件时退出循环
#先执行一次逻辑，然后判定逻辑是否满足，如果满足则退出，否则继续
repeat
sql逻辑代码
until 条件
end repeat;
eg:
create procedure p4(
in n int
)
begin
declare total int DEFAULT 0;
repeat
set total = total+n;
set n = n-1;
until n<0
end repeat;
select total;
end;
call p4(3);
loop
loop实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用其来实现简单的死循环，loop可以配合以下两个语句使用
- leave ：配合循环使用，退出循环
- iterate ：必须用在循环中，作用和 C++的continue相同
语法： 标记名:loop SQL逻辑代码 end loop 标记名; leave lable; #退出指定标记的循环体 iterate label; #直接进入下一次循环 eg: create procedure p5( in n int ) begin declare total int DEFAULT 0; sum:loop if n<=0 then leave sum; end if; set total = total+n; set n = n-1; end loop sum; select total; end; call p5(3); #如果加到奇数的话iterate create PROCEDURE p6( in n int ) begin declare total int DEFAULT 0; sum:loop if n<=0 then leave sum; end if; if n%2=1 then set n = n-1; ITERATE sum; end if; set total = total+n; set n = n-1; end loop sum; select total; end; call p6(3);
游标
游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。
声明游标：
declare 游标名 cursor for 查询语句;
打开游标： #使用游标之前需要先打开游标
open 游标名;
获取游标记录：
fetch 游标名 into 变量;
关闭游标：
close 游标名;
eg:
根据传入的参数age，查询students表中，所有学生出生日期小于age的用户姓名和专业，并将用户的姓名和专业插入到一个新表中
思路：A：声明游标，B：创建表，C：开启游标，D：获取游标中的记录，E：插入数据到新表，F：关闭游标
create PROCEDURE p7(
in uage date #传入出生日期
)
begin
declare usname varchar(100); #保存符合条件的学生信息的两个局部变量
declare upro varchar(100);
declare u_cursor cursor for #声明游标，
select sname,smajor from students where sbirthday<uage; #找出 出生日期大于uage的
#如果存在则删除，这样的话就保证表中只显示比uage大的学生信息
drop table if exists tb_user;
create table if not exists tb_user( #创建表
id int primary key auto_increment, #id设置自增主键
uname varchar(100),
sdapt varchar(100)
);
open u_cursor; #打开游标
while true do #进入while循环
fetch u_cursor into usname,upro; # 把游标中的记录存入两个局部变量
insert into tb_user values(null,usname,upro); #插入新表
end while;
close u_cursor; #关闭游标
end;
call p7('1999-03-1');
存储函数
存储函数是有返回值的存储过程，存储函数的参数只能是in类型的。
语法：
create function 存储函数名(参数列表)
returns type [characteristic...]
begin
SQL语句
return ...;
end;
characteristic说明：
deterministic：相同的输入参数总是产生相同的结果
no sql ：不包含sql语句
reads sql data： 包含读取数据的语句，但不包含写入数据的语句
eg：
create function fun1(n int)
returns int deterministic
begin
declare total int default 0;
while n>0 do
set total = total+n;
set n = n-1;
end while;
return total;
end;
select fun1(10);
注意：存储函数必须要有返回值
标签：set,end,示例,int,游标,语法,SQL,total,declare From： https://www.cnblogs.com/Rui6/p/18281451