Mysql 存储过程、函数写法
存储过程
注意： varchar类型参数要带字段大小 如：CREATE PROCEDURE XX( IN varchar(30))
语法：
创建
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);
CREATE DEFINER=`root`@`%` FUNCTION `queryChildrenDepInfo`(dep_id INT) RETURNS varchar(4000) CHARSET utf8mb4 BEGIN DECLARE sTemp VARCHAR(4000); DECLARE sTempChd VARCHAR(4000); SET sTemp='$'; SET sTempChd = CAST(dep_id AS CHAR); WHILE sTempChd IS NOT NULL DO SET sTemp= CONCAT(sTemp,',',sTempChd); SELECT GROUP_CONCAT(id) INTO sTempChd FROM department_info WHERE FIND_IN_SET(parent_id,sTempChd)>0; END WHILE; RETURN sTemp; END
注意：存储函数必须要有返回值
Error Code: 1414. OUT or INOUT argument 2 for routine company.new_procedure is not a variable or NEW
1、错误叙述性说明
16:27:36 call new_procedure(20150112,1) Error Code: 1414. OUT or INOUT argument 2 for routine company.new_procedure is not a variable or NEW pseudo-variable in BEFORE trigger0.063 sec
2.错误原因
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(in `departId` int,out `num` int)
BEGIN
select count(t.depart_name) into num from t_department_info t where t.depart_id = departId;
END
call new_procedure(20150112,1);
3、解决的方法
call new_procedure(20150112,@depart_id);
out參数是数据库表中的字段
select @depart_id as depart_id_out;
CREATE DEFINER=`root`@`%` PROCEDURE `hazardStore`(IN `id` VARCHAR(40) , OUT `name` VARCHAR(40)) BEGIN SELECT store_area_name into name from hazard_store_tank_dtl d INNER JOIN hazard_store_tank_area_dtl h on h.id =d.sscgq where d.id = id ; END
https://blog.csdn.net/m0_62951223/article/details/128092288
https://blog.csdn.net/weixin_30876945/article/details/98118876
个人学习笔记，记录日常学习，便于查阅及加深，仅为方便个人使用。
浙公网安备 33010602011771号