행위

"ORACLE 추출쿼리"의 두 판 사이의 차이

DB CAFE

(새 문서: 이럴때 꼭 알아야 하는것이 DB사전입니다. DBA 가 되실라면 이중에 많이 쓰는것은 외우고 있죠 아마...^^ //----------------------------------------...)
 
1번째 줄: 1번째 줄:
−
이럴때 꼭 알아야 하는것이 DB사전입니다. DBA 가 되실라면 이중에 많이 쓰는것은 외우고 있죠 아마...^^
+
= 테이블 추출 =
−
 
 
−
//----------------------------------------
 
  
 
SELECT  TABLE_NAME Y  
 
SELECT  TABLE_NAME Y  
49번째 줄: 47번째 줄:
 
;  
 
;  
  
−
/******************************************************************************* 사용예
 
  
 
/**  
 
/**  
107번째 줄: 104번째 줄:
  
 
/**  
 
/**  
−
  *  필드 주석 추출   
+
  *  코멘트 주석 추출   
 
  **/  
 
  **/  
 
SELECT  'COMMENT ON COLUMN ' || 'korvans'||'.'||C.TABLE_NAME||'.'||COLUMN_NAME || ' IS ' || ''''|| COMMENTS ||'''' || ';'  
 
SELECT  'COMMENT ON COLUMN ' || 'korvans'||'.'||C.TABLE_NAME||'.'||COLUMN_NAME || ' IS ' || ''''|| COMMENTS ||'''' || ';'  
 
   FROM  DBA_COL_COMMENTS C  
 
   FROM  DBA_COL_COMMENTS C  
−
  WHERE  OWNER  = UPPER('$ownerName');
+
  WHERE  OWNER  = UPPER('$ownerName');
−
 
 
−
/**
 
−
* 프로시져 추출
 
−
*/
 
−
select  OWNER
 
−
    ,  NAME
 
−
    ,  TYPE
 
−
    ,  LINE
 
−
    ,  decode(line,1,'CREATE OR REPLACE '
 
−
        ||replace(text,'PACKAGE BODY ','PACKAGE BODY '
 
−
        ||owner
 
−
        ||'.'),text) AS PKG_SOURCE
 
−
from dba_source where type='PROCEDURE' and owner='$ownerName';
 
−
 
 
−
/**
 
−
* 펑션 추출 
 
−
**/
 
−
select  OWNER
 
−
    ,  NAME
 
−
    ,  TYPE
 
−
    ,  LINE
 
−
    ,  decode(line,1,'CREATE OR REPLACE '
 
−
        ||replace(text,'PACKAGE BODY ','PACKAGE BODY '
 
−
        ||owner
 
−
        ||'.'),text) AS PKG_SOURCE
 
−
from dba_source where type='FUNCTION' and owner='$ownerName';
 
−
 
 
−
/**
 
−
* 트리거 추출 
 
−
**/
 
−
select  OWNER
 
−
    ,  NAME
 
−
    ,  TYPE
 
−
    ,  LINE
 
−
    ,  decode(line,1,'CREATE OR REPLACE '
 
−
        ||replace(text,'PACKAGE BODY ','PACKAGE BODY '
 
−
        ||owner
 
−
        ||'.'),text) AS PKG_SOURCE
 
−
from dba_source where type='TRIGGER' and owner='$ownerName';
 

2018년 8월 28일 (화) 13:30 판

thumb_up 추천메뉴 바로가기

테이블 추출[편집]

SELECT TABLE_NAME Y 

       ,0 X 
       ,'CREATE TABLE ' ||RTRIM(TABLE_NAME) ||'(' 
 FROM  DBA_TABLES 
WHERE  OWNER = UPPER('사용자명')

UNION SELECT TC.TABLE_NAME Y 

       ,COLUMN_ID X 
       ,RTRIM(DECODE(COLUMN_ID,1,NULL,',')) 
       || RTRIM(COLUMN_NAME)|| ' ' 
       || RTRIM(DATA_TYPE) 
       || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,'('),'(')) 
       || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'CHAR',DATA_LENGTH,'VARCHAR2',DATA_LENGTH,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,TO_CHAR(DATA_PRECISION)
       || ',' 
       || TO_CHAR(DATA_SCALE)),'LONG',NULL,'******ERROR')) 
       || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,')'),')')) 
       || ' ' 
       || RTRIM(DECODE(NULLABLE,'N','NOT NULL',NULL)) 
 FROM  DBA_TAB_COLUMNS TC 
       ,DBA_OBJECTS O 
WHERE  O.OWNER = TC.OWNER 
 AND  O.OBJECT_NAME = TC.TABLE_NAME 
 AND O.OBJECT_TYPE = 'TABLE' 
 AND O.OWNER = UPPER('사용자명')

