[MSSQL] 날짜,시간 데이터 생성하기



날짜나 시간단위의 데이터가 로우로 들어 오는 경우


간혹 데이터 자체가 누락 되는 경우가 있다


시간 순서대로 보여줘야 하는데 데이터 누락으로 로우가 없어 데이터가 땡겨져버리는 경우가 있기 때문에


그런종류의 데이터는 별도로 시간이나 날짜 로우를 만들어 낸 후 거기에 끼워 맞추는 식으로 가져 온다.



oracle을 사용할때는 별 문제 없이 만들었는데


mssql에서는 날짜나 시간을 만들어 내는 쿼리를 몰라 한참을 해매다 알아낸 방법이다


1. 날짜+시간 단위 생성 방법


1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @start_date DATETIME = '2018-07-12 00:00:00.000'-- 시작시간
DECLARE @end_date DATETIME = '2018-07-13 00:00:00.000'-- 
 
WITH    AllDays
          AS ( SELECT   @start_date AS [Date], 1 AS [level]
               UNION ALL
               SELECT   DATEADD(hour, 1, [Date]), [level] + 1
               FROM     AllDays
               WHERE    [Date] < @end_date )
 
 
     SELECT [Date], [level]
     FROM   AllDays OPTION (MAXRECURSION 0)
cs



1-1 결과값






위와 같이 쿼리를 짜주게 되면 해당 범위만큼의 날짜,시간을 생성해 준다



2. 날짜단위 생성방법


1
2
3
4
5
6
7
8
DECLARE @Date1 DATE, @Date2 DATE
SET @Date1 = '20150430'
SET @Date2 = '20150601'
 
SELECT DATEADD(DAY,number+1,@Date1) [Date]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number+1,@Date1) < @Date2
cs


2-1 결과값






이런 결과를 얻어낼 수 있다.



와...


그런데 이거 포스팅하다가 실제로 프로젝트에 써야 하는 쿼리 실수로 날려먹음...



다시 짜야 되는데 스트레스 엄청받는다.


oracle MyBatis에서 다중 Insert 하는 방법



mssql에서 oracle로 마이그레이션을 하는 도중 하나의 맵핑된 쿼리 안에서


여러개의 행을 insert하는 것에서 문제가 발생했다


mssql을 사용할떈 그냥 insert문을 여러개 쓰는것 만으로 한번에 여러 행을 입력 할 수 있었는데


오라클에선 구문오류가 발생하게 된다.


그렇게 방법을 찾다가 발견한 방법은


INSERT ALL 이다



사용법은 아주아주 굉장히 간단하다.


일단 MyBatis에서 여러 데이터를 맵형태로 보내 다이나믹 쿼리로 만드는 방법은


아래 링크를 참고하고

2015/07/09 - [Yame Programmer/전자정부프레임워크] - [ibatis] 동적쿼리 생성 및 outOfBoundsException 에러


ibatis와 mybatis의 사용방법이 상이하긴 하지만 맥락은 같으니 참고하면 좋을 것이다.




쿼리문은 아래와 같이 사용 하면 된다.


