오라클에서 소계, 합계, 총계의 쿼리(SQL)를 작성할 때는 ROLLUP을 많이 사용한다. ROLLUP의 경우 나열된 컬럼의 단계별로 소계, 합계를 자동으로 집계를 한다. 그에 반해 GROUPING SETS는 여러 그룹핑 쿼리를 UNION ALL 한 것과 같은 결과를 만들 수 있어 조금 더 유연하게 소계, 합계를 집계할 수 있다. SELECT job , deptno , COUNT(*) cnt FROM emp GROUP BY GROUPING SETS((job, mgr), (job, deptno), ()) GROUPING SETS( 컬럼, 컬럼, 컬럼, ... ) GROUPING SETS( (컬럼그룹), (컬럼그룹), (컬럼그룹), ... ) GROUPING SETS 사용방법 - job 컬럼별 소계와 dep..
오라클에서 EXISTS( ) 함수를 처음 접하면 조금 어려움 느낌이 들 수도 있다. EXISTS를 사용하지 않아도 비슷한 결과를 만들 수 있는 기능이 많기 때문에, EXISTS에 대해 자세히 익히지 않고 넘어가는 경우가 많다. EXISTS(서브 쿼리)는 서브 쿼리의 결과가 "한 건이라도 존재하면" TRUE 없으면 FALSE를 리턴한다. EXISTS는 서브 쿼리에 일치하는 결과가 한 건이라도 있으면 쿼리를 더 이상 수행하지 않는다. 아래의 샘플 테이블을 참고하여 예제 쿼리를 보면 쉽게 이해할 수 있을 것이다. SELECT a.empno , a.ename , a.deptno FROM emp a WHERE a.job = 'MANAGER' AND EXISTS (SELECT 1 FROM dept_history a..
COUNT( ) 함수는 오라클에서 아주 기본적인 함수이며 자주 사용한다. 그러나 전체 건수를 반환하는 COUNT(*) 외에 다양한 사용방법이 있으니 아래를 참고하여 활용하면 좋을 것 같다. COUNT( ) 함수 사용법 SELECT COUNT(*) cnt , COUNT(mgr) cnt2 , COUNT(DISTINCT job) cnt3 FROM emp COUNT(*) - 조회된 전체행 건수를 반환한다 COUNT(컬럼) - 컬럼의 값이 NULL인 행은 카운트 하지 않는다 COUNT(DISTINCT 컬럼) - 컬럼 값을 중복제거하고, 컬럼의 값 건수를 반환한다 GROUP BY ~ HAVING - COUNT( ) 함수는 집계 함수이기 때문에 GROUP BY 절과 함께 사용할 수 있다 - WHERE 절에는 COUN..
오라클은사용자 함수를 직접 생성하여 사용할 수 있다. 보통 하나의 값만 반환하는 함수를 자주 사용하며, 테이블을 반환하는 파이프라인(PIPELINED) 함수도 생성하여 사용할 수 있다. 기본 함수 생성 방법 기본 함수는 하나의 값만 반환이 가능하다. CREATE OR REPLACE FUNCTION fn_get_dept_name (p_deptno NUMBER) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; END; ① 함수생성 DDL 명령어 - CREATE 명령문만 사용 시 함수를 재컴파일 할 때 오류가 발생하기 때문에 REPLACE..
오라클 URL 자르기 오라클에서 사이트 주소(URL)의 hostname을 추출 할 수 있는 함수는 없다. 그러나 정규식 함수를 사용하여 비슷하게 주소를 잘라서 hostname을 추출 할 수 있다. 아래는 REGEXP_REPLACE 정규식 함수를 이용하여 URL을 자른는 방법이다. SELECT REGEXP_REPLACE('https://www.google.com/?hl=ko' , '(http[s]?://)?(.*?)((/|:)(.)*|$)', '\1') protocol , REGEXP_REPLACE('https://www.google.com/?hl=ko' , '(http[s]?://)?(.*?)((/|:)(.)*|$)', '\2') hostname , REGEXP_REPLACE('https://www.go..
오라클에서 성적, 급여, 매출 등 순위를 구하기 위해서 순위 함수(RANK, DENSE_RANK)를 사용하면 된다. 순위 함수의 순위는 OVER 함수 내부의 ORDER BY 컬럼 값으로 결정된다. RANK() : 중복 순위 개수만큼 다음 순위 값을 증가 시킴 DENSE_RANK() : 중복 순위가 존재해도 순차적으로 다음 순위 값을 표시함 RANK와 DENSE_RANK 차이점은 아래 예제를 보면 쉽게 알 수 있다. SELECT ENAME , SAL , RANK() OVER (ORDER BY SAL DESC) RANK , DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK FROM EMP ORDER BY SAL DESC RANK() 함수는 2순위가 2명 이므로 3순위를 ..
오라클에서 날짜의 월을 빼거나 더하기 위해서는 ADD_MONTHS 함수를 사용한다. 날짜의 일은 변경되지 않으며 연산값에 따라서 년도와 월만 변경이 된다. 함수를 사용하면서 주의할 점은 날짜의 이전달이나 다음달에 기준날짜의 일자가 존재하지 않으면 해당 월의 마지막 일자가 리턴된다. 예를 들어 "2019-03-31"의 이전달은 "2019-02-28"이 된다. ADD_MONTHS( '날짜', '숫자' ) 사용 방법 SELECT ADD_MONTHS(TO_DATE('2019-12-16','YYYY-MM-DD'),-1) PREV_MONTH --이전달 , ADD_MONTHS(TO_DATE('2019-12-16','YYYY-MM-DD'), 1) NEXT_MONTH --다음달 FROM DUAL 이전달 (월 빼기) 다음..
오늘을 기준으로 이전(직전) 일요일에 해당하는 날짜를 확인해야 하는 경우가 있다. 이럴때는 NEXT_DAY 함수를 활용하여 날짜를 구할 수 있다. NEXT_DAY 함수는 기준일자의 다음에 오는 날짜를 구하는 함수이지만 기준일자에서 8일을 빼주면 이전 요일에 해당하는 날짜를 구할 수 있다. 기준일자를 제외하고 이후 날짜 중 조건에 주어진 요일에 해당하는 날짜를 반환 한다. 함수 : NEXT_DAY('기준일자', '찾을요일') SELECT SYSDATE , NEXT_DAY(SYSDATE-8,'SUN') prev_sunday --이전 일요일 , NEXT_DAY(SYSDATE,'SUN') next_sunday --다음 일요일 FROM DUAL 요일은 아래의 표에서 어떤 값을 넣어도 동일하게 동작 한다. SELE..
오라클에서 문자열을 합치기 위해서는 CONCAT 함수 또는 "||" 파이프 두개를 사용한다. CONCAT 함수는 여려개의 문자열을 합칠 때 조금 번거로운 면이 있기 때문에, 실무에서는 "||"를 많이 사용하는 편이다. 아래의 쿼리 예시를 보면 쉽게 이해할 수 있다. SELECT CONCAT('가나다라', '아자차카') , '가나다라' || '아자차카' FROM DUAL CONCAT 함수를 사용하는 방법 CONCAT 함수를 사용하여 3개 이상의 문자열을 합칠경우 함수를 여러번 겹쳐서 사용해야 하므로 가독성이 많이 떨어진다. "||" 를 사용하는 방법 "||" 를 사용하여 여러개의 문자열을 합칠경우 동일한 패턴으로 문자열을 합치기 때문에 가독성이 좋다. 오라클 Group By 문자열 합치기 3가지 방법 오라..
MySql이나 postgreSQL에서 사용하는 LIMIT절을 오라클에서 사용하고 싶을 때가 있다. 그러나 아쉽게도 오라클에서는 LIMIT를 사용할수 없다. 오라클에서 LIMIT 대신 ROWNUM을 사용하면 된다고 하는데, LIMIT와 ROWNUM은 사용법과 용도가 전혀 다르다는것을 알고 사용해야 한다. LIMIT는 쿼리가 ORDER BY 절까지 모두 실행 후 해당 결과에서 원하는 행의 데이터를 가져오는 것이며, ROWNUM은 쿼리가 완전히 수행되지 않은 원 데이터의 정렬순서대로 번호를 매기기 때문에 전혀 다른 결과가 출력된다. ※ ROWNUM은