参数与游标

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;