1、如果让存储过程跟外边进行数据交互,需要用到参数
第一种,在过程内部使用用户变量 @xxx
delimiter //
create procedure parameter_test()
begin
declare number int default 100;
set @sum = number * 101;
end//
delimiter ;
call parameter_test();
select @sum;
另外,可以将表中查询到的数据赋予参数:
drop procedure if exists parameter_test1;
delimiter //
create procedure parameter_test1()
begin
select count(*) into @count from string_rand;
select str_name into @name_str from string_rand limit 1;
end //
delimiter ;
--调用
call parameter_test1();
select @count,@name_str;
使用过程内部定义用户变量的方式,容易在调用的时候产生冲突、混淆,所以最好使用外部定义,结合 in/out/inout 类型参数:
1、IN类型
使用IN类型来传递信息,存储过程内部可以对参数的值进行修改,但是修改后的值调用者不可见。
delimiter //
drop procedure if exists parameter_test2;
create procedure parameter_test2(in str_name varchar(20))
begin
insert into string_rand(str_name) values(str_name);
set str_name = 'lisi';
end//
delimiter ;
set @name = 'zhangsan';
call parameter_test2(@name);
delimiter //
drop procedure if exists parameter_test2;
create procedure parameter_test2(in str_name varchar(20))
begin
insert into string_rand(str_name) values(str_name);
set str_name = 'lisi';
select str_name;
end//
delimiter ;
set @name = 'zhangsan';
call parameter_test2(@name);
select * from string_rand where str_name = 'zhangsan';
select @name;
可以看出虽然设置了变量name的值为zhangsan,但是在存储过程内部修改了name的值为lisi,name的值并未返回给调用者。
2、OUT类型
使用OUT类型来传递信息,在存储过程内部,该值的默认值为NULL,无论调用者是否传值给存储过程。
delimiter //
drop procedure if exists parameter_test3;
create procedure parameter_test3(out number int)
begin
select number;
select count(*) into number from string_rand;
end//
delimiter ;
delimiter //
drop procedure if exists parameter_test3;
create procedure parameter_test3(out number int)
begin
select number;
select count(*) into number from string_rand;
end//
delimiter ;
set @number = 1007;
call parameter_test3(@number);
select @number;
可以看出虽然设置了变量number的值为1007,但是在存储过程内部number的值为null,最后number的值在存储过程内修改后返回调用者。
3、INOUT类型
使用INOUT类型来传递信息,存储过程内部可以对参数的值进行修改,并将最终值返回给调用者。
delimiter //
drop procedure if exists parameter_test4;
create procedure parameter_test4(inout number int)
begin
select number;
select count(*) into number from string_rand;
end//
delimiter ;
set @number = 1007;
call parameter_test4(@number);
delimiter //
drop procedure if exists parameter_test4;
create procedure parameter_test4(inout number int)
begin
select number;
select count(*) into number from string_rand;
end//
delimiter ;
set @number = 1007;
call parameter_test4(@number);
select @number;
可以看出设置了变量number的值为1007,在存储内部将number的值修改为1007,最后number的值返回给调用者。
如果需要处理表中的多行数据,就需要用到游标(Cursor):
delimiter //
drop procedure if exists parameter_test5;
create procedure parameter_test5(out sum bigint)
begin
declare iii int;
declare done int default 0;
declare c_person cursor for select id from string_rand; # 1.游标的定义
declare continue handler for not found set done = 1; # 捕获系统抛出的 not found 错误,如果捕获到,将 done 设置为 1
set sum = 0;
open c_person; # 2.打开游标
xxx:loop
fetch c_person into iii; # 3.使用游标
if done = 1 then # 4.设定退出条件
leave xxx;
end if;
set sum = sum + iii;
end loop;
close c_person; # 5.关闭游标
end//
delimiter ;
-- 调用
set @sum = 0;
call parameter_test5(@sum);
select @sum;