oracle

카테고리 없음 2010. 10. 6. 17:46
http://loveu.oranc.co.kr/bbs/zboard.php?id=skill&page=2&sn1=&divpage=1&category=2&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=48

오라클 데이터베이스  scott유저의 emp테이블을 보면 empno와 mgr컬럼이 있습니다.


mgr 컬럼의 데이터는 해당 사원의 관리자의 empno를 의미 합니다.


예를 들어서 아래의 데이터를 보면은..


  EMPNO ENAME         SAL        MGR
------ --------- ------- ----------
   7369     SMITH          800       7902
   7902     FORD          3000       7566


empno 7369사원의  관리자는 7902의 empno를 가진 사원이며
empno 7902사원의  관리자는 7566의 empno를 가진 사원입니다.


이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITH와 CONNECT BY를 이용해서
쉽게 가져올 수 있습니다.

상품의 카테고리(대분류,중분류,소분류...)를 조회 할때 START WITH와 CONNECT BY를 이용하면
트리 구조로 편리하게 조회 할 수 있습니다.


게시판에서의 일반글과 답변글 과의 관계에서도 사용 할 수 있습니다.




⊙ START WITH와 CONNECT BY를 이용해 데이터를 계층적인 순서로  조회할 수 있습니다.

 
◈ START WITH

- 계층 질의의 루트(부모행)로 사용될 행을 지정 합니다..
- 서브쿼리를 사용할 수도 있습니다.


◈ CONNECT BY

- 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 합니다.
- 보통 PRIOR 연산자를 많이 사용 합니다..
- 서브쿼리를 사용할 수 없습니다..



◈ CONNECT BY의 실행순서는 다음과 같습니다.

- 첫째 START WITH절
- 둘째 CONNECT BY 절
- 세째 WHERE 절 순서로 풀리게 되어있습니다.
 




-- 테스트를 위해서 scott유저로 접속을 합니다.
SQLPLUS scott/tiger


예제1)상위계층과 하위 계층 관계의 순서대로 쿼리해 옴


-- LEVEL 예약어를 사용하여  depth를 표현 할 수 있습니다.
-- 직업이 PRESIDENT을 기준으로 계층 구조로 조회합니다.

SQL>SELECT LEVEL,empno,ename, mgr
        FROM  emp
        START WITH job = ’PRESIDENT’   -- 직업이 PRESIDENT를 기준으로
        CONNECT BY PRIOR  empno = mgr; -- 사원(empno)과 관리자(mgr)의  관계를 계층 구조로 조회
   
    LEVEL      EMPNO ENAME                       MGR
--------- ---------- -------------------- ----------
        1       7839      KING
        2       7566      JONES                      7839
        3       7788      SCOTT                     7566
        4       7876      ADAMS                     7788
        3       7902      FORD                       7566
        4       7369      SMITH                       7902
        2       7698      BLAKE                       7839
        3       7499      ALLEN                       7698
        3       7521      WARD                       7698
        3       7654      MARTIN                     7698
        3       7844      TURNER                     7698
        3       7900      JAMES                       7698
        2       7782      CLARK                       7839
        3       7934      MILLER                      7782


--  LEVEL컬럼은 depth를 나타냅니다.
-- JONES의 관리자는 KING을 나타냅니다.
--  SCOTT의 관리자는 JONES를 나타냅니다.
--  예제와 같이 상/하의 계층 구조를 쉽게 조회 할 수 있습니다.  




예제2) 사원성명을 계층 구조로 보여 줌


SQL>SET LINESIZE 100
SQL>SET PAGESIZE 100
SQL>COL ename FORMAT A20

-- 예제1의 결과를 가지고 LPAD함수를 이용해서 ename왼쪽에 공백을 추가 했습니다.
SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job
       FROM emp
       START WITH job=’PRESIDENT’
       CONNECT BY PRIOR empno=mgr;

ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
KING                        7839                   PRESIDENT
    JONES                  7566       7839      MANAGER
        SCOTT             7788       7566      ANALYST
            ADAMS         7876       7788      CLERK
        FORD               7902       7566      ANALYST
            SMITH          7369       7902      CLERK
    BLAKE                  7698       7839      MANAGER
        ALLEN               7499       7698     SALESMAN
        WARD               7521       7698     SALESMAN
        MARTIN             7654       7698     SALESMAN
        TURNER            7844       7698     SALESMAN
        JAMES              7900       7698     CLERK
    CLARK                  7782       7839     MANAGER
        MILLER              7934       7782     CLERK




