文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle语法迁移> 模式对象> 数据库关键字

数据库关键字

分享
更新时间: 2019/08/09 GMT+08:00

Migration Tool支持DWS关键字,如NAME、LIMIT、OWNER、KEY和CAST。这些关键字需通过双引号引出。

Gauss 关键字 (NAME、VERSION、LABEL、POSITION)

NAME, VERSION, LABEL, POSITION关键字迁移为AS 关键字

输入:NAME,VERSION,LABEL,POSITION

SELECT id, NAME,label,description
        FROM (SELECT a.id             id,
                     b.NAME           NAME,
                     b.description    description,
                     b.default_label  label,
                     ROWNUM           ROW_ID
                FROM CTP_ITEM A
                LEFT OUTER JOIN CTP_ITEM_NLS B ON A.ID = B.ID
                                              AND B.LOCALE = i_language
               ORDER BY a.id ASC)
       WHERE ROW_ID >= to_number(begNum)
         AND ROW_ID < to_number(begNum) + to_number(fetchNum);
 
SELECT DISTINCT REPLACE(VERSION,' ','') ID, VERSION TEXT
        FROM (SELECT T1.SOFTASSETS_NAME, T2.VERSION
                FROM SPMS_SOFT_ASSETS T1, SPMS_SYSSOFT_ASSETS T2
               WHERE T1.SOFTASSETS_ID = T2.SOFTASSETS_ID)
          WHERE SOFTASSETS_NAME = I_SOFT_NAME;
 
SELECT COUNTRY, AMOUNT
          FROM (SELECT '' COUNTRY || '' AMOUNT, '1' POSITION
                  FROM DUAL )
         ORDER BY POSITION;

输出

SELECT id,NAME,label,description FROM (
 SELECT a.id id,b.NAME AS NAME,
 b.description description
 ,b.default_label AS label,
 ROW_NUMBER( ) OVER( ) ROW_ID
 FROM CTP_ITEM A LEFT OUTER JOIN 
 CTP_ITEM_NLS B
 ON A.ID = B.ID AND 
 B.LOCALE = i_language
 ORDER BY a.id ASC) WHERE
 ROW_ID >= to_number( begNum )
 AND 
 ROW_ID < to_number( begNum ) + to_number( fetchNum )
;
 
SELECT
  DISTINCT REPLACE( VERSION ,' ' ,'' ) ID
     ,VERSION AS TEXT
     FROM
        (
         SELECT
         T1.SOFTASSETS_NAME
         ,T2.VERSION
         FROM
        SPMS_SOFT_ASSETS T1
       ,SPMS_SYSSOFT_ASSETS T2
        WHERE
        T1.SOFTASSETS_ID = T2.SOFTASSETS_ID
       )
      WHERE SOFTASSETS_NAME = I_SOFT_NAME ;
   
 
   
SELECT COUNTRY ,AMOUNT
FROM ( SELECT '' COUNTRY || '' AMOUNT
        ,'1' AS POSITION
          FROM
         DUAL
       )
     ORDER BY
     POSITION
;

TEXT和YEAR

输入:TEXT, YEAR

SELECT 
  NAME, 
  VALUE, 
  DESCRIPTION TEXT, 
  JOINED YEAR, 
  LIMIT 
FROM 
  EMPLOYEE;
  
SELECT 
  NAME, 
  TEXT, 
  YEAR, 
  VALUE, 
  DESCRIPTION, 
  LIMIT 
FROM 
  EMPLOYEE_DETAILS;

输出

SELECT 
  "NAME", 
  VALUE, 
  DESCRIPTION AS TEXT, 
  JOINED AS YEAR, 
  "LIMIT" 
FROM 
  EMPLOYEE;
 
SELECT 
  "NAME", 
  "TEXT", 
  "YEAR", 
  VALUE, 
  DESCRIPTION, 
  "LIMIT" 
FROM 
  EMPLOYEE_DETAILS;

NAME和LIMIT

输入:DWS关键词NAME和LIMIT

