iTouch - Programing story

==== 난 이거 사용.

SELECT A1.TABLE_COMMENTS

           , A1.TABLE_NAME

           , A1.COLUMN_ID

           , A1.COLUMN_NAME

           , A1.COLUMN_COMMENTS

           , (CASE

              WHEN B1.CONSTRAINT_TYPE = 'P'

              THEN 'Y'

              END) PK_FLAG

           , NVL(A1.NULL_FLAG, 'N') AS NULL_FLAG 

           , A1.DATA_TYPE||

             (CASE A1.DATA_TYPE 

              WHEN 'NUMBER' 

              THEN '('||TO_CHAR(A1.DATA_LENGTH)||','||TO_CHAR(A1.DATA_PRECISION)||')'

              WHEN 'DATE'   THEN ' ' 

              ELSE '('||A1.DATA_LENGTH||')' 

              END) DATA_TYPE 

  FROM (SELECT B.COMMENTS TABLE_COMMENTS

                        , A.TABLE_NAME TABLE_NAME

                        , C.COMMENTS COLUMN_COMMENTS

                        , A.COLUMN_NAME COLUMN_NAME

                        , (CASE A.NULLABLE

                           WHEN 'Y'

                           THEN 'Y'

                           END) NULL_FLAG

                        , A.DATA_TYPE DATA_TYPE

                        , A.DATA_LENGTH 

                        , A.COLUMN_ID AS COLUMN_ID

                        , A.DATA_PRECISION

                FROM USER_TAB_COLUMNS A

                        , USER_TAB_COMMENTS B

                        , USER_COL_COMMENTS C

              WHERE (A.TABLE_NAME = B.TABLE_NAME)

                  AND (A.TABLE_NAME = C.TABLE_NAME

                          AND A.COLUMN_NAME = C.COLUMN_NAME

                          )

                  AND B.TABLE_TYPE = 'TABLE') A1

         , (SELECT A.TABLE_NAME

                       , A.COLUMN_NAME

                       , B.CONSTRAINT_TYPE

              FROM USER_CONS_COLUMNS A

                      , USER_CONSTRAINTS B

            WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)

                AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1

WHERE ( A1.TABLE_NAME = B1.TABLE_NAME(+)

     AND A1.COLUMN_NAME = B1.COLUMN_NAME(+))

  --AND A1.TABLE_NAME LIKE 'VMS%'

ORDER BY A1.TABLE_NAME, A1.COLUMN_ID

==== 원본 내용

--우선 코맨트를 전부 넣은 상태에서 실행해야 합니다. 
COMMENT ON TABLE    스키마명.테이블명 IS '테이블설명';
COMMENT ON COLUMN 스키마명.테이블명.컬럼명 IS '컬럼설명';
-- 테이블 정의서 1번째 방법
SELECT A1.TABLE_COMMENTS
           , A1.TABLE_NAME
