Posts List

Translate

레이블이 오라클인 게시물을 표시합니다. 모든 게시물 표시
레이블이 오라클인 게시물을 표시합니다. 모든 게시물 표시

2014년 2월 12일 수요일

ORACLE EXISTS Condition


SQL: EXISTS Condition
The EXISTS condition is considered "to be met" if the subquery returns at least one row.

The syntax for the EXISTS condition is:

SELECT columns
FROM tables
WHERE EXISTS ( subquery );

The EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.

Example #1
Let's take a look at a simple example.  The following is an SQL statement that uses the EXISTS condition:

SELECT *
FROM suppliers
WHERE EXISTS
  (select *
    from orders
    where suppliers.supplier_id = orders.supplier_id);

This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.

Example #2 - NOT EXISTS
The EXISTS condition can also be combined with the NOT operator.

For example,

SELECT *
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

This will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.

Example #3 - DELETE Statement
The following is an example of a delete statement that utilizes the EXISTS condition:

DELETE FROM suppliers
WHERE EXISTS
  (select *
    from orders
    where suppliers.supplier_id = orders.supplier_id);

 

Example #4 - UPDATE Statement
The following is an example of an update statement that utilizes the EXISTS condition:

UPDATE supplier
SET supplier_name = ( SELECT customer.name
FROM customers
WHERE customers.customer_id = supplier.supplier_id)
WHERE EXISTS
  ( SELECT customer.name
    FROM customers
    WHERE customers.customer_id = supplier.supplier_id);

Example #5 - INSERT Statement
The following is an example of an insert statement that utilizes the EXISTS condition:

INSERT INTO supplier
(supplier_id, supplier_name)
SELECT account_no, name
FROM suppliers
WHERE exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

실무 적용했던 사례

((EXISTS ( SELECT  1
                      FROM SYS0060 T1,
                           SYS0020 T2
                    WHERE 1=1
                    AND ROLE_CD =  CD
                    AND CD_IDX = 'ROLE'
                    AND CD ='10'
                    AND USER_ID = #{user.USER_ID})) OR (T1.PJT_CD IN (SELECT DISTINCT PJT_CD FROM ITA0030 WHERE USER_ID = #{user.USER_ID})))
작성자: 시간: 댓글 없음 :
라벨: , ,

2014년 2월 4일 화요일

[ORACLE 월별 집계]ORACLE 월별 집계 Example 



[ORACLE 월별 집계]ORACLE 월별 집계 Example


/*---------------------------------------------------
* 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 A.DEPTNO, A.EMPNO     /* 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
작성자: 시간: 댓글 없음 :
라벨: , , ,

2014년 2월 3일 월요일

ORACLE MERGE INTO 문 한번에 INSERT , UPDATE 하기

MERGE INTO 타겟테이블 TT
    USING
        소스테이블 ST
    ON (TT.필드1=ST.필드1 AND TT.필드2=ST.필드2 ....)
   
    WHEN MATCHED THEN -- 존재하면 UPDATE
        UPDATE SET
            TT.타겟_필드1=ST.소스_필드1,
            TT.타겟_필드2=ST.소스_필드2

    WHEN NOT MATCHED THEN -- 없으면 INSERT
            INSERT (타겟_필드1, 타겟_필드2, 타겟_필드3....)
            VALUES(
                ST.소스_필드1,
                ST.소스_필드2,
                ST.소스_필드3,
                ,
                ,
            );

[실제 Example]
MERGE INTO ITA0010 T1
USING DUAL
ON (1=1
  AND T1.USER_ID = #{USER_ID}
  AND T1.YY = #{YY}
)
WHEN NOT MATCHED THEN
INSERT(
            T1.USER_ID,
            T1.YY,
            T1.CRE_ANUL_CNT,
            T1.REGR_ID,
            T1.MODR_ID,
            T1.REG_DT,
            T1.MOD_DT,
            T1.RMK
            )VALUES(
             #{USER_ID},
             #{YY},
             #{CRE_ANUL_CNT},
             #{user.USER_ID},
             #{user.USER_ID},
             SYSDATE,
             SYSDATE,
             #{RMK}
             )
WHEN MATCHED THEN
UPDATE SET
                    T1.CRE_ANUL_CNT = #{CRE_ANUL_CNT},
                    T1.MODR_ID = #{user.USER_ID},
                    T1.MOD_DT = SYSDATE,
                    T1.RMK = #{RMK}

작성자: 시간: 댓글 없음 :
라벨: , , ,

ORACLE NVL 함수 & DECODE 함수

오라클 함수

1. NVL함수
    NVL(value,1)
     -> value가 null 일경우 1을 반환
                     그렇지 않을경우 value값을 반환
2.NVL2 함수
NVL2(expr1, expr2, expr3) 함수는
   expr1이 null이 아니면 expr2를 반환하고,
   expr1이 null이면 expr3을 반환한다.
ex) select nvl2('','Corea','Korea') from dual;


3. DECODE 함수
DECODE(col1,
              value1, value_data1,
              value2, value_data2,
              value3, value_data3,
              ............
              last_data
'col1'의 값이 'value1' 이면 'value_data1' 이고, 'value2' 이면 'value_data2'
그리고 모든 조건에 만족하지 않는다면 'last_data'가 된다.

두 가지 함수를 아래와 같이 응용해볼 수 있다.
DECODE(NVL(USE_YN,'N'),'Y','N','Y')

USE_YN의 값이 NULL 값이면 'N'으로 치환하고, 그 값이 'Y' 이면 'Y'이고
'N' 이면 'N'이며, 이것도 저것도 아니면 'Y' 라는 결과를 내는 것이다.
작성자: 시간: 댓글 없음 :
라벨: , , ,
피드 구독하기: 글 ( Atom )