오라클에서 이전 행의 값을 찾거나 다음 행의 값을 찾기 위해서는 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 : 정렬 컬럼명, 필수 기본 ..
오라클에서 GREATEST 함수는 최대값, LEAST 함수는 최소값을 반환하는 함수이다. MAX, MIN 함수와 차이점은 서로다른 컬럼의 값을 비교한다는 것이다. 같은 행(Row)에서 서로다른 컬럼의 값을 비교하여 최댓값 또는 최솟값을 반환한다. 함수의 인자 개수는 무한대 수준으로 입력이 가능하다. GREATEST("값1", "값2", "값3", "값4", "값5", "값6", ...) 인자값 중 최댓값을 반환 LEAST("값1", "값2", "값3", "값4", "값5", "값6", ...) 인자값 중 최솟값을 반환 최댓값을 반환 (GREATEST) SELECT GREATEST(100, 200, 300, 400, 500) FROM dual --결과: 500 최솟값을 반환 (LEAST) SELECT LE..
오라클에서 쿼리문을 작성할 때 TO_CHAR() 함수는 날짜, 숫자 등의 값을 문자열로 변환하는 함수이다. 자주 사용하는 기본 함수이므로 아래의 다양한 변환 방법을 알고 있으면 많은 도움이 된다. 날짜 포맷 변경 (YYYY-MM-DD) SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') --20200723 , TO_CHAR(SYSDATE, 'YYYY/MM/DD') --2020/07/23 , TO_CHAR(SYSDATE, 'YYYY-MM-DD') --2020-07-23 , TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') --2020-07-23 11:10:52 FROM dual YYYY: 년, MM: 월, DD: 일, HH24: 24시간, HH: 12시간, MI: 분,..
오라클의 ANY, SOME, ALL 연산자는 실무에서 자주 사용은 안하지만, 사용법이 궁금해서 찾아보면 사용법이 생각보다 어렵고 어떤 상황의 쿼리문에서 사용해야 할지 머릿속에 그려지지 않는다. SOME은 ANY와 이름만 다를뿐 동일한 기능의 연산자이며 아래의 예제는 ANY로만 작성하였다. ANY(SOME), ALL은 주로 서브쿼리와 함께 사용하는 다중 행 연산자이며, ANY는 조건을 만족하는 값이 하나라도 있으면 결과를 리턴하고, ALL은 모든 값이 조건을 만족해야 결과를 리턴한다. ● ANY 조건 예시 (만족하는 값 하나만 있으면 됨) 조건 결과 설명 1000 > ANY (500, 1000, 2000) TRUE ANY 값에 1000 보다 작은 500이 있으므로 TRUE 1000 = ANY (500, 1..
오라클에서 쿼리문을 작성하다 보면 가장 많이 사용하는 연산자 중 하나는 BETWEEN일 것이다. BETWEEN은 날짜, 숫자의 범위 검색에 아주 유용하게 사용할 수 있으며, 문자의 범위 검색도 가능하다. 아래의 BETWEET 연산자①와 비교 연산자(>=, = 시작일자 AND 컬럼명
오라클에서 소계, 합계, 총계의 쿼리(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..