mssql에 있는 프로시저를 mysql로 변환하려고 합니다.
아무리 자료를 찾아보고 mysql 기본 프로시저 문법을 보고 바꾸어 볼려고 해도 자꾸 에러가 나서 저장조차 못하고 있습니다. ㅠ.ㅠ
mysql은 5.0을 사용하고 있구요 툴은 나비켓을 이용하고 있습니다
많은 선배님들 조언좀 부탁드립니다
아래 소스는 mssql로 되어있는 원본 소스 입니다.
-------------------------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE SP_SET_BLACK_LIST_MEMBER
@USER_ID VARCHAR(20),
@ADMIN_ID VARCHAR(20),
@HOLD_DAY INT,
@MEMO VARCHAR(100)
AS
BEGIN
DECLARE @NOW_TIME VARCHAR(20)
DECLARE @SEAT_ID VARCHAR(20)
DECLARE @COUNT INT
DECLARE @NEW_BLK_EXPIRE_TIME VARCHAR(20)
SELECT @NOW_TIME = CONVERT(VARCHAR(8),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','')
SELECT @SEAT_ID = SEAT_ID,@COUNT = COUNT(SEAT_ID)
FROM HD_SEAT_TRUE_TIME_LOG
WHERE USER_ID = @USER_ID
GROUP BY SEAT_ID
IF @COUNT > 0
BEGIN
UPDATE HD_SEAT_LOG SET CONFIRM_OUT_TIME = @NOW_TIME,OUT_TYPE = 'ADMIN', MEMO = @MEMO
WHERE SEAT_ID = @SEAT_ID AND OUT_TYPE = 'NOT'
DELETE HD_SEAT_TRUE_TIME_LOG WHERE SEAT_ID = @SEAT_ID
END
IF @HOLD_DAY > 0
BEGIN
SELECT @NEW_BLK_EXPIRE_TIME = CONVERT(VARCHAR(8),DATEADD(DAY,@HOLD_DAY,GETDATE()),112) + REPLACE(CONVERT(VARCHAR(8),DATEADD(DAY,@HOLD_DAY,GETDATE()),108),':','')
END
ELSE
BEGIN
SELECT @NEW_BLK_EXPIRE_TIME = CONVERT(VARCHAR(8),DATEADD(DAY,BLK_HOLD_DAY,GETDATE()),112) + REPLACE(CONVERT(VARCHAR(8),DATEADD(DAY,BLK_HOLD_DAY,GETDATE()),108),':','')
FROM HD_DFT_SETUP
END
INSERT HD_BLACK_LIST (USER_ID,BLK_FLAG,REG_ADMIN,REG_TIME,EXPIRE_TIME,MEMO)
VALUES (@USER_ID,'REG',@ADMIN_ID,@NOW_TIME,@NEW_BLK_EXPIRE_TIME,@MEMO)
UPDATE HD_MEMBER SET COMPULSION_END_CNT = 0 ,BLK_START_TIME = @NOW_TIME , BLK_END_TIME = @NEW_BLK_EXPIRE_TIME WHERE USER_ID = @USER_ID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
<아래는 제가 나름 고쳐본 소스입니다>
DELIMITER $$
DROP PROCEDURE IF EXISTS SP_SET_BLACK_LIST_MEMBER$$
ALTER PROCEDURE SP_SET_BLACK_LIST_MEMBER(
@USER_ID VARCHAR(20),
@ADMIN_ID VARCHAR(20),
@HOLD_DAY INT,
@MEMO VARCHAR(100))
BEGIN
DECLARE $NOW_TIME VARCHAR(20);
DECLARE $SEAT_ID VARCHAR(20);
DECLARE $COUNT INT;
DECLARE $NEW_BLK_EXPIRE_TIME VARCHAR(20);
SELECT $NOW_TIME = CONVERT(VARCHAR(8),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','');
SELECT $SEAT_ID = SEAT_ID,$COUNT = COUNT(SEAT_ID)FROM HD_SEAT_TRUE_TIME_LOG WHERE USER_ID = @USER_ID GROUP BY SEAT_ID;
IF $COUNT > 0 THEN
UPDATE HD_SEAT_LOG SET CONFIRM_OUT_TIME = $NOW_TIME,OUT_TYPE = 'ADMIN', MEMO = @MEMO WHERE SEAT_ID = $SEAT_ID AND OUT_TYPE = 'NOT';
DELETE HD_SEAT_TRUE_TIME_LOG WHERE SEAT_ID = $SEAT_ID;
END IF;
IF @HOLD_DAY > 0 THEN
SELECT $NEW_BLK_EXPIRE_TIME = CONVERT(VARCHAR(8),DATEADD(DAY,@HOLD_DAY,GETDATE()),112) + REPLACE(CONVERT(VARCHAR(8),DATEADD(DAY,@HOLD_DAY,GETDATE()),108),':','');
ELSE
SELECT $NEW_BLK_EXPIRE_TIME = CONVERT(VARCHAR(8),DATEADD(DAY,BLK_HOLD_DAY,GETDATE()),112) + REPLACE(CONVERT(VARCHAR(8),DATEADD(DAY,BLK_HOLD_DAY,GETDATE()),108),':','') FROM HD_DFT_SETUP;
END IF;
INSERT HD_BLACK_LIST (USER_ID,BLK_FLAG,REG_ADMIN,REG_TIME,EXPIRE_TIME,MEMO)VALUES (@USER_ID,'REG',@ADMIN_ID,$NOW_TIME,$NEW_BLK_EXPIRE_TIME,@MEMO);
UPDATE HD_MEMBER SET COMPULSION_END_CNT = 0 ,BLK_START_TIME = $NOW_TIME , BLK_END_TIME = $NEW_BLK_EXPIRE_TIME WHERE USER_ID = @USER_ID;
END
$$
DELIMITER ;
==========================================================
기본적인것만 바꾸어 봤는데 아예 전부 고쳐야하는건지 감을 못잡겠습니다 ㅠ.ㅠ |