1
2
3
4
5
6
7
8
9
10
    <insert id="insertMtrlOrderList" parameterType="java.util.Map">
        <foreach collection="list" item="item" index="index"  open="INSERT ALL " separator=" " close="SELECT * FROM DUAL" 
         into FM_MATERIALS_ORDER_LIST(MTRL_ID, MTRL_ORDER_ID, 
            MTRL_ENTERPRISE, UNIT_COST, MTRL_ORD_QUANTITY, APPLY_DATE)
        values
            (#{item.MTRL_ID}, #{item.MTRL_ORDER_ID}, 
        #{item.MTRL_ENTERPRISE}, #{item.UNIT_COST}, 
        #{item.MTRL_ORD_QUANTITY}, SYSDATE)
        </foreach>
    </insert>
cs



시작할떄 INSERT ALL 을 써주고 본 쿼리으 insert문은 insert를 제외한 into부터 시작해서 작성하면 끝


역시 사람은 아는게 많아야 손발이 고생을 안하는 것 같다.

보통 전자정부같은 Spring기반의 프로젝트를 할때 에러가 나던가 혹은 서버에서 어떤 액션이 일어나면 이클립스 콘솔창에


많은 정보들이 후두두둑 하고 올라간다.


내가 날린 쿼리가 무엇인지, 내가 요청한 URL은 무엇있지 버그나 오류가 있을땐 어떤 오류인지 온갖 정보들이 나타나는데


가끔 쿼리를 실행해도 어떤 쿼리를 실행했는지 나타나지 않는 경우가 있다. 단지 오류가 났을때만 콘솔창에 로그를 뿌려주는 경우


이럴때 버그가 있더라도 시스템에서 오류가 나지 않는다면  어떤 쿼리를 실행시켰는지 어떤 url을 요청했는지 알기 힘들게 된다.



이런경우의 대부분은 로깅 프로퍼티의 설정 레벨이 warn 정도로 되어 있는 경우 인것 같다.


정상적인 쿼리도 콘솔창에 보이게 할수 있도록 설정하는 방법을 알아보자.



log4j를 쓰던 log4j2를 쓰던 logback를 쓰던 설정 xml파일의 이름엔 [log]가 들어간다


프로젝트의 구성,설계에 따라 해당 xml파일은 리소스에 들어가는 경우도 있고 web-inf에 들어가는 경우도 있다


파일을 열어 보면


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration 
    xmlns:log4j="http://jakarta.apache.org/log4j/" 
    debug="false">
    
    <appender name="console" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%d %5p [%c] %m%n" />
        </layout>   
    </appender>
    
    <logger name="egovframework" additivity="false">
        <level value="DEBUG"/>
        <appender-ref ref="console"/>
    </logger>
 
    <!-- log SQL with timing information, post execution -->
    <logger name="jdbc.sqltiming" additivity="false">
        <level value="WARN" />
        <appender-ref ref="console" />
    </logger>
 
    <logger name="jdbc.audit" additivity="false">
        <level value="WARN" />
        <appender-ref ref="console" />
    </logger>
    
    <logger name="jdbc.resultset" additivity="false">
        <level value="WARN" />
        <appender-ref ref="console" />
    </logger>
    
    <logger name="org.springframework" additivity="false">
        <level value="INFO" />
        <appender-ref ref="console" />
    </logger>
    
    <logger name="java.sql" additivity="false">
        <level value="WARN"/
        <appender-ref ref="console"/
    </logger
    
    <root>
        <level value="INFO" />
        <appender-ref ref="console" />
    </root>
                       
</log4j:configuration>
 
cs


이런식으로 되어 있을 것이다. 이부분은 log4j나 logback나 크게 다른 부분은 없다.


단지 로거 안에 레벨이 들어가냐 안들어가냐 어펜드가 포함되냐 안되냐 그리고 최상단에의 정보들같은  이런 약간의 차이만 있을 뿐이다.



보면 <level value="warn"/> 이라고 되어있는데


여기서 밸류값에 들어가는 종류는


DEBUG, INFO, WARN, ERROR, FATAL, ALL, OFF 가 있다.



ALL은 모든 로깅

OFF는 로깅 해제


DEBUG = 디버깅

INFO = 강조정보

WARN = 경고

ERROR = 오류

FATAL = 심각한 오류


라는 의미 이며


WARN은 경고수준 이하 레벨의 정보는 로깅하지 않는 다는 뜻이다

그러니 정상적으로 돌아가는 SQL쿼리문은 콘솔창에 나타나지 않으니


레벨의 밸류를 전부 DEBUG 로 바꿔주자


로거 name="" << 이부분의 네임은 어떤 부분에서 로그를 띄워줄지 적는 부분이다.


뭔지 모르겠고 sql만 띄우고 싶다면 jdbc.sqltiming  이부분의 레벨만 DEBUG 로 바꿔보도록 하자


설정에 따라  


1
2
3
4
5
6
7
<logger name="org.springframework" level="DEBUG " additivity="false">
     <appender-ref ref="console" />
</logger>
  
<logger name="org.mybatis" level="DEBUG "  additivity="false">
    <appender-ref ref="console" />
</logger>
cs


이런식의 스프링프레임워크나 마이바티스에 관한 로그를 띄울 수도 있다.



아무튼 원하는 부분의 레벨을 DEBUG로 설정한 후 구동시키면 정상적으로 실행되는 정보들도 출력이 된다.


additivity 이부분은 

http://seosh81.info/?p=404  << 이분의 블로그에서 잘 설명이 되어 있으니 궁금하면 들어가서 보자


간단히 설명하자면 로그이벤트를 부모에게 전달하도록 하느냐 마느냐 정도의 설정부분인것 같은데


어차피 나는 야매개발자라서 정확하고 자세하겐 모른다.



 <appender name="console" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%d %5p [%c] %m%n" />
        </layout>   
    </appender>


이부분에서 ConversionPattern이부분의 밸류는 콘솔에 표현되는 표현식을 지정하는 부분이다.


표현식은 아래와 같다.


%m: 로그 내용 출력
%p: debug, info, warn, error, fatal 등의 priority 출력
%r: 어플이 시작 후 이벤트가 발생하는 시점까지의 경과시간 밀리세컨드로 출력
%c: package 출력
%c{n}: n(숫자) 만큼의 package를 가장 하단 부터 역으로 출력
        예) %c{2} 일때 a.b.c 는 b.c 로 출력된다.
