WITH절이란 WITH절은 오라클9 이후 버전부터 사용이 가능하며 이름이 부여된 서브쿼리라고 생각하시면 됩니다. 임시테이블을 만든다는 관점에서본다면 VIEW와 쓰임새가 비슷한데 차이점이 있다면 VIEW는 한번 만들어놓으면 DROP할때까지 없어지지 않지만 WITH절의 같은경우 한번 실행할 쿼리문내에 정의되어 있을경우, 그 쿼리문안에서만 실행된다는 차이점이 있습니다. WITH절을 사용하는 이유 WITH절은 복잡한 SQL에서 동일 블록에 대해 반복적으로 SQL문을 사용하는 경우 그 블록에 이름을 부여하여 재사용 할 수 있게 함으로서 쿼리 성능을 높일 수 있는데 WITH절을 이용하여 미리 이름을 부여해서 Query Block을 만들 수 있습니다. 자주 실행되는 경우 한번만 Parsing되고 Plan 계획이 수립..
그룹별 순번매기기 ROW_NUMBER() 카테고리(CATEGORY_ID)별 저렴한 금액(STANDARD_COST)순으로 순번 매기기 SELECT A.CATEGORY_ID, B.CATEGORY_NAME, A.STANDARD_COST, ROW_NUMBER() OVER (PARTITION BY A.CATEGORY_ID ORDER BY A.CATEGORY_ID, A.STANDARD_COST) AS NUM --그룹별 순번 FROM PRODUCTS A INNER JOIN PRODUCT_CATEGORIES B ON A.CATEGORY_ID = B.CATEGORY_ID 카테고리별 가격의 순번을 매기는 쿼리입니다. CPU카테고리에서는 70번까지의 순번이 값이 작은 순에서 큰 순으로 순번이 매겨졌고 그 뒤로 두번째 카테고..
오라클 순번매기기 ROWNUM SELECT ROWNUM, A.PRODUCT_NAME, A.STANDARD_COST FROM PRODUCTS A ROWNUM이라는 시스템 함수를 사용하면 위와 같이 라인별 순번을 가져올 수 있습니다. 하지만 위와 같이 아무런 정렬이 되지 않은 데이터에 순번을 매긴다면 그 순번은 상당히 의미 없는 숫자가 되어 버릴 것입니다. 정렬된 데이터에 순번매기기 위의 예제와 같이 순번을 매기는 시스템 함수는 ROWNUM이고 정렬은 ORDER BY를 사용하시면 되는데 ROWNUM와 ORDER BY를 같이 사용할 경우 매겨놓은 순번이 섞여버리는 현상이 발생합니다. SELECT ROWNUM,--순번매기기 A.PRODUCT_NAME, A.STANDARD_COST FROM PRODUCTS A O..
COALESCE 함수는 인자값으로 들어오는 표현식에서 NULL이 아닌 첫번째 표현식을 리턴해주는 함수입니다. COALESCE 함수 사용법 SELECT COALESCE('A','B','C'), --결과값 : A COALESCE(NULL,'B','C'), --결과값 : B COALESCE(NULL,NULL,'C'), --결과값 : C COALESCE(NULL,NULL,NULL) --결과값 : NULL FROM DUAL COALESCE 작동원리는 NVL, NVL2함수와 비슷합니다. 인자값으로 들어온 값 중에서 NULL값이 아닌 첫번째 값을 반환합니다. 만약 NULL값만 들어왔다면 NULL값을 반환합니다. 파라미터의 타입은 일치하여야 합니다. SELECT COALESCE(NULL,'A',1) FROM DUAL ..
오라클에서 날짜를 비교할 때 주의사항이 있습니다. DATE형/ 문자형끼리 비교는 문제가 없지만 DATE형과 문자형의 비교일 경우 서로 데이터 타입을 맞춰줘야 합니다. 오라클에서 주로 날짜 포맷을 맞추는 TO_CHAR 함수의 리턴 값이 문자형이므로 이런 실수가 자주 나옵니다. 날짜 비교 WITH EXAMPLE AS( SELECT TO_CHAR(SYSDATE,'YY/MM/DD') AS DAY1, --19/12/01' SYSDATE AS DAY2 --19/12/01' FROM DUAL ) SELECT '두 날짜는 서로 같습니다.' FROM EXAMPLE WHERE DAY1 = DAY2 위 쿼리를 조회해보면 데이터가 조회되지 않습니다. 데이터가 조회되지 않은 이유는 바로 서로의 데이터 타입이 다르기 때문입니다...
날짜 계산 (연산자) SELECT SYSDATE + 100, --현재날짜 +100일 SYSDATE - 100, --현재날짜 -100일 SYSTIMESTAMP + 100, --현재날짜 +100일 SYSTIMESTAMP - 100 --현재날짜 -100일 FROM DUAL 위와같이 + , - 연산자를 활용하여 간단한 날짜 계산이 가능합니다. 날짜 계산 (함수) SELECT ADD_MONTHS(SYSDATE,6), --현재시간 + 6개월 뒤 LAST_DAY(SYSDATE), --해당월 마지막 일자 계산 NEXT_DAY(SYSDATE,'일요일'), --다음 주 일요일 계산 MONTHS_BETWEEN(SYSDATE, SYSDATE-100) --DATE1과 DATE2의 개월 수 반환 FROM DUAL ADD_MONT..
쿼리문을 짜다보면 원하는 날짜와 시간의 형태를 뽑아내고 싶을 경우가 많습니다. 오라클에서 날짜 포맷의 변환은 TO_CHAR함수를 활용하여 변경하실 수 있습니다. 다양한 날짜 / 시간 포맷 SELECT --날짜 포맷 TO_CHAR(SYSDATE,'YYYYMMDD'),--20191130 TO_CHAR(SYSDATE,'YYYY-MM-DD'),--2019-11-30 TO_CHAR(SYSDATE,'YYYY/MM/DD'),--2019/11/30 TO_CHAR(SYSDATE,'YYYY'),--2019 TO_CHAR(SYSDATE,'YY'),--19 TO_CHAR(SYSDATE,'MM'),--11 TO_CHAR(SYSDATE,'MON'),--11월 TO_CHAR(SYSDATE,'D'),--7 (주중의 일을 1~7로 표..
오라클에서 현재날짜 / 현재시간을 나타내주는 시스템함수는 SYSDATE, SYSTIMESTAMP함수입니다. 현재날짜 구하기 (SYSDATE, SYSTIMESTAMP) SELECT SYSDATE, SYSTIMESTAMP FROM DUAL 오라클에서 SYSDATE,와 SYSTIMESTAMP를 입력하기만 하면 위와같은 형태로 날짜가 출력됩니다. 위 데이터 포맷은 기본데이터 포맷으로 리턴한 값이므로 환경에따라 다를 수 있습니다. SYSDATE와 SYSTIMESTAMP함수는 리턴하는 타입이 서로 다릅니다. SYSDATE DATE형태로 리턴하고 SYSTIMESTAMP함수는 TIMESTAMP타입으로 리턴됩니다. 두 타입간의 차이점은 아래 표와 같습니다. 데이터타입 설명 DATE BC 4712년 1월 1일부터 9999..
대부분의 개발 언어에서는 %로 나머지계산을 합니다. 하지만 오라클에는 %연산자가 존재하지 않습니다. 그러므로 나머지값을 구하기 위해서는 나머지를 구해주는 함수를 사용하시는것이 좋습니다. 오라클에서 나머지를 구하는 함수는 MOD, REMAINDER함수가 있습니다. 오라클 나머지 함수(MOD, REMAINDER) 사용법 --사용법(대상 값, 나눌 값) MOD(n2,n1) REMAINDER(n2,n1) --예제 SELECT MOD(10,3), REMAINDER(10,3) FROM DUAL MOD함수와 REMAINDER함수의 차이점 두 함수는 오라클에서 모두 나머지를 구하는 함수이고 두번째 인자가 첫번째 인자값을 나눈 나머지를 반환한다는 점에서 사용법까지 완전히 일치합니다. 하지만 두 함수는 나머지를 구하는 내..
제곱구하기 POWER(n1, n2) --사용법 POWER(대상숫자, 제곱숫자) --예제 SELECT POWER(5,2), POWER(5,-3), POWER(-5,3), POWER(5,3.1) FROM DUAL POWER함수는 첫번째 인자를 두번째 인자만큼 제곱한 결과를 반환합니다. 위와 같이 정수와 실수 모두 올 수 있습니다. 단. 첫번째 인자값이 음수일경우에는 실수를 제곱할 수 없습니다. EX : POWER(-5,3.01) 같은 연산은 할 수 없습니다. 제곱근 구하기 SQRT(n) --사용법 SQRT(대상숫자) --예제 SELECT SQRT(9), SQRT(5.4) FROM DUAL SQRT함수는 인자값의 제곱근을 반환합니다. 인자값으로는 양의자리 정수와 실수만 올 수 있습니다. EX : SQRT(-5..
오라클에서 절대값을 구하는 함수는 ABS입니다. 총 매출같이 음수의 금액도 양수로 바꿔서 계산해야할 경우가 종종 있는데 이때 유용하게 사용될 수 있는 함수입니다. 절대값 함수 ABS 사용법 --사용법 ABS(숫자) --예제 SELECT ABS(100), ABS(-100), ABS(-100.15) FROM DUAL ABS함수는 숫자를 매개변수로 받아 매개변수로 받은 숫자의 절대값을 반환해주는 함수입니다.
갸끔 요구사항으로 자릿수를 일정하게 맞춰달라는 요구가 종종 있습니다. 이럴때 오라클에서 유용하게 사용할 수 있는 함수는 LPAD, RPAD함수입니다. LPAD, RPAD함수는 n자리만큼 왼쪽, 오른쪽 부터 채워서 반환하는 문자열 함수입니다. LPAD, RPAD 본래 용도는 문자열 삽입을 주 용도로 사용하지만. 자릿수를 일정하게 맞추는 용도로도 사용할 수 있습니다. LPAD, RPAD 함수 사용법 LPAD(expr1, n, expr2) --왼쪽에서부터 문자열 채우기 RPAD(expr1, n, expr2) --오른쪽에서부터 문자열 채우기 LPAD, RPAD함수는 매개변수로 들어온 expr2문자열을 n자리만큼 왼쪽 / 오른쪽부터 채워넣어 expr1을 반환하는 함수입니다. n은 총 자릿수를 의미합니다. 자릿수 ..
오라클 가끔씩 데이터에 공백이 들어가는 경우가 있습니다. 이럴 경우 공백이 들어가는 문제점을 찾아 수정하는것이 가장 좋지만 그것이 어렵다면 오라클 TRIM함수를 사용하여 공백을 제거하는 방법도 사용할 수 있습니다. 오라클에서 공백제거는 TRIM, LTRIM, RTRIM함수를 통해서 제거할 수 있습니다. 또 LTRIM, RTRIM함수는 문자열에서 공백을 제거할때도 유용하게 사용할 수 있지만 문자열에서 특정 문자를 제거하는데도 사용할 수 있습니다. 왼쪽에서부터 문자 / 공백제거 LTRIM 함수 --사용법 (문자열,제거문자) LTRIM(STR,TARGET_STR) --예제 WITH EXAMPLE AS( SELECT ' BLANK ' AS STR1, 'ABCDEF' AS STR2 FROM DUAL ) SELEC..