oracle 表语法,表（Oracle）_数据仓库服务 GaussDB(DWS)_工具指南_DSC SQL语法迁移工具_SQL语法迁移参考_Oracle语法迁移（至GaussDB A）_华为云...

CREATE TABLE

Oracle的CREATE TABLE语句用于创建表。GaussDB直接支持该语句，无需迁移

ALTER TABLE

Oracle的ALTER TABLE语句用于新增、重命名、修改或删除表列。GaussDB直接支持该语句，无需迁移。

PRIMARY KEY

Oracle中如果存在两张表具有相同的主键字段，则在执行ALTER TABLE时需加上表名进行区分。

输入：PRIMARY KEY

CREATE TABLE CTP_ARM_CONFIG

( HOSTNAME VARCHAR2(50),

OPNAME VARCHAR2(50),

PARAMTYPE VARCHAR2(2),

PARAMVALUE NUMBER(*,0),

MODIFYDATE DATE

) SEGMENT CREATION DEFERRED

PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE( PCTINCREASE 0

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE SPMS_DATA ;

ALTER TABLE CTP_ARM_CONFIG ADD CONSTRAINT PKCTP_ARM_CONFIG PRIMARY KEY (HOSTNAME, OPNAME)

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE( PCTINCREASE 0

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE SPMS_DATA ENABLE;

输出

CREATE

TABLE

CTP_ARM_CONFIG (

HOSTNAME VARCHAR2 (50)

,OPNAME VARCHAR2 (50)

,PARAMTYPE VARCHAR2 (2)

,PARAMVALUE NUMBER (

38

,0

)

,MODIFYDATE DATE

,CONSTRAINT PKCTP_ARM_CONFIG PRIMARY KEY (

HOSTNAME

,OPNAME

)

) /*SEGMENT CREATION DEFERRED*/

/*PCTFREE 10*/

/*PCTUSED 0*/

/*INITRANS 1*/

/*MAXTRANS 255*/

/*NOCOMPRESS*/

/*LOGGING*/

/*STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/

/*TABLESPACE SPMS_DATA */

;

UNIQUE约束

以下ALTER TABLE语句包含约束，如果在GaussDB直接调用会报错：Cannot create index whose evaluation cannot be enforced to remote nodes.

该约束迁移和PRIMARY KEY类似。如果已有PRIMARY KEY/UNIQUE约束，无需迁移，保持原样。

输入

CREATE

TABLE

GCC_PLAN.T1033 (

ROLLOUT_PLAN_LINE_ID NUMBER NOT NULL

,UDF_FIELD_VALUE_ID NUMBER NOT NULL

) ;

ALTER TABLE

GCC_PLAN.T1033 ADD CONSTRAINT UDF_FIELD_VALUE_ID_PK UNIQUE (UDF_FIELD_VALUE_ID) ;

输出

CREATE TABLE

GCC_PLAN.T1033

(

ROLLOUT_PLAN_LINE_ID NUMBER NOT NULL

,UDF_FIELD_VALUE_ID NUMBER NOT NULL

,CONSTRAINT UDF_FIELD_VALUE_ID_PK UNIQUE (UDF_FIELD_VALUE_ID)

) ;

NULL约束

在以下包中声明局部变量时不支持NULL约束：

L_CONTRACT_DISTRIBUTE_STATUS SAD_DISTRIBUTION_HEADERS_T.STATUS%TYPE NULL ;

输入

CREATE OR REPLACE FUNCTION CONTRACT_DISTRIBUTE_STATUS_S2(PI_CONTRACT_NUMBER IN VARCHAR2)

RETURN VARCHAR2 IS

L_CONTRACT_DISTRIBUTE_STATUS BAS_SUBTYPE_PKG.STATUS NULL;

BEGIN

FOR CUR_CONTRACT IN (SELECT HT.CONTRACT_STATUS

FROM SAD_CONTRACTS_V HT

WHERE HT.HTH = PI_CONTRACT_NUMBER)

LOOP

IF CUR_CONTRACT.CONTRACT_STATUS = 0 THEN

L_CONTRACT_DISTRIBUTE_STATUS := 'Cancel';

ELSE

L_CONTRACT_DISTRIBUTE_STATUS := BAS_SUBTYPE_PKG.G_HEADER_WAITING_SPLIT_STATUS;

END IF;

END LOOP;

RETURN L_CONTRACT_DISTRIBUTE_STATUS;

END CONTRACT_DISTRIBUTE_STATUS_S2;

/

输出

CREATE OR REPLACE FUNCTION CONTRACT_DISTRIBUTE_STATUS_S2

( PI_CONTRACT_NUMBER IN VARCHAR2 )

RETURN VARCHAR2

PACKAGE

IS

L_CONTRACT_DISTRIBUTE_STATUS BAS_SUBTYPE_PKG.STATUS /*NULL*/;

BEGIN

FOR CUR_CONTRACT IN ( SELECT HT.CONTRACT_STATUS

FROM SAD_CONTRACTS_V HT

WHERE HT.HTH = PI_CONTRACT_NUMBER )

LOOP

IF CUR_CONTRACT.CONTRACT_STATUS = 0 THEN

L_CONTRACT_DISTRIBUTE_STATUS := 'Cancel' ;

ELSE

L_CONTRACT_DISTRIBUTE_STATUS := BAS_SUBTYPE_PKG.G_HEADER_WAITING_SPLIT_STATUS ;

END IF ;

END LOOP ;

RETURN L_CONTRACT_DISTRIBUTE_STATUS ;

END ;

/

未创建索引

如果ALTER TABLE中使用了INDEX或STORAGE参数，需要删掉。需要在CREATE TABLE中添加约束。

输入：PRIMARY KEY

CREATE TABLE CTP_ARM_CONFIG

( HOSTNAME VARCHAR2(50),

OPNAME VARCHAR2(50),

PARAMTYPE VARCHAR2(2),

PARAMVALUE NUMBER(*,0),

MODIFYDATE DATE

) SEGMENT CREATION DEFERRED

PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE( PCTINCREASE 0

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE SPMS_DATA ;

ALTER TABLE CTP_ARM_CONFIG ADD CONSTRAINT PKCTP_ARM_CONFIG PRIMARY KEY

(HOSTNAME, OPNAME)

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE( PCTINCREASE 0

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE SPMS_DATA ENABLE;

输出

CREATE TABLE

CTP_ARM_CONFIG (

HOSTNAME VARCHAR2 (50)

,OPNAME VARCHAR2 (50)

,PARAMTYPE VARCHAR2 (2)

,PARAMVALUE NUMBER (

38

,0

)

,MODIFYDATE DATE

,CONSTRAINT PKCTP_ARM_CONFIG PRIMARY KEY (

HOSTNAME

,OPNAME

)

) /*SEGMENT CREATION DEFERRED*/

/*PCTFREE 10*/

/*PCTUSED 0*/

/*INITRANS 1*/

/*MAXTRANS 255*/

/*NOCOMPRESS*/

/*LOGGING*/

/*STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE

DEFAULT)*/

/*TABLESPACE SPMS_DATA */

;

分区

大表和索引的维护越来越耗费时间和资源。同时，这些对象会导致数据访问性能明显降低。表和索引的分区可从各方面提升性能、便于维护。

图1表的分区和子分区

DSC支持范围分区。

该工具不支持以下分区/子分区(在迁移脚本中会被注释掉)：

列表分区

Hash分区

范围子分区

列表子分区

Hash子分区

未来可能会支持当前不支持的分区/子分区。该工具中，用户可设置配置参数，启用/禁用对不支持语句的注释功能。详情请参见Oracle配置参数。

PARTITION BY HASH

Hash分区是一种分区技术，其中Hash算法用于在不同分区(子表)之间均匀分配行。通常在无法进行范围分区时使用该技术，例如通过员工ID、产品ID等进行分区。DSC不支持PARTITION BY HASH和SUBPARTITION BY HASH，且会注释掉这些语句。

输入：HASH PARTITION

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32)) PARTITION BY HASH(deptno) PARTITIONS 16;

输出

CREATE TABLE dept ( deptno NUMBER ,deptname VARCHAR( 32 ) ) /* PARTITION BY HASH(deptno) PARTITIONS 16 */ ;

输入：HASH PARTITION，不使用分区名

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))

