나의 향기에 젖다...

SELECT X.BAYONG, X.YONGNM, X.GADAMT, 
             RANK() OVER(ORDER BY X.GADAMT DESC) RNKAMT
   FROM ( SELECT A.BAYONG, B.YONGNM, SUM(A.CHARGE + A.DLVAMT) GADAMT
                  FROM INVCON A
                    JOIN YNGMST B
                      ON B.YONGCD = A.BAYONG
                WHERE A.JBDATE BETWEEN '2011-11-01' AND '2011-11-02'
                GROUP BY A.BAYONG, B.YONGNM
             ) X
  ORDER BY RNKAMT

>>

BAYONG YONGNM     GADAMT   RNKAMT
--------- ---------- ---------- ----------
TG11        남대구         8909456          1
SEB8        광장            8540758          2
KGB3       파주탄현       8163140          3
JN55        장흥             7447275          4
KG14       공단안산        7071694          5
...

CREATE OR REPLACE PROCEDURE NOSUN.CREATE_BAEHAD
(V_LSTSEQ VARCHAR2, V_BAENUM VARCHAR2) AS

  C_JBDATE INVLOG.JBDATE%TYPE;
  C_MAINNB INVLOG.MAINNB%TYPE;

  S_CNT NUMBER;
  S_DLVGUB CHAR(1);

  CURSOR C_LIST IS
    SELECT A1.MAINNB, A1.BSTSEQ, A1.DLVAMT
       FROM INVLOG A1
     WHERE A1.MAINNB = V_LSTSEQ;   
BEGIN
  S_CNT := 0;

  UPDATE INVLOG
       SET BAENUM = V_BAENUM
   WHERE BSTSEQ = V_LSTSEQ;            

  OPEN C_LIST;
  LOOP
    <<start_point>>
       
    FETCH C_LIST INTO C_MAINNB, C_BSTSEQ, C_DLVAMT;
    EXIT WHEN C_LIST%NOTFOUND;

    IF C_DLVAMT <> 0 THEN
      S_DLVGUB := '1';
    ELSE
      S_DLVGUB := '2';
    END IF;          

    BEGIN
      SELECT APPCOD, RERAT1, RERAT2
           INTO S_APPCOD, S_RERAT1, S_RERAT2
         FROM RATTRF
       WHERE APPCOD = (CASE WHEN C_BTOCHK = 'Y' THEN 'BTOC' ELSE 'ZZ00' END);

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        V_OUTPUT := '운송장번호 : '||C_MAINNB||'에 대한 운송요율이 등록되있지 않습니다.';
        EXIT;                               
    END;
 
    GOTO START_POINT;

  END LOOP;
  CLOSE C_LIST;
 
  UPDATE BEHEAD
     SET JNGCHK = 'Y'
   WHERE BAENUM = V_BAENUM;      

END CREATE_BAEHAD;
/

CREATE OR REPLACE FUNCTION NOSUN.GET_RELCAR
( V_STRDAT IN VARCHAR2,  V_SUNCOD IN VARCHAR2 )
RETURN VARCHAR2 IS

    O_RELCAR VARCHAR2(30);
    S_CARNO4 VARCHAR2(4);

    CURSOR C_1 IS
      SELECT DISTINCT CARNO4
          FROM INVLOG
        WHERE BADATE= V_STRDAT
            AND SUNCOD= V_SUNCOD;
BEGIN

    OPEN C_1;
    LOOP
        FETCH C_1 INTO S_CARNO4;
        EXIT WHEN C_1%NOTFOUND;

        BEGIN
            O_RELCAR := TRIM(O_RELCAR || ' ' || S_CARNO4);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
            O_RELCAR := '';
        END;
    END LOOP;
    CLOSE C_1;

RETURN O_RELCAR;

END GET_RELCAR;
/

* SAVEPOINT

트랜잭션을 작은 섹션으로 나누는 SAVEPOINT 문을 사용하여 현재 트랜잭션에서 표시자를 생성할 수 있습니다.

그런 다음 ROLLBACK TO SAVEPOINT 문을 사용하여 해당 표시자에 보류 중인 변경 사항을 폐기할 수 있습니다.

이전의 저장점과 동일한 이름으로 두번째 저장점을 만들면 이전의 저장점이 삭제됩니다. 

 UPDATE...
 SAVEPOINT UPDATE_DONE;
 INSERT...
 ROLLBACK TO UPDATE_DONE;


* SET TRANSACTION READ ONLY - 읽기 일관성

이 구문을 실행하면 9시에 실행했다면, 9시 시점에서 커밋된 내용만 보여준다.

중간에 UPDATE 등 데이터 수정이 있어도 9시 시점의 데이터를 보여준다. 

명령 실행 후 READ 만 가능하다. 수정/삭제작업은 불가하다.

작업 해제는 ROLLBACK을 이용하면 된다.

* 테이블을 삭제했을 때, 5분전 데이터 확인하기

DELETE EMP;  로 데이터를 모두 삭제 한 후

SELECT *
   FROM EMP AS OF TIMESTAMP (SYSDATE - INTERVAL '5' MINUTE);  -- 5분전의 데이터를 확인  (10g)
   ** 조회조건 추가 가능 **

* 삭제된 테이블 돌리기

DROP TABLE EMP;