예제3) 레벨이 2까지만 쿼리해서 가져오는 예제
  
SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job
        FROM emp
        START WITH job=’PRESIDENT’
        CONNECT BY PRIOR empno=mgr
        AND LEVEL <=2 ;
   
ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
KING                        7839                   PRESIDENT
    JONES                  7566       7839      MANAGER
    BLAKE                  7698       7839      MANAGER
    CLARK                  7782       7839      MANAGER  




예제4) 각 label별로 급여의 합과 인원수를 구하는 예제

SQL> SELECT LEVEL, SUM(sal) total,COUNT(empno) cnt
        FROM emp
        START WITH job=’PRESIDENT’
        CONNECT BY PRIOR empno=mgr      
        GROUP BY LEVEL
        ORDER BY LEVEL;

     LEVEL      TOTAL        CNT
---------- ---------- ----------
         1       5000          1
         2       8275          3
         3      13850          8
         4       1900          2




◈ 데이터가 많아질 경우....

- 첫째로 풀리는 START WITH job=’PRESIDENT’ job 컬럼에 index가 생성되어 있지 않는다면
    속도를 보장할 수 없습니다.

- 그리고 둘째로 풀리는 CONNECT BY PRIOR empno = mgr 역시 PRIOR 쪽의 컬럼값이 상수가
   되기 때문에 MGR컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.
  
- 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 Desc으로
   표현하기가 어렵 습니다.




아래 강좌에서 START WITH와 CONNECT BY절을 이용해서 계층 구조로 쿼리를 해오는것을 살펴봤습니다.

이번에는 계층구조를 이용해서 역순으로 쿼리를 해오는것을 보겠습니다.
 
SQLPLUS scott/tiger
-- 조금 깔끔하게 보기 위해서.. 셋팅 먼저 하고요..
SQL> SET LINESIZE 100
SQL> SET PAGESIZE 100
SQL> COL ename FORMAT A20

SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job
        FROM emp
        START WITH EMPNO=7839
        CONNECT BY PRIOR empno=mgr;
ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
KING                         7839                            PRESIDENT
    JONES                  7566          7839           MANAGER
        SCOTT              7788          7566          ANALYST
            ADAMS          7876          7788          CLERK
        FORD               7902         7566           ANALYST
            SMITH          7369          7902           CLERK
       
           
위 SQL문은 EMPNO가 7839인 것을 기준으로 EMPNO와 MGR컬럼을 가지고 계층구조로 비교해서 결과값을 반환 합니다.

위 SQL문의 CONNECT BY PRIOR empno=mgr 이 부분을..한번 바꺼 볼까요..

아래처럼.. CONNECT BY empno=PRIOR mgr 이렇게 바꺼서 한번 실행해 보세요..
그럼..역순으로.. 쿼리를 해서 결과값을 반환 합니다

           
SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job
        FROM emp
        START WITH EMPNO=7369
       CONNECT BY empno=PRIOR mgr;


ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
SMITH                      7369         7902   CLERK
    FORD                   7902         7566   ANALYST
        JONES              7566         7839   MANAGER
            KING            7839                    PRESIDENT
           

위에 결과값과 비교해 보세요..
 
이 SQL문은 실제로..실무에서 카테고리의 Depth를 표현할때 아주 많이 사용 합니다.
 
컴퓨터/소프트웨어>모니터>일반CRT모니터
 
이런 카테고리 구조를 하나의 SQL문으로 쉽게 가져 올수가 있죠..

Posted by [czar]
,
START WITH와 CONNECT BY절을 이용해서 계층 구조로 쿼리

http://loveu.oranc.co.kr/bbs/zboard.php?id=skill&page=2&sn1=&divpage=1&category=2&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=49



아래 강좌에서 START WITH와 CONNECT BY절을 이용해서 계층 구조로 쿼리를 해오는것을 살펴봤습니다.

이번에는 계층구조를 이용해서 역순으로 쿼리를 해오는것을 보겠습니다.
 
SQLPLUS scott/tiger
-- 조금 깔끔하게 보기 위해서.. 셋팅 먼저 하고요..
SQL> SET LINESIZE 100
SQL> SET PAGESIZE 100
SQL> COL ename FORMAT A20

SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job
        FROM emp
        START WITH EMPNO=7839
        CONNECT BY PRIOR empno=mgr;
ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
KING                         7839                            PRESIDENT
    JONES                  7566          7839           MANAGER
        SCOTT              7788          7566          ANALYST
            ADAMS          7876          7788          CLERK
        FORD               7902         7566           ANALYST
            SMITH          7369          7902           CLERK
       
           
위 SQL문은 EMPNO가 7839인 것을 기준으로 EMPNO와 MGR컬럼을 가지고 계층구조로 비교해서 결과값을 반환 합니다.

위 SQL문의 CONNECT BY PRIOR empno=mgr 이 부분을..한번 바꺼 볼까요..

아래처럼.. CONNECT BY empno=PRIOR mgr 이렇게 바꺼서 한번 실행해 보세요..
그럼..역순으로.. 쿼리를 해서 결과값을 반환 합니다

           
SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job
        FROM emp
        START WITH EMPNO=7369
       CONNECT BY empno=PRIOR mgr;


ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
SMITH                      7369         7902   CLERK
    FORD                   7902         7566   ANALYST
        JONES              7566         7839   MANAGER
            KING            7839                    PRESIDENT
           

위에 결과값과 비교해 보세요..
 
이 SQL문은 실제로..실무에서 카테고리의 Depth를 표현할때 아주 많이 사용 합니다.
 
컴퓨터/소프트웨어>모니터>일반CRT모니터
 
이런 카테고리 구조를 하나의 SQL문으로 쉽게 가져 올수가 있죠..
Posted by [czar]
,
oracle 행열,열행 변환
[오라클]행->열, 열->행 변환

/*---------------------------------------------------
* ROW => COLUMN의 변환
* COLUMN => ROW의 변환
----------------------------------------------------*/
---------------
DEPTNO  EMPNO
---------------
10      7782
10      7839
10      7934
20      7369
20      7566
20      7788
30      7499
30      7521
30      7654

------------------------------
DEPTNO  EMP1  EMP2  EMP3
------------------------------
10      7782  7839  7934
20      7369  7566  7788
30      7499  7521  7654

/* COLUMN => ROW 시작 */
SELECT A.DEPTNO,
       DECODE(C.NO, 1, A.EMP1,
                    2, A.EMP2,
                    3, A.EMP3) EMPNO
FROM
     (
     /* ROW => COLUMN 시작 */
     SELECT DEPTNO,
            MAX(DECODE(RID, 1, EMPNO)) EMP1,
            MAX(DECODE(RID, 2, EMPNO)) EMP2,
            MAX(DECODE(RID, 3, EMPNO)) EMP3
     FROM (
          SELECT DEPTNO,
                 ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RID,
                EMPNO
          FROM EMP
          )
     GROUP BY DEPTNO
     /* ROW => COLUMN 종료 */
     ) A, COPY_T C
WHERE C.NO <= 3
/* COLUMN => ROW 종료 */



/*---------------------------------------------------
* CROSSTAB에서 열을 행으로 행을 열로 변환
* ROW => COLUMN, COLUMN => ROW을 한꺼번에 구현
----------------------------------------------------*/
------------------------------
DEPTNO   EMP1  EMP2  EMP3
------------------------------
10       7782  7839  7934
20       7369  7566  7788
30       7499  7521  7654

------------------------------
EMP  DEPT_10 DEPT_20 DEPT_30
------------------------------
EMP1 7782    7369    7499
EMP2 7839    7566    7521
EMP3 7934    7788    7654


SELECT DECODE(C.NO, 1, 'EMP1',
                    2, 'EMP2',
                    3, 'EMP3') EMP,
       MAX(DECODE(A.DEPTNO||C.NO2, '1001', A.EMP1,
                                   '1002', A.EMP2,
                                   '1003', A.EMP3)) DEPT_10,
       MAX(DECODE(A.DEPTNO||C.NO2, '2001', A.EMP1,
                                   '2002', A.EMP2,
                                   '2003', A.EMP3)) DEPT_20,
       MAX(DECODE(A.DEPTNO||C.NO2, '3001', A.EMP1,
                                   '3002', A.EMP2,
                                   '3003', A.EMP3)) DEPT_30
