MySQL StoredProcedure 만드는 방법을 살포시 살펴보자

그냥 회사에서 일하다가 필요해서 만든 저장프로시저 인데

잘 안써먹으면 쉽사리 잊혀지기에 여기에 그냥 붙여 놓고 나중에 보기로 한다.

 

변수선언 / 동적쿼리 정도를 사용한 간단한 저장프로시저

 

delimiter $$

drop procedure if exists BDC_ContactMemoModify $$

create procedure BDC_ContactMemoModify($unitnumber varchar(10), $dwellingnumber varchar(5), $housenumber varchar(5), $status1 int, $status2 int, $status3 int, $etcmemo varchar(100))
begin


declare $currentStatus1 int;
declare $currentStatus2 int;
declare $currentStatus3 int;
declare $finalstatus int;
declare $contactcnt int;

select
ifnull(STATUS_1, 0), ifnull(STATUS_2, 0), ifnull(STATUS_3, 0) into $currentStatus1, $currentStatus2, $currentStatus3
from BROADCAST_HOUSELIST where UNIT_NUMBER = $unitnumber and DWELLING_NUMBER = $dwellingnumber and HOUSE_NUMBER = $housenumber;

set @sql1 = 'update BROADCAST_HOUSELIST set CONTACT_CNT = CONTACT_CNT + 1 ';

if (($status1 > 0) and ($currentStatus1 <> $status1)) then
set @sql1 = CONCAT(@sql1, ', STATUS_1 = ', $status1, ' ');
set @sql1 = CONCAT(@sql1, ', STATUS_1_DATETIME = ''', now(), ''' ');
end if;

if (($status2 > 0) and ($currentStatus2 <> $status2)) then
set @sql1 = CONCAT(@sql1, ', STATUS_2 = ', $status2, ' ');
set @sql1 = CONCAT(@sql1, ', STATUS_2_DATETIME = ''', now(), ''' ');
end if;

if (($status3 > 0) and ($currentStatus3 <> $status3)) then
set @sql1 = CONCAT(@sql1, ', STATUS_3 = ', $status3, ' ');
set @sql1 = CONCAT(@sql1, ', STATUS_3_DATETIME = ''', now(), ''' ');
end if;

if ($status3 > 0) then
set @sql1 = CONCAT(@sql1, ', FINAL_STATUS = ', $status3, ' ');
elseif ($status2 > 0) then
set @sql1 = CONCAT(@sql1, ', FINAL_STATUS = ', $status2, ' ');
elseif ($status1 > 0) then
set @sql1 = CONCAT(@sql1, ', FINAL_STATUS = ', $status1, ' ');
end if;

if ($status3 = 5) then
set @sql1 = CONCAT(@sql1, ', STATUS_3_MEMO = ''', cast($etcmemo as char character set utf8), ''' ');
elseif ($status2 = 5) then
set @sql1 = CONCAT(@sql1, ', STATUS_2_MEMO = ''', cast($etcmemo as char character set utf8), ''' ');
elseif ($status1 = 5) then
set @sql1 = CONCAT(@sql1, ', STATUS_1_MEMO = ''', cast($etcmemo as char character set utf8), ''' ');
end if;





set @sql1 = CONCAT(@sql1, 'where UNIT_NUMBER = ''', $unitnumber, ''' and DWELLING_NUMBER = ''', $dwellingnumber, ''' and HOUSE_NUMBER = ''', $housenumber, ''';');

#select @sql1 as a;

prepare sql1 from @sql1;

execute sql1;
deallocate prepare sql1;



end
$$

delimiter ;