무료하던 나의 일상에 흥미(?)로운 내용을 발견하게 돼 포스팅 하게 된다.

2년전쯤 개발한 사이트의 사용자 테이블 암호화 작업을 추가해야 하는데 고객의 급한(?)요구가 있어 관련된 내용이 무엇이 있을까 살펴 보았더니 딱 이거다 하는 내용이 있어 내용을 분석/파악하여 방금전 운영에 반영을 하고 왔다.

기존 Password컬럼에 비밀번호가 고스란히 표시가 되었고 이부분을 암호화하여 저장하여 향후 유지관리 될 수 있도록 학는게 목표였는데 MS-SQL에 PWDENCRYPT, PWDCOMPARE 함수를 이용하였다.

사실 좀 아쉬운 점은 암호화는 되지만 복호화 부분이 참일경우 '1', 거짓일 경우 '0' 경우라 제한적인 부분이란 것이다.

로그인이 '1' 일 때는 성공, '0' 일 때는 실패로 처리하여 별 무리 없이 진행은 되었다.(그래도 아쉬워 ㅠ)

중요한 점은 Password가 저장될 컬럼이 VARCHAR형태가 아닌 VARBINARY 형태로 저장을 하여야 이용을 할 수 있는 점이 특징이다.

개발하랴 유지보수 업무 하랴 바쁘다 바빠 하앍하앍~~

CREATE TABLE MEMBER (
 USERID VARCHAR(10),
 USERPASSWORD VARBINARY(100)
);

INSERT INTO MEMBER(USERID, USERPASSWORD) VALUES ('aa', PWDENCRYPT('1111'));

SELECT USERID, USERPASSWORD, PWDCOMPARE('1111', USERPASSWORD) FROM MEMBER;

=============

aa | 1111 | 1

참고 사이트 : http://ladon.tistory.com/18

CREATE TRIGGER [dbo].[TRG_AAA] on [dbo].[AAA]

AFTER INSERT

AS
BEGIN
   SET NOCOUNT ON
  
   DECLARE @V_A VARCHAR(10)

   SELECT @V_A = A FROM inserted
  
   IF (@V_A = 'A')
   BEGIN
      INSERT INTO BBB
      SELECT A, B FROM inserted
   END
  
  
   --IF @@RowCount > 0
   --BEGIN
   --   INSERT INTO BBB
   --   SELECT A, B FROM inserted
   --END
  
END

---------------------------------------------

INSERT INTO AAA (A, B) VALUES ('A', 0)

SELECT * FROM AAA
SELECT * FROM BBB

MSSQL 트리거도 오라클과 똑같을 줄 알았고 별 생각없이 인터넷상에 도는 트리거 예제를 긁어다가 적용했는데

왠걸 오늘 큰건 하나 저질렀다.

내용인 즉슨 바로 'AFTER INSERT' 이 옵션이었는데 오라클에서는 주로 내가 쓰던건 INSERTED(?) 였다. INSERT가 되고난 이후 처리되는 뭐 이 정도로 이해를 했다.

오늘 저지른 실수의 옵션은 바로 'INSTEAD OF INSERT' 이미 늦었지만 눈에 들어오기 시작한것은 INSTEAD OF... INSTEAD OF... INSTEAD OF...

대신에... 대신에... 대신에... INSERT 대신에... OTL

INSERT되지 않고 설정한 내용으로 처리가 되다보니 정작 본래 테이블에는 데이터를 하나도 안들어가는 불상사가 발생... 그것도 운영 DB로...

뭐 좋은 경험으로 삼고 다음에 조심해 나가면 되겠지...

SP_RENAME '기존 테이블명', '바뀔 테이블명'

SP_RENAME '테이블명.기존컬럼명', '바뀔 컬럼명'

Management Studio에서 테이블 구조를 변경할때 기본적으로 저장이 반영되지 않음

이럴땐 다음과 같은 옵션 설정을 해 주어야함

이상 끝

FETCH처리를 하니 역시 속도는 떨어지는거 같다.

DROP PROC UP_TEST_001

GO

CREATE PROC UP_TEST_001

(

   @TEST VARCHAR(99)

)

AS

DECLARE @I_COUNT     INT = 0

BEGIN

    DECLARE @KEY_MAT_ID VARCHAR(90)

          , @MAT_ID     VARCHAR(90)

          , @QTY        INT

    DECLARE BOM_CURSOR CURSOR FOR

SELECT KEY_MAT_ID, MAT_ID, QTY 

 FROM MES_BOM_DEF 

WHERE KEY_MAT_ID = 'XXX'

    OPEN BOM_CURSOR

FETCH NEXT FROM BOM_CURSOR

INTO @KEY_MAT_ID, @MAT_ID, @QTY

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @KEY_MAT_ID, @MAT_ID, @QTY

FETCH NEXT FROM BOM_CURSOR

INTO @KEY_MAT_ID, @MAT_ID, @QTY

END

    CLOSE BOM_CURSOR

    DEALLOCATE BOM_CURSOR

END

EXEC   sp_addextendedproperty 'MS_Description', '테이블설명', 'user', dbo, 'table',테이블명
//컬럼들
EXEC   sp_addextendedproperty 'MS_Description', '컬럼설명', 'user', dbo, 'table', 테이블명, 'column', 컬럼명

2. 주석수정 (update)

//테이블

EXEC   sp_updateextendedproperty 'MS_Description', '테이블설명', 'user', dbo, 'table',테이블명

//컬럼들