--        , A1.COLUMN_ID
           , A1.COLUMN_NAME AS COLUMN_ID
           , A1.COLUMN_COMMENTS AS COLUMN_NAME
           , A1.DATA_TYPE AS DATA_TYPE     
           , (CASE A1.DATA_TYPE WHEN 'NUMBER' THEN TO_CHAR(A1.DATA_LENGTH)
                                              WHEN 'DATE'   THEN ' ' 
                                              ELSE TO_CHAR(A1.DATA_LENGTH) END) AS DATA_LENGTH
           , NVL(A1.NULL_FLAG, 'N') AS NULL_FLAG                              
           , (CASE WHEN B1.CONSTRAINT_TYPE = 'P' THEN 'PK' END) PK_FLAG
  FROM (SELECT B.COMMENTS TABLE_COMMENTS
                        , A.TABLE_NAME TABLE_NAME
                        , C.COMMENTS COLUMN_COMMENTS
                        , A.COLUMN_NAME COLUMN_NAME
                        , (CASE A.NULLABLE WHEN 'Y' THEN 'Y' END) NULL_FLAG
                        , A.DATA_TYPE DATA_TYPE
                        , A.DATA_LENGTH 
                        , A.COLUMN_ID AS COLUMN_ID
                        , A.DATA_PRECISION
               FROM USER_TAB_COLUMNS A
                       , USER_TAB_COMMENTS B
                       , USER_COL_COMMENTS C
             WHERE (A.TABLE_NAME = B.TABLE_NAME)
                 AND (      A.TABLE_NAME = C.TABLE_NAME
                        AND A.COLUMN_NAME = C.COLUMN_NAME
                        )
                 AND B.TABLE_TYPE = 'TABLE') A1
           , (SELECT A.TABLE_NAME
                         , A.COLUMN_NAME
                         , B.CONSTRAINT_TYPE
                 FROM USER_CONS_COLUMNS A
                         , USER_CONSTRAINTS B
               WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
                    AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1
WHERE (    A1.TABLE_NAME = B1.TABLE_NAME(+)
     AND A1.COLUMN_NAME = B1.COLUMN_NAME(+))
  --AND A1.TABLE_NAME LIKE 'VMS%'
ORDER BY A1.TABLE_NAME, A1.COLUMN_ID
 
 
-- 테이블정으서 만들기 2번째 방법
SELECT 
    X.TABLE_NAME TABLE_ID, 
    (SELECT COMMENTS FROM USER_TAB_COMMENTS 
                  WHERE TABLE_NAME = X.TABLE_NAME) TABLE_NM,
    X.COLUMN_NAME FIELD_ID, 
    (SELECT COMMENTS FROM USER_COL_COMMENTS 
                  WHERE TABLE_NAME = X.TABLE_NAME AND COLUMN_NAME = X.COLUMN_NAME) FIELD_NAME,
    X.DATA_TYPE AS "TYPE", 
    DECODE(X.DATA_TYPE, 
                 'DATE', NULL,
                 'BLOB', NULL, 
                  X.DATA_LENGTH || CASE WHEN X.DATA_PRECISION IS NOT NULL THEN ',' || X.DATA_SCALE END ) 
                  DATATYPES, 
    CASE WHEN SUBSTR(Y.INDEX_NAME, 1, 2) = 'PK' THEN 'PK' 
             ELSE DECODE(X.NULLABLE, 'N', 'NOT NULL', '') END "NOT NULL", 
    X.DATA_DEFAULT 
FROM COLS X, USER_IND_COLUMNS Y 
WHERE X.TABLE_NAME = Y.TABLE_NAME(+) AND X.COLUMN_NAME = Y.COLUMN_NAME(+) 
ORDER BY X.TABLE_NAME, X.COLUMN_ID 
 
-- 테이블정으서 만들기 3번째 방법(주로 사용)
SELECT A1.TABLE_COMMENTS
           , A1.TABLE_NAME
           , A1.COLUMN_ID
           , A1.COLUMN_NAME
           , A1.COLUMN_COMMENTS
           , (CASE
              WHEN B1.CONSTRAINT_TYPE = 'P'
              THEN 'Y'
              END) PK_FLAG
           , NVL(A1.NULL_FLAG, 'N') AS NULL_FLAG 
           , A1.DATA_TYPE||
             (CASE A1.DATA_TYPE 
              WHEN 'NUMBER' 
              THEN '('||TO_CHAR(A1.DATA_LENGTH)||','||TO_CHAR(A1.DATA_PRECISION)||')'
              WHEN 'DATE'   THEN ' ' 
              ELSE '('||A1.DATA_LENGTH||')' 
              END) DATA_TYPE 
  FROM (SELECT B.COMMENTS TABLE_COMMENTS
                        , A.TABLE_NAME TABLE_NAME
                        , C.COMMENTS COLUMN_COMMENTS
                        , A.COLUMN_NAME COLUMN_NAME
                        , (CASE A.NULLABLE
                           WHEN 'Y'
                           THEN 'Y'
                           END) NULL_FLAG
                        , A.DATA_TYPE DATA_TYPE
                        , A.DATA_LENGTH 
                        , A.COLUMN_ID AS COLUMN_ID
                        , A.DATA_PRECISION
                FROM USER_TAB_COLUMNS A
                        , USER_TAB_COMMENTS B
                        , USER_COL_COMMENTS C
              WHERE (A.TABLE_NAME = B.TABLE_NAME)
                  AND (A.TABLE_NAME = C.TABLE_NAME
                          AND A.COLUMN_NAME = C.COLUMN_NAME
                          )
                  AND B.TABLE_TYPE = 'TABLE') A1
         , (SELECT A.TABLE_NAME
                       , A.COLUMN_NAME
                       , B.CONSTRAINT_TYPE
              FROM USER_CONS_COLUMNS A
                      , USER_CONSTRAINTS B
            WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
                AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1
WHERE ( A1.TABLE_NAME = B1.TABLE_NAME(+)
     AND A1.COLUMN_NAME = B1.COLUMN_NAME(+))
  --AND A1.TABLE_NAME LIKE 'VMS%'
ORDER BY A1.TABLE_NAME, A1.COLUMN_ID
 
 
-- 테이블 목록 만들기(대략적인 크기로..)
SELECT TA1.TABLE_NAME AS TALBE_ID, 
       TA1.COMMENTS AS TABLE_NAME, 
       TA2.DATA_LENGTH AS DATA_LENGTH,
       TA3.MAX_NUM AS MAX_NUM,
       TA3.INITIAL_NUM AS INITIAL_NUM,
       (TA2.DATA_LENGTH*TA3.MAX_NUM) AS EXTEND_NUM
  FROM
       (SELECT  TABLE_NAME, COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_TYPE = 'TABLE') TA1,
       (SELECT  TABLE_NAME, SUM(DATA_LENGTH) AS DATA_LENGTH FROM USER_TAB_COLUMNS GROUP BY TABLE_NAME) TA2,
       (SELECT  TABLE_NAME
        ,(CASE 
              WHEN (NUM_ROWS < 1000) THEN 1000
              WHEN (10000   > NUM_ROWS) AND (NUM_ROWS > 1000)   THEN 10000
              WHEN (100000  > NUM_ROWS) AND (NUM_ROWS > 10000)  THEN 100000
              WHEN (1000000 > NUM_ROWS) AND (NUM_ROWS > 100000) THEN 1000000
              ELSE 10000000
          END    ) AS MAX_NUM
        , INITIAL_EXTENT AS INITIAL_NUM
        , GREATEST(4, CEIL(NUM_ROWS / 
                     DECODE(((ROUND(((1958 - (INI_TRANS * 23)) * ((100 - PCT_FREE) /100)) / 
                                    DECODE(AVG_ROW_LEN,0,1,AVG_ROW_LEN))))*2,0,1,
                        ((ROUND(((1958 - (INI_TRANS * 23)) * ((100 - PCT_FREE) /100)) / 
                                    DECODE(AVG_ROW_LEN,0,1,AVG_ROW_LEN))))*2)) * 2)
          AS TABLESIZE_KBYTES
           FROM  USER_TABLES) TA3
WHERE TA1.TABLE_NAME = TA2.TABLE_NAME
  AND TA1.TABLE_NAME = TA3.TABLE_NAME 
ORDER BY TA1.TABLE_NAME

출처: http://egloos.zum.com/lune23/v/437445

계층구조 쿼리란?

오라클 데이터베이스 scott 유저의 emp 테이블을 보면 empno와 mgr컬럼이 있으며, mgr 컬럼 데이터는 해당 사원의 관리자의 empno를 의미 한다.

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

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

이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITH와 CONNECT BY를 이용해서 쉽게 조회 할 수 있다.

계층구조 쿼리 Synctax

START WITH

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

CONNECT BY

• - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
• - PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
• - CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
• - CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
• - CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
• - 서브쿼리를 사용할 수 없다.

LEVEL Pseudocolumn

• - LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.

ORDER SIBLINGS BY

• - ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.

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

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

계층구조 쿼리 예제

간단예제

아래는 직업이 PRESIDENT을 기준으로 계층 구조로 조회하는 예이다.

출처: http://www.gurubee.net/lecture/1300

커서의 내용을 미리 정의 해 놓고 사용하는 방법.

DECLARE
  CURSOR C_LIST IS
    SELECT MY_ID FROM MY_TABLE WHERE 조건;
BEGIN

  FOR I_ID IN C_LIST LOOP
    DBMS_OUTPUT.put_line(I_ID);
  END LOOP;
END;

비추천 

DECLARE
	I_ID   VARCHAR2(100);		-- 변수 정의				
  C_LIST SYS_REFCURSOR;		-- 커서 정의
BEGIN
  OPEN C_LIST FOR
  SELECT MY_ID   
    FROM MY_TABLE
    WHERE 조건;
  LOOP					-- LOOP 돌기.
      FETCH C_LIST
      INTO  I_ID;			--  하나씩 변수에 넣기.
      EXIT WHEN C_LIST%NOTFOUND;	-- 더이상 없으면 끝내기.
      DBMS_OUTPUT.put_line(I_ID);    --  출력
  END LOOP;
  CLOSE C_LIST;
END;

DECLARE

BEGIN

  FOR C_LIST IN (SELECT MY_ID FROM MY_TABLE WHERE 조건) 
  LOOP
    DBMS_OUTPUT.put_line(C_LIST.I_ID);
  END LOOP;
END;

출처: https://blog.kjslab.com/20 [새 보다 자유롭게 - 자바 프로그래머 갈매기S(Jonathan Livingston Seagull)]

오라클 커서 및 벌크 콜렉트 사용 예제