不需要使用游标循环来读取,直接select * into @变量就OK了,还可以传递参数来执行。 例如:
DROP
PROCEDURE
IF
EXISTS
stat_cube.sp_get_end_date;
CREATE PROCEDURE stat_cube.`sp_get_end_date`(
p_sp_name varchar ( 50 ),
out p_ret_date date
)
BEGIN
declare src_1,src_2, src_3, src_4, src_5 varchar ( 50 );
select src_table_1,src_table_2,src_table_3,src_table_4,src_table_5 into src_1,src_2, src_3, src_4, src_5
from stat_cube.storeprocedure_info
where sp_name = p_sp_name;
set @stmt = concat( ' select least( ' , if (src_1 is null , '' ,concat( ' (select max(dt) from ' ,src_1, ' ) ' ))
, if (src_2 is null , '' ,concat( ' ,(select max(dt) from ' ,src_2, ' ) ' ))
, if (src_3 is null , '' ,concat( ' ,(select max(dt) from ' ,src_3, ' ) ' ))
, if (src_4 is null , '' ,concat( ' ,(select max(dt) from ' ,src_4, ' ) ' ))
, if (src_5 is null , '' ,concat( ' ,(select max(dt) from ' ,src_5, ' ) ' ))
, ' ) into @ret_date; ' );
PREPARE stmt1 FROM @stmt ;
EXECUTE stmt1;
deallocate prepare stmt1;
set p_ret_date = @ret_date ;
END ;
CREATE PROCEDURE stat_cube.`sp_get_end_date`(
p_sp_name varchar ( 50 ),
out p_ret_date date
)
BEGIN
declare src_1,src_2, src_3, src_4, src_5 varchar ( 50 );
select src_table_1,src_table_2,src_table_3,src_table_4,src_table_5 into src_1,src_2, src_3, src_4, src_5
from stat_cube.storeprocedure_info
where sp_name = p_sp_name;
set @stmt = concat( ' select least( ' , if (src_1 is null , '' ,concat( ' (select max(dt) from ' ,src_1, ' ) ' ))
, if (src_2 is null , '' ,concat( ' ,(select max(dt) from ' ,src_2, ' ) ' ))
, if (src_3 is null , '' ,concat( ' ,(select max(dt) from ' ,src_3, ' ) ' ))
, if (src_4 is null , '' ,concat( ' ,(select max(dt) from ' ,src_4, ' ) ' ))
, if (src_5 is null , '' ,concat( ' ,(select max(dt) from ' ,src_5, ' ) ' ))
, ' ) into @ret_date; ' );
PREPARE stmt1 FROM @stmt ;
EXECUTE stmt1;
deallocate prepare stmt1;
set p_ret_date = @ret_date ;
END ;
上面的例子有个问题,如果在其他SP中调用,如下,结果不会有问题:
declare
p_end_date date;
call stat_cube.sp_get_end_date( @sp_name ,p_end_date);
select p_end_date;
call stat_cube.sp_get_end_date( @sp_name ,p_end_date);
select p_end_date;
但是如果用下面的方式调用,则返回NULL:
call stat_cube.sp_get_end_date(
@sp_name
,
@p_end_date
);
select @p_end_date ;
select @p_end_date ;
总结的结论是:SP中out参数类型为date时,实参传入前需要确定为date类型,否则会出错。