%n: 개행문자 출력. 플렛폼에 따라 \r\n 또는 \n 출력.
%d: 이벤트 발생 날짜 출력 ( 프로그램의 실행속도를 느리게 한다.)
        예) %d{HH:mm:ss} 또는 %d{dd MMMM yyyy HH:mm:ss}
%C: 호출자의 클래스명 출력
        예) %C{2} 일때 a.b.c.TestClass 는 c.TestClass 로 출력된다.
%M: method 이름.
%F: 프로그램 파일명.
%l: caller의 정보
%L: caller의 라인수
%x: thread와 관련된 NDC(nested diagnostic context) 
%X: thread와 관련된 MDC(mapped diagnostic context) 
%%: % 표시를 출력  
%t: 쓰레드 이름


로그띄울줄 몰라서 들어온 사람이라면 어차피 저 표현식을 쓸일은 없을테니 그냥 이런게 있다는것만 알아두고 넘어가자.



테이블의 값을 변경하기 전에 해당 인덱스의 값이 있는지 확인 하고


insert하거나 update 하는 방법입니다.


ms sql의 exists를 활용하여 아래와 같은 쿼리문을 작성 할 수 있습니다.


1
2
3
4
5
6
7
8
9
10
11
IF EXISTS(
               해당 데이터가 존재 하는지 셀렉트
         )
    BEGIN
        --데이터가 있는 경우 실행할 쿼리
    END
    
    ELSE
        BEGIN
        --데이터가 없는 경우 실행할 쿼리
       END
cs

EXISTS 안에 확인할 데이터를 셀렉트 하는 쿼리를 넣고


데이터가 있는경우와 없는경우 실행할 쿼리를 작성 하면 됩니다.




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
IF EXISTS(
                SELECT CODE_ID FROM tbl
                WHERE CODE_ID = 'code_001'
         )
    BEGIN
        SELECT 99 cnt
    END
    
    ELSE
        BEGIN
        insert into tbl
            (
            DEVICE_ID
              ,CODE_ID
              ,CODE_NAME
            )
        values
            (
              ,'device_001'
              ,'code_001'
              ,''
            )
       END
cs



위의 코드는 code_id라는 컬럼에 code_001이라는 데이터가 있는지 확인하고


있다면 99라는 숫자를 반환하고


