오라클(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..
오라클(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..