CREATE TABLE NAME
      ( NAME VARCHAR2(50) NOT NULL
      , VALUE VARCHAR2(255)
      , DESCRIPTION VARCHAR2(4000)
      , LIMIT NUMBER(9)
      )
  /*TABLESPACE users*/
  pctfree 10   initrans 1   maxtrans 
  255  
     storage ( initial 256K next 256K 
     minextents 1 maxextents 
     unlimited ); 
   
 SELECT NAME, VALUE, DESCRIPTION, LIMIT
   FROM NAME;

输出

CREATE TABLE "NAME"
         ( "NAME" VARCHAR2 (50) NOT NULL
         , VALUE VARCHAR2 (255)
         , DESCRIPTION VARCHAR2 (4000)
         , "LIMIT" NUMBER (9)
         ) 
      /*TABLESPACE users*/ 
      pctfree 10 initrans 1 maxtrans 255 
      storage ( initial 256 K NEXT 256 K minextents 1 
      maxextents unlimited );

 SELECT "NAME", VALUE, DESCRIPTION, "LIMIT"
   FROM "NAME";

OWNER

Bulk操作

输入:DWS关键词OWNER使用SELECT

SELECT
          owner
     FROM
          Test_Col;

输出

SELECT
          "OWNER"
     FROM
          Test_Col;

输入:DWS关键词OWNER使用DELETE

DELETE FROM emp14
     WHERE
          ename = 'Owner';

输入

DELETE FROM emp14
     WHERE
          ename = 'Owner'

KEY

Blogic操作

输入:DWS关键词KEY

CREATE
     OR REPLACE FUNCTION myfct RETURN VARCHAR2 parallel_enable IS res VARCHAR2 ( 200 ) ;
     BEGIN
          res := 100 ;
          INSERT INTO emp18 RW ( RW.empno ,RW.ename ) SELECT
               res ,RWN.ename KEY
          FROM
               emp16 RWN ;
               COMMIT ;
          RETURN res ;
END ;
/

输出

CREATE
     OR REPLACE FUNCTION myfct RETURN VARCHAR2 IS res VARCHAR2 ( 200 ) ;
     BEGIN
          res := 100 ;
          INSERT INTO emp18 ( empno ,ename ) SELECT
               res ,RWN.ename "KEY"
          FROM
               emp16 RWN ;
               /* COMMIT; */
          null ;
          RETURN res ;
END ;

范围、账号和语言

当Gauss关键字被用作SELECT列表中任何列的别名,AS关键字没有被定义时,需要使用AS关键字来定义别名。

输入

CREATE
     OR REPLACE /*FORCE*/
     VIEW SAD.FND_TERRITORIES_TL_V (
          TERRITORY_CODE
          ,TERRITORY_SHORT_NAME
          ,LANGUAGE
          ,Account
          ,Range
          ,LAST_UPDATED_BY
          ,LAST_UPDATE_DATE
          ,LAST_UPDATE_LOGIN
          ,DESCRIPTION
          ,SOURCE_LANG
          ,ISO_NUMERIC_CODE
     ) AS SELECT
               t.TERRITORY_CODE
               ,t.TERRITORY_SHORT_NAME
               ,t.LANGUAGE
               ,t.Account
               ,t.Range
               ,t.LAST_UPDATED_BY
               ,t.LAST_UPDATE_DATE
               ,t.LAST_UPDATE_LOGIN
               ,t.DESCRIPTION
               ,t.SOURCE_LANG
               ,t.ISO_NUMERIC_CODE
          FROM
               fnd_territories_tl t
     UNION
     ALL SELECT
               'SS' TERRITORY_CODE
               ,'Normal Country' TERRITORY_SHORT_NAME
               ,NULL LANGUAGE
               ,NULL Account
               ,NULL Range
               ,NULL LAST_UPDATED_BY
               ,NULL LAST_UPDATE_DATE
               ,NULL LAST_UPDATE_LOGIN
               ,NULL DESCRIPTION
               ,NULL SOURCE_LANG
               ,NULL ISO_NUMERIC_CODE
          FROM
               DUAL ;

