[MSSQL] 로우 합치기 STUFF, FOR XML PATH, mysql group_concat 기능


개발을 하다보면 가끔씩 필요한 기능이다.


중복되는 로우가 있는데 조인하면 다른 데이터들도 같이 나올때


중복되는거 합치고 여러로우로 된 데이터들만 한 컬럼에 한줄로 표현하고 싶을때가 있다.



ID 

data 

1 

 사과

1 

배 

 2

망고 

2 

사과 

2 

배 


이런 경우


ID 

data 

1 

사과,배 

 2

망고,사과,배 


이렇게 뽑아내고 싶을때 사용하는 방법이다.



여기서 사용할건 STUFF와 FOR XML PATH다


간단하게 그냥 예제를 보도록 하자


1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    DISTINCT ID, -- id 중복 제거
    STUFF(( 
        SELECT 
            ',' + DATA
        FROM 
            dataTbl b 
        WHERE 
            b.ID = a.ID 
            FOR XML PATH('') ),1,1,'') AS NAME 
FROM 
    dataTbl a
 
cs


이런식으로 사용하면 된다.


dataTbl이 단일 테이블이 아니라 그냥 괄호로 묶어서 조인해놓은 서브쿼리를 사용해도 무방하다.



저작자표시

'Yame Programmer > SQL' 카테고리의 다른 글

