윤성최의 TIL (Today I Learned)

oracle의 무료 DB Tool

DBever를 사용 중이지만, 오라클의 프로시저를 테스트 하기 위해서 설치

경로 : https://www.oracle.com/kr/database/sqldeveloper/technologies/download/

##용도

-로그확인

##사용 예제

[간단 예제]

v_num NUMBER := 0;

DBMS_OUTPUT.PUT_LINE('v_num : '||v_num);

[출력결과 ]

v_num : 0

##사용시 주의사항

SET SERVEROUTPUT ON;

을 먼저 수행해주어야 한다. (기본설정은 OFF)

## := 의 의미

대입연산자

##사용 예제

변수 := 값

ORA-01481 : invalid number format model ,숫자 형식 모델이 부적합합니다.

[해결방법]

TO_DATE 를 사용해주니 해결!

[왜 오류를 만났을까]

왜일까.... to_date 대신 to_char를 써서 그런거였다.ㅋㅋㅋㅋㅋ

암튼 간단히 해결!

오라클에서 문자열 자르기 할 때 쓰는 함수

substr 간단하지만 생각보다 많이 쓰지도 않지만 아예 안쓰지도 않고 해서 맞나 싶어서 검색하면서 쓴닼ㅋㅋㅋㅋ

그럴바엔 내 블로그에 정리해서 내가 내것을 보자는 마음으로 정리!

(사용 예제)

select substr(원본문장, 시작위치, 길이)

from dual

;

시작위치와 길이는 숫자로 작성하고 시작위치부터 길이만큼 원본문장을 자르겠다는 의미!

(예)

select substr('yunsung', 1, 3)

from dual;

->출력되는 문자 : yun

업무 하다보면 기존 소스를 찾아볼 일이 빈번하다. 그리고 변수라던가 텍스트라던가 뭐 어디에 위치 하고 있는지 등등을 찾아야 하는것도 많다.

그때 사용하기 유용한 쿼리들!!!ㅋㅋㅋㅋㅋ

<수정전>

문제점 : 새로운 값이 입력 되면 적용종료일이 몽땅 다 업데이트가 된다.

