오라클 SQL에서 LISTAGG 함수를 사용할 때 WITHIN GROUP 절을 함께 사용하는 것을 많이 볼 수 있다. WITHIN GROUP 절은 평소에 많이 사용하지 않기 때문에 다소 생소하게 느껴질 수 있다. WITHIN GROUP 절은 LISTAGG, RANK, DENSE_RANK 함수와 함께 사용할 수 있다. 그 외에 사용할 수 있는 곳은 아직 찾지는 못했다... 아직 사용할 수 있는 곳이 많지 않기 때문에 아래의 예제만 이해하고 있어도 될 듯하다. WITHIN GROUP 절은 집계 대상 데이터의 정렬을 지정한다. WITHIN GROUP ( ORDER BY 컬럼, 컬럼... [ ASC | DESC ] ) LISTAGG 함수 예제 LISTAGG 함수를 사용하여 컬럼의 문자열을 합칠 때 WITHIN ..
오라클 SQL에서 GROUP BY 절을 사용하여 그룹별 건수나 합계를 얻을 수 있다. 그룹별 집계된 결과 중 원하는 조건의 결과만 필터링하기 위해서는 HAVING 절을 사용하여 필터 조건을 사용할 수 있다. HAVING 절과 WHERE 절의 다른 점은 HAVING 절은 GROUP BY 절과 함께 사용해야 하며 집계 함수를 사용하여 조건절을 작성하거나 GROUP BY 컬럼만 조건절에 사용할 수 있다. SELECT job , COUNT(*) cnt FROM emp WHERE deptno IN ('10', '20', '30') GROUP BY job HAVING COUNT(*) > 2 위의 쿼리문은 직군(job) 별 직원 수가 3명 이상일 경우만 조회하는 예제이다. HAVING 절에는 COUNT, SUM, A..
오라클에서 뷰(VIEW)는 테이블과 흡사한 오브젝트이다. 뷰는 실제로 데이터를 저장하고 있지는 않지만 DML 작업이 가능한 가상의 테이블이라고 생각하면 된다. 뷰는 복잡한 쿼리를 단순화 시킬수 있다. 뷰는 사용자에게 필요한 정보만 접근하도록 접근을 제한할 수 있다. - OR REPLACE : 해당 구문을 사용하면 뷰를 수정할 때 DROP 없이 수정이 가능하다. - FORCE : 뷰를 생성할 때 쿼리문의 테이블, 컬럼, 함수 등이 존재하지 않아도 생성이 가능하다. - NORORCE : 뷰를 생성할 때 쿼리문의 테이블, 컬럼 함수 등이 존재하지 않으면 생성되지 않는다. - column_aliases : SELECT 컬럼의 별칭을 미리 정의할 수 있다. - WITH READ ONLY : SELECT 만 가..
오라클에서 뷰(VIEW)를 생성할 때 FORCE, NOFORCE 옵션을 부여할 수 있다. 옵션을 부여하지 않으면 기본값은 NOFORCE로 설정된다. CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰이름 [(column_aliases)] AS SELECT 문 ; FORCE : 쿼리문의 테이블, 컬럼, 함수 등이 일부 존재하지 않아도 생성 됨 NOFORCE : 쿼리문의 테이블, 컬럼 함수 등이 모두 정상적으로 생성되어 있어야 생성됨 FORCE 옵션을 사용 후 컴파일 오류가 발생하면 INVALID 상태로 뷰가 생성 되고, 오류가 없을경우 정상적으로 생성 된다. NOFORCE 옵션을 사용 후 컴파일 오류가 발생하면 뷰는 생성되지 않는다. NOFORCE 사용 NOFORCE 옵션을 사용했..
오라클의 프로시저(Procedure)는 일련의 작업들을 하나로 묶어서 모듈화한 후 필요할 때 마다 호출하여 사용할 수 있는 기능이다. 함수(Function)와 비슷해 보이지만 프로시저는 리턴 값이 없다. (C언어의 void 함수와 비슷) 그러나 프로시저에서 레퍼런스 변수를 사용하면 결과 값을 리턴할 수 있다. 프로시저 생성 예시 부서코드 UPDATE 프로시저 (단순 작업 수행) CREATE OR REPLACE PROCEDURE pc_update_deptno ( p_job IN VARCHAR2, p_deptno IN VARCHAR2 ) IS BEGIN UPDATE emp SET deptno = p_deptno WHERE job = p_job; EXCEPTION WHEN OTHERS THEN ROLLBACK..
오라클에서 이전 행의 값을 찾거나 다음 행의 값을 찾기 위해서는 LAG, LEAD 함수를 사용하면 된다. LAG(expr [,offset] [,default]) OVER([partition_by_clause] order_by_clause) LEAD(expr [,offset] [,default]) OVER([partition_by_clause] order_by_clause) LAG 함수 : 이전 행의 값을 리턴 LEAD 함수 : 다음 행의 값을 리턴 expr : 대상 컬럼명 offset : 값을 가져올 행의 위치 기본값은 1, 생략가능 default : 값이 없을 경우 기본값, 생략가능 partition_by_clause : 그룹 컬럼명, 생략가능 order_by_clause : 정렬 컬럼명, 필수 기본 ..
오라클의 쿼리 결과에서 첫번째 행의 값을 가져올때는 FIRST_VALUE 함수를 사용하고, 마지막 행의 값을 가져올때는 LAST_VALUE 함수를 사용하면 된다. FIRST_VALUE, LAST_VALUE는 오라클에서만 지원하는 함수이기 때문에 타 DB에서는 사용할 수 없다. 기본적인 사용법 SELECT ename , job , sal , FIRST_VALUE(sal) OVER() AS sal_first , LAST_VALUE(sal) OVER() AS sal_last FROM emp WHERE job in ('MANAGER', 'ANALYST') 쿼리 결과의 첫번째 행의 급여(sal) 값과 마지막 행의 급여(sal)의 값을 표시한다. ORDER BY, PARTITION BY 사용 SELECT ename..
Oracle SQL Developer 쿼리툴에서 PROCEDURE의 CURSOR를 출력하기 위해서는 REFCURSOR 변수를 선언하여 프로시저의 인자에 할당하면 된다. VAR rc REFCURSOR EXEC PC_TEST('10', :rc) PRINT rc 스크립트 실행(F5)으로 프로시저를 실행하면 커서의 결과가 출력된다
오라클에서 PIVOT을 사용하다보면 IN절에 동적으로 값을 할당하고 싶을때가 있다. 그러나 PIVOT의 IN절은 SQL의 IN절과 다르다. PIVOT의 IN절은 할당된 값의 개수와 순서를 맞춰서 결과가 출력된다. 일반적인 쿼리문의 PIVOT은 IN절에 동적으로 값을 할당 할 수 없지만, PIVOT XML 또는 Dynamic SQL을 활용하여 동적 PIVOT을 구현 할 수 있다. 일반적인 PIVOT 쿼리 SELECT * FROM (SELECT job , deptno , sal FROM emp ) PIVOT ( SUM(sal) FOR deptno IN ('10', '20', '30', '40') ) ORDER BY job PIVOT 쿼리는 IN절 ('10', '20', '30', '40')의 값과 순서에 따..
오라클에서 날짜별로 건수를 집계하면 존재하는 날짜만 조회되고 존재하지 않는 날짜는 표시되지 않는다. 상황에 따라서 건수가 존재하는 않는 날짜도 "0"으로 조회 되어야 할 상황이 있다. 가상의 날짜뷰를 만들어서 아우터 조인을 하면 해당 날짜가 데이터에 존재하지 않는 날짜도 표시가 가능하다. 필요에 따라서 이런 케이스가 많다면 물리적인 테이블을 생성해 놓고 사용하는 방법도 있다. 일반적인 일별 통계 (존재하는 일자만 표시) SELECT TO_CHAR(hiredate, 'YYYY-MM-DD') AS hiredate , COUNT(*) FROM emp WHERE hiredate BETWEEN TO_DATE('1981-02-01', 'YYYY-MM-DD') AND TO_DATE('1981-02-28', 'YYYY..