오라클(Oracle) 인덱스 조회 쿼리 오라클 쿼리를 작성하기 전에 필수적으로 인덱스(Index)를 확인해야 한다. 인덱스를 무시하고 쿼리를 작성하면 심각한 성능 이슈가 발생 할 수 있다. 인덱스는 오라클 시스템뷰의 하나인 ALL_IND_COLUMNS 뷰를 사용하여 쉽게 조회가 가능하다. SELECT a.table_name , a.index_name , a.column_name FROM all_ind_columns a WHERE a.table_name = 'EMP' ORDER BY a.index_name , a.column_position [컬럼 코멘트를 포함하여 조회] SELECT a.table_name , a.index_name , a.column_name , b.comments FROM all_in..
오라클(Oracle) PK 조회 쿼리 제약조건(Constraints) 뷰를 사용하여 PK 컬럼을 쉽게 조회할 수 있다. 테이블명을 대문자로 TABLE_NAME 조건에 넣으면 PK명과 PK컬럼이 조된다. 해당 테이블에 액세스 권한이 있어야 한다. SELECT A.TABLE_NAME , A.CONSTRAINT_NAME , B.COLUMN_NAME , B.POSITION FROM ALL_CONSTRAINTS A , ALL_CONS_COLUMNS B WHERE A.TABLE_NAME = 'JOB_HISTORY' AND A.CONSTRAINT_TYPE = 'P' AND A.OWNER = B.OWNER AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME ORDER BY B.POSITION 사..
오라클에서 문자열을 자를 때는 SUBSTR 함수를 사용한다. 다른 DB의 SQL 함수인 SUBSTRING, LEFT, RIGHT 함수와 동일한 기능을 하며, 문자열을 바이트 단위로 자를 때는 SUBSTRB 함수를 사용하면 된다. SUBSTR 함수는 자를 문자열의 시작위치와 자를 길이를 사용하여 문자열을 자르며, 뒤에서 문자열을 자를 때는 시작 위치를 음수(-) 값으로 부여하면 된다. 목차 문자열 자르기 (SUBSTR) 뒤에서부터 문자열 자르기 (SUBSTR) 바이트 단위로 문자열 자르기 (SUBSTRB) 문자열 자르기 (SUBSTR) WITH emp AS ( SELECT '7566' empno, 'JONES' ename, '매니저' job FROM dual ) SELECT ename , SUBSTR(en..
오라클(Oracle) TRIM, LTRIM, RTRIM 함수 사용방법 TRIM 함수는 문자열의 양쪽 공백을 제거하는 기본적인 함수이다. LTRIM 함수, RTRIM 함수는 왼쪽과 오른쪽의 공백을 제거할 때 사용가능 하지만, 반복적인 문자나 특정 문자를 제거할 때 자주 사용한다. TRIM 함수 TRIM 함수는 문자열의 양쪽 공백(스페이스바)을 제거한다. - 함수 : TRIM("문자열") WITH DEPT AS ( SELECT '0010' DEPTNO, 'ACCOUNTING ' DNAME, ' NEW YORK ' LOC FROM DUAL ) SELECT DNAME , TRIM(DNAME) , LOC , TRIM(LOC) FROM DEPT LTRIM 함수 LTRIM 함수는 문자열의 왼쪽(좌측) 공백 제거, 문..
오라클에서 소수점을 절사(버림)하거나 날짜의 시간을 절사 하기 위해서는 TRUNC 함수를 사용한다. 소수점, 시간 등 단순한 절사 방법 외에 TRUNC 함수의 옵셥을 사용하여 다양한 절사 방법이 있으니 아래를 참고하면 된다. 목차 기본 사용법 시간 절사 방법 일자/요일 절사 방법 숫자/소수점 절사 방법 기본 사용법 기본 사용법 WITH temp AS ( SELECT TO_DATE('2018-12-13 22:10:59', 'YYYY-MM-DD HH24:MI:SS') dt, 1234.56 nmb FROM dual ) SELECT dt , TRUNC(dt) --시간 절사 , nmb , TRUNC(nmb) --소수점 절사 FROM temp 사용법 : TRUNC("값", "옵션") TRUNC 함수는 주로 소수점 ..
오라클에서 왼쪽, 오른쪽에 특정문자를 채워서 문자열 길이를 맞출 때는 LPAD, RPAD 함수를 사용한다. 주로 숫자 앞에 '0'을 채워서 문자열 길이를 맞출 때 자주 사용한다. 문자열의 자릿수를 고정할 때도 사용한다. LPAD(123, 5, '0') → 결과: 00123 LPAD(123, 8, '0') → 결과: 00000123 LPAD 함수 WITH emp AS ( SELECT '7839' empno, 'JAMES' ename, '30' deptno FROM dual ) SELECT empno , ename , deptno , LPAD(deptno, 5) -- 왼쪽에 공백을 채움 , LPAD(deptno, 5, ' ') -- 왼쪽에 공백을 채움 , LPAD(deptno, 5, '0') -- 왼쪽에 '..
오라클 NVL, NVL2 함수 사용법 해당 칼럼의 값이 NULL 값인 경우 특정값으로 출력하고 싶으면 NVL 함수를 사용하고, NULL 값이 아닐 경우 특정값으로 출력하고 싶으면 NVL2 함수를 사용하면 된다. NVL 함수 NVL 함수는 값이 NULL인 경우 지정값을 출력하고, NULL이 아니면 원래 값을 그대로 출력한다. - 함수 : NVL("값", "지정값") WITH emp AS ( SELECT 7839 empno, 'KING' ename, 'PRESIDENT' job, NULL comm FROM dual UNION ALL SELECT 7654 empno, 'MARTIN' ename, 'SALESMAN' job, 1400 comm FROM dual ) SELECT empno , ename , com..
1. ROWNUM 키워드 이용하는 방법 - 조회된 순서되로 순번을 매긴다. SELECT ROWNUM , a.* FROM emp a - ORDER BY 를 사용하면 순번이 뒤섞이므로 정렬된 서브쿼리 결과에 ROWNUM 을 매겨야 한다. SELECT ROWNUM , x.* FROM ( SELECT a.* FROM emp a ORDER BY a.ename ) x 2. ROW_NUMBER() 함수를 사용하는 방법 - ORDER BY 된 결과에 순번을 매길때에는 ROWNUM 보다 ROW_NUMBER() 함수가 더 편하다. SELECT ROW_NUMBER() OVER(ORDER BY a.job, a.ename) row_num , a.* FROM emp a ORDER BY a.job, a.ename - 그룹별(PAR..
WM_CONCAT DISTINCT 오류 시 대체 방법 오라클 10g 부터 WM_CONCAT 함수를 사용하여 컬럼 값을 합칠수 있다. (오라클 12C 부터는 사용못함)WM_CONCAT(DISTINCT JOB) 을 쿼리툴에서는 잘 실행이 되지만 Procedure, Function 에서 사용시 에러가 발생한다. PL/SQL: ORA-30482: DISTINCT 옵션은 이 함수에 사용할 수 없습니다 WM_CONCAT보다는 조금 복잡하지만 아래의 방법으로 대체할 수 있다. --Oracle 10g, 11g r1 SELECT REGEXP_REPLACE( SUBSTR(XMLAGG(XMLELEMENT(JOB, ',', JOB) ORDER BY JOB).EXTRACT('//text()'), 2) , '([^,]+)(,\1..
오라클 버전 확인 쿼리 오라클 함수를 사용하기 위해 오라클 버전을 확인해야 하는 경우가 있다. 아래의 쿼리문을 사용하여 오라클 버전을 확인하면 된다. 첫 번째 방법 SELECT * FROM PRODUCT_COMPONENT_VERSION 두 번째 방법 SELECT * FROM v$version