FROM
     (
     /* 원래의 ROW, COLUMN구조 시작 */
     SELECT DEPTNO,
            MAX(DECODE(RID, 1, EMPNO)) EMP1,
            MAX(DECODE(RID, 2, EMPNO)) EMP2,
            MAX(DECODE(RID, 3, EMPNO)) EMP3
     FROM (
          SELECT DEPTNO,
                 ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RID,
                EMPNO
          FROM EMP
          )
     GROUP BY DEPTNO
     /* 원래의 ROW, COLUMN구조 종료 */
     ) A, COPY_T C
WHERE C.NO <= 3
GROUP BY DECODE(C.NO, 1, 'EMP1',
                      2, 'EMP2',
                      3, 'EMP3')
      
/*---------------------------------------------------
* 참고) COPY_T 의 생성
----------------------------------------------------*/
CREATE TABLE COPY_T
AS
SELECT ROWNUM                  NO
      ,TO_CHAR(ROWNUM, 'FM00') NO2
FROM ALL_OBJECTS
WHERE ROWNUM <= 31

CREATE UNIQUE INDEX COPY_T_IDX1 ON COPY_T(NO)
CREATE UNIQUE INDEX COPY_T_IDX2 ON COPY_T(NO2)


/*---------------------------------------------------
* ROW_NUMBER() 함수의 기능을 구현 => 테이블을 두번 읽기
* ROWNUM이 지원되지 않는 DBMS에서 ROWNUM 구현도 유사
----------------------------------------------------*/
SELECT DEPTNO,
       ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RID,
       EMPNO
FROM EMP


SELECT A.DEPTNO,
       COUNT(*) RID,
       A.EMPNO
FROM EMP A, EMP B
WHERE A.DEPTNO = B.DEPTNO      /* PARTITION BY 기능 */    
AND A.EMPNO >= B.EMPNO         /* ORDER BY 기능 => 반드시 UNIQUE 해야함 */
GROUP BY A.DEPTNO, A.EMPNO     /* PARTITION BY,ORDER BY 기능 */
ORDER BY A.DEPTNO, A.EMPNO     /* PARTITION BY,ORDER BY 기능 */


/*---------------------------------------------------
* COPY_T 테이블이 없을때 COPY_T 기능 구현방법
----------------------------------------------------*/
SELECT NO, NO2
FROM COPY_T
WHERE NO <= 5

/*------------------------------------------
* 1.USER_OBJECTS 테이블의 이용
*   최대한 가벼운 테이블 이용
*   USER_OBJECTS가 가벼운지는 검증할 문제임
-------------------------------------------*/
SELECT ROWNUM NO,
       TO_CHAR(ROWNUM, 'FM00') NO2
FROM USER_OBJECTS
WHERE ROWNUM <= 5

/*------------------------------------------
* 2.DUAL 테이블의 이용
*   복사갯수가 적을때 이용(2~3개)
-------------------------------------------*/
SELECT NO,
       TO_CHAR(NO, 'FM00') NO2
FROM (
     SELECT 1 NO FROM DUAL
     UNION ALL
     SELECT 2 FROM DUAL
     UNION ALL
     SELECT 3 FROM DUAL
     UNION ALL
     SELECT 4 FROM DUAL
     UNION ALL
     SELECT 5 FROM DUAL
     )

/*---------------------------------------------------
* 참고) DUAL 테이블이 없는 경우의 구현(SQL SERVER)
----------------------------------------------------*/
CREATE VIEW DUAL
AS
SELECT 'X' DUMMY_COL

SELECT GETDATE() FROM DUAL
Posted by [czar]
,
http://loveu.oranc.co.kr/bbs/zboard.php?id=skill&page=2&sn1=&divpage=1&category=2&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=90


oracle


오라클 버젼확인 : select * from product_component_version;

산술 함수

ABS(n) : 절대값 |n|
sin() cos() tan() sinH() cosH() tanH() : radian값을 이용
ex> 30' 일 경우 sin(30* (3.14.../180));
exp(n) : 지수값 e를 밑으로 하는 e의 n승 값;
LN(n):자연 log값 밑수 e 지수 n
SQRT(n): 루트n
power(m,n): m의 n승
MOD(m.n) : m을  n 으로 나눈 나머지
floor(n): n보다 작은 정수값들중에서 최대값
ceil(n): n보다 큰 정수값들중에서 최소값
ex> floor(3.5)   -->  3   ,    ceil (3.5)  -->  4
      floor(-3.2) --> -4  ,     ceil (-3.5) --> -3
