오라클 날짜타입의 값에서 시간을 빼거나 더하기 위해서는, 기본으로 제공해주는 오라클 함수가 없기 때문에 별도의 연산작업을 해줘야 한다. 시간, 분, 초를 더하기나 빼기 위해서는 일반적으로 1/24 [1시간], 1/(24*60) [1분], 1/(24*60*60) [1초]를 사용하여 연산을 한다. 그러나 INTERVAL이라는 오라클 키워드를 조금 더 명시적으로 쿼리를 작성할 수 있다. 일반수식을 이용하는 방법 WITH temptable AS ( SELECT to_date('2019-08-21 08:00:00', 'yyyy-mm-dd hh24:mi:ss') curtime FROM dual ) SELECT curtime , curtime + 5/24 hour --5시간 더하기 , curtime + 5/(24*6..
오라클 SQL에서 소수점 자리수를 지정하기 위해서 ROUND 함수를 사용한다. ROUND 함수는 특정 소수점을 반올림하고 나머지를 버리는 함수 이다. 소수점을 반올림하지 않고 절사만 원한다면 TRUNC 함수를 사용해야 한다. ROUND 함수- 함수 : ROUND("값", "자리수") 소수점 반올림-1 (소수점 첫째자리) SELECT ROUND(1235.543) --① , ROUND(1235.443) --② , ROUND(1235.443, 0) --③ FROM dual①,②,③ 모두 소수점 첫번째 자리수를 반올림 한다. ①은 소수점 첫번째 자리수가 5이므로 반올림 되었고, ②는 4이므로 그냥 버려졌다. ③은 ①,②와 동일하게 소수점 첫번째 자리수를 반올림하는 것이며 두번째 파라미터(0)은 생략이 가능하다...
오라클에서 MONTHS_BETWEEN 함수를 사용하여 쉽게 나이를 계산 할 수 있다. 만나이 계산 방법을 많이 사용하며, 이것을 응용하여 한국나이를 계산할 수있다. 1세 미만의 나이는 개월수 또는 일수로 계산할 수 있는 쿼리도 있으니 아래를 참고하면 된다. 만나이 계산 쿼리 SELECT TRUNC(MONTHS_BETWEEN(TRUNC(SYSDATE), TO_DATE('19890326','YYYYMMDD')) / 12) FROM DUAL 한국나이 계산 쿼리 SELECT MONTHS_BETWEEN(TRUNC(SYSDATE,'YEAR'), TRUNC(TO_DATE('19890326','YYYYMMDD'),'YEAR')) /12 +1 FROM DUAL※ 한국나이 = 현재년도 - 출생년도 + 1 만나이, 개월수,..
오라클에서 문자열을 치환하기 위해서는 대부분 replace 함수와 translate 함수를 사용 한다. translate 함수는 사용방법을 정확히 숙지하지 않으면 너무 어렵게 느껴지는 함수이기도 하다. 함수사용법 : translate('문자열', '대상문자', '변환문자') 대상문자와 변환문자는 1:1로 변환이되며, 대상문자에는 있는데 변환문자에는 없으면 해당 문자는 제거 된다.replace 함수에서는 특정문자를 제거할때 변환문자에 ''을 사용하여 제거하지만, translate 함수에서는 변환문자에 ''을 사용할 수 없다. 예) translate('010-1234-5678', '-', '') : 변환문자에 ''사용할 수 없음 사용예제 SELECT TRANSLATE('hello world!!!', 'hw'..
오라클에서 특정 문자열을 치환하거나 제거하기 위해서는 REPLACE 함수를 사용하면 된다. 단순 문자열 치환 외에도 엔터값 제거, 전화번호 특수문자 제거 등 다양한 상황에서 사용할 수 있다. 오라클 10g부터 정규식 사용이 가능한 REGEXP_REPLACE 함수가 추가되었다. 목차 특정문자 치환 특정문자 제거 엔터값, 탭문자 제거 전화번호 구분자 제거 REGEXP_REPLACE 함수 (정규식) 특정문자 치환 SELECT REPLACE('Oracle Database', 'Oracle', 'Ora') AS result1 , REPLACE('Oracle Database', 'Database', 'DB') AS result2 , REPLACE('Oracle Database', 'D', 'd') AS result..
DECODE 함수는 오라클 쿼리에서 가장 많이 사용하는 함수 중 하나이다. 표준 SQL 함수가 아니라서 사용을 꺼려하기도 하지만 잘 사용하면 아주 편하기 때문에 유용하다. 최근에는 CASE WHEN 구문 사용을 많이 권장하기도 한다. DECODE 함수는 프로그래밍에서의 if else 와 비슷한 기능을 수행한다. 간단한 사용방법은 아래와 같다. 예) DECODE(컬럼, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3..........) WITH temp AS ( SELECT 'M' gender FROM dual UNION ALL SELECT 'F' gender FROM dual UNION ALL SELECT 'X' gender FROM dual ) SELECT gender , DECODE(gender,..
오라클에서 데이터 조회 시 데이터 중복을 제거하기 위해서는 대표적으로 2가지 방법이 있다. DISTINCT 키워드를 사용하여 중복을 제거하는 방법과, GROUP BY 절을 사용하여 데이터 중복을 제거하는 방법이다. DISTINCT를 사용하여 중복 제거 GROUP BY 절을 사용하여 중복 제거 오라클 쿼리문에서 데이터의 중복을 제거할 때는 DISTINCT 키워드를 사용할 것을 권장한다. DISTINCT를 사용하면 쿼리문을 분석할 때 중복을 제거한 결과가 반환된다는 것을 직관적으로 파악이 가능하다. 그러나 상황에 따라서 GROUP BY 절을 사용하여 중복을 제거할 수도 있다. DISTINCT를 사용하여 중복 제거 SELECT DISTINCT a.deptno , b.dname FROM emp a , dep..
오라클(ORACLE)에서 전화번호를 저장 시 하이픈 없이 숫자만을 저장하는 경우가 많다. 전화번호를 조회해서 보기 좋게 하이픈을 다시 넣어서 조회를 해야하는 경우가 많다. 오라클 10g 이상의 버전에서는 정규식 함수(REGEXP_REPLACE)를 사용하여 전화번호 포맷을 쉽게 변경할 수 있다. 핸드폰 번호만 있을 경우 WITH temp_table AS ( SELECT '01023456789' tel_no FROM dual UNION ALL SELECT '0114567890' tel_no FROM dual UNION ALL SELECT '0161234567' tel_no FROM dual ) SELECT tel_no , REGEXP_REPLACE(tel_no, '(.{3})(.+)(.{4})', '\1-\..
오라클(Oracle) 쿼리문을 작성하다 보면 사용자 정의 함수를 많이 만들어서 사용하게 된다. 함수 사용이 쿼리 속도를 느리게 만드는 원이이 될수도 있기 때문에 사용을 권장하지 않지만, 특정 기능들을 모듈화 할 수 있어서 복잡한 쿼리문을 간결하게 만들고 차후 쿼리문을 수정할 때 아주 큰 도움이 된다. 함수 사용시 캐싱 기능을 이용하면 함수 내부의 쿼리를 실행하지 않고 입력값이 동일하면 캐시에 이미 저장된 결과값을 사용할 수 있다. 캐싱 기능을 이요하기 위해서는 두가지 방법이 있다. 스칼라 서브쿼리 캐싱효과 이용 함수 결과 캐싱 (function result cache) 이용 (오라클 11g 이상에서 사용가능) 캐싱 기능을 사용시 주의할점 함수의 입력 값 종류가 적을때 효과적이며, 입력 값의 종류가 다양하..
오라클(Oracle) 컬럼 코멘트 조회 오라클 쿼리를 작성하다 보면 컬럼의 의미 파악이 안될때가 있다. 이럴때는 오라클 코멘트 시스템뷰를 사용하여 컬럼 코멘트를 쉽게 조회 할 수 있다. 컬럼 코멘트 시스템뷰는 ALL_COL_COMMENTS, USER_COL_COMMENTS, DBA_COL_COMMENTS가 있으며 동일한 내용을 조회하는 뷰이다. 사용자의 권한 및 상황에 따라서 위에 3개의 뷰중 하나를 사용하면 된다. SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'EMP' [컬럼 타입을 포함하여 상세조회] SELECT A.COLUMN_ID , A.COLUMN_NAME , B.COMMENTS , A.DATA_TYPE || (CASE WHEN DATA_TYPE LI..