[MSSQL] 프로시저 트리거 while 반복문 사용 방법, 변수를 컬럼명으로 사용 하는 방법  (0) 2017.05.17
[MSSQL]Insert실행시 자동증가되는 IDENTITY값 바로 가져오기  (0) 2017.04.26
[Oracle MyBatis] 오라클 마이바티스에서 다중 insert 하는 방법(insert all)  (0) 2017.01.16
[Mybatis] getter for property named 에러 해결방법  (3) 2017.01.11
[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11

[MSSQL] 프로시저 트리거 while 반복문, 변수값 컬럼명으로 사용하는 방법





1
2
3
4
5
6
7
8
9
10
11
12
declare @colCount int, @colNum int, @columnName CHAR(8) -- 변수 선언
set @colCount = 0
 
while @colCount < 24 -- 반복문 시작
    begin
        SET @columnName = 'T' -- 변수를 컬럼명으로 사용하기 위한 변수
        IF @colCount < 10 BEGIN
            SET @columnName = 'T0' -- 컬럼명이 T00 T01 T02 이런식일때 
        END
        exec ('select ' + @columnName + @colCount + 'into tValData from T_TABLE_NAME')
        set @colCount = @colCount +1 -- 
    end -- 반복문 끝
cs



테이블의 컬럼명이 T00 T01 이런식으로 될떄가 있다.


for문을 돌리고 싶다거나 변수값을 컬럼명으로 사용하고 싶을땐 위와 같이 사용 하면 된다.

저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[MSSQL] 로우 합치기 STUFF, FOR XML PATH, mysql group_concat 기능  (0) 2020.03.12
[MSSQL]Insert실행시 자동증가되는 IDENTITY값 바로 가져오기  (0) 2017.04.26
[Oracle MyBatis] 오라클 마이바티스에서 다중 insert 하는 방법(insert all)  (0) 2017.01.16
[Mybatis] getter for property named 에러 해결방법  (3) 2017.01.11
[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11

[MSSQL]Insert실행시 자동증가되는 IDENTITY값 바로 가져오기



insert를 실행시키고 자동으로 증가 되도록 해놓은 PK값이 바로 필요한 경우가 있다.


A테이블에 값이 입력되면 그 값에 종속되는 B테이블의 값을 넣어야 하는데


PK를 알아오기 위해서 insert후 다시 select 하더라도 한 컨트롤러에서 해결하기란 힘들다.


게다가 해당 pk값을 모르기 때문에 다시 select 하는 것도 문제.


해결 방법


1
2
SELECT @@IDENTITY
 
cs

이걸 사용 하는 것 이다.


1
2
3
4
5
6
7
8
9
10
11
12
13
 
INSERT INTO 
        T_AUTH
        (
         VAL1
        ,VAL2
        ,VAL3)
values(
        #{VAL1}
        ,#{VAL2}
        ,#{VAL3})
 
SELECT @@IDENTITY AS SEQ
cs



이런식으로 사용 하면


SEQ라는 컬럼에 자동증가된 값을 반환 한다


참고로 MYBAITIS에서는


INSERT가 아니라 SELECT로 감싸줘야 정상 작동 한다


예를 들어


1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="insertAuth" parameterType="VO" resultType="VO">
    INSERT INTO 
        T_AUTH
        (
         VAL1
        ,VAL2
        ,VAL3)
    values(
        #{VAL1}
        ,#{VAL2}
        ,#{VAL3})
 
    SELECT @@IDENTITY AS SEQ
</select> 
cs



이런식으로 만들면 VO SEQ에 해당 값이 담겨서 리턴 된다.

저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[MSSQL] 로우 합치기 STUFF, FOR XML PATH, mysql group_concat 기능  (0) 2020.03.12
[MSSQL] 프로시저 트리거 while 반복문 사용 방법, 변수를 컬럼명으로 사용 하는 방법  (0) 2017.05.17
[Oracle MyBatis] 오라클 마이바티스에서 다중 insert 하는 방법(insert all)  (0) 2017.01.16
[Mybatis] getter for property named 에러 해결방법  (3) 2017.01.11
[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11

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부터 시작해서 작성하면 끝


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

저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[MSSQL] 프로시저 트리거 while 반복문 사용 방법, 변수를 컬럼명으로 사용 하는 방법  (0) 2017.05.17
[MSSQL]Insert실행시 자동증가되는 IDENTITY값 바로 가져오기  (0) 2017.04.26
[Mybatis] getter for property named 에러 해결방법  (3) 2017.01.11
[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11
[MSSQL] MSSQL 날짜 포맷 변환표  (0) 2017.01.09




간단하다 mybatis에 써놓은 쿼리중에 #{변수} << 이부분 이름 틀린거


예를들어 VO엔


1
2
3
4
5
6
7
8
9
    private String FLOOR_PEOPLE;//
    
    public String getFLOOR_PEOPLE() {
        return FLOOR_PEOPLE;
    }
    public void setFLOOR_PEOPLE(String fLOOR_PEOPLE) {
        FLOOR_PEOPLE = fLOOR_PEOPLE;
    }
 
cs


이렇게 되어있는데


쿼리 작성시엔 아래와 같이 대소문자를 다르게 썻다던가


1
2
3
4
5
UPDATE EASY_COMMON.CM_FLOOR_INFO
        SET 
            FLOOR_NAME = #{FLOOR_NAME},
            FLOOR_PEOPLE = #{FLOOR_people}
        WHERE FLOOR_ID = #{FLOOR_ID}
cs


혹은 다른 이름을 썻다던가 할때 발생하는 에러이다.


한꺼번에 특정 문자열을 바꾸는 작업후에 발견되는 경우가 종종 있다.


org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'EASY_COMMON' in 'class egovframework.easyplatform.common.place.space.vo.SpaceVO'

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-1.2.0.jar:1.2.0]

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364) ~[mybatis-spring-1.2.0.jar:1.2.0]

at com.sun.proxy.$Proxy84.selectList(Unknown Source) ~[?:?]

at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:194) ~[mybatis-spring-1.2.0.jar:1.2.0]

at egovframework.rte.psl.dataaccess.EgovAbstractMapper.selectList(EgovAbstractMapper.java:238) ~[egovframework.rte.psl.dataaccess-3.5.0.jar:?]

at egovframework.easyplatform.common.setting.standardsetting.service.impl.SpaceSettingServiceImpl.insertSpace(SpaceSettingServiceImpl.java:30) ~[classes/:?]

at egovframework.easyplatform.common.setting.standardsetting.web.SpaceSettingController.insertSpace(SpaceSettingController.java:71) ~[classes/:?]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_91]

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_91]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_91]

at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.8.0_91]

at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) ~[spring-web-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137) ~[spring-web-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) ~[spring-webmvc-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:747) ~[spring-webmvc-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:676) ~[spring-webmvc-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938) [spring-webmvc-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870) [spring-webmvc-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961) [spring-webmvc-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863) [spring-webmvc-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at javax.servlet.http.HttpServlet.service(HttpServlet.java:650) [servlet-api.jar:?]

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837) [spring-webmvc-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) [servlet-api.jar:?]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) [catalina.jar:7.0.69]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.69]

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat7-websocket.jar:7.0.69]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.69]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.69]

