본문 바로가기

SQL

[오라클] rollup과 cube 한방에 이해하기 오라클 group by절에 사용되는 rollup과 cube는 편리한 함수입니다. 이 함수들이 없다면 데이터 복제를 통해 group by된 값의 sum(총계)등을 통해 구현해야 합니다. 그러지 않으면 어플리케이션에서 해야 겠죠. 편리함에도 불구하고 rollup과 cube가 자꾸 햇갈립니다. 아래 공식으로 한방에 이해해 보죠. 1. rollup ( group by절 오른쪽 컬럼부터 삭제하며 그룹을 생성하라) ex) select a, b, count(*) from tmp group by rollup(a,b) -- 나올수 있는 집합 a, b, count(*) union all a, null, count(*) union all null, null, count(*) ==> 오른쪽 컬럼인 b부터 null로 대체 되면.. 더보기
ORACLE|KEEP(DENSE_RANK FIRST ORDER BY …) - A컬럼 기준 정렬 후 지정 된 행의 B컬럼값 추출하기 필드 a와 같은 record에 있는 필드 b의 값을 가져오는 방법. first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다. MIN(B) (DENSE_RANK FIRST ORDER BY A [ASC|DESC]) MAX(B) (DENSE_RANK LAST ORDER BY A [ASC|DESC]) 【형식】 집합함수 KEEP ( DENSE_RANK FIRST ORDER BY expr [DESC|ASC][NULL{FIRST|LAST}],...) 【예제】 SQL> select 2 min(salary) keep (dense_rank first order by salary) "Worst", 3 max(salary) keep (dense_rank last order by salary) "Bes.. 더보기
PL/SQL 커서 - BULK COLLECT 1. BULK COLLECT BULK COLLECT 를 이용하면 데이터베이스에 암시적이나 명시적으로 한번만 질의를 하여도 다중행을 검색할 수 있다. BULK COLLECT는 PL/SQL과 SQL문의 실행 엔진 사이에 문맥전환 수를 줄여주고, 데이터를 검색하는 부담을 줄여준다. 질의의 INTO요소에 BULK COLLECT절을 사용한다. 커서에서 이 절을 사용하면, PL/SQL로 제어를 반환하기 전에, 질의에서 지정한 컬렉션으로 페치되는 다중 행들을 대량으로 바인드하게 한다. 구문 ... BULK COLLECT INTO collection_name[, collection_name]... 규칙과 제약사항 - 9i 이전에는 정적 SQL에서만 BULK COLLECT를 사용할수 있었다. 9i에서는 동적, 정적SQL.. 더보기
DBMS_LOB 관련 함수 (SUBSTR,INSTR,GETLENGTH) 알아두면 편한 DBMS_LOB 관련 함수 * DBMS_LOB.SUBSTR(CLOB_컬럼, 가져올 길이, OFFSET) 더보기
oracle extract EXTRACT('//text()').GetStringVal() 앞쪽에 써여진 XML함수에서 TEXT부분말 발췌하는 기능입니다. 원래 EXTRACT 함수는 extract(datetime)으로 시간형식에서 해당하는 부분을 발췌하는 기능입니다. select extract(year from date '2012-04-13') from dual; select extract(month from date '2012-04-13') from dual; select extract(day from date '2012-04-13') from dual; select extract(year from sysdate) from dual; 더보기
oracle XML 저장(extractValue,updateXML,existsNode) XPath를 이용한 XMLType 데이터 인출 및 검색 XMLType 테이블 혹은 컬럼에 대해 XML 문서 전체나 일부 정보를 얻어오기 위해 W3C 표준인 XPath 표현을 사용할 수 있다. extractValue() 함수와 extract() 함수를 이용해 XMLType에 대해 XPath 연산을 취해 원하는 값을 구하면 된다. 은 extractValue() 문법을 나타낸다. extractValue()는 XPath를 만족하는 단일 노드 혹은 하나의 애트리뷰트를 인출할 때 사용되며 검색 조건을 만족하는 노드 혹은 애트리뷰트의 값을 리턴한다. extractValue() 문법 은 앞에서 입력한 BookStore XMLType 테이블에 대해 다양한 XPath 조건을 입력해 조건을 만족하는 XML 노드의 값을 검색.. 더보기
ORACLE - KEEP(DENSE_RANK FIRST ORDER BY …) - A컬럼 기준 정렬 후 지정 된 행의 B컬럼값 추출하기 필드 a와 같은 record에 있는 필드 b의 값을 가져오는 방법. ex) 등록일이 제일 빠른 2013-09-05 의 같은 record에 있는 id값 가져오기 max(id) 와는 다른.... 등록일이 제일 늦은 2013-09-01 의 같은 record에 있는 id값 가져오기 min(id) 와는 다른 .... first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.. MIN(B) (DENSE_RANK FIRST ORDER BY A [ASC|DESC]) MAX(B) (DENSE_RANK LAST ORDER BY A [ASC|DESC]) 【형식】 집합함수 KEEP ( DENSE_RANK FIRST ORDER BY expr [DESC|ASC][NULL{FIRST|LAST}],...) 【.. 더보기
REGEXP_REPLACE 정규식 표현을 이용한 행변환 DATA 조작을 할때 구분자로 되어 있는 DATA를 조작하기는 쉽지 않다. 복잡하기도 하고 너무 길게 작성하면 가독성도 떨어진다. REGEXP_REPLACE 사용하여 간단히 작성해 보자! WITH T_TABLE AS (SELECT '1' AS ID ,'영희,철수,민수,정철,바보,멍충이,개똥,개똥똥' AS NM FROM DUAL UNION ALL SELECT '2' AS ID ,'국수,새롬' AS NM FROM DUAL UNION ALL SELECT '3' AS ID ,'영호' AS NM FROM DUAL UNION ALL SELECT '5' AS ID ,'아름,딸기,키위' AS NM FROM DUAL) SELECT ID -- ,영희,철수,민수, 첫번째 , 의 위치를 찾고 거기에 1을 더해서 영의 위치값(.. 더보기
oracle xmltype 오라클의 XMLTYPE을 사용한 칼럼이 있는 테이블을 조회할 경우 그냥 조회를 하면 데이타 확인이 안된다. 이럴때는 아래와 같은 방법을 사용하자.. XMLTYPE이 있는 테이블명 : TB_XML XMLTYPE의 칼럼명 : col_xml SELECT XMLSERIALIZE(CONTENT col_xml as VARCHAR2(2000)) FROM TB_XML; 추가로 한가지 항목만을 가져오고 싶을 경우 예를 들어 col_xml 칼럼에 아래와 같은 xml문서가 들어있고, PNAME을 조회하고 싶을때 FOTOG 011-111-1111 SELECT extractValue(col_xml, '/PERSON/PNAME') FROM TB_XML; ! 단 extractValue를 뽑아낼때 컬럼이 xml type이 아닐 경우.. 더보기
oracle XMLELEMENT,XMLAGG,XMLFOREST,XMLATTRIBUTES 함수 1. xlmelement함수 -> 조회된 결과에 테그를 붙여 결과를 xml형태의 자료로 만든다. 예) select a.deptcd, XMLELEMENT(dept, a.deptcd) xdept from m_dept a 결과) dept xdept 012345 012345 123231 123231 , , 2. xmlagg함수 -> XMLELEMENT에 의해 XML태그를 한줄로 만들어주는 문장 예) select XMLAGG(XMLELEMENT(dept, a.deptcd)) xdept from m_dept a 결과) xdept 012345123231,,,,,, 응용) -> XMLELEMENT함수로 조회된 행을 XML태그로 만들고 XMLAGG로 한줄로 만들어서 REPALCE같은 함수로 태그를 적절히 없애주면 끝.... 더보기

티스토리툴바