FLASHBACK TABLE EMP TO BEFORE DROP;  -- 오라클 10g 부터 이렇게 복원 가능

디비에서 테이블을 drop으로 삭제 했을때

테이블이 없어지긴 했는데 BIN$어쩌구저쩌구 테이블이 대신 생긴다.

테스트로 여러테이블을 만들고 지우는데

BIN$ 테이블이 너무 많이 생겨서 지저분해 보인다.

SQL> PURGE RECYCLEBIN; 

BIN$으로 시작하는 테이블이 모두 삭제되었다.  


결국 BIN$ 테이블은 복원할 일이 없다면 필요없는 테이블이라고 봐도 된다.

그것을 PURGE RECYCLEBIN; 을 통해

휴지통 비우기를 하면 없어지게 된다.


===========================================================================================
 
 
추가로 오라클 PURGE에 대해 검색해 보았다. 

휴지통(Recycle Bin)

오라클이 10g에서 새롭게 추가한 '휴지통' 이라는 기능.

테이블을 DROP 하더라도 언제던지 복원할 수 있게 하고자 하는 것이고요,

윈도우의 휴지통과 같다고 보시면 됩니다. 
 
 
휴지통에 들은 테이블을 조회.

SQL> show recyclebin;
  
 
휴지통의 모든 내용이 비워집니다.

SQL> purge recyclebin;
  
 
삭제된 테이블을 되살리고 싶다면

SQL> flashback table 테이블명 to before drop;
  
 
만약, 특정 테이블을 휴지통에 남기지 않고 모두 삭제하려면

SQL> drop table 테이블명 purge;
  
 
purge문 없이 그냥 drop 한 후에는

SQL> purge table 테이블명;

아래 처럼 두개의 테이블이 있고

TB_A
 ------      -------     ------
 A_ID        B_ID        A_DATA
 ------      -------     ------
 1           1           가가
 2           1           나나
 3           1           다다
 4           2           거거
 5           2           너너
 ....
 

TB_B
 -------    ------
 B_ID       B_DATA 
 -------    ------ 
 1          123 
 2          456 
 3          789 
 4          123 
 5          111


여기서 TB_B 의 B_ID = 1인 데이타를 조회 한 결과...
 
 -------    ------     -------
 B_ID       B_DATA     A_DATA
 -------    ------     -------
 1          123        가가, 나나, 다다   <--- 이렇게 한줄로...


[MSSQL]
  
 SELECT B_ID, B_DATA,
              STUFF( (SELECT  ',' + A_DATA 
                              FROM  TB_A A WHERE A.B_ID = B.B_ID 
                           FOR XML PATH('')),1,1,'')
    FROM TB_B B
  WHERE B_ID = 1;
  
 
[ORACLE]
 
 SELECT B_ID,
              SUBSTR(MAX(sys_connect_By_pAth(A_DATA,',')),2)
    FROM (
                SELECT A.B_ID, B_DATA,A_DATA,
                             ROW_NUMBER() OVER(PARTITION BY A.B_ID ORDER BY A.A_ID) rnum
                   FROM TB_A A, TB_B B
                 WHERE A.B_ID = B.B_ID
              )
   WHERE B_ID = 1
    START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum-1
       AND PRIOR B_ID = B_ID
   GROUP BY B_ID;

SAMPLE

CASE 1

UPDATE  /*+ BYPASS_UJVC */
( 
  SELECT  B.BIZREGNO,  B.POINT,  A.MPOINT 
     FROM  ISS_CUST_REM_POINT B
             ,  ( SELECT  BIZREGNO  ,SUM(POINT) AS MPOINT
                      FROM  ISS_POINT_USE_DET
                    WHERE  USE_TP_CD = 'E01'
                        AND  TRAD_DE IN ('20110303','20110317')
                        AND REG_ID = 'admin11'
                    GROUP  BY  BIZREGNO
                )  A 
   WHERE  B.BIZREGNO=A.BIZREGNO
)
SET    POINT = POINT+MPOINT; 



CASE 2

  UPDATE  /*+ BYPASS_UJVC */                    
  (                                            
  SELECT  A.IBKAMT,  A.BADATE,  A.YONGCD,  B.IMONEY 
     FROM  MULCAL A,                             
            (  SELECT  Y.BADATE,  Y.YONGCD,  Y.IMONEY 
                   FROM  MULIBK X                     
                     JOIN  MULIBL Y                     
                       ON  Y.IBKNUM  =  X.IBKNUM          
                 WHERE  X.IBKNUM  =  '201105250001'            
            ) B                                   
   WHERE  A.BADATE  =  B.BADATE                   
       AND  A.YONGCD  =  B.YONGCD                   
  )                                            
  SET  IBKAMT  =  IBKAMT  -  IMONEY                 

CASE 1 :

WHERE ( CASE WHEN SJVOY1 = 'B' THEN CHLDAT
                      WHEN SJVOY1 = 'J' THEN ARIDAT
                                                 ELSE REGDAT END )
            = '2010-11-01';


CASE 2:

WHERE REGDAT
           = ( CASE WHEN VESCOD = 'DCA' THEN '2011-03-15'
                                                          ELSE '2011-03-16' END );

SJVOY1, VESCOD 의 값이 무엇인지에 따라서 조회되는 값 및 대상 필드 변경됨