EXEC   sp_updateextendedproperty 'MS_Description', '컬럼설명', 'user', dbo, 'table', 테이블명, 'column', 컬럼명

-- 아래는 응용 : 프로시저를 만들어 아래와 같이 이용하면 도움이 될 듯!

-- EXEC UP_SET_TABLE_COMMENT 'I', 'MES_EQUIP_DEF', 'LINE_CD',   '라인코드'

-- EXEC UP_SET_TABLE_COMMENT 'I', 'MES_EQUIP_DEF', 'OPER_CD', '공정코드'

...

/******************************************************************************************************* 

    1. Procedure    : C_SP_SET_BPA_PLC_READY

    2. Process Func : 테이블 설명 추가(테이블, 컬럼)

    3. Create Date  : 2013-11-28 오전 11:24:32

    4. Create User  : YHKIM

    5. Execute Test : EXEC UP_SET_TABLE_COMMENT 'I', 'MES_EQUIP_DEF', 'FACTORY', '공장코드'

    6. History Info :

*******************************************************************************************************/ 

ALTER PROC  [dbo].[UP_SET_TABLE_COMMENT]

(

   @I_TYPE        VARCHAR(01),   -- I:추가, U:수정

   @S_TABLE       VARCHAR(50),

   @S_COLUMN      VARCHAR(50),

   @S_COMMENT     VARCHAR(1000)

)

AS

BEGIN

   IF @I_TYPE = 'I'

   BEGIN

      IF @S_COLUMN = ''

         -- TABLE 설명

         EXEC   sp_addextendedproperty 'MS_Description', @S_COMMENT, 'user', dbo, 'table', @S_TABLE

      ELSE

         -- 컬럼 설명

         EXEC   sp_addextendedproperty 'MS_Description', @S_COMMENT, 'user', dbo, 'table', @S_TABLE, 'column', @S_COLUMN

   END

   IF @I_TYPE = 'U'

   BEGIN

      IF @S_COLUMN = ''

         -- TABLE 설명

         EXEC   sp_updateextendedproperty 'MS_Description', @S_COMMENT, 'user', dbo, 'table', @S_TABLE

      ELSE

         -- 컬럼 설명

         EXEC   sp_updateextendedproperty 'MS_Description', @S_COMMENT, 'user', dbo, 'table', @S_TABLE, 'column', @S_COLUMN

   END

END

프로시저 파라미터 값을 던질때 'AAA^BBB^CCC^DDD^' 이렇게 던지면 프로시저 내부에서 LOOP처리 하며 일일이

계산하여 처리했는데 그 해결책을 찾은 듯 하다!

--------------------------------------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[SPLIT_STR](@S_VAL NVARCHAR(4000), @S_SPLIT_CHAR NVARCHAR(1))

RETURNS @T_SPLIT TABLE(NUM INT, VAL NVARCHAR(4000))

AS

-- SELECT * FROM DBO.SPLIT_STR('AB|BC|CD', '|')

BEGIN

   DECLARE @oPos INT, @nPos INT, @i INT

   DECLARE @tempVar NVARCHAR(4000)

   SET @oPos = 1

   SET @nPos = 1

   SET @i    = 0

   WHILE(@nPos > 0)

   BEGIN

      SET @nPos = CHARINDEX(@S_SPLIT_CHAR, @S_VAL, @oPos)

      IF @nPos = 0

         SET @tempVar = RIGHT(@S_VAL, LEN(@S_VAL) - @oPos + 1)

      ELSE

         SET @tempVar = SUBSTRING(@S_VAL, @oPos, @nPos - @oPos)

      IF LEN(@tempVar) > 0

         INSERT INTO @T_SPLIT VALUES (@i, @tempVar)

      SET @oPos = @nPos + 1

      SET @i = @i + 1

   END

   RETURN

END

[참조] http://cong4u.tistory.com/157

AAA 테이블에 있는 데이터를 BBB의 테이블에 주기적으로 UPDATE를 하는 경우가 종종 있다.

아래와 같이 사용하자!

   UPDATE A

      SET A.SEQ     = B.CMT_NO

        , A.CarType = B.CAR_TYPE

     FROM TABLE_AAA A

          JOIN TABLE_BBB B ON A.OPCode = B.OP_CODE

    WHERE A.LineCode = '조건'

지난번 BAK 파일로 복원하기에 대해 잠깐 설명했는데

오늘 2008 R2 ENT. 버전으로 재설치를 하게돼 기존의 DB가 사라진 상황에서

BAK에 의해 풀려진(?) MDF, LDF파일을 이용하여 복원하는 기술에 대해 잠깐 기록하고자 한다.

물론 네이년으로 도움으로 얻은 자료이긴 하다.

발췌한 블로그 주인장께 직접 얘기는 못했지만 그 분이 이 내용을 혹시나 보게 된다면 진심으로 감사의 뜻을 표하며

주소까지 기록해 놓을까 한다. ^^

우선 내용부터 기록해 놓으면

EXEC sp_attach_db @dbname = N'Msg'   -- 복원할 DB의 이름
   , @filename1 = N'E:\data\home\database\MSSQL2005\MSSQL.1\MSSQL\Data\Msg_log.ldf'   -- LDF 파일 위치
   , @filename2 = N'E:\data\home\database\MSSQL2005\MSSQL.1\MSSQL\Data\Msg.mdf'   --  MDF 파일 위치

위의 SCRIPT만을 이용해서 복원이 가능하다는거... 참 쉬운거 같다.

[참고] http://jjong.co.kr/60