UNION SELECT TABLE_NAME Y 

     ,999999 X 
     ,')' || CHR(10) 
           -- ||' STORAGE(' || CHR(10) 
           -- ||' INITIAL ' || INITIAL_EXTENT || CHR(10) 
           -- ||' NEXT ' || NEXT_EXTENT || CHR(10) 
           -- ||' MINEXTENTS ' || MIN_EXTENTS || CHR(10) 
           -- ||' MAXEXTENTS ' || MAX_EXTENTS || CHR(10) 
           -- ||' PCTINCREASE '|| PCT_INCREASE || ')' ||CHR(10) 
           -- ||' INITRANS ' || INI_TRANS || CHR(10) 
           -- ||' MAXTRANS ' || MAX_TRANS || CHR(10) 
           -- ||' PCTFREE ' || PCT_FREE || CHR(10) 
           -- ||' PCTUSED ' || PCT_USED || CHR(10) 
           -- ||' PARALLEL (DEGREE ' || DEGREE || ') ' || CHR(10) 
           -- ||' TABLESPACE ' || RTRIM(TABLESPACE_NAME) ||CHR(10) 
           ||';'||CHR(10)||CHR(10) 
 FROM  DBA_TABLES 
WHERE  OWNER = UPPER('사용자명') 
ORDER  BY 1,2


/** 

* 테이블 추출 Query  
**/

SELECT TABLE_NAME Y 

       ,0 X 
       ,'CREATE TABLE ' ||RTRIM(TABLE_NAME) ||'(' 
 FROM  DBA_TABLES 
WHERE  OWNER = UPPER('$ownerName')

UNION SELECT TC.TABLE_NAME Y 

       ,COLUMN_ID X 
       ,RTRIM(DECODE(COLUMN_ID,1,NULL,',')) 
       || RTRIM(COLUMN_NAME)|| ' ' 
       || RTRIM(DATA_TYPE) 
       || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,'('),'(')) 
       || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'CHAR',DATA_LENGTH,'VARCHAR2',DATA_LENGTH,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,TO_CHAR(DATA_PRECISION)
       || ',' 
       || TO_CHAR(DATA_SCALE)),'LONG',NULL,'******ERROR')) 
       || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,')'),')')) 
       || ' ' 
       || RTRIM(DECODE(NULLABLE,'N','NOT NULL',NULL)) 
 FROM  DBA_TAB_COLUMNS TC 
       ,DBA_OBJECTS O 
WHERE  O.OWNER = TC.OWNER 
 AND  O.OBJECT_NAME = TC.TABLE_NAME 
 AND O.OBJECT_TYPE = 'TABLE' 
 AND O.OWNER = UPPER('$ownerName')

UNION SELECT A.TABLE_NAME Y 

     ,999999 X 
     ,')' || CHR(10) 
           -- ||' STORAGE(' || CHR(10) 
           -- ||' INITIAL ' || INITIAL_EXTENT || CHR(10) 
           -- ||' NEXT ' || NEXT_EXTENT || CHR(10) 
           -- ||' MINEXTENTS ' || MIN_EXTENTS || CHR(10) 
           -- ||' MAXEXTENTS ' || MAX_EXTENTS || CHR(10) 
           -- ||' PCTINCREASE '|| PCT_INCREASE || ')' ||CHR(10) 
           -- ||' INITRANS ' || INI_TRANS || CHR(10) 
           -- ||' MAXTRANS ' || MAX_TRANS || CHR(10) 
           -- ||' PCTFREE ' || PCT_FREE || CHR(10) 
           -- ||' PCTUSED ' || PCT_USED || CHR(10) 
           -- ||' PARALLEL (DEGREE ' || DEGREE || ') ' || CHR(10) 
           -- ||' TABLESPACE ' || RTRIM(TABLESPACE_NAME) ||CHR(10) 
           ||';'||CHR(10)||CHR(10) 
           || 'COMMENT ON TABLE ' || A.TABLE_NAME || ' IS '||' || B.COMMENTS ||' 
           ||';'||CHR(10)||CHR(10) 
 FROM  DBA_TABLES A 
   ,  DBA_TAB_COMMENTS B 
WHERE  A.TABLE_NAME    = B.TABLE_NAME 
 AND  A.OWNER        = B.OWNER 
 AND  A.OWNER        = UPPER('$ownerName') 
ORDER  BY 1,2


/** 

*  코멘트 주석 추출  
**/

SELECT 'COMMENT ON COLUMN ' || 'korvans'||'.'||C.TABLE_NAME||'.'||COLUMN_NAME || ' IS ' || '|| COMMENTS ||' || ';' 

 FROM  DBA_COL_COMMENTS C 
WHERE  OWNER  = UPPER('$ownerName');
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=ORACLE_추출쿼리&oldid=263"