Coding Life

안녕하세요.

코딩연습생입니다~

요즘 블로그  글쓰기에 많이 소홀해 진거 같습니다ㅠ

그래도 전에는 주에 하나는 꼭 쓴거 같은데...이제는 뭐..월에 하나 쓸까 말까 네요ㅠ

컨텐츠 선정의 어려움도 문제고... 회사에서 감당하기 어려울 정도의 업무량을 짧은 기간에 적용하길 원하고..

여기서 치이고 저기서 치이고ㅋㅋㅋ 넉두리 시간이였습니다ㅎ

이번 포스팅 글에서는 예전 MSSQL MERGE기능을 통해 한번에 INSERT와 UPDATE를 하는 구문 쿼리를 

포스팅 했었는데요

https://codingman.tistory.com/98?category=715730

그런데 이 MERGE라는 기능이 MSSQL 2005이상의 버전에서만 지원이 가능합니다

물론 요즘은 2005 이하 버전을 잘 사용하지는 않치만 옛날 시스템을 운영 중이거나 할 경우에는 난감하게 되죠

그래서 MERGE문과 동일한 기능을 할수 있는 구조의 쿼리를 알려 드리겠습니다

UPDATE 테이블명
   SET 컬럼 = 값
 WHERE 조건컬럼= 조건값

업데이트의 기본 형식입니다

프로시져나 쿼리에서 다음과 같이 업데이트문을 작성하고 밑에

IF @@ROWCOUNT = 0
BEGIN
	INSERT INTO 테이블명
    (테이블에 사용할 컬럼명)
    VALUES
    (컬럼에 삽입할려는 값)
END

@@ROWCOUNT 구분을 붙여주고 업데이트의 결과 값이 0이면 INSERT문이 돌아가게 하면

프로시져 운영할떄 한번에 UPDATE와 INSERT를 동작 시킬수 있습니다

[전체 쿼리]

UPDATE 테이블명
   SET 컬럼 = 값
 WHERE 조건컬럼= 조건값
 
 IF @@ROWCOUNT = 0
 BEGIN
 	INSERT INTO 테이블명
    (테이블 컬럼 정의)
    VALUES
    (컬럼별 등록 값)
 END

안녕하세요~

코딩연습생입니다

MSSQL로 쿼리(Query)를 작성하실때 조회조건으로 가장 많이 사용되는것이 바로 날짜죠..

기간별 조회, 기간이후 조회, 등

그래서 이번 포스팅은 MSSQL에서 대표적인 날짜 계산 함수에 대해 간략히 소개해 드릴려고 합니다

1. Dateadd

   - dateadd 함수는 날짜를 더하거나 빼기를 할 수 있는 함수 입니다

   예) 월의 마지막 날 구하기

        → select dateadd(month, 1, getdate())-day(getdate())

        1일 더하기

        → select dateadd(day, 1, getdate())

        1월 더하기

        → select dateadd(month, 1, getdate())

        1년 더하기

        → select dateadd(year, 1, getdate())

2. datediff

    - datediff 함수는 날짜와 날짜의 차이를 구하는 함수 입니다

    예) 1일 차이(분) 구하기

         → select datediff(mi, gatdate(), getdate()+1)

             : 1440분

         1일 차이(초) 구하기

         → select datediff(s, getdate(), getdate()+1)

             : 86400초

         1일 차이(시간) 구하기

         → select datediff(hour, getdate(), getdate()+1)

             : 24시간

         1일 차이(일자) 구하기

         → select datediff(day, getdate(), getdate()+1)

             : 1일

         차이(월) 구하기

         → select datediff(month, getdate(), getdate()+31)

             : 1 개월

         차이(년) 구하기

         → select datediff(year, getdate(), getdate()+730)

             : 2년(730일)

안녕하세요~ 코딩 연습생입니다

코로나 사태 여러분 괜찮으신가요??

언제쯤 잠잠해질지 참...얼른 백신이나 대책이 나왔으면 좋겠는데 마스크 때문에 숨도 잘 안쉬어지네요~

그래도 할건 해야겠죠?ㅎㅎ

그래서 저는 매일 출근합니다;;;ㅋ

오늘 포스팅 주제는 MSSQL 쿼리인데요

MSSQL을 접해보신 분들한데는 참 쉬운 정보일수 있지만 혹시 모르시는 분들을 위해서 포스팅 합니다

프로그램을 작성하다가 DB와 Data를 주고 받을때 이미 있는 데이터를 처리하기 위해서 SELECT를 한번 더 해야 하고

중복인지 아닌지를 체크해서 중복이면 UPDATE를 신규이면 INSERT 뭐 이런식으로 프로그램을 많이 했습니다

그런데 이 MERGE라는 함수를 알게 되고 부턴 한번의 쿼리로 INSERT / UPDATE가 조건에 의해 한번에 처리가 되니

참 편하더라구요

