오라클에서 쿼리문을 작성하다 보면 가장 많이 사용하는 연산자 중 하나는 BETWEEN일 것이다. BETWEEN은 날짜, 숫자의 범위 검색에 아주 유용하게 사용할 수 있으며, 문자의 범위 검색도 가능하다. 아래의 BETWEET 연산자①와 비교 연산자(>=, = 시작일자 AND 컬럼명
오라클에서 쿼리문을 작성할 때 NULL을 제대로 이해하지 않으면 원하는 결과가 출력되지 않을 때가 많다. 다른 DB나 언어와 다른 부분이 있으니 아래의 예제를 보면서 오라클에서 NULL 사용법을 조금이라도 이해하면 좋을 것이다. 오라클에서 빈 문자열( '' )은 NULL로 인식하기 때문에, 컬럼의 값이 빈 문자열이면 NULL과 동일한 조건으로 쿼리를 작성해야 한다. [ 잘못된 사용 ] ① job = NULL (NULL은 비교 연산자를 사용할 수 없다) ② job != NULL (NULL은 비교 연산자를 사용할 수 없다) ③ job = '' (빈 문자열은 비교 연산자를 사용할 수 없다) ④ sal + NULL (수치값에 NULL을 사칙연산하면 결과는 NULL 이다) [ 올바른 사용 ] ① job IS NU..
오라클에서 소계, 합계, 총계의 쿼리(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..