at egovframework.easyplatform.common.interceptor.SimpleCORSFilter.doFilter(SimpleCORSFilter.java:26) [classes/:?]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.69]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.69]

at egovframework.rte.ptl.mvc.filter.HTMLTagFilter.doFilter(HTMLTagFilter.java:52) [egovframework.rte.ptl.mvc-3.5.0.jar:?]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.69]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.69]

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) [spring-web-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.0.9.RELEASE.jar:4.0.9.RELEASE]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.69]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.69]

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) [catalina.jar:7.0.69]

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) [catalina.jar:7.0.69]

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505) [catalina.jar:7.0.69]

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169) [catalina.jar:7.0.69]

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) [catalina.jar:7.0.69]

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956) [catalina.jar:7.0.69]

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) [catalina.jar:7.0.69]

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:436) [catalina.jar:7.0.69]

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1078) [tomcat-coyote.jar:7.0.69]

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625) [tomcat-coyote.jar:7.0.69]

at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318) [tomcat-coyote.jar:7.0.69]

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_91]

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_91]

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-coyote.jar:7.0.69]

at java.lang.Thread.run(Unknown Source) [?:1.8.0_91]

저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[MSSQL]Insert실행시 자동증가되는 IDENTITY값 바로 가져오기  (0) 2017.04.26
[Oracle MyBatis] 오라클 마이바티스에서 다중 insert 하는 방법(insert all)  (0) 2017.01.16
[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11
[MSSQL] MSSQL 날짜 포맷 변환표  (0) 2017.01.09
[oracle] 값이 있는지 확인 후 insert update 하기 [MERGE INTO]  (1) 2017.01.06

Oracle 부적합한 열유형 null 처리 jdbcType=VARCHAR를 사용하지 않고 해결하는 방법


mssql로 만들어진 프로젝트를 Oracle로 마이그레이션 작업을 하던 도중 어마어마한 노가다 작업에 봉착했다


Oracle 사용시 Null 허용컬럼에 값이 null이 들어가는 경우


부적합한 열 유형 이라는 에러 메세지가 뜨게 된다.


이때 이를 해결하기 위해서 


value1 = #{value1, jdbcType=VARCHAR},

value2 = #{value2, jdbcType=VARCHAR}


이런식으로 값 뒤에  , jdbcType=VARCHAR]  를 붙이게 되는데 


생각해보니 이게 양이 너무나도 많은것이다. 이걸 하나하나 언제 다 수정하고 앉아있을까


그래서 구글링해서 찾아낸 방법이 있다.


단 이것은 자신이 mybatis 설정파일을 변경할 권한이 있는 경우 사용하길 바란다.



1. mybatis-config.xml 파일을 연다. 만약 없는 경우

전자정부 프레임워크를 사용한다면 context-sqlMap.xml 파일을 열어 보면


1
2
3
4
5
6
7
8
9
10
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:/egovframework/sqlmap/example/sql-map-config.xml" />
        <!-- <property name="mapperLocations" value="classpath:/egovframework/sqlmap/example/sample/PostgreSQL.xml" />
        
        <property name="configLocation" value="classpath:/mapper/mybatis-config.xml" /> -->
        <!-- Core File관련 Table SQL MAP 설정 위치 -->
        <!-- DB변동시 sql 맵퍼 패턴 수정 -->
        <property name="mapperLocations" value="classpath:/egovframework/sqlmap/example/mappers/${Globals.Mapping}"    /> 
    </bean>
cs



이런 부분이 있는데 여기서  프로퍼티 네임이 configLocation 인것을 찾는다


value값에 보면 컨피그로케이션의 경로가 있는데 저 경로에 찾아가면


아마 mybatis 설정하는 부분이 있거나 typeAlias를 선언해 주는 부분일텐데 일단 해당 파일을 연다




2. configuration 태그 아래 settings 이라는 태그가 있는지 확인