그래서 이 MERGE함수 사용하는 방법을 포스팅 하겠습니다

일단 쿼리 작성을 위해서 MSSQL을 접속합니다

접속을 하신뒤 새쿼리를 눌러 쿼리를 작성합니다

저의 경우 USER_MAST라는 테이블을 생성하였습니다

*USER_MAST 테이블

  - 컬럼정보 : USER_ID, USER_NAME, USER_PSWD

이렇게 3개의 컬럼이 존재합니다

그럼 조회를 한번 해보겠습니다

보시는거와 같이 USER_MAST라는 테이블에는 TEST라는 정보가 이미 존재하고 있습니다

그럼 MERGE 쿼리를 작성해 볼께요

*Query

MERGE USER_MAST
      USING (SELECT 'x' AS DUAL) AS B
         ON [USER_ID] = 'TEST'
       WHEN MATCHED THEN
            UPDATE SET [USER_NAME] = 'TEST111111'
       WHEN NOT MATCHED THEN
            INSERT([USER_ID], [USER_NAME], USER_PSWD) VALUES('TEST', 'TEST', '');

쿼리의 내용을 말로 설명하자면 이렇게 됩니다

USER_MAST라는 테이블에 [USER_ID]가 'TEST'인 테이터가 존재하면

UPDATE문으로 [USER_NAME] 컬럼에 'TEST111111'을 UPDATE하고

존재하지 않으면 INSERT문으로 [USER_ID]가 'TEST'이고 [USER_NAME]가 'TEST'인 DATA를 생성해라

이렇게 됩니다 어렵지 않죠? 

그럼 MERGE 쿼리를 실행해 보겠습니다

※ 아래 이미지는 USER_PSWD가 빠져있는데 쿼리 자체게 문제는 없습니다 참고하세요

실행했으면 USER_MAST 테이블을 다시 조해해보겠습니다

위에서 보았듯이 이미 USER_MAST 테이블에는 TEST라는 데이터가 존재했습니다

그래서 USER_NAME 컬럼에 TEST111111을 UPDATE한 결과를 확인 할 수 있습니다

그럼 다음 조건을 테스트하기 위해서 USER_MAST 테이블의 값을 지워 볼께요

DELETE문을 실행했습니다

이제 USER_MAST 테이블에는 'TEST'라는 데이터는 존재하지 않습니다

다시 MERGE문을 실행해 보겠습니다

그럼 저희는 예측할수가 있죠

USER_MAST 테이블에 USER_ID컬럼이 'TEST'이고 USER_NAME컬럼이 'TEST'인 데이터가 생성되어야 합니다

조회 해보겠습니다

예상했던 결과값이 보여지네요

이렇게 INSERT와 UPDATE를 하나의 쿼리에서 동작할 수 있는 방법을 알아봤습니다

안녕하세요.

코딩하는남자 코딩연습생입니다

간혹 쿼리 작성하시다 보면 서브쿼리나 하위쿼리를 많이 사용하게 되는데

이때 의미 없이 속도가 느려지거나 하는 경우가 발생합니다

이렇때 쿼리 튜닝을 하게 되는데 오라클의 경우에는 힌트 사용이나 조건절을 변경해서

성능 튜닝을 하게 됩니다

하지만 MSSQL의 경우에는 힌트 사용이 안되는거 같더라구요

그래서 이럴때는 넌클러스터 인덱스를 생성해서 해당 쿼리에 지정하여 적용할 수가 있습니다

지정 방법은 간단합니다

[Query]

SELECT TOP 1 *	FROM 테이블이름 K WITH(NOLOCK, INDEX=인덱스명)

이렇게 지정하게 되면 해당 쿼리가 해당 인덱스를 우선시하여 검색을 하게 되어서 쿼리 성능이 빨라지더라구요

혹시 잘 사용되던 쿼리가 갑작이 느려지거나 할 경우 또는 쿼리 튜닝을 해야 할 경우 한번 사용해 보세요

감사합니다

안녕하세요

코딩하는남자 코딩연습생입니다

MSSQL에서 ROW_NUMBER 함수를 이용해서 중복 데이터를 제외한 MAX의 데이만 추출 하는 방법을 설명

해볼려고 합니다

먼저 무정리된 ROW 데이터를 조회 해 볼꼐요

중요 정보는 흑백 처리 했습니다

이 조회된 데이터를 보시면 HOPPER_CART에 중복으로 많은 데이터가 있습니다

여기에 ROW_NUMBER 함수를 써서 HOPPER_CART의 데이터중에 HOPPRE_TIME의 값이 가장 높은것을이

나오도록 쿼리문 짜보겠습니다

