오라클에서 아우터 조인 시 BETWEEN 연산자를 사용해야 할 경우가 있다. 일반적인 아우터 조인과 상황이 조금 틀리기 때문에 당황할 수 있지만 조인 방법을 큰 차이가 없다. BETWEEN 연사자를 사용할 경우 오라클 Outer Join과 ANSI Outer Join 방법은 아래의 예제를 참고하면 쉽게 이해할 수 있을 것이다. 목차 오라클 Outer Join을 사용하는 방법 ANSI Outer Join을 사용하는 방법 오라클 Outer Join을 사용하는 방법 WITH employees AS ( SELECT 101 AS employee_id, 'Neena' AS first_name, 'Kochhar' AS last_name, TO_DATE('2005-09-21', 'YYYY-MM-DD') AS hire_..
오라클 10g부터 정규식을 사용할 수 있도록 함수가 추가되었다. 정규식을 사용하면 문자열을 패턴으로 찾거나 자를 수 있기 때문에 기존의 복잡하게 구현된 쿼리문을 정규식 함수를 사용하여 간단하게 처리할 수 있다. 일반적인 프로그래밍 언어에서 사용하는 정규식을 그대로 사용할 수 있으나, 전방 탐색 또는 후방 탐색 등 일부 지원하지 않는 패턴이 있다. 아래의 기본 기능을 이해했다면 정규식을 응용하여 사용하는데 어렵지 않을 것이다. 목차 기본 메타 문자 수량자 그룹과 역참조 정규식 함수 기본 메타 문자 메타 문자 설명 . 임의의 한 문자 | OR과 동일 (왼쪽 또는 오른쪽과 일치) [] 문자 클래스 [-] 문자 범위 (0-9, a-z, A-Z, 가-힝) [^] 부정 문자 클래스 \ 다음에 오는 문자를 이스케이프..
오라클에서 GROUP BY 절과 SUM 함수를 사용하여 숫자를 합산할 수 있다. 그러나 문자열인 경우 SUM 함수를 사용하여 문자열을 합칠 수는 없고 대신에 LISTAGG, XMLAGG, WM_CONCAT 함수를 사용하여 그룹별 문자열 칼럼의 값을 하나의 칼럼으로 합칠 수 있다. 문자열을 합치는 함수는 오라클 버전마다 조금씩 사용법이 다르므로 아래의 예제를 참고하여 상황에 맞는 방법을 사용하면 된다. 목차 LISTAGG 함수를 사용하여 문자열 합치기 (Oracle 11g R2 이상) XMLAGG 함수를 사용하여 문자열 합치기 (Oracle 9i 이상) WM_CONCAT 함수를 사용하여 문자열 합치기 (Oracle 10g ~ 11g R1) LISTAGG 함수를 사용하여 문자열 합치기 (Oracle 11g ..
오라클에서 그룹별 가장 최근 데이터 또는 마지막 데이터 1건만 조회하기 위해서는 분석함수(ROW_NUMBER, RANK)를 사용하면 쉽게 해결할 수 있다. 또 다른 방법으로 서브 쿼리를 사용하여 MAX 값을 추출하여 일치하는 데이터만 조회하는 방법도 있다. 마지막 날짜 데이터, 그룹별 최솟값 최댓값 등 1건의 데이터를 조회하는 방법은 아래의 예시를 참고하여 활용하면 된다. 목차 ROW_NUMBER 함수를 사용하여 최근 데이터 가져오기 RANK 함수를 사용하여 최근 데이터 가져오기 서브쿼리와 MAX 함수를 사용하여 최근 데이터 가져오기 ROW_NUMBER 함수를 사용하여 최근 데이터 가져오기 SELECT job , empno , ename , hiredate FROM ( SELECT empno , enam..
오라클에서 그룹별로 평균을 구하기 위해서는 AVG 함수를 사용하면 된다. 일반적으로 GROUP BY 절과 AVG 함수를 사용하여 그룹별 평균을 구하며, 전체 데이터를 그룹화하지 않고 조회된 데이터에서 그룹별 평균값을 표시하고 싶을 때는 분석함수(OVER 절)를 사용하면 된다. 평균값의 소수점을 없애기 위해서는 ROUND 함수를 사용하여 반올림하거나, TRUNC 함수를 사용하여 소수점을 절사 할 수 있다. 목차 GROUP BY 절을 사용하여 그룹별 평균 구하기 분석함수를 사용하여 그룹별 평균 구하기 GROUP BY 절을 사용하여 그룹별 평균 구하기 그룹별 평균 구하기 SELECT job , AVG(sal) FROM emp GROUP BY job 직업(job)별 평균 급여(sal)를 구하는 예제이다. 그룹별..
오라클에서 HTML 문자열 내부의 특정 태그 값을 추출하기 위해서는 정규식 함수를 사용하면 쉽게 해결된다. 정규식 함수는 Oracle 10g 이상에서 사용할 수 있으며, Oracle 11g 이상에서는 XMLTYPE 함수를 사용하여 문자열에서 태그 값을 추출할 수 있다. XMLTYPE 함수는 사용하기는 편하지만 특정 패터만 추출이 가능하며, 조금 더 세밀한 태그 값을 추출하기 위해서는 정규식 함수(REGEXP_SUBSTR)를 사용하면 된다. 목차 REGEXP_SUBSTR 함수를 사용하여 태그 값 추출 XMLTYPE 함수를 사용하여 태그 값 추출 REGEXP_SUBSTR 함수를 사용하여 태그 값 추출 정규식을 사용하여 문자열에서 태그 값 추출 (Oracle 10g 이상) WITH temp AS ( SELEC..
오라클에서 문자열에서 괄호 안의 텍스트, 글자, 문자, 숫자를 추출할 때는 정규식 함수를 사용하면 쉽게 추출할 수 있다. 그러나 오라클 9i 이하인 경우 정규식 함수를 사용할 수 없기 때문에 SUBSTR 함수를 사용해서 추출해야 한다. 정규 표현식을 사용하여 문자를 추출할 때는 딱 정해진 방법이 없고, 상황에 따라서 가장 최적의 방법을 사용하면 된다. 아래의 예제를 참고하여 상황에 따라서 정규 표현식을 추가하거나 변형하여 사용하면 된다. 목차 REGEXP_SUBSTR 함수를 사용하여 괄호 안 문자 추출 SUBSTR 함수를 사용하여 괄호 안 문자 추출 REGEXP_SUBSTR 함수를 사용하여 괄호 안 문자 추출 문자열에서 괄호와 문자를 같이 추출할 경우 WITH temp AS ( SELECT 'Oracle..
오라클에서 칼럼의 문자열에서 한글이 아닌 문자를 제거하고 한글만 추출해야 하는 경우가 있다. 문자열에서 한글만 추출할 때는 정규식 함수를 사용하면 쉽게 해결된다. 오라클에서 정규식 함수는 대부분 10g 이상의 버전에서 사용 가능하니 이점에 유의하여 아래의 기본적인 예제를 참고하여 활용하면 된다. 목차 문자열에서 한글만 추출 방법 문자열에 한글이 포함된 데이터만 조회 방법 문자열에서 한글만 추출 방법 WITH temp AS ( SELECT '오라클 데이터베이스 21c' AS ora_nm FROM dual UNION ALL SELECT 'Oracle Database 21c' AS ora_nm FROM dual UNION ALL SELECT 'Oracle 21c (오라클)' AS ora_nm FROM dual..
오라클에서 날짜, 전화번호와 같은 문자열에서 특수문자 또는 문자를 제거하고 숫자만 추출해야 할 경우가 있다. 문자열에서 숫자만 추출하기 위해서는 Oracle 10g부터 추가된 정규식 함수를 사용하면 쉽게 해결이 된다. Oracle 10g 이전 버전에서는 TRANSLATE 함수를 사용하여 문자열에서 숫자만 추출할 수 있다. 정규식 함수를 사용하면 문자열에서 원하는 위치의 숫자만 추출할 수 있기 때문에 조금 더 다양한 결과를 얻을 수 있다. 목차 REGEXP_REPLACE 함수를 사용하여 숫자만 추출 (Oracle 10g 이상) TRANSLATE 함수를 사용하여 숫자만 추출 (Oracle 8i 이상) 문자열의 마지막 숫자만 추출 (REGEXP_SUBSTR 함수) REGEXP_REPLACE 함수를 사용하여 숫..
오라클에서 다른 스키마에 생성된 패키지의 실행 또는 사용 권한을 부여하기 위해서는 "GRANT EXECUTE" 구문을 사용하여 실행 권한을 부여하면 된다. 실행 권한을 부여하면 패키지 내부에 선언된 모든 함수, 프로시저를 사용할 수 있다. 패키지의 생성 권한을 부여하기 위해서는 "GRANT CREATE PROCEDURE" 구문을 사용하여 생성 권한을 부여하면 된다. 패키지의 권한 부여 방법은 프로시저의 권한 부여 방법과 동일하다. 목차 패키지 실행 권한 부여 (EXECUTE) 패키지 생성 권한 부여 (CREATE) 패키지 실행 권한 부여 (EXECUTE) -- 패키지 실행 권한 부여 GRANT EXECUTE ON hr.pkg_hello TO scott; -- 패키지 실행 권한 제거(회수) REVOKE E..