输出

CREATE
     OR REPLACE /*FORCE*/
     VIEW SAD.FND_TERRITORIES_TL_V (
          TERRITORY_CODE
          ,TERRITORY_SHORT_NAME
          ,LANGUAGE
          ,CREATED_BY
          ,CREATION_DATE
          ,LAST_UPDATED_BY
          ,LAST_UPDATE_DATE
          ,LAST_UPDATE_LOGIN
          ,DESCRIPTION
          ,SOURCE_LANG
          ,ISO_NUMERIC_CODE
     ) AS SELECT
               t.TERRITORY_CODE
               ,t.TERRITORY_SHORT_NAME
               ,t.LANGUAGE
               ,t.CREATED_BY
               ,t.CREATION_DATE
               ,t.LAST_UPDATED_BY
               ,t.LAST_UPDATE_DATE
               ,t.LAST_UPDATE_LOGIN
               ,t.DESCRIPTION
               ,t.SOURCE_LANG
               ,t.ISO_NUMERIC_CODE
          FROM
               fnd_territories_tl t
     UNION
     ALL SELECT
               'SS' TERRITORY_CODE
               ,'Normal Country' TERRITORY_SHORT_NAME
               ,NULL AS LANGUAGE
               ,NULL CREATED_BY
               ,NULL CREATION_DATE
               ,NULL LAST_UPDATED_BY
               ,NULL LAST_UPDATE_DATE
               ,NULL LAST_UPDATE_LOGIN
               ,NULL DESCRIPTION
               ,NULL SOURCE_LANG
               ,NULL ISO_NUMERIC_CODE
          FROM
               DUAL ;

主键,唯一键

如果在表创建时声明了主键和唯一键两个约束,只需考虑主键迁移。

create table SD_WO.WO_DU_TRIGGER_REVENUE_T
(
  TRIGGER_REVENUE_ID NUMBER not null,
  PROJECT_NUMBER     VARCHAR2(40),
  DU_ID              NUMBER,
  STANDARD_MS_CODE   VARCHAR2(100),
  TRIGGER_STATUS     NUMBER,
  TRIGGER_MSG        VARCHAR2(4000),
  BATCH_NUMBER       NUMBER,
  PROCESS_STATUS     NUMBER,
  ENABLE_FLAG        CHAR(1) default 'Y',
  CREATED_BY         NUMBER,
  CREATION_DATE      DATE,
  LAST_UPDATE_BY     NUMBER,
  LAST_UPDATE_DATE   DATE
)
;  

alter table SD_WO.WO_DU_TRIGGER_REVENUE_T
  add constraint WO_DU_TRIGGER_REVENUE_PK primary key (TRIGGER_REVENUE_ID);
alter table SD_WO.WO_DU_TRIGGER_REVENUE_T
  add constraint WO_DU_TRIGGER_REVENUE_N1 unique (DU_ID, STANDARD_MS_CODE);

输出

CREATE
     TABLE
          SD_WO.WO_DU_TRIGGER_REVENUE_T (
               TRIGGER_REVENUE_ID NUMBER NOT NULL
               ,PROJECT_NUMBER VARCHAR2 (40)
               ,DU_ID NUMBER
               ,STANDARD_MS_CODE VARCHAR2 (100)
               ,TRIGGER_STATUS NUMBER
               ,TRIGGER_MSG VARCHAR2 (4000)
               ,BATCH_NUMBER NUMBER
               ,PROCESS_STATUS NUMBER
               ,ENABLE_FLAG CHAR( 1 ) DEFAULT 'Y'
               ,CREATED_BY NUMBER
               ,CREATION_DATE DATE
               ,LAST_UPDATE_BY NUMBER
               ,LAST_UPDATE_DATE DATE
               ,CONSTRAINT WO_DU_TRIGGER_REVENUE_PK PRIMARY KEY (TRIGGER_REVENUE_ID)
          ) ;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区