오라클의 GROUPING, GROUPING_ID 함수는 소계와 합계를 집계할 때 사용하는 ROLLUP, CUBE, GROUPING SETS 함수와 함계 사용된다. 소계와 합계로 집계되어 출력된 행을 구분할 때 사용한다. 기본 사용법 GROUPING 함수는 1개의 매개변수만 있으며, GROUPING_ID 함수는 여러개의 매개변수를 입력할 수 있다. GROUPING 함수 SELECT job , deptno , SUM(sal) , GROUPING(job) , GROUPING(deptno) FROM emp WHERE job IN ('ANALYST', 'MANAGER') GROUP BY ROLLUP(job, deptno) GROUPING 함수는 소계, 합계로 집계된 행의 컬럼 NULL을 구분할 수있다. NULL인..
오라클에서 합계와 소계를 구하기 위해서는 ROLLUP 함수를 많이 사용한다. ROLLUP 함수는 소계와 합계를 순서에 맞게 반환하지만 CUBE 함수는 계산 가능한 모든 소계와 합계를 반환한다. 기본사용법 SELECT job , SUM(sal) FROM emp GROUP BY CUBE(job) ROLLUP 함수와 다르게 합계를 먼저 표시한다. 그룹 컬럼이 여러개인 경우 SELECT job , deptno , SUM(sal) FROM emp GROUP BY CUBE(job, deptno) 그룹 컬럼으로 산출할 수 있는 모든 소계와 합계를 반환한다. 원하는 소계, 합계 추출하기 - 1 SELECT job , deptno , SUM(sal) FROM emp GROUP BY CUBE(job, deptno) HAV..
오라클에서 전체 합계 대비 비율 또는 백분율을 구하기 위해서는 RATIO_TO_REPORT 함수를 이용하면 된다. RATIO_TO_REPORT 이용하면 비율이 반환되는데, 여기에 100을 곱하면 백분율(%)로 바꿀 수 있다. 비율 구하기 SELECT ename , sal , RATIO_TO_REPORT(sal) OVER() AS sal_ratio FROM emp WHERE job = 'MANAGER' 조회된 전체 급여(sal) 합계 대비 해당 행의 비율이 반환된다. 백분율 구하기 SELECT ename , sal , ROUND(RATIO_TO_REPORT(sal) OVER(), 2) * 100 || '%' AS sal_rate FROM emp WHERE job = 'MANAGER' 비율에서 소수점 둘째자..
오라클에서 여러 개의 SELECT 문 결과를 합치기 위해서는 UNION, UNION ALL을 사용하면 된다. UNION을 사용할 경우 합쳐진 결과에서 중복을 제거한 결과를 반환하고, UNION ALL을 사용하면 중복을 제거하지 않고 합쳐진 결과를 그대로를 반환한다. UNION : 각 쿼리의 결과 합을 반환하는 합집합 (중복제거) UNION ALL : 각 쿼리의 모든 결과를 포함한 합집합 (중복제거 안함) 기본 사용법 UNION 사용법 (중복 제거) SELECT job , deptno FROM emp WHERE sal >= 3000 UNION SELECT job , deptno FROM emp WHERE deptno = 10 PRESEIDENT, ANALYST의 중복된 데이터는 제거되고, 중복되지 않은 데..
오라클 11g부터 PIVOT과 UNPIVOT 기능을 사용할 수 있다. PIVOT은 행을 열로 변환하는 기능이며, UNPIVOT은 열을 행으로 변환하는 기능이다. 아래는 UNPIVOT을 설명한 예제이다. [Oracle] 오라클 PIVOT 사용법 (행을 열로 변환) UNPIVOT을 처음 사용할 때는 많이 헷갈릴 수 있으니, 아래의 예제를 차근차근 따라 해 보면서 이해하면 된다. SELECT * FROM ( 피벗 대상 쿼리문 ) UNPIVOT ( 컬럼별칭(값) FOR 컬럼별칭(열) IN (피벗열명 AS '별칭', ... ) 컬럼별칭(값) : UNPIVOT을 할 때 열의 값을 표시할 컬럼명을 지정하는 부분이다. 위에 예제에서 HIRE_CNT에 해당된다. 사용자가 임의로 별칭을 지정하면 된다. 컬럼별칭(열) : ..
오라클 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..