文档首页/ 数据仓库服务 GaussDB(DWS)/ 更多文档/ 工具指南（阿布扎比区域）/ DSC SQL语法迁移工具/ Oracle语法迁移/ 模式对象/ 数据库关键字
更新时间：2023-03-17 GMT+08:00
查看PDF
分享

数据库关键字

DSC支持GaussDB(DWS)关键字，如NAME、LIMIT、OWNER、KEY和CAST。这些关键字必须放在双引号内。

Gauss关键字（NAME/VERSION/LABEL/POSITION）

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

输入：NAME，VERSION，LABEL，POSITION

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
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;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
SELECT 
  NAME, 
  VALUE, 
  DESCRIPTION TEXT, 
  JOINED YEAR, 
  LIMIT 
FROM 
  EMPLOYEE;
  
SELECT 
  NAME, 
  TEXT, 
  YEAR, 
  VALUE, 
  DESCRIPTION, 
  LIMIT 
FROM 
  EMPLOYEE_DETAILS;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
SELECT 
  "NAME", 
  VALUE, 
  DESCRIPTION AS TEXT, 
  JOINED AS YEAR, 
  "LIMIT" 
FROM 
  EMPLOYEE;
 
SELECT 
  "NAME", 
  "TEXT", 
  "YEAR", 
  VALUE, 
  DESCRIPTION, 
  "LIMIT" 
FROM 
  EMPLOYEE_DETAILS;

NAME和LIMIT

输入：GaussDB(DWS)关键字NAME和LIMIT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
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;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
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操作

输入：使用SELECT查询GaussDB(DWS)关键字OWNER

1
2
3
4
 
SELECT
          owner
     FROM
          Test_Col;

输出

1
2
3
4
 
SELECT
          "OWNER"
     FROM
          Test_Col;

输入：DELETE，GaussDB(DWS)关键字OWNER

1
2
3
 
DELETE FROM emp14
     WHERE
          ename = 'Owner';

输入

1
2
3
 
DELETE FROM emp14
     WHERE
          ename = 'Owner'

KEY

Blogic操作

输入：GaussDB(DWS)关键字KEY

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
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 ;
/

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
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关键字”的格式来定义别名。

输入

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
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 ;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
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 ;

主键和唯一键

如果在建表时声明了主键和唯一键两个约束，仅迁移主键。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
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)
          ) ;

PROMPT命令

PROMPT命令应转换成Gauss支持的\ECHO命令。

Oracle语法

迁移后语法

 
prompt
prompt Creating table product
prompt ===============================
prompt
create table product
(
  product_id     VARCHAR2(20),
  product_name   VARCHAR2(50)
);
 
\echo
\echo Creating table product
\echo ===============================
\echo
CREATE TABLE product
(
  product_id     VARCHAR2(20),
  product_name   VARCHAR2(50)
);
父主题： 模式对象