PARTITION BY HASH(deptno) PARTITIONS 16;

输出

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))

/* PARTITION BY HASH(deptno) PARTITIONS 16 */;

输入：HASH SUBPARTITION

CREATE TABLE sales

( prod_id NUMBER(6)

, cust_id NUMBER

, time_id DATE

, channel_id CHAR(1)

, promo_id NUMBER(6)

, quantity_sold NUMBER(3)

, amount_sold NUMBER(10,2)

)

PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)

SUBPARTITIONS 8STORE IN (ts1, ts2, ts3, ts4)

( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))

, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))

, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

);

输出CREATE TABLE sales

( prod_id NUMBER(6)

, cust_id NUMBER

, time_id DATE

, channel_id CHAR(1)

, promo_id NUMBER(6)

, quantity_sold NUMBER(3)

, amount_sold NUMBER(10,2)

)

PARTITION BY RANGE (time_id) /*SUBPARTITION BY HASH (cust_id)

SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4) */

( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))

, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))

, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

);

PARTITION BY LIST

列表分区是一种分区技术，在每个分区的说明中指定分区键的离散值列表。DSC不支持PARTITION BY LIST和SUBPARTITION BY LIST，且会注释掉这些语句。

输入：LIST PARTITION

CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER, store_name VARCHAR(30), state_code VARCHAR(2), sale_date DATE) STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tbs5 PARTITION BY LIST (state_code) ( PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ') STORAGE (INITIAL 8M) TABLESPACE tbs8, PARTITION region_west VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO') NOLOGGING, PARTITION region_south VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'), PARTITION region_central VALUES ('OH','ND','SD','MO','IL','MI','IA'), PARTITION region_null VALUES (NULL), PARTITION region_unknown VALUES (DEFAULT) );

输出

CREATE UNLOGGED TABLE sales_by_region ( item# INTEGER ,qty INTEGER ,store_name VARCHAR( 30 ) ,state_code VARCHAR( 2 ) ,sale_date DATE ) TABLESPACE tbs5 /* PARTITION BY LIST(state_code)(PARTITION region_east VALUES('MA','NY','CT','NH','ME','MD','VA','PA','NJ') TABLESPACE tbs8, PARTITION region_west VALUES('CA','AZ','NM','OR','WA','UT','NV','CO') , PARTITION re