IN ( ) 절에 Multiple 인자값 넣어 조회 (ex. where id in (12, 34, 56) )

create table usp_multi_tab

as

select @rnum := @rnum + 1 as id, b.*

from (select @rnum := 0) a, information_schema.GLOBAL_STATUS b limit 10;

select replace(replace('1,3,5', ',', ''), ' ', '') regexp "[^0-9]+";

delimiter //

drop procedure if exists usp_multi //

create procedure usp_multi (

in pi_keylist text

)

begin

declare v_SQL text default '';

declare v_sqlstate varchar(5) default '00000';

declare v_err_no int;

declare exit handler for sqlexception, sqlwarning

begin

get diagnostics condition 1 v_sqlstate = RETURNED_SQLSTATE, v_err_no = MYSQL_ERRNO;

ROLLBACK;

SELECT v_sqlstate, v_err_no;

end;

if (replace(replace(pi_keylist, ',',''),' ', '') regexp "[^0-9]+") = 0 then

set v_SQL = CONCAT(v_SQL, 'SELECT *');

set v_SQL = CONCAT(v_SQL, '    FROM usp_multi_tab');

set v_SQL = CONCAT(v_SQL, '    WHERE id IN (', pi_keylist, ');');

-- select v_SQL;

set @vSQL := v_SQL;

PREPARE stmt FROM @vSQL;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

end if;

end //

delimiter ;

select * from usp_multi_tab;

call usp_multi('A');

call usp_multi('1,3,5');