원하는 바 : 새로운 값이 입력 되면 가장 최근것의 적용종료일만 업데이트가 되어야 한다.

        UPDATE CMC069TM
            ED_DT       = TO_CHAR(TO_DATE(#{ST_DT}) -1,'YYYYMMDD')
        WHERE (AD_NO , ST_DT, TYPE_RT) 
        IN  (
						SELECT AD_NO , ST_DT, TYPE_RT
						FROM (SELECT * 
						      FROM CMC069TM
						      ORDER BY ST_DT DESC)
						 WHERE --ROWNUM = 1 
						  --AND 
						  AD_NO   = #{AD_NO}
						) 
        AND ST_DT <![CDATA[<=]]> TO_CHAR(TO_DATE(#{ST_DT}) -1,'YYYYMMDD')

<수정 후>

새로운 값을 입력하면 기존에 입력된 값 중 가장 최근 데이터의 적용 종료일만 업데이트가 되었다.

[수정한 쿼리]

        UPDATE CMC069TM
            ED_DT       = TO_CHAR(TO_DATE(#{ST_DT}) -1,'YYYYMMDD')
        WHERE (AD_NO , ST_DT, TYPE_RT) 
        IN  (
						SELECT AD_NO , ST_DT, TYPE_RT
						FROM (SELECT * 
						      FROM CMC069TM
						      ORDER BY ST_DT DESC)
						 WHERE ROWNUM = 1 
						  AND  AD_NO   = #{AD_NO}
						  AND ST_DT <![CDATA[<=]]> TO_CHAR(TO_DATE(#{ST_DT}) -1,'YYYYMMDD')
						)

[해결방법]

WHERE 절에 ROWNUM =1 을 넣고 

AND ST_DT <![CDATA[<=]]> TO_CHAR(TO_DATE(#{ST_DT}) -1,'YYYYMMDD') 을 인라인뷰로 위치를 바꾸어 주어서 해결했다.

[오류]

한개의 함수를 2개로 분기 한 뒤에

원래의 함수에 분기된 함수를 심어서 분기된 함수가 잘 되었는지 확인하려다가 에러를 만났다.

값의 수가 너무 많다니??!! 무슨 값이 많은건데??? 암튼 찾아보니 컬럼의 갯수가 서로 안맞는거였다.

아래는 오라클 에러에 나온 설명, 하지만 짧은 설명 뿐이어서 도움은 블로그를 통해 받았다.ㅋㅋㅋㅋㅋ

https://docs.oracle.com/en/database/oracle/oracle-database/21/drdas/error-code.html#GUID-3DB3995E-5FF8-4650-9216-A5B5C41CE2D7

위의 링크를 타고 들어가면 Error Code Description : Too many values 을 확인 할 수 있다.

달랑 한줄로 뭐 어찌라고??ㅋㅋㅋㅋㅋㅋ

[해결 방법]

(처음 소스)

SELECT FN_TEST()
INTO V_APR_CD , V_SUN_MSG
FROM DUAL;

FN_TEST()는 1개의 값인데, INTO 에서는 V_ , V_ 로 2개의 값에 넣어주기를 요구 하고 있어서 에러가 난다.

때문에 INTO를 1개로 줄여 주던가, SELECT 문에 한개 더 넣어주던지 해서 서로의 값을 맞추어 준다.

(수정 소스)

SELECT FN_TEST()
INTO V_APR_CD
FROM DUAL;

나는 INTO에 변수 한개를 줄여서 SELECT에 1개 INTO에 1개로 갯수를 맞추어 주었더니, 오류가 나타나지 않았다.

오늘도 또 배웠다.ㅎㅎㅎㅋㅋㅋㅋㅋ

[요청 사항]

문의사항 하나를 등록 했는데 조회를 하면 똑같은 내용이 2개씩 나오는게 보여짐. 중복이 보이지 않게 해달라는 요청

[문제점]

테이블이

FROM  EXA11 A11 
      LEFT JOIN EXA1 A1 ON A11.CUST_NO = A1.CUST_NO

EXA11 테이블과 EXA1이 조인으로 연결이 되어있다.

중복된 항목으로 각 테이블을 조회해보니,

EXA11 테이블에는 데이터가 1건

EXA1 테이블에는 데이터가 2건

이 있어서 1:N으로 매칭되어 조회 할 때 게시글이 여러건이 보였던 것이었다.

[나의 해결 방법]

SELECT DISTINCT는 되지 않길래,

서브쿼리 인라인뷰에서 group by로 해결을 하였다.

(참고 : 인라인뷰 FROM (SELECT ... FROM ....)) :하나의 테이블 처럼 사용.

FROM  EXA11 A11 
      LEFT JOIN (SELECT CUST_NO, MAX(LOAN_NO) AS LOAN_NO FROM EXA1 GROUP BY CUST_NO) A1

      ON A11.CUST_NO = A1.CUST_NO

[빠른 결론]

[설명 및 이야기]

select 의 조건절이 이상하다고 해서 연락을 받아서 확인하다가, 

count 를 이용하여 숫자 갯수를 맞추는 와중에 좀 이상한 느낌이 들어서 확인해봤다.

운영 프로그램에서는 99건이 검색되어지는데, 

운영 DB 로 검색했을때는 76건으로 보여지는 것이었다.

일단 내가 시행한 방법은 카운트를 뺀 쿼리를 조회하여 값을 엑셀로 익스포트 하였다.

그리고 난 뒤 count(컬럼이름)을 넣은 후 조회한 값을 엑셀로 익스포트 한 후 비교를 해보았다.

(노란색으로 배경을 칠한 쪽이 count(컬럼이름)이고 아무표시도 안한 쪽이 일반 쿼리 조회.)

엑셀로 확인해보니 둘 다 99건이었다.

그럼 왜 오라클에서  count(컬럼이름)으로 했을 때는 99건이 아닌 76건으로 상이했을까??

count(컬럼이름) 은 null 값을 포함하지 않고 값을 센다고 한다.

그래서 null 값까지 모두 필요하면 count(*)로 값을 확인하여야 한다.