greatest(m,n): 두 수중에서 큰것
least(m,n);두 수중에서 작은 것
TO_NUMBER('숫자형 문자열'):문자를 숫자로 변환
round(m,n):반올림
trunc(m,n):절삭한다 즉 반올림을 허용하지 않는다.
 
ex>  Round(2.4678, 2)   -->  2.47   ,    trunc(2.4687, 2)    -->  2.46
    Round(3654.26, -2) --> 3700  ,   trunc (3654.26, -2 ) --> 3600
NVL(col명,값): col의 값이 null를 가질때 값을 가진다.
ex> 100+NULL은 NULL이 되기 때문에 이러한 문제점을 해결할 때 사용한다.
select ename,sal,comm,sal+nvl(comm,0) from emp;
          -->sal값과 comm값을 더하는데 comm값이 null값이면 0값을 취한다...

문자열 함수

lower():소문자로 변환시켜주는 함수
upper():대문자로 변환시켜주는 함수
Initcap():단어의 첫자만 대문자로 나머지는 소문자로
 
Initcap('i LOVE you') ---->  I Love You
 

length():문자열의 길이
substr(문자열,위치,갯수): 해당위치에서 갯수만큼의 문자열을 추출한다.
 
substr ('KOREA', 3, 2) ---->  'RE'
-- 3번째인 R부터 2글자 출력
instr(문자열,찾을 문자열[,위치,찾을위치])
:문자열에서 이 문자를 찾아라. return값은 찾은 위치
 

Instr("abcdabkdoerabjdlfjdg","ab") -----> 1
-- 1번째에서 시작해서 처음  ab가 나타나는 1출력
Instr("abcdabkdoerabjdlfjdg","ab",4,2) ---->  12
-- 4번째부터 시작해서 두번째 나타나는  ab 출력
 

user:현재 접속된 사용자를 보여준다.
decode(col명,비교값,취할값,비교값,취할값,....기본값)
:col값이 비교값이면 취할값으로 대체.

decode(deptno,10,'총무부',20,'영업부',30,'전산부','관리부'):나머지는 관리부
-- deptno가 10이면 총무부 , 20이면 영업부, 30이면 전산부 나머지 는 관리부 출력

trim : 좌우 공백 제거

날짜 함수

TO_CHAR(날짜,'포맷'): 날짜를 문자열로 바꾼다.
TO_DATE(문자열,'포맷'):문자열을 날짜로 ex> TO_DATE('97-10-14',' -----')
ADD_MONTHS(날짜,n):현재 날짜에 n개월수를 더한다.
MONTHS_BETWEEN(날짜1,날짜2):두 날짜의 개월수의 차를 구한다.
LAST_DAY(날짜):그 달의 마지막 날짜를 구한다.
NEXT_DAY(날짜,'요일'):날짜로 부터 다음에 나오는 요일은 며칠인가
next_day(sysdate,'금요일')
날짜 + n : 날짜에다 n 일을 더한다.
포맷:
yy : 년도를 2자리로
yyyy : 년도를 4자리로
year : 영어 철자로 표시
mm :월을 표시(01-12)
mon :월을 표시(DEC....):약자로 나온다.
month :월을 표시(1월,2월,....혹은 ,DECEMBER)
d :일을 표시(주에 대한 일)
dd :일을 표시 (월에 대한 일)
ddd :일을 표시 (년에 대한 일)
Q : 분기를 구한다.
DAY :요일 (월요일)
DY  :요일(월)
HH & HH12 :시간을 12시간제로
HH24 :24시간제로
MI:분
SS:초
AM & PM & A.M & P.M:12시간제 일때 오전 오후를 표시
접미사로 사용하는 포맷
TH:서수로 표시 ex> 4 -> DD ->4
                             4-> DDTH -> 4TH
SP:철자로 표시 ex> 4 ->DDSP -> FOUR
SPTH & THSP  ex> 4 -> DDSPTH -> FOURTH
근무한 년월 수 ex> column "근무년수" Format a 14;

그룹 함수

