oracle -> mssql 쿼리변환 ... 0 0 853

by 초짜 [PL/SQL] [2018.06.27 17:01:41]


 

아래의 쿼리를 오라클에서 MSSQL,로 변환 할려고 하는데 업렵네됴..ㅠ.ㅠ

 

고수분들의 도움 요청 드립니다. ...

 

DECLARE
   V_TABLE_NAME_ENG VARCHAR2(200);
   V_TABLE_NAME_KOR   VARCHAR2(200);
   V_COULUMN_NAME_KOR   VARCHAR2(200);
   V_COULUMN_NAME_ENG   VARCHAR2(200);
   V_TEXT_TABLE   VARCHAR2(200);
   V_TEXT          VARCHAR2(200);
   V_TEXT_TWO      VARCHAR2(200);
   V_TEXT_THREE      VARCHAR2(200);
   V_TEXT_FORE VARCHAR2(400);
   V_YP_KG     VARCHAR2(200);
   V_TS_MPA    VARCHAR2(200);
   V_TS_KG     VARCHAR2(200);
   V_EL        VARCHAR2(200);
   V_NVAL      VARCHAR2(200);
   V_THK       VARCHAR2(200);
   V_MQL_TSTP_NO VARCHAR2(200);
   V_MQL_TSTP_GTH_PRT VARCHAR2(200);
   V_PLNT_TP VARCHAR(200);
   V_TEXT_SIN_QT VARCHAR(200);
   V_TEXT_DBO VARCHAR(200);
   V_TEXT_COLUMN VARCHAR(200);
   V_TEXT_SENTENCT VARCHAR(200);
   CURSOR C1 IS
          SELECT DECODE(ROWNUM,1,'TABLE1'
                               ,2,'TABLE12'
                              ,3,'OIS_ARV_DELIVERY_SHIP'
                              ,4,'OIS_BERTH'
                              ,5,'OIS_PUBLIC_CODE'
                              ,6,'OIS_SHIP_INFO'
                              ,7,'OIS_SHIP_INFO_NEW'
                              ,8,'OIS_SHIP_INFO_REMARK_HISTORY'
                              ,9,'OIS_TERMINAL_FEEDBACK'
                              ,10,'OIS_VETTING_ASSESSMENT'
                              ,11,'OIS_VETTING_REQUEST'
                              --,12,'TB_MD_G_LOG_IF'
--                              ,14,'TB_MD_I_INOUT_ATTACH'
--                              ,15,'TB_MD_I_INOUT_ITEM'
--                              ,16,'TB_MD_I_INOUT_LOAD'
--                              ,17,'TB_MD_I_INOUT_MST'
--                              ,18,'TB_MD_L_INOUT_ORDER'
--                              ,19,'TB_MD_L_MOVE_PLAN'
--                              ,20,'TB_MD_L_MOVE_RESULT'
                             
                               
          )
         
         
         
         
         
         
          CODE_T
        
           FROM DUAL CONNECT BY LEVEL < = 11;
 BEGIN
     V_TEXT := 'EXEC sp_addextendedproperty ''MS_Description''';
     V_TEXT_TWO := ',';
     V_TEXT_THREE := '''USER''';
     V_TEXT_FORE :=  '''TABLE''';
     V_TEXT_SIN_QT := '''';
     V_TEXT_DBO :='dbo';
     V_TEXT_TABLE :=  '''TABLE''';
     V_TEXT_COLUMN := '''COLUMN''';

 FOR C1_REC IN C1 LOOP 
   V_MQL_TSTP_NO := C1_REC.CODE_T;
   -- DBMS_OUTPUT.PUT_LINE(V_MQL_TSTP_NO);
   --1.TABLE 코멘트 GET
      SELECT  A.TABLE_NAME,B.COMMENTS INTO V_TABLE_NAME_ENG, V_TABLE_NAME_KOR
      FROM    ALL_TABLES A, ALL_TAB_COMMENTS B
      WHERE   A.TABLE_NAME = B.TABLE_NAME
      AND     A.TABLE_NAME = V_MQL_TSTP_NO
      AND     A.OWNER = 'SPIRITS'
      AND     A.OWNER = B.OWNER;
      --CONNECT BY LEVEL < = 1
       DBMS_OUTPUT.PUT_LINE('-------TABLE--------');
       V_TEXT_SENTENCT := V_TEXT||V_TEXT_TWO||V_TEXT_SIN_QT||V_TABLE_NAME_KOR||V_TEXT_SIN_QT||V_TEXT_TWO||V_TEXT_THREE||V_TEXT_TWO||V_TEXT_DBO||V_TEXT_TWO||V_TEXT_FORE||V_TEXT_TWO||V_TABLE_NAME_ENG;
       DBMS_OUTPUT.PUT_LINE(V_TEXT_SENTENCT);
  
  --2.COLUMN 코멘트 GET
  DECLARE
  
   CURSOR D1 IS
     SELECT  A.COLUMN_NAME AS COLUMN_NAME,B.COMMENTS AS COMMENTS
      FROM   ALL_TAB_COLUMNS A
            ,ALL_COL_COMMENTS B
      WHERE  A.TABLE_NAME = B.TABLE_NAME
      AND    A.COLUMN_NAME = B.COLUMN_NAME
      AND    A.TABLE_NAME = V_TABLE_NAME_ENG
      AND    A.OWNER = 'SPIRITS'
      AND    A.OWNER = B.OWNER
      ORDER BY A.TABLE_NAME, A.COLUMN_ID;
          BEGIN
           DBMS_OUTPUT.PUT_LINE('-----COLUMN--------');
           FOR D1_REC IN D1 LOOP 
            DBMS_OUTPUT.PUT_LINE(V_TEXT||V_TEXT_TWO||V_TEXT_SIN_QT||D1_REC.COMMENTS||V_TEXT_SIN_QT||V_TEXT_TWO||V_TEXT_THREE||V_TEXT_TWO||V_TEXT_DBO||V_TEXT_TWO||V_TEXT_TABLE||V_TEXT_TWO||V_TABLE_NAME_ENG||V_TEXT_TWO
            ||V_TEXT_COLUMN||V_TEXT_TWO||D1_REC.COLUMN_NAME
            );
           -- DBMS_OUTPUT.PUT_LINE(D1_REC.COMMENTS);
            END LOOP; 
           
END;

   END LOOP;

 

 
 
 END;

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입