SELECT HOPPER_CODE, HOPPER_SEQ,
       HOPPER_CART, HOPPER_LOTNO,
       HOPPER_JAJIL, PCARD_NO, HOPPER_TIME
  FROM (
         SELECT HOPPER_CODE, HOPPER_SEQ,
       			HOPPER_CART, HOPPER_LOTNO,
       			HOPPER_JAJIL, PCARD_NO, HOPPER_TIME
           		ROW_NUMBER() OVER(PARTITION BY HOPPER_CODE ORDER BY HOPPER_SEQ DESC) AS ROWIDX
           FROM MIX_HOPPER_RFID_RESULT_TEMP WITH(NOLOCK)
       ) A
WHERE ROWIDX = 1

자, 이렇게 쿼리문을 작성한뒤 조회를 하면

다음과 같은 결과 값이 나오네요

HOPPER_CART별 HOPPER_TIME가 가장 높은 값으로 조회가 됩니다

물론 GROUPBY를 서브쿼리로 짜서도 같은 결과값을 만들수 있지만 서브에 서브 쿼리를 많이 다는것보다

row_number() 함수를 활용해서 좀 간결한(?) 쿼리로 GROUP BY와 같은 효과를 나타낼수 있습니다

안녕하세요

코딩하는 남자의 코딩연습생입니다

이번 중국 프로젝트를 진행하면서 알게된 내용입니다만 혹시 모르시는 분들이 계실까봐

한번 올려 봅니다ㅎㅎ

일반적으로 varcahr은 가변 문자열이라고 하고 nvarchar은 가변 유니코드 문자열이라고 하네요

그래서 프로그램 개발시에 다국어를 염두하고 있다면 MSSQL 연동시에 필드를 nvarchar를 사용해야 한다고 합니다

보통 영문이나 숫자는 1바이트이고 한글이나 중국 간체 등은 2바트로 구성되어지는데

varchar과 nvarchar의 차이가 바로 이 문자 저장 바이트 크기 차이 라고 합니다

소소하지만 저는 모르고 있었던 내용입니다ㅎ

아마 저와 같이 별거 아니지만 모르고 계셨던 분들은 이 블로그를 읽으시고 적용하시면

도움이 되지 않을까 싶습니다~

[테스트 Query 결과]

varchar(3)에 테스트라는 문자열을 넣었는데 길이가 1바이트 밖에 되지 않아 한자리만 출력되고 있습니다

그에 반면 nvarchar(3)은 3자리가 모두 출력되는 결과가 나오네요

무조껀 SQL 작성할때 저는 nvarchar을 사용하려고 합니다

안녕하세요

코딩하는 남자의 코딩연습생입니다

MSSQL에서 Split 제공 함수가 없습니다

그래서 사용자 Function으로 만들어 두시면 프로시져 내부에서 사용하시가 편합니다

CREATE FUNCTION arr_split(
    @sText VARCHAR(500), -- 대상 문자열
    @str CHAR(1) = '|', -- 구분기호(Default '|')
    @idx INT -- 배열 인덱스

 )

RETURNS VARCHAR(20)
AS
BEGIN
     DECLARE @word CHAR(20), -- 반환할 문자
     @sTextData VARCHAR(600), 
     @num SMALLINT;

      SET @num = 1;
      SET @str = LTRIM(RTRIM(@str));
      SET @sTextData = LTRIM(RTRIM(@sText)) + @str; 

      WHILE @idx >= @num
      BEGIN

             IF CHARINDEX(@str, @sTextData) > 0
             BEGIN
                   -- 문자열의 인덱스 위치의 요소를 반환
                   SET @word = SUBSTRING(@sTextData, 1, CHARINDEX(@str, @sTextData) - 1);
                   SET @word = LTRIM(RTRIM(@word));

                   -- 반환된 문자는 버린후 좌우공백 제거 
                   SET @sTextData = LTRIM(RTRIM(RIGHT(@sTextData, LEN(@sTextData) - (LEN(@word) + 1))))
             END

             ELSE

             BEGIN
                   SET @word = NULL;
             END
             SET @num = @num + 1
      END
   RETURN(@word);
END

--서버등록

exec sp_addlinkedserver

@server='', --링크서버이름

@srvproduct='', --OLEDB 데이터 원본 제품이름

@provider='SQLOLEDB', --공급자고유식별자

@datasrc='', --가져올 DB서버 주소

@provstr='', --OLEDB공급자연결문자열

@catalog='' --가져올 db서버 데이터베이스명

go

--서버등록확인

select * from master.dbo.sysservers

--연결계정등록

exec sp_addlinkedsrvlogin

@rmtsrvname='' --서버이름

@useself='false' --로그인이름사용유무

@locallogin=NULL, --로컬서버로그인여부

@rmtuser='SA', --사용자이름

@rmtpassword='12345' --사용자암호

--원하는 테이블 확인

select * from ssuyastory.KBbank.dbo.employee

--연결계정삭제

exec sp_droplinkedsrvlogin

@rmtsrvname = 'ssuyastory',

@locallogin = NULL

--연결서버삭제

exec sp_dropserver

@server='ssuyastory'