NULL값은 계산되지 않는다.
여러개의 데이타에 대한 결과 이므로 그룹함수라 한다
단일 필드와 함께 사용할 수 없다.
만약 단일 필드와 사용되면 group by절에 반드시 나와야 한다.
sum() : 합
avg() : 평균
count() : 갯수  count(*): 리턴되는 레코드의 수를 계산
ex> select deptno,sum(sapay) from sawon;  에러
-->단위 필드이므로 따라서 마지막에 group by deptno;라고 지정해주면 사용가능
max() : 최대값
min() : 최소값
stddev : 표준편차
variance : 분산  

:: 예제 ::

오늘 날짜에 2틀씩 뺀것
select sysdate+2, sysdate-2 from dual;

오늘날짜에 3시간을 더한것과 20분을 더한것
select sysdate+3/24, sysdate+20/1440 from dual;

오늘날짜에 며칠을 뺀것
alter session
set nls_date_format = 'yyyy-mm-dd;
select sysdate-to_date('2005-03-02') from dual;
위에서 세션을 변경해준것은 2005-03-02가 문자로 인식되기 때문에 바꾸어 준것이과 기존에 되어 있던 날짜 입력형태가 시간까지 들어가 있기 때문에 그런것이다.

오늘날짜에 3개월을 더하고 3개월을 뺀것
select add_months(sysdate,3), add_months(sysdate,-3) from dual;

오늘을 기준으로 가장 가까운 토요일, 가장가까운 일요일
select next_day(sysdate,'토'),next_day(sysdate,'월'),next_day(sysdate,'금') from dual;
여기에서 next_day의 형식은 날짜 다음 요일이라는 것이 오라클 사에서 지정이 되어 있기 때문에 '토'가 문자라 인싟하지 않는 것이다

오늘부터 1월1일까지 몇개월 지났을까
select months_between(sysdate,'2005-01-01) from personel;

[형변환]

To_Number : 숫자로 바꾸어라
T0_date : 날짜로 바꾸어라

세션변경---------------------
alter session
set nls_date_format = 'yyyy-mm-dd day hh24:mi:ss BC';
변경후 위에 것을 다시 실행해 보세요

날짜에 숫자를 연산했을 경우에는 날짜가 나옴
  data + number  = date
  data - number = date
  date - date = Number of days
  date + number/24 = Date
Posted by [czar]
,
http://loveu.oranc.co.kr/bbs/zboard.php?id=skill&page=1&sn1=&divpage=1&category=2&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=100

1. ROLLUP의 단계적 이해

: 소계, 합계를 같이 구할 때 ROLLUP 사용한다.



a. 전체 합

SELECT  SUM(salary)
FROM  employees
WHERE  department_id < 50;



SUM(SALARY)
-----------
      54800



b. 부서별/직급별 합

SELECT  department_id, job_id, SUM(salary)
FROM  employees
WHERE  department_id < 50
GROUP BY department_id, job_id;



DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
           10 AD_ASST           4400
           20 MK_MAN           13000
           20 MK_REP            6000
           30 PU_MAN           11000
           30 PU_CLERK         13900
           40 HR_REP            6500



c. 부서별 합

SELECT  department_id, SUM(salary)
FROM  employees
WHERE  department_id < 50
GROUP BY department_id;



DEPARTMENT_ID SUM(SALARY)
------------- -----------
           10        4400
           20       19000
           30       24900
           40        6500



d. a,b,c 의 결과를 union all한 결과와 비교

SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);



DEPARTMENT_ID   JOB_ID       SUM(SALARY)
-------------   ----------   -----------
           10   AD_ASST      4400
           10                4400     -->> DEPARTMENT_ID별 SUM
           20   MK_MAN       13000
           20   MK_REP       6000     -->> DEPARTMENT_ID별 SUM
           20                19000
           30   PU_MAN       11000

           30   PU_CLERK     13900

           30                24900    -->> DEPARTMENT_ID별 SUM

           40   HR_REP       6500

           40                6500     -->> DEPARTMENT_ID별 SUM

                             54800    -->> DEPARTMENT_ID별 총계

2. 컬럼의 구분은 어떻게 ?

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)

