hanbyeol쓰

엑셀로 받게되는 다량의 데이터를 어떻게 하면 INSERT문을 만들어서 쉽게 Database에 입력할 수 있을까?

에 대한 대답으로 스터디를 시작한다.

1. TESTTBLE을 만들어줌

2. 엑셀 데이터

3. 해당코드값에 따른 값을 데이터베이스에 넣어줄건데, Y/N 여부가 N인경우 VALUE4에 VLUE3를 중복해서 넣는다고 가정

INSERT INTO TESTTBLE ('CODE', 'VALUE', 'VALUE2', 'VALUE3', 'VALUE4')
VALUES ('A','H','K','M')

이런식으로 24번을 다 넣어주어야할까?

아니다~!

엑셀의 좋은기능을 한번 살펴보자

해당 셀옆에 ="" 를 쓰면 된다.

큰따옴표안에, 생성할 쿼리를 작성해주는데,

셀을 지칭할때에는 "&셀번호&" 이렇게 입력해주면된다.

그리고 아래로 쭈욱~ 스크롤내려주면 이와같이 대량의 INSERT문이 생성됨.

해당쿼리를 실행시키면 잘들어가는것을 볼 수 있다.

앗 그런데우리는 y/n여부에따라 네번째컬럼의값을 다르게하기로 했었다.

그렇다면 UPDATE문을 이용한다.

해당쿼리실행후

잘업데이트된것을 볼 수 있다!

끝!

저번주금요일, 퇴근직전 분명 개발서버에선 잘만 조회되는 쿼리가 운영서버에서는 조회되지 않았다...

운영서버로그를 볼 수 없어서 너무답답했는데, 

오늘 새로운 마음으로 천천히 조회되는 쿼리부터 한자한자 입력해보며 비교해보고 이유를 알아내버렸다..... 울뻔 😪 

---------------------------------------------------복사해온글 입니다 ↓----------------------------------------------------------------

[문제]

같은 소스인데도 불구하고 테스트계에서는 에러가 발생하지 않았지만 운영계에서 ORA-01861 에러가 발생했다.

에러로그는 --> (literal does not match format string)

[이유]

OS의 LANG 설정이 달라서 문자열을 묵시적으로 날짜로 변환을 못해서 발생하는 에러라고 한다.

(리눅스 문자셋(캐릭터셋))

보통 서버를 교체하고 나서 LANG에 관련된 설정을 하지 않거나

비교하는 대상의 두 서버의 LANG설정이 다르면 발생한다.

내경우에는 테스트계는 기존에 사용하던 서버였고, 운영계는 이번에 신설된 서버였다.

LANG설정이 누락된 듯 싶어서

테스트계와 운영계의 LANG설정을 각각 확인해보았다.

리눅스 명령어

echo @LANG

locale

[해결방법]

서버의 LANG설정을 에러가 나지 않는 환경의 설정으로 동일하게 맞춤

에러가 나는 쿼리에 분명히 to_date나 to_char함수의 사용이 있을 거고

분명 to_date(#start_date#)이런 식으로 날짜 형식에 대한 정의가 빠져있을 것이다.

따라서, to_date, to_char 사용한 부분들을 찾아 명시적으로 프로그래밍을 해주면 해결된다.

ex) to_date(#start_date#)  >>>>>> to_date(#start_date#), 'YYYYMMDD')

https://mamoruoppa.tistory.com/106 

오늘은 안쓰고 자려다가 오늘 쿼리를 수정하며 이건 꼭 정리해 놓아야지 했던 것이 생각나서 끄적여본다.

이미 기존 데이터에 구분값이 사장(A001), 임원(A002), 팀장(A003), 사원(A004)의 코드 값으로 구분되어 들어가 있었다고 가정하였을때, 갑자기 사장과 임원 사이에 부사장이라는 직급이 생겼다고 가정하자.

기존에 ORDER BY를 하여 사장 > 임원 > 팀장 > 사원 순으로 조회되던 데이터를

                                           사장 > 부사장 > 임원 > 팀장 > 사원 순으로 조회되어야 한다고 한다.

이런경우 임원(A002), 팀장(A003), 사원(A004)의 코드 값을 하나씩 늘려 임원(A003), 팀장(A004), 사원(A005) 로 업데이트 하고, 부사장의 코드를 A002로 다시 정리하여 조회하는 방법도 있겠지만, 

이미 공통으로 사용되고 있던 코드이기 때문에, 코드의 값을 바꾸는 것이 내키지 않는 경우가 있다. 

그런경우!

부사장에 'A005'코드를 부여하고 조회할때 

ORDER BY 절 안에서 CASE문을 사용해 정렬해주면 깔끔하게 해결된다.

SELECT 직급, 이름
FROM 회사사람들
ORDER BY 직급

👇

SELECT 직급, 이름
FROM 회사사람들
ORDER BY (CASE 직급 WHEN 'A001' THEN 1
                   WHEN 'A002' THEN 3
                   WHEN 'A003' THEN 4
                   WHEN 'A004' THEN 5
                   WHEN 'A005' THEN 2
          END
          )ASC

끝!

LISTAGG함수는 여러행으로 조회되는 것을 한 행으로 합쳐서 보여줄 때 용이하게 사용된다.

때문에 GROUP BY 또는 PARTITION BY 절과 함께 사용된다.

WITH TEST_DB AS (
SELECT '김철수'AS NM, '101'AS CROOM FROM DUAL
UNION
SELECT '김영수'AS NM, '102'AS CROOM FROM DUAL
UNION
SELECT '김동미'AS NM, '103'AS CROOM FROM DUAL
UNION
SELECT '김슬기'AS NM, '101'AS CROOM FROM DUAL
UNION
SELECT '김지영'AS NM, '102'AS CROOM FROM DUAL
)
SELECT * FROM TEST_DB;

WITH TEST_DB AS (
SELECT '김철수'AS NM, '101'AS CROOM FROM DUAL
UNION
SELECT '김영수'AS NM, '102'AS CROOM FROM DUAL
UNION
SELECT '김동미'AS NM, '103'AS CROOM FROM DUAL
UNION
SELECT '김슬기'AS NM, '101'AS CROOM FROM DUAL
UNION
SELECT '김지영'AS NM, '102'AS CROOM FROM DUAL
)
SELECT CROOM, LISTAGG(NM, ',') WITHIN GROUP(ORDER BY NM) AS NMS
FROM TEST_DB
GROUP BY CROOM;