오라클에서 테이블 목록을 조회할 때는 오라클에서 제공하는 3가지 시스템 뷰를 많이 사용한다. 3가지 시스템 뷰는 all_tables, all_tab_comments, all_objects이며 접근 권한에 따라서 접두어(all)를 바꿔서 사용할 수 있다. 테이블 목록 조회 방법 3가지 SELECT * FROM all_tables all_tables는 권한을 가진 모든 테이블이 조회되며 통계 정보를 쉽게 확인할 수 있다. SELECT * FROM all_tab_comments WHERE table_type = 'TABLE' all_tab_comments는 권한을 가진 모든 테이블 목록과 테이블 설명(COMMENT)을 조회할 때 사용한다. SELECT * FROM all_objects WHERE object_..
오라클에서 문자열의 특정 구분자를 기준으로 자르기 위해서는 SUBSTR, INSTR 함수를 사용한다. 그러나 오라클 10g부터 REGEXP_SUBSTR 정규식 함수를 사용하면 간편하게 문자열을 구분자로 자를 수 있다. 예제 1 - SUBSTR, INSTR 함수 사용 WITH tab AS ( SELECT '123,456' AS str FROM dual ) SELECT a.str , SUBSTR(a.str, 1, INSTR(a.str, ',') - 1) AS str1 , SUBSTR(a.str, INSTR(a.str, ',') + 1) AS str2 FROM tab a SUBSTR([문자열], [자를시작위치], INSTR([문자열], [구분자])) 문자열 '123,456'의 구분자 쉼표(',')를 기준으로 ..
오라클 쿼리문을 보다 보면 이해하기 힘든 쿼리 형태를 발견하는 경우가 많다. SUM(1) 또한 처음 봤을때는 어떤 의미가 있는지 궁금한 적이 있다. SUM(1)은 잘못된 사용법은 아니며 상황에 따라서 한 두 번은 사용할 수도 있는 문법이다. 그러나 잘못된 사용은 쿼리문을 복잡하게 만들 수 있다. SUM(1)은 COUNT(*)와 동일하게 조회된 행의 건수를 집계할 때 사용한다. 그러나 조회된 데이터가 없을때 반환 값이 다르니 이점을 유의해야 하며 상황에 따라서 필요한 방식을 사용하면 된다. SELECT SUM(1) FROM emp SUM(1)과 COUNT(*) 모두 동일하게 조회된 데이터 건수를 반환한다. 단순히 데이터 건수를 집계할때는 COUNT(*)를 사용할 것을 권장한다. COUNT(*)를 사용하면 ..
오라클에서 그룹별로 순번을 부여하기 위해서는 OVER 절과 분석함수(DENSE_RANK, ROW_NUMBER)를 사용하면 된다. 그룹별 단일 순번을 부여하기 위해서는 DENSE_RANK 함수를 사용하고, 연속된 순번을 부여하기 위해서는 ROW_NUMBER 함수를 사용하면 된다. 예제 1 - 그룹별 단일 순번 SELECT job , empno , ename , sal , DENSE_RANK() OVER(ORDER BY job) AS rnk FROM emp ORDER BY job DENSE_RANK() OVER(ORDER BY [그룹칼럼1, 그룹칼럼2, 그룹칼럼3 ...]) DENSE_RANK 함수와 OVER 절을 사용하여 직군별(job) 단일 순번을 부여하였다. 순번을 매길 칼럼(그룹)을 OVER 절 내부..
오라클에서 문자열을 날짜형 데이터로 형 변환을 하기 위해서는 TO_DATE 함수를 사용하면 된다. TO_DATE("문자열", "날짜 포맷") 아래의 예제를 보면 쉽게 이해할 수 있다. SELECT TO_DATE('2021-12-12', 'YYYY-MM-DD') , TO_DATE('2021-12-12 17:10:00', 'YYYY-MM-DD HH24:MI:SS') FROM dual 오라클에서는 날짜 포맷의 대소문자를 구분하지 않는다. 'YYYY-MM-DD HH24:MI:SS' → 'yyyy-mm-dd hh24:mi:ss'로 변경해도 정상적으로 작동한다. 날짜의 시간이 12시간 단위면 hh24:mi:ss → hh:mi:ss로 변경하면 된다. SELECT TO_DATE('20211212', 'YYYYMMDD'..
오라클에서 날짜의 시간을 오전, 오후로 구분하기 위해서는 TO_CHAR 함수를 사용하면 된다. TO_CHAR('날짜', 'AM')을 사용하면 AM, PM으로 변환되어 값이 반환 된다. 오라클의 NLS_DATE_LANGUAGE의 언어 설정에 따라서 영문(AM, PM) 또는 한글(오전, 오후)로 반환될 수 있으니 이점을 유의해야 한다. SELECT TO_CHAR(SYSDATE, 'AM') FROM dual TO_CHAR('날짜', 'AM')를 사용하여 오전(AM), 오후(PM)를 구분할 수 있다. SELECT TO_CHAR(SYSDATE, 'AM') , TO_CHAR(SYSDATE, 'PM') FROM dual TO_CHAR(SYSDATE, 'AM'), TO_CHAR(SYSDATE, 'PM') 어느 것을 사..
오라클에서 분석함수를 사용할 때 PARTITION BY를 사용하여 그룹으로 묶어서 연산을 할 수 있다. GROUP BY 절을 사용하지 않고, 조회된 각 행에 그룹으로 집계된 값을 표시할 때 OVER 절과 함께 PARTITION BY 절을 사용하면 된다. 위의 예제를 보면 데이터를 조회한 각 행에 분석함수로 집계한 값을 추가로 각 행에 표시하며, 조회된 데이터는 GROUP BY 절을 사용하지 않았기 때문에 데이터가 변형되지 않는다. 집계된 값은 GROUP BY 절을 사용할 때와 동일한 값이며, 분석함수를 사용하지 않고 값을 표시할 때는 서브 쿼리를 사용하여 해당 값을 표시해야 하기 때문에 쿼리문이 복잡해진다. 분석함수([칼럼]) OVER(PARTITION BY 칼럼1, 칼럼2... [ORDER BY 절] ..
오라클에서 검색된 결과의 행을 정렬할 때는 ORDER BY 절을 사용한다. 정렬 방법에는 오름차순 정렬(ascending sort)과 내림차순(descending sort) 정렬 두 가지가 있다. 오름차순은 작은 값부터 큰 값으로 정렬하며, 내림차순은 큰 값부터 작은 값으로 정렬된다. SELECT 컬럼명 FROM 테이블명 WHERE 조건식 GROUP BY 컬럼명 ORDER BY 컬럼명1 [ASC|DESC], 컬럼명2 [ASC|DESC], 컬럼명3 [ASC|DESC] ... 아래는 자주 사용하는 정렬 방법을 정리하였다. 기본 사용법 오름차순 정렬 (ASC) SELECT empno , ename , job , sal FROM emp WHERE deptno = 30 ORDER BY sal -- ORDER BY..
오라클에서 문자열의 날짜를 TO_DATE 함수를 사용하여 날짜 형식으로 변경할 때 형식이 맞지 않으면 "ORA-01839: 지정된 월에 대한 날짜가 부적합합니다" 라는 오류가 발생한다. 문자열을 날짜 형식으로 변환하기 전에 미리 날짜 형식이 맞는지 체크를 한 후 변환을 해야 오류 발생을 방지할 수 있다. 오라클 12c R2부터는 형식 체크 함수가 생겨서 쉽게 체크할 수 있지만, 이하 버전에서는 형식 체크 방법을 직접 구현해야 한다. 문자열 날짜 형식 체크 방법 3가지 VALIDATE_CONVERSION 함수를 사용하는 방법 (오라클 12c R2 이상) IS_DATE 사용자 정의 함수를 생성하여 사용하는 방법 (모든 버전) REGEXP_INSTR 정규식 함수를 사용하는 방법 (오라클 10g 이상) VAL..
오라클에서 문자열에서 특정 문자의 개수를 구하기 위해서는 아래의 2가지 방법을 사용하면 쉽게 구할 수 있다. 특히 값을 특수문자로 구분하여 하나의 컬럼에 저장하였을 경우 값의 개수를 구할 때 유용하게 사용할 수 있다. REGEXP_COUNT 함수를 이용하는 방법 (오라클 11g 이상) SELECT REGEXP_COUNT('A/B/C/D', '/') FROM dual 위의 예제는 문자열에 포함된 슬래쉬("/") 개수를 세는 방법이다. 오라클 11g부터 사용 가능한 REGEXP_COUNT 정규식 함수를 사용하여 문자열에 포함된 특정 문자 개수를 쉽게 구할 수 있다. 오라클 버전이 11g 아래 버전이면 아래 두 번째 방법을 사용해야 한다. SELECT REGEXP_COUNT('A,B,C', ',') --쉼표 ..