DEPTNO JOB        SUM(SAL)                    
10     CLERK      1300
10     MANAGER    2450
10     PRESIDENT  5000
10                8750     ---->> DEPTNO별 SUM
20     CLERK      1900
20     ANALYST    6000
20     MANAGER    2975
20                10875    ---->> DEPTNO별 SUM
30     CLERK      950
30     MANAGER    2850
30     SALESMAN   5600
30                9400     ---->> DEPTNO별 SUM
                  29025    ---->> 총계





소계, 합계를 구분하고자 할때 GROUPING(컬럼) 또는 GROUPING_ID(컬럼1, 컬럼2)

를 사용한다.GROUPING() 함수는 해당 컬럼이 집합에 참여하지 않으면 1을 반환, 집합계산에 참여하면 0을 반환



SELECT DEPTNO, JOB, SUM(SAL),

       GROUPING(DEPTNO) A,

       GROUPING(JOB) B,

       GROUPING_ID(DEPTNO,JOB) C
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)

DEPTNO JOB       SUM(SAL)   A     B     C
10     CLERK     1300       0     0     0
10     MANAGER   2450       0     0     0
10     PRESIDENT 5000       0     0     0
10               8750       0     1     1  ---->> DEPTNO별 SUM
20     CLERK     1900       0     0     0
20     ANALYST   6000       0     0     0
20     MANAGER   2975       0     0     0
20               10875      0     1     1  ---->> DEPTNO별 SUM
30     CLERK     950        0     0     0
30     MANAGER   2850       0     0     0
30     SALESMAN  5600       0     0     0
30               9400       0     1     1  ---->> DEPTNO별 SUM
                 29025      1     1     3  ---->> 총계
Posted by [czar]
,
ORA-00054 : 자원이 사용중이고, NOWAIT가 지정되어 있습니다 SQL_튜닝 

2006/12/19 18:42

복사 http://blog.naver.com/js7325/140032449038

--  특정 테이블을 access 하고 있는 것을 조회 
SELECT * 
  FROM v$access 
  WHERE OBJECT = '오브젝트 이름'
;
--  위의 결과 중 SID 로 세션 조회
SELECT *
  FROM v$session
 WHERE SID = 718
;
--  위의 결과 중 SID, SERIAL# 로 세션 KILL 
ALTER SYSTEM KILL SESSION '718,1014'
;


Orange ==> Session Monitor ==> ACTIVE 상태의 세션을 클릭하여 SQL 확인하고 해당 Process ID를 KILL 한다

Posted by [czar]
,
오라클의 startup 시에 발생하는 ORA-01157,ORA-01110 오류는 OS상에 해당 테이블스페이스가 없거나 다른 이유에 의해서 열수 없는 경우입니다. 

=========================================================================================================================

SQL>STARTUP 
        ....
        ....
        ....
        ....
        Database mounted
        ORA-01157 : cannot identify/lock data file 12 - see DBWR trace file
        ORA-01110 : data file 12: ' 파일 경로 ' 

이렇게 오류가 발생하면 밑의 쿼리를 실행하면 해결이 됩니다. 

SQL> ALTER DATABASE DATAFILE ' 파일 경로 ' OFFLINE DROP;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered 
Posted by [czar]
,
오라클 Connect By 절 응용 쿼리 - 콤마(,)로 구분된 스트링을 레코드로 만드

원 제목: 오라클 Connect By 절 응용 쿼리 - 콤마(,)로 구분된 스트링을 레코드로 만드는 쿼리.
예를 들어 :Bind1 = 123,232,345,2353,54747,74432,457547 라는 변수가 있다고 하면
이 스트링값의 변수를
==============
SUB
==============
123
232
345
2353
54747
74432
457547
==============
와 같이 변환 해 주는 쿼리
======================================================================================


SELECT
SUBSTR(
CODE,
INSTR (CODE, ',', 1, LEVEL) + 1,
INSTR (CODE, ',', 1, LEVEL + 1) - INSTR (CODE, ',', 1, LEVEL)
- 1
) SUB
FROM DUAL A
CROSS JOIN (SELECT ','|| :Bind1 ||',' AS CODE FROM DUAL) B
CONNECT BY LEVEL<= LENGTH(CODE) - LENGTH(REPLACE(CODE, ',')) - 1;


======================================================================================
선택된 시퀀스 들로 부터 무엇인가 공통된 작업을 진행하고자 할때 프로그램으로 부터 넘어온
배열을 그대로 사용가능하게 해주는 쿼리......
Posted by [czar]
,