3. 없으면 직접 설정한다


4. 있던 없던 아래와 같은 셋팅 프로퍼티를 추가 한다.


1
2
3
4
5
6
<!--  oracle null 처리  -->
    <settings>
        <setting name="cacheEnabled" value="false" />
        <setting name="jdbcTypeForNull" value="NULL" />
    </settings>
 
cs




그럼 이제 저 귀찮음 많은 jdbcType=VARCHAR 선언들을 해주지 않아도 된다!


역시 개발자는 귀찮은거 싫어하고 머리를 굴려야 한다는걸 다시 깨닫게 된다.


저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[Oracle MyBatis] 오라클 마이바티스에서 다중 insert 하는 방법(insert all)  (0) 2017.01.16
[Mybatis] getter for property named 에러 해결방법  (3) 2017.01.11
[MSSQL] MSSQL 날짜 포맷 변환표  (0) 2017.01.09
[oracle] 값이 있는지 확인 후 insert update 하기 [MERGE INTO]  (1) 2017.01.06
[sql 튜닝] 이론 임시 내용[하드 파싱 소프트 파싱 캐시 블락]  (0) 2016.10.27


 - 기준날짜

 

​   2000년 01월 02일 13시 14분 15.678초 기준 날짜 형변환

​

 

 - MSSQL 날짜 변환표 (기준날짜를 대상으로 CONVERT 실행하여 날짜 형변환)

번호

쿼리

 결과코드

 0

 CONVERT(CHAR(19), DATETIME, 0)

 01 02 2000 1:14PM MM DD YYYY H:MM

 1

 CONVERT(CHAR(10), DATETIME, 1)

 01/02/2000

 MM/DD/YYYY

 2

 CONVERT(CHAR(8), DATETIME, 2)

 00.01.02

 YY.MM.DD

 3

 CONVERT(CHAR(8), DATETIME, 3)

 02/01/00

 DD/MM/YY

 4

 CONVERT(CHAR(8), DATETIME, 4)

 02.01.00

 DD.MM.YY

 5

 CONVERT(CHAR(8), DATETIME, 5)

 02-01-00

 DD-MM-YY

 6

 CONVERT(CHAR(8), DATETIME, 6)

 02 01 00

 DD MM YY

 7

 CONVERT(CHAR(9), DATETIME, 7)

 01 02, 00

 MM DD, YY

 8

 CONVERT(CHAR(8), DATETIME, 8)

 13:14:15

 HH:MM:SS

 9

 CONVERT(CHAR(26), DATETIME, 9)

 01 02 2000 1:14:15.678PM

 NN DD YYYY H:MM:SS.MS

 10

 CONVERT(CHAR(8), DATETIME, 10)

 01-02-00

 MM-DD-YY

 11

 CONVERT(CHAR(8), DATETIME, 11)

 02/01/00

 DD/MM/YY

 12

 CONVERT(CHAR(6), DATETIME, 12)

 000102

 YYMMDD

 13

 CONVERT(CHAR(24), DATETIME, 13)

 02 01 2000 13:14:15.678

 DD MM YYYY HH:MM:SS.MS

 14

 CONVERT(CHAR(12), DATETIME, 14)

 13:14:15.678

 HH:MM:SS.MS

 20

 CONVERT(CHAR(19), DATETIME, 20)

 2000-01-02 13:14:15

 YYYY-MM-DD HH:MM:SS

 21

 CONVERT(CHAR(23), DATETIME, 21)

 2000-01-02 13:14:15.678

 YYYY-MM-DD HH:MM:SS.MS

 22

 CONVERT(CHAR(20), DATETIME, 22)

 01/02/00 1:14:15 PM

 MM/DD/YY H:M:S

 23

 CONVERT(CHAR(10), DATETIME, 23)

 2000-01-02

 YYYY-MM-DD

 24

 CONVERT(CHAR(8), DATETIME, 24)

 13:14:15

 HH:MM:SS

 25

 CONVERT(CHAR(23), DATETIME, 25)

 2000-01-02 13:14:15.678

 YYYY-MM-DD HH:MM:SS.MS

 100

 CONVERT(CHAR(19), DATETIME, 100)

 01 02 2000 1:02PM

 MM DD YYYY H:MM

 101

 CONVERT(CHAR(10), DATETIME, 101)

 01/02/2000

 MM/DD/YYYY

 102

 CONVERT(CHAR(10), DATETIME, 102)

 2000.01.02

 YYYY.MM.DD

 103

 CONVERT(CHAR(10), DATETIME, 103)

 02/01/2000

 DD/MM/YYYY

 104

 CONVERT(CHAR(10), DATETIME, 104)

 02/01/2000

 DD/MM/YYYY

 105

 CONVERT(CHAR(10), DATETIME, 105)

 02-01-2000

 DD-MM-YYYY

 106

 CONVERT(CHAR(11), DATETIME, 106)

 02 01 2000

 DD MM YYYY

 107

 CONVERT(CHAR(12), DATETIME, 107)

 01 02, 2000

 MM DD, YYYY

 108

 CONVERT(CHAR(8), DATETIME, 108)

 13:14:15

 HH:MM:SS

 109

 CONVERT(CHAR(26), DATETIME, 109)

 01 02 2000 1:14:15.678PM

 MM DD YYYY H:MM:DD.MS

 110

 CONVERT(CHAR(10), DATETIME, 110)

 01-02-2000

 MM-DD-YYYY

 111

 CONVERT(CHAR(10), DATETIME, 111)

 2000/01/02

 YYYY/MM/DD

 112

 CONVERT(CHAR(8), DATETIME, 112)

 20000102

 YYYYMMDD

 113

 CONVERT(CHAR(24), DATETIME, 113)

 02 01 2000 13:14:15.678

 DD MM YYYY HH:MM:DD.MS

 114

 CONVERT(CHAR(12), DATETIME, 114)

 13:14:15:678

 HH:MM:DD:MS

 120

 CONVERT(CHAR(19), DATETIME, 120)

 2000-01-02 13:14:15

 YYYY-MM-DD HH:MM:SS

 121

 CONVERT(CHAR(23), DATETIME, 121)

 2000-01-02 13:14:15.678

 YYYY-MM-DD HH:MM:SS.MS

 126

 CONVERT(CHAR(23), DATETIME, 126)

 2000-01-02T13:14:15.678

 YYYY-MM-DDT HH:MM:SS.MS

 127

 CONVERT(CHAR(23), DATETIME, 127)

 2000-01-02T13:14:15.678

 YYYY-MM-DDT HH:MM:SS.MS

 131

 CONVERT(CHAR(25), DATETIME, 131)

 1/06/1421 1:13:14:678PM

 

 

 [참고사항] 자주 쓰이는 변환표

