[ MySQL 쿼리 기초 ] 다이나믹 쿼리

다이나믹 쿼리 예시를 나중에 찾아보기 쉽게 남겨둔다.

해당 쿼리는 그냥 실행시키는 건 아니고 SP 안에서 활용해야 한다.

 

declare v_fdate varchar(10);
declare v_tdate varchar(10);
declare v_finished integer default 0;
declare v_parts varchar(100);
declare v_pname varchar(100);
declare v_psort integer default 0;

declare cur_Items cursor for
select 'parts_1', 'name_1', 1
union all
select 'parts_2', 'name_2', 2
union all
select 'parts_3', 'name_3', 3
;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
set v_fdate = p_fdate;
set v_tdate = DATE_ADD(p_fdate, INTERVAL 1 day);

set @stmt = concat('insert into table_to (cdate, uid, parts, parts_sort, id) ');

OPEN cur_Items;
get_Items: LOOP
    FETCH cur_Items INTO v_parts, v_pname, v_psort;
    IF v_finished = 1 THEN LEAVE get_Items; END IF;

    set @stmt = concat(@stmt, ' select cdate, uid ');
    set @stmt = concat(@stmt, ' , "',v_pname,'" parts ');
    set @stmt = concat(@stmt, ' , ',v_psort,' parts_sort ');
    set @stmt = concat(@stmt, ' , ',v_parts,'_ID id ');
    set @stmt = concat(@stmt, ' from table_from ');
    set @stmt = concat(@stmt, ' where ',v_parts,'_ID <> "0" ');
    if v_parts <> 'parts_3' then
        set @stmt = concat(@stmt, ' union all ');
    end if;

END LOOP get_Items;
CLOSE cur_Items;

set @stmt = concat(@stmt, ' ; ');

PREPARE dquery FROM @stmt; 
EXECUTE dquery;
DEALLOCATE PREPARE dquery;