오라클 SQL에서는 칼럼에 변형이 생기는 경우 인덱스를 사용하지 못한다. 그러나 함수 기반 인덱스(FBI)를 사용하면 인덱스를 사용하면 칼럼에 함수나 표현식을 사용해도 인덱스를 사용할 수 있도록 인덱스를 생성할 수 있다. 함수 기반 인덱스를 사용할 경우 조금 더 유연하게 인덱스를 사용할 수 있는 장점이 있지만, DML 작업 시 성능 저하가 발생할 수 있으므로 DML이 많은 테이블인 경우 신중히 사용하는 것이 좋다. 목차 함수 기반 인덱스 기본 개념 함수 기반 인덱스 생성 방법 함수 기반 인덱스 기본 개념 SELECT /*+ index(a emp_ix01) */ a.empno , a.ename , a.hiredate , a.sal FROM emp a WHERE TO_CHAR(a.hiredate, 'YYYY..
오라클 SQL에서 데이터의 형 변환을 하기 위해서는 CAST 함수를 사용할 수 있다. 오라클에서 형 변환을 할 때는 TO_CHAR, TO_DATE, TO_NUMBER 함수를 많이 사용하지만, CAST 함수는 다른 DBMS와 호환이 가능한 표준 함수이며, CAST 함수만의 장점이 있기 때문에 익혀두면 많은 도움이 될 수 있다. 목차 CAST 함수 기본 사용법 CAST 함수 형 변환 시 오류 처리 방법 CAST 함수 형 변환 시 fmt 사용법 CAST 함수 기본 사용법 SELECT CAST(7566 AS VARCHAR2(4)) AS number_to_char , CAST('1981-04-02' AS DATE) AS char_to_date , CAST('2975' AS NUMBER(5)) AS char_to_..
오라클의 DECODE 함수 안에 SELECT 문을 사용하기 위해서는 스칼라 서브쿼리를 사용하면 된다. SELECT 문을 작성할 때는 쿼리문의 시작과 끝을 괄호로 감싸서 작성해야 한다. SELECT a.empno , a.ename , a.deptno , DECODE(a.deptno,'10','ACCOUNTING','20',(SELECT aa.dname FROM dept aa WHERE aa.deptno = a.deptno)) dname FROM emp a WHERE job = 'MANAGER' ORDER BY a.deptno 위의 예제는 deptno 값이 "10"인 경우 "ACCOUNTING"를 반환하고, "20"인 경우 서브쿼리(SELECT 문)의 결과를 반환한다. "30"인 경우는 조건에 없기 때문에 ..
오라클의 스칼라 서브쿼리에서 특정 칼럼으로 정렬해서 ROWNUM으로 1건의 데이터를 추출할 때 "ORA-00907: 누락된 우괄호", "ORA-00904: 부적합한 식별자" 오류가 발생할 수 있다. 정렬해야 하는 칼럼과 추출해야 하는 칼럼이 다른 경우 ORDER BY 절과 ROWNUM을 사용하는 방법은 아래의 예시를 참고하면 된다. 위의 쿼리문은 아주 그럴싸하게 작성된 쿼리문으로 보인다. 쿼리문을 실행하면 "ORA-00907: 누락된 우괄호" 오류가 발생한다. 서브쿼리에서는 ORDER BY를 사용할 수 없다.(인라인뷰 서브쿼리는 예외) 누락된 우괄호 오류가 발생하지 않더라도 일반적인 쿼리문에서는 ROWNUM이 ORDER BY 절보다 먼저 실행되므로 정렬되지 않은 1건의 데이터가 추출된다. 서브쿼리가 아니..
오라클에서 칼럼명으로 테이블을 찾을 때는 주로 칼럼 코멘트 시스템 뷰와 칼럼 정보 시스템 뷰에서 찾는다. 여러 가지 시스템 뷰를 조인해서 쿼리문을 작성하면 조금 더 많은 정보가 조회되므로 유용하게 사용할 수 있다. 목차 칼럼 코멘트 시스템 뷰에서 찾기 (ALL_TAB_COMMENTS) 칼럼 정보 시스템 뷰에서 찾기 (ALL_TAB_COLUMNS) 칼럼 데이터 타입과 코멘트를 같이 찾기 칼럼 코멘트 시스템 뷰에서 찾기 (ALL_TAB_COMMENTS) SELECT * FROM all_col_comments WHERE column_name = 'DEPTNO' --컬럼명 대문자 칼럼명이 포함된 테이블을 찾을 때는 칼럼 코멘트 시스템 뷰를 사용하면 쉽게 찾을 수 있다. 해당 칼럼명이 포함된 테이블명과 칼럼 설명..
오라클에서 칼럼의 Default 값을 추가하거나 삭제하기 위해서는 ALTER TABLE ~ MODIFY 명령을 사용하면 된다. 목차 칼럼 Default 값 추가 방법 칼럼 Default 값 삭제 방법 칼럼 Default 값 추가 방법 ALTER TABLE emp MODIFY (job DEFAULT 'NEWCOMER') ALTER TABLE 스키마명.테이블명 MODIFY (칼럼명 DEFAULT 디폴트값) 스키마명은 생략할 수 있으며, 아래와 같이 스키마명을 붙여서 사용할 수도 있다. ALTER TABLE scott.emp MODIFY (job DEFAULT 'NEWCOMER') --스키마명을 붙여서 사용할 경우 칼럼 Default 값 조회 SELECT owner , table_name , column_na..
오라클에서 테이블 칼럼 설명(COMMENT)을 추가하거나 수정할 때는 COMMENT ON COLUMN 명령을 사용하면 된다. 추가하거나 수정할 때는 동일한 명령어를 사용하면 된다. 칼럼 코멘트 추가 방법 COMMENT ON COLUMN emp.empno IS '사원번호' COMMENT ON COLUMN 스키마명.테이블명.칼럼명 IS '칼럼 코멘트' 스키마명은 생략할 수 있으며, 아래와 같이 스키마명을 붙여서 사용할 수도 있다. COMMENT ON COLUMN scott.emp.empno IS '사원번호' --스키마명을 붙여서 사용할 경우 칼럼 코멘트 조회 SELECT * FROM all_col_comments WHERE table_name = 'EMP' empno 칼럼의 코멘트가 추가된 것을 확인할 수 ..
오라클에서 NULL을 치환하기 위해서 NVL 함수를 자주 사용한다. NVL 함수와 유사한 NVL2 함수가 존재하며, NVL2 함수의 사용법이 익숙하지 않아서 헷갈리는 경우가 많다. 아래는 2개의 함수를 비교하고, 각각 함수를 사용하는 예시이니 참고하기 바란다. NVL 함수와 NVL2 함수 비교 SELECT ename , comm , NVL(comm, 0) AS nvl_comm , NVL2(comm, 'Y', 'N') AS nvl2_comm FROM emp WHERE job IN ('MANAGER', 'SALESMAN') NVL 함수는 칼럼의 값이 NULL인 경우 특정 값으로 치환하기 위해서 사용한다. NVL2 함수는 칼럼의 값이 NULL인 경우와 NULL이 아닌 경우 두 가지 케이스 모두 특정 값을 반환..
오라클의 WHERE 절에서 날짜 조건을 검색하기 위해서는 BETWEEN 연산자와 부등호 연산자를 사용한다. 조회할 날짜 칼럼에 시간이 포함되어 있다면 조건 값에 시간까지 포함해야 누락된 데이터가 없이 조회된다. 아래는 실무에서 날짜를 조회할 때 자주 사용하는 방법이니 참고해서 사용하면 된다. 목차 BETWEEN 연산자를 사용하여 날짜 조회 방법 부등호(=, >, , = TO_DATE('2023-11-01', 'YYYY-MM-DD') AND hiredate = 시작일자 AND 칼럼 = TO_DATE('2023-11-01', 'YYYY-MM-DD') AND hiredate < TO_DATE('2023-11-30', 'YYYY-MM-DD') 이퀄(=) 연산자 없이 부등호 연산자만 사용하여 날짜를 검색할 수 있..
오라클의 WHERE 절에 if 문을 사용하기 위해서는 동적 쿼리문을 사용하거나 Procedure를 만들면 내부에서 if 문을 사용할 수 있다. 순수 SQL 문에서는 if 문을 사용할 수 없으나, WHERE 절에 CASE WHEN, OR, UNION ALL을 사용하면 if 문과 유사한 효과를 낼 수 있다. 아래는 자주 사용하는 if 문과 유사한 효과를 낼 수 있는 3가지 방법이다. 목차 OR 연산자를 사용하여 if 문 효과 내기 CASE WHEN을 사용하여 if 문 효과 내기 UNION ALL을 사용하여 if 문 효과 내기 ※ 아래는 SQL Developer 쿼리툴을 사용하여 실행한 결과이다. 쿼리툴의 종류에 따라서 define 입력 변수를 사용하는 방법이 조금씩 다를 수 있으니 이점을 유의하여 참고하면 ..