번호

쿼리

 결과코드

 21

 CONVERT(CHAR(23), DATETIME, 21)

 2000-01-02 13:14:15.678 YYYY-MM-DD HH:MM:SS.MS

 23

 CONVERT(CHAR(10), DATETIME, 23)

 2000-01-02

 YYYY-MM-DD

 112

 CONVERT(CHAR(8), DATETIME, 112)

 20000102

 YYYYMMDD

 

 - 예제 1

 

   오늘 날짜를 YYYY-MM-DD 형태로 CONVERT를 이용하여 표시하시오. (2000년 01월 02일 13시 14분 15초 기준)

 

 

 - 예제 1 쿼리

 

   SELECT CONVERT(CHAR(10), GETDATE(), 23) AS 날짜

 

 

날짜

2000-01-02

 

 - 예제 2

 

   오늘 날짜를 YYYY-MM-DD HH:MM:SS.MS 형태로 CONVERT를 이용하여 표시하시오. (2000년 01월 02일 13시 14분 15초 기준)

 

 

 - 예제 2 쿼리

 

   SELECT CONVERT(CHAR(23), GETDATE(), 21) AS 날짜

 

 

날짜

2000-01-02 13:14:15.678

  

 [참고사항] GETDATE()

 

   GETDATE()함수는 오늘날짜를 연, 월, 일, 시, 분, 초, 밀리세컨드까지 갖고 오는 함수입니다.

 

 