없다면 데이터를 입력하게 하는 쿼리 입니다.



insert가 아닌 update도 사용 가능하며 일반 select문도 가능합니다.



값을 입력할때 있는지 확인하기 위해서 컨트롤러에서 DOA까지 두번이나 왕복할 필요가 없겠죠


제 포스팅이 도움이 되었나요? 

그렇다면 공감하기 한번 눌러주세요 블로거에게 큰 힘이 됩니다


쿼리를 작성하다 보면 부등호 (>,<) 를 사용해야 할 때가 있다. 그러나 mybatis에서 사용하면 에러가 나는데


이때 해결방법을 알아 보자.




쿼리에 부등호 기호를 사용하면  


The content of elements must consist of well-formed character data or markup.


라는 경고문구가 나타나기도 한다.


부등호 이외에도 '&' 같은 특수기호에서도 나타난다


이때 해결방법은 간단하다


<![CDATA ]]> 를 사용하면 된다.



1
2
3
4
5
6
7
8
9
10
11
12
13
<select id="loadCartList"  parameterType="userInfoVO" resultType="cartVO">
    
    <![CDATA
    SELECT
        *    
    FROM
        table_name
    WHERE
        coulumA < 10
    AND 
        coulumB > 4
    ]]>
    </select>
cs



이렇게 쿼리를 CDATA로 감싸주면 된다.


혹은


1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="loadCartList"  parameterType="userInfoVO" resultType="cartVO">
    
    SELECT
        *    
    FROM
        table_name
    
    WHERE
    <![CDATA
        coulumA < 10
    AND 
        coulumB > 4
    ]]>
    </select>
cs




이런 식으로 사용 할 수도 있다.



방금 댓글을 남겨주신 분이 추가 정보를 달아주셨다


> - &gt;
< - &lt;
>= - &gt;=
<= - &lt;=


CDATA를사용하지 않고 저렇게 HTML에서 사용하던 방식으로도 치환이 가능 하다



1
2
3
4
5
6
7
8
9
10
11
12
13
<select id="loadCartList"  parameterType="userInfoVO" resultType="cartVO">
    
    SELECT
        *    
    FROM
        table_name
    
    WHERE
        coulumA &lt; 10
    AND 
        coulumB &gt; 4
    </select>
 
cs


 다른 테이블과 조인 걸고 삭제[delete] 하기


가끔씩 delete 구문 조건문에 다른 테이블에서 가져온 정보를 넣고 싶을때가 있다.


외래키가 적용이 되어있기 때문에 A라는 테이블의 로우를 삭제 하기 전에  B라는 테이블에 있는


A와 외래키 관계에 있는 정보를 먼저 삭제 한 후 A테이블의 데이터를 삭제 해야 하는 경우가 


그런 경우일 것이다.


다른 테이블과 조인하여 조건을 걸고 데이터를 삭제 하는 방법을 알아보자


1. 일단 삭제 하고자 하는 delete문을 만들어 보자



1
2
3
DELETE FROM
    테이블AA
 
cs



2. 삭제 하고자 하는 내용을 뽑아오는 selete문을 만들어 보자

1
2
3
4
5
6
7
8
9
10
SELECT
    aa.*
FROM
    테이블AA aa
INNER JOIN 
    테이블BB bb
ON
    bb.MENU_ID = aa.MENU_ID
WHERE 
    bb.MENU_ID= '조건'
cs


3. 1번과 2번을 합체!

1
2
3
4
5
6
7
8
9
10
DELTE FROM
    테이블AA
FROM 
    테이블AA aa
INNER JOIN
    테이블BB bb
ON 
    bb.MENU_ID = aa.MENU_ID
WHERE 
    bb.MENU_ID = '조건'
cs



from 이 두번 나와서 이상해 보이지만 저게 맞다.


이렇게 하면 delete문에 다른 테이블과 조인을 걸어서 만든 조건으로 삭제 할 수가 있게 된다.

+ Recent posts