[2] Database/MySQL
MySQL StoredProcedure 만드는 방법을 살포시 살펴보자
코볼
2014. 6. 12. 14:50
728x90
반응형
SMALL
그냥 회사에서 일하다가 필요해서 만든 저장프로시저 인데
잘 안써먹으면 쉽사리 잊혀지기에 여기에 그냥 붙여 놓고 나중에 보기로 한다.
변수선언 / 동적쿼리 정도를 사용한 간단한 저장프로시저
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 ;
728x90
반응형
LIST