출처(http://blog.naver.com/diceworld/220156570441)

[출처] MSSQL 날짜 변환 (DATETIME CONVERT)|작성자 창공


저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[Mybatis] getter for property named 에러 해결방법  (3) 2017.01.11
[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11
[oracle] 값이 있는지 확인 후 insert update 하기 [MERGE INTO]  (1) 2017.01.06
[sql 튜닝] 이론 임시 내용[하드 파싱 소프트 파싱 캐시 블락]  (0) 2016.10.27
[sql튜닝] cmd 명령문 실행 계획  (0) 2016.10.27

Oracle MERGE INTO를 활용해 값이 있는지 확인후 insert , update , delete ,selete 하는 방법 


이전에 포스팅 했던 MSSQL의 EXISTS 기능을 오라클에서 사용하는 방법이다


2016/08/12 - [Yame Programmer/SQL] - [MSSQL] 값이 존재하는지 확인 후 update insert [EXISTS] 활용



MSSQL을 사용했던 프로젝트를 Oracle로 마이그레이션 하게 되면서 다시 사용하게 되었다.


입사 초기에 Oracle를 사용하다 이후 쭉 MSSQL만 써서 기억이 가물가물 했다ㅋㅋ



1. 기본 포맷


1
2
3
4
5
6
MERGE INTO 테이블명  USING ( 서브쿼리 ) 
ON  ( 조건 )
WHEN MATCHED THEN 
조건에 값이 있는 경우 실행할 쿼리 
WHEN NOT MATCHED THEN 
조건에 값이 없는 경우 실행할 쿼리 
cs



2. 간단한 예제


1) 서브쿼리 없이 사용


1
2
3
4
5
6
7
8
9
10
11
12
13
MERGE INTO 
    TABLENAME  
USING DUAL 
    ON (PK_CD = 'PKCD001'  AND DATA_NAME = 'dataName')
    WHEN MATCHED THEN     
        UPDATE SET     
                DATA_COL1 = '업데이트 내용 1',             
                DATA_COL2 = '업데이트 내용 2',             
                DATA_COL3 = '업데이트 내용 3',         
        
    WHEN NOT MATCHED THEN
        INSERT (PK_CD, DATA_NAME,  DATA_COL1, DATA_COL2, DATA_COL3)
        VALUES('PKCD001', 'dataName', '넣을 내용1', '넣을 내용3', '넣을 내용3')
cs




2) 서브쿼리 사용

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
MERGE INTO 
    TABLE_NAME  TARGETTB
USING(
    SELECT 
        CHECKTB.PK_CD
        ,CHECKTB.DATA_NAME
    FROM
        TABLE_NAME CHECKTB
     LEFT JOIN
        JOIN_TB JT
    ON
        JT.DATA = CHECKTB.DATA 
        ) CHECK
 
    ON (TARGETTB.PK_CD = CHECK.PK_CD    AND   TARGETTB.DATA_NAME = CHECK.DATA_NAME)
    WHEN MATCHED THEN     
        UPDATE SET     
                DATA_COL1 = '업데이트 내용 1',             
                DATA_COL2 = '업데이트 내용 2',             
                DATA_COL3 = '업데이트 내용 3',         
        
    WHEN NOT MATCHED THEN
        INSERT (PK_CD, DATA_NAME,  DATA_COL1, DATA_COL2, DATA_COL3)
        VALUES('PKCD001', 'dataName', '넣을 내용1', '넣을 내용3', '넣을 내용3')
cs

ㅇ

꼭 INSERT UPDATE만 넣을 수 있는게 아니라 SELECT DELETE를 넣어서 사용 할 수도 있다.




저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11
[MSSQL] MSSQL 날짜 포맷 변환표  (0) 2017.01.09
[sql 튜닝] 이론 임시 내용[하드 파싱 소프트 파싱 캐시 블락]  (0) 2016.10.27
[sql튜닝] cmd 명령문 실행 계획  (0) 2016.10.27
[Oracle] 값이 존재하는지 확인 후 insert, update 하기  (0) 2016.10.05

하드 파싱보단 소프트 파싱을 하는 것이 좋다


하드파싱을 하는 경우 CPU의 사용률이 높아진다



라이브러리 캐시 = 메모리


캐시되어질 공간이 없다면 기존의 캐시를 밀어내고 사용 한다.




블락?


자주 액세스되는 블럭은 hot block?



어떨때 핫블락이 밀어내어 지는가??(질문)


인덱스를 경유??




--- 중간에 다 날아감... 개 짜증 --



sga 와 pga가 있다


sga - 백그라운드 프로세스와 서버 프로세스 경유 메모리 [system global area]


pga - 프라이빗? 




세가지 조인 방법


nasted loops join



sort merge join


hash join




해시 조인 세미는 또 뭐냐 시벌...





옵티마이저


가장 효율적인 방법으로 sql을 수행할 최적의 처리 경로를 생성해주느,ㄴ dbms 핵심 엔진



규칙기반과 비용기반이 있음



1쪽집합은 뭘까... 





----

시험에 나온다


하드파싱 과정 소프트파싱 과정


옵티마이저 종류 2가지 설명


옵티마이저 모드 설정 값 설명


쿼리 변환 join 제거 조건절 이행 view mergeing 힌트와 같이 어떤방식으로 쿼리 변환이 이루어 지는지

알아야함

(언네스팅으로 풀기도 하고 필터조건으로 풀기도 하고.. 시발 뭔말인지 1도 모르겠다)


플랜 순서 이해 하는지 


join에서 먼저 엑세스 되는 테이블이 뭐고 이너,아우터 테이블 이해


파싱과정에서의 경합 요인? 


네스티드 루프조인 수행 원리. 어떤 경우에 네스티드 루프조인이 효과적인가


인덱스!


옵티마이저 비용계산. 비용계산 전에 선택도와 카디널리티의 이해 + 클러스터링 팩터, 히스토그램




http://bysql.net/index.php?document_srl=14266&mid=w201101

교육내용 동일 코드??


set linesize 유용하네



http://koleekr7.tistory.com/category/%EC%98%A4%EB%9D%BC%ED%81%B4%EC%84%B1%EB%8A%A5%EA%B3%A0%EB%8F%84%ED%99%94%EC%9B%90%EB%A6%AC%EC%99%80%ED%95%B4%EB%B2%95




저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[MSSQL] MSSQL 날짜 포맷 변환표  (0) 2017.01.09
[oracle] 값이 있는지 확인 후 insert update 하기 [MERGE INTO]  (1) 2017.01.06
[sql튜닝] cmd 명령문 실행 계획  (0) 2016.10.27
[Oracle] 값이 존재하는지 확인 후 insert, update 하기  (0) 2016.10.05
[Oracle] java.sql.SQLException: ORA-00911: 문자가 부적합합니다.  (1) 2016.10.05



> sqlplus / as sysdba


> alert user scott

identified by oracle

account unlock;


> connect

scott/oracle


> select tname from tab;





- 실습문제배포용의 데이터파일 , 템프파일 경로 바꾸기


> conn / as sysdba



 - 내용 복사해서 붙여넣기 (콘솔)


> conn EXPERT/EXPERT


연결되었습니다


> @D:\oracle\script들\tuning_basic.sql





> conn / as sysdba

> show parameter db_keep_cache_size  

> show parameter db_keep_cache_size

> show parameter db_keep_cache_size


(킵,리사이클 디폴트 캐시 사이즈 확인)

기본은 하한 설정이 되어있지 않아 0으로 되어 있을것











실행계획




>grant select on v_$sql to scott;

>grant select on v_$session to scott;

>grant select on v_$sql_plan_statistics_all to scott;

권한 부여


>conn scott/oracle


>alter session set statistics_level=all;







auto trace로 보는 방법


>conn / as sysdba

> @?




저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[oracle] 값이 있는지 확인 후 insert update 하기 [MERGE INTO]  (1) 2017.01.06
[sql 튜닝] 이론 임시 내용[하드 파싱 소프트 파싱 캐시 블락]  (0) 2016.10.27
[Oracle] 값이 존재하는지 확인 후 insert, update 하기  (0) 2016.10.05
[Oracle] java.sql.SQLException: ORA-00911: 문자가 부적합합니다.  (1) 2016.10.05
[MSSQL] mssql에서 dual과 같은 가상 테이블 사용 하기  (0) 2016.08.12

+ Recent posts

티스토리툴바