oracle 表语法,表（Oracle）_数据仓库服务 GaussDB(DWS)_工具指南_DSC SQL语法迁移工具_SQL语法迁移参考_Oracle语法迁移（至GaussDB A）_华为云...
CREATE TABLEOracle的CREATE TABLE语句用于创建表。GaussDB直接支持该语句，无需迁移ALTER TABLEOracle的ALTER TABLE语句用于新增、重命名、修改或删除表列。GaussDB直接支持该语句，无需迁移。PRIMARY KEYOracle中如果存在两张表具有相同的主键字段，则在执行ALTER TABLE时需加上表名进行区分。输入：PRIMARY KE
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 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) ) */ ;
输入：LIST PARTITION(使用STORAGE参数)
CREATE TABLE store_master
( Store_id NUMBER
, Store_address VARCHAR2 (40)
, City VARCHAR2 (30)
, State VARCHAR2 (2)
, zip VARCHAR2 (10)
, manager_id NUMBER
)
/*TABLESPACE users*/
STORAGE ( INITIAL 100 k NEXT 100 k
PCTINCREASE 0 )
PARTITION BY LIST (city)
( PARTITION south_florida
VALUES ( 'MIA', 'ORL' )
/*TABLESPACE users*/
STORAGE ( INITIAL 100 k NEXT 100
k PCTINCREASE 0 )
, PARTITION north_florida
VALUES ( 'JAC', 'TAM', 'PEN' )
/*TABLESPACE users*/
STORAGE ( INITIAL 100 k NEXT 100
k PCTINCREASE 0 )
, PARTITION south_georga VALUES
( 'BRU', 'WAY', 'VAL' )
/*TABLESPACE users*/
STORAGE ( INITIAL 100 k NEXT 100
k PCTINCREASE 0 )
, PARTITION north_georgia
VALUES ( 'ATL', 'SAV', NULL )
);
输出
CREATE TABLE store_master
( Store_id NUMBER
, Store_address VARCHAR2 (40)
, City VARCHAR2 (30)
, State VARCHAR2 (2)
, zip VARCHAR2 (10)
, manager_id NUMBER
)
/*TABLESPACE users*/
STORAGE ( INITIAL 100 k NEXT 100 k );
输入：LIST PARTITIONTABLE，基于其他TABLE
CREATE TABLE tab1_list
PARTITION BY LIST (col1)
( partition part1 VALUES ( 1 )
, partition part2 VALUES ( 2,
3, 4 )
, partition part3 VALUES
(DEFAULT)
)
AS
SELECT *
FROM tab1;
输出
CREATE TABLE tab1_list
AS
( SELECT *
FROM tab1 );
输入：LIST PARTITION，使用SUBPARTITIONS
CREATE TABLE big_t_list PARTITION BY LIST(n10) (partition part1 VALUES (1) ,partition part2 VALUES (2,3,4) ,partition part3 VALUES (DEFAULT)) AS SELECT * FROM big_t;
输出
CREATE TABLE big_t_list /* PARTITION BY LIST(n10)(partition part1 VALUES(1) ,partition part2 VALUES(2,3,4) ,partition part3 VALUES(DEFAULT)) */ AS ( SELECT * FROM big_t ) ;
输入：LIST PARTITION，使用SUBPARTITION TEMPLATE
CREATE TABLE q1_sales_by_region
( deptno NUMBER
, deptname varchar2 (20)
, quarterly_sales NUMBER
(10,2)
, state varchar2 (2)
)
PARTITION BY LIST (state)
SUBPARTITION BY RANGE
(quarterly_sales)
SUBPARTITION TEMPLATE
( SUBPARTITION original VALUES
LESS THAN (1001)
, SUBPARTITION acquired VALUES
LESS THAN (8001)
, SUBPARTITION recent VALUES
LESS THAN (MAXVALUE)
)
( PARTITION q1_northwest VALUES
( 'OR', 'WA' )
, PARTITION q1_southwest VALUES
( 'AZ', 'UT', 'NM' )
, PARTITION q1_northeast VALUES
( 'NY', 'VM', 'NJ' )
, PARTITION q1_southcentral VALUES
( 'OK', 'TX' )
);
输出
CREATE TABLE q1_sales_by_region
( deptno NUMBER
, deptname varchar2 (20)
, quarterly_sales NUMBER (10,2)
, state varchar2 (2)
);
PARTITION BY RANGE
范围分区是一种分区技术，将不同范围数据分别存储在不同的子表中。当用户需要将不同范围的数据(例如日期字段)存储在一起时，范围分区很有用。DSC支持 PARTITION BY RANGE，不支持SUBPARTITION BY RANGE，且会注释掉该语句。
输入：RANGE PARTITION(使用STORAGE参数)
CREATE
TABLE
CCM_TA550002_H (
STRU_ID VARCHAR2 (10)
,ORGAN1_NO VARCHAR2 (10)
,ORGAN2_NO VARCHAR2 (10)
) partition BY range (ORGAN2_NO) (
partition CCM_TA550002_01
VALUES LESS than ('00100') /* TABLESPACE users */
/*pctfree 10*/
/*initrans 1*/
/*storage(initial 256 K NEXT 256 K minextents 1 maxextents unlimited )*/
,partition CCM_TA550002_02
VALUES LESS than ('00200') /* TABLESPACE users */
/*pctfree 10*/
/*initrans 1*/
/* storage ( initial 256 K NEXT
256K minextents 1
maxextents unlimited
pctincrease 0 )*/
输出
CREATE TABLE CCM_TA550002_H
( STRU_ID VARCHAR2 (10)
, ORGAN1_NO VARCHAR2 (10)
, ORGAN2_NO VARCHAR2 (10)
)
partition BY range (ORGAN2_NO)
( partition CCM_TA550002_01 VALUES LESS
than ('00100')
/*TABLESPACE users*/
, partition CCM_TA550002_02 VALUES LESS
than ('00200')
/*TABLESPACE users*/
);
输入：RANGE PARTITION，使用SUBPARTITIONS
CREATE TABLE composite_rng_list (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
cust_state VARCHAR2(2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY LIST (cust_state)
SUBPARTITION TEMPLATE(
SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE part1,
SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE part2,
SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE part3) (
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN(MAXVALUE));
输出
CREATE TABLE composite_rng_list (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
cust_state VARCHAR2(2),
time_id DATE)
PARTITION BY RANGE(time_id)
/*SUBPARTITION BY LIST (cust_state)
SUBPARTITION TEMPLATE(
SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE part1,
SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE part2,
SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE part3)*/ (
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN(MAXVALUE));
输入：RANGE PARTITION，使用SUBPARTITION TEMPLATECREATE TABLE composite_rng_rng (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
cust_state VARCHAR2(2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY RANGE (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION original VALUES LESS THAN (1001) TABLESPACE part1,
SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE part2,
SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACE part3) (
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN (MAXVALUE));
输出
CREATE TABLE composite_rng_rng (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
cust_state VARCHAR2(2),
time_id DATE)
PARTITION BY RANGE(time_id)
/*SUBPARTITION BY RANGE (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION original VALUES LESS THAN (1001) TABLESPACE part1,
SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE part2,
SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACE part3)*/ (
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN (MAXVALUE));
分区表的PRIMARY KEY/UNIQUE约束
如果CREATE TABLE语句包含范围/Hash/列表分区，则添加约束会产生如下错误：
Invalid PRIMARY KEY/UNIQUE constraint for partitioned table
注意：PRIMARY KEY/UNIQUE约束所在的列必须包含PARTITION KEY。
脚本： wo_integrate_log_t.SQL, wo_change_log_t.SQL
输入：
create table SD_WO.WO_INTEGRATE_LOG_T
(
LOG_ID NUMBER not null,
PROJECT_NUMBER VARCHAR2(40),
MESSAGE_ID VARCHAR2(100),
BUSINESS_ID VARCHAR2(100),
BUSINESS_TYPE VARCHAR2(100),
INTEGRATE_CONTENT CLOB,
OPERATION_RESULT VARCHAR2(100),
FAILED_MSG VARCHAR2(4000),
HOST_NAME VARCHAR2(100) not null,
CREATED_BY NUMBER not null,
CREATION_DATE DATE not null,
LAST_UPDATED_BY NUMBER not null,
LAST_UPDATE_DATE DATE not null,
SOURCE_CODE VARCHAR2(100),
TENANT_ID NUMBER
)
partition by range (CREATION_DATE)
(
partition P2018 values less than (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P53873 values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P104273 values less than (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P105533 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P108153 values less than (TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P127173 values less than (TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P130313 values less than (TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA
);
alter table SD_WO.WO_INTEGRATE_LOG_T
add constraint WO_INTEGRATE_LOG_PK primary key (LOG_ID);
create index SD_WO.WO_INTEGRATE_LOG_N1 on SD_WO.WO_INTEGRATE_LOG_T (BUSINESS_ID);
create index SD_WO.WO_INTEGRATE_LOG_N2 on SD_WO.WO_INTEGRATE_LOG_T (CREATION_DATE, BUSINESS_TYPE);
create index SD_WO.WO_INTEGRATE_LOG_N3 on SD_WO.WO_INTEGRATE_LOG_T (PROJECT_NUMBER, BUSINESS_TYPE);
输出：
CREATE
TABLE
SD_WO.WO_INTEGRATE_LOG_T (
LOG_ID NUMBER NOT NULL
,PROJECT_NUMBER VARCHAR2 (40)
,MESSAGE_ID VARCHAR2 (100)
,BUSINESS_ID VARCHAR2 (100)
,BUSINESS_TYPE VARCHAR2 (100)
,INTEGRATE_CONTENT CLOB
,OPERATION_RESULT VARCHAR2 (100)
,FAILED_MSG VARCHAR2 (4000)
,HOST_NAME VARCHAR2 (100) NOT NULL
,CREATED_BY NUMBER NOT NULL
,CREATION_DATE DATE NOT NULL
,LAST_UPDATED_BY NUMBER NOT NULL
,LAST_UPDATE_DATE DATE NOT NULL
,SOURCE_CODE VARCHAR2 (100)
,TENANT_ID NUMBER
,CONSTRAINT WO_INTEGRATE_LOG_PK PRIMARY KEY (LOG_ID)
) partition BY range (CREATION_DATE) (
partition P2018
VALUES LESS than (
TO_DATE( ' 2018-10-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
,partition SYS_P53873
VALUES LESS than (
TO_DATE( ' 2018-11-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
,partition SYS_P104273
VALUES LESS than (
TO_DATE( ' 2018-12-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
,partition SYS_P105533
VALUES LESS than (
TO_DATE( ' 2019-01-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
,partition SYS_P108153
VALUES LESS than (
TO_DATE( ' 2019-02-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
,partition SYS_P127173
VALUES LESS than (
TO_DATE( ' 2019-03-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
,partition SYS_P130313
VALUES LESS than (
TO_DATE( ' 2019-04-01 00:00:00' ,'SYYYY-MM-DD HH24:MI:SS'/*, 'NLS_CALENDAR=GREGORIAN'*/ )
) /* tablespace SDWO_DATA */
) ;
CREATE
index WO_INTEGRATE_LOG_N1
ON SD_WO.WO_INTEGRATE_LOG_T (BUSINESS_ID) LOCAL ;
CREATE
index WO_INTEGRATE_LOG_N2
ON SD_WO.WO_INTEGRATE_LOG_T (
CREATION_DATE
,BUSINESS_TYPE
) LOCAL ;
CREATE
index WO_INTEGRATE_LOG_N3
ON SD_WO.WO_INTEGRATE_LOG_T (
PROJECT_NUMBER
,BUSINESS_TYPE
) LOCAL ;
输入：
create table SD_WO.WO_INTEGRATE_LOG_T
(
LOG_ID NUMBER not null,
PROJECT_NUMBER VARCHAR2(40),
MESSAGE_ID VARCHAR2(100),
BUSINESS_ID VARCHAR2(100),
BUSINESS_TYPE VARCHAR2(100),
INTEGRATE_CONTENT CLOB,
OPERATION_RESULT VARCHAR2(100),
FAILED_MSG VARCHAR2(4000),
HOST_NAME VARCHAR2(100) not null,
CREATED_BY NUMBER not null,
CREATION_DATE DATE not null,
LAST_UPDATED_BY NUMBER not null,
LAST_UPDATE_DATE DATE not null,
SOURCE_CODE VARCHAR2(100),
TENANT_ID NUMBER
)
partition by range (CREATION_DATE)
(
partition P2018 values less than (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P53873 values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P104273 values less than (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P105533 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P108153 values less than (TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P127173 values less than (TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P130313 values less than (TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA
);
alter table SD_WO.WO_INTEGRATE_LOG_T
add constraint WO_INTEGRATE_LOG_PK primary key (LOG_ID);
create index SD_WO.WO_INTEGRATE_LOG_N1 on SD_WO.WO_INTEGRATE_LOG_T (BUSINESS_ID);
create index SD_WO.WO_INTEGRATE_LOG_N2 on SD_WO.WO_INTEGRATE_LOG_T (CREATION_DATE, BUSINESS_TYPE);
create index SD_WO.WO_INTEGRATE_LOG_N3 on SD_WO.WO_INTEGRATE_LOG_T (PROJECT_NUMBER, BUSINESS_TYPE);
输出：
create table SD_WO.WO_INTEGRATE_LOG_T
(
LOG_ID NUMBER not null,
PROJECT_NUMBER VARCHAR2(40),
MESSAGE_ID VARCHAR2(100),
BUSINESS_ID VARCHAR2(100),
BUSINESS_TYPE VARCHAR2(100),
INTEGRATE_CONTENT CLOB,
OPERATION_RESULT VARCHAR2(100),
FAILED_MSG VARCHAR2(4000),
HOST_NAME VARCHAR2(100) not null,
CREATED_BY NUMBER not null,
CREATION_DATE DATE not null,
LAST_UPDATED_BY NUMBER not null,
LAST_UPDATE_DATE DATE not null,
SOURCE_CODE VARCHAR2(100),
TENANT_ID NUMBER
)
partition by range (CREATION_DATE)
(
partition P2018 values less than (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P53873 values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P104273 values less than (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P105533 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P108153 values less than (TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P127173 values less than (TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA,
partition SYS_P130313 values less than (TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SDWO_DATA
);
alter table SD_WO.WO_INTEGRATE_LOG_T
add constraint WO_INTEGRATE_LOG_PK primary key (LOG_ID);
create index SD_WO.WO_INTEGRATE_LOG_N1 on SD_WO.WO_INTEGRATE_LOG_T (BUSINESS_ID);
create index SD_WO.WO_INTEGRATE_LOG_N2 on SD_WO.WO_INTEGRATE_LOG_T (CREATION_DATE, BUSINESS_TYPE);
create index SD_WO.WO_INTEGRATE_LOG_N3 on SD_WO.WO_INTEGRATE_LOG_T (PROJECT_NUMBER, BUSINESS_TYPE);
数据类型
删除数据类型中的BYTE关键字。
Oracle语法
迁移后语法
CREATE TABLE BL_ORACLE
(
ID Number,
Name VARCHAR2(100 BYTE),
ADDRESS VARCHAR2(200 BYTE)
);
CREATE TABLE TBL_ORACLE
(
ID NUMBER
,Name VARCHAR2 (100)
,ADDRESS VARCHAR2 (200)
) ;
分区(注释分区)
oracle配置参数中“#分区表唯一或主键约束”为“comment_partition”。
Oracle语法
迁移后语法
CREATE TABLE TBL_ORACLE
(
ID Number,
Name VARCHAR2(100 BYTE),
ADDRESS VARCHAR2(200 BYTE)
)
TABLESPACE space1
PCTUSED 40
PCTFREE 0
INITRANS 1
MAXTRANS 255
NOLOGGING
PARTITION BY RANGE (ID)
(
PARTITION PART_2010 VALUES LESS THAN (10)
NOLOGGING,
PARTITION PART_2011 VALUES LESS THAN (20)
NOLOGGING ,
PARTITION PART_2012 VALUES LESS THAN (MAXVALUE)
NOLOGGING
)
ENABLE ROW MOVEMENT;
ALTER TABLE TBL_ORACLE ADD CONSTRAINT SAMPLE_PK PRIMARY KEY (ID);
CREATE UNLOGGED TABLE TBL_ORACLE
(
ID NUMBER
,Name VARCHAR2 (100)
,ADDRESS VARCHAR2 (200)
,CONSTRAINT SAMPLE_PK PRIMARY KEY (ID)
)
TABLESPACE space1
/*PCTUSED 40*/
PCTFREE 0
INITRANS 1
MAXTRANS 255
/* PARTITION BY RANGE(ID)(PARTITION PART_2010 VALUES LESS THAN(10) ,
PARTITION PART_2011 VALUES LESS THAN(20) , PARTITION PART_2012 VALUES LESS THAN(MAXVALUE) )
ENABLE ROW MOVEMENT */
;
分区(注释约束)
oracle配置参数中“#分区表唯一或主键约束”为“comment_unique”。
Oracle语法
迁移后语法
CREATE TABLE TBL_ORACLE
(
ID Number,
Name VARCHAR2(100 BYTE),
ADDRESS VARCHAR2(200 BYTE)
)
TABLESPACE space1
PCTUSED 40
PCTFREE 0
INITRANS 1
MAXTRANS 255
NOLOGGING
PARTITION BY RANGE (ID)
(
PARTITION PART_2010 VALUES LESS THAN (10)
NOLOGGING,
PARTITION PART_2011 VALUES LESS THAN (20)
NOLOGGING ,
PARTITION PART_2012 VALUES LESS THAN (MAXVALUE)
NOLOGGING
)
ENABLE ROW MOVEMENT;
ALTER TABLE TBL_ORACLE ADD CONSTRAINT SAMPLE_PK PRIMARY KEY (ID);
CREATE UNLOGGED TABLE TBL_ORACLE
(
ID NUMBER
,Name VARCHAR2 (100)
,ADDRESS VARCHAR2 (200)
/*,CONSTRAINT SAMPLE_PK PRIMARY KEY (ID)*/
)
TABLESPACE space1
/*PCTUSED 40*/
PCTFREE 0
INITRANS 1
MAXTRANS 255
PARTITION BY RANGE (ID)
(
PARTITION PART_2010 VALUES LESS THAN (10)
,PARTITION PART_2011 VALUES LESS THAN (20)
,PARTITION PART_2012 VALUES LESS THAN (MAXVALUE)
) ENABLE ROW MOVEMENT ;
分区(一)
在非分区表中，为表“ALTER TABLE TRUNCATE PARTITION”添加注释。
Oracle语法
迁移后语法
CREATE TABLE product_range
(
product_id VARCHAR2(20),
Product_Name VARCHAR2(50),
Year_Manufacture DATE
)
partition by range (Year_Manufacture)
(
partition Year_Manufacture values less than (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
pctfree 10
initrans 1
);
CREATE TABLE product_list
(
product_id VARCHAR2(20),
Product_Name VARCHAR2(50),
Year_Manufacture vARCHAR2(10)
)
partition by list (Year_Manufacture)
(
partition P_2020 VALUES (2020)
pctfree 10
initrans 1
);
CREATE OR REPLACE PROCEDURE Range_test
IS
V_ID VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE product TRUNCATE PARTITION PART'||V_ID;
NULL;
END;
/
CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE product TRUNCATE PARTITION PART'||V_ID;
NULL;
END;
/
CREATE TABLE product_range
(
product_id VARCHAR2(20),
Product_Name VARCHAR2(50),
Year_Manufacture DATE
)
partition by range (Year_Manufacture)
(
partition Year_Manufacture values less than (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
pctfree 10
initrans 1
);
CREATE TABLE product_list
(
product_id VARCHAR2(20),
Product_Name VARCHAR2(50),
Year_Manufacture vARCHAR2(10)
)
/*partition by list (Year_Manufacture)
(
partition P_2020 VALUES (2020)
pctfree 10
initrans 1
)*/;
CREATE OR REPLACE PROCEDURE Range_test
IS
V_ID VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE product TRUNCATE PARTITION PART'||V_ID;
NULL;
END;
/
CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
/*EXECUTE IMMEDIATE 'ALTER TABLE product TRUNCATE PARTITION PART'||V_ID;*/
NULL;
END;
/
分区(二)
在非分区表中，删除表“ALTER TABLE TRUNCATE PARTITION”中的数据。
Oracle语法
迁移后语法
CREATE TABLE product_list
(
product_id VARCHAR2(20),
Product_Name VARCHAR2(50),
Year_Manufacture vARCHAR2(10)
)
partition by list (Year_Manufacture)
( partition PART_2015 VALUES (2011,2012,2013,2014,2015) pctfree 10 initrans 1 ,
partition PART_2016 VALUES (2016) pctfree 10 initrans 1 ,
partition PART_2017 VALUES (2017) pctfree 10 initrans 1 ,
partition PART_2018 VALUES (2018) pctfree 10 initrans 1 ,
partition PART_2019 VALUES (2019) pctfree 10 initrans 1 ,
partition PART_2020 VALUES (2020) pctfree 10 initrans 1 ,
PARTITION PART_unknown VALUES (DEFAULT) );
CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_2020;
NULL;
END;
/
CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID;
NULL;
END;
/
CREATE TABLE product_list
(
product_id VARCHAR2(20),
Product_Name VARCHAR2(50),
Year_Manufacture vARCHAR2(10)
)
/*partition by list (Year_Manufacture)
( partition PART_2015 VALUES (2011,2012,2013,2014,2015) pctfree 10 initrans 1 ,
partition PART_2016 VALUES (2016) pctfree 10 initrans 1 ,
partition PART_2017 VALUES (2017) pctfree 10 initrans 1 ,
partition PART_2018 VALUES (2018) pctfree 10 initrans 1 ,
partition PART_2019 VALUES (2019) pctfree 10 initrans 1 ,
partition PART_2020 VALUES (2020) pctfree 10 initrans 1 ,
PARTITION PART_unknown VALUES (DEFAULT) )*/;
CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID;
NULL;
END;
/
CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
/* EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID; */
IF 'PART_' || V_ID = 'PART_2015' THEN
DELETE FROM product_list WHERE Year_Manufacture IN (2011,2012,2013,2014,2015);
ELSIF 'PART_' || V_ID = 'PART_2016' THEN
DELETE FROM product_list WHERE Year_Manufacture IN (2016);
ELSIF 'PART_' || V_ID = 'PART_2017' THEN
DELETE FROM product_list WHERE Year_Manufacture IN (2017);
ELSIF 'PART_' || V_ID = 'PART_2018' THEN
DELETE FROM product_list WHERE Year_Manufacture IN (2018);
ELSIF 'PART_' || V_ID = 'PART_2019' THEN
DELETE FROM product_list WHERE Year_Manufacture IN (2019);
ELSIF 'PART_' || V_ID = 'PART_2020' THEN
DELETE FROM product_list WHERE Year_Manufacture IN (2020);
ELSE
DELETE FROM product_list WHERE Year_Manufacture NOT IN (2011,2012,2013,2014,2015,2016,2017,2018,2019,2020);
END IF;
NULL;
END;
/
SEGMENT CREATION
GaussDB不支持SEGMENT CREATION { IMMEDIATE | DEFERRED }，因此该语句在迁移后被注释掉，需要设置commentStorageParameter=true。
输入：TABLE，使用SEGMENT CREATION
CREATE TABLE T1
( MESSAGE_CODE VARCHAR2(50),
MAIL_TITLE VARCHAR2(1000),
MAIL_BODY VARCHAR2(1000),
MAIL_ADDRESS VARCHAR2(1000),
MAIL_ADDRESS_CC VARCHAR2(1000)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE Test ;
输出：
CREATE TABLE T1
( MESSAGE_CODE VARCHAR2(50),
MAIL_TITLE VARCHAR2(1000),
MAIL_BODY VARCHAR2(1000),
MAIL_ADDRESS VARCHAR2(1000),
MAIL_ADDRESS_CC VARCHAR2(1000)
) /*SEGMENT CREATION DEFERRED */
/*PCTFREE 10*/
/* PCTUSED 0 */
/*INITRANS 1 */
/*MAXTRANS 255 */
/* NOCOMPRESS LOGGING*/
/* STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
/* TABLESPACE Test */;
STORAGE
GaussDB不支持BUFFER_POOL、MAXEXTENTS等存储参数。如果commment_storage_parameter设置为true，出现在表或索引中的这些参数在迁移时会被注释掉。
输入：TABLE，使用STORAGE
CREATE UNIQUE INDEX PK_BASE_APPR_STEP_DEF ON BASE_APPR_STEP_DEF (FLOW_ID, NODE_ID, STEP_ID)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE SPMS_DATA ;
CREATE TABLE UFP_MAIL
( MAIL_ID NUMBER(*,0),
MAIL_TITLE VARCHAR2(1000),
MAIL_BODY VARCHAR2(4000),
STATUS VARCHAR2(50),
CREATE_TIME DATE,
SEND_TIME DATE,
MAIL_ADDRESS CLOB,
MAIL_CC CLOB,
BASE_ID VARCHAR2(20),
BASE_STATUS VARCHAR2(50),
BASE_VERIFY VARCHAR2(20),
BASE_LINK VARCHAR2(4000),
MAIL_TYPE VARCHAR2(20),
BLIND_COPY_TO CLOB,
FILE_NAME VARCHAR2(4000),
FULL_FILEPATH VARCHAR2(4000)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE SPMS_DATA
LOB (MAIL_ADDRESS) STORE AS BASICFILE (
TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB (MAIL_CC) STORE AS BASICFILE (
TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB (BLIND_COPY_TO) STORE AS BASICFILE (
TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
输出
CREATE
UNIQUE INDEX PK_BASE_APPR_STEP_DEF
ON BASE_APPR_STEP_DEF (
FLOW_ID
,NODE_ID
,STEP_ID
) /*PCTFREE 10*/
/*INITRANS 2*/
/*MAXTRANS 255*/
/*COMPUTE STATISTICS*/
/*STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
/*TABLESPACE SPMS_DATA */
;
如果commment_storage_parameter设为true，存储参数会被注释掉。
STORE
Gauss不支持LOB列的STORE关键字，因此该关键字在迁移后会被注释掉。
输入：TABLE，使用STORE
CREATE TABLE CTP_PROC_LOG
( PORC_NAME VARCHAR2(100),
LOG_TIME VARCHAR2(100),
LOG_INFO CLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE SPMS_DATA
LOB (LOG_INFO) STORE AS BASICFILE (
TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
输出：
CREATE
TABLE
CTP_PROC_LOG (
PORC_NAME VARCHAR2 (100)
,LOG_TIME VARCHAR2 (100)
,LOG_INFO CLOB
) /*SEGMENT CREATION IMMEDIATE*/
/*PCTFREE 10*/
/*PCTUSED 0*/
/*INITRANS 1*/
/*MAXTRANS 255*/
/*NOCOMPRESS*/
/*LOGGING*/
/*STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
/*TABLESPACE SPMS_DATA */
/*LOB (LOG_INFO) STORE AS BASICFILE ( TABLESPACE SPMS_DATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))*/
;
PCTINCREASE
所有表均不支持存储参数PCTINCREASE。此外，分区表不支持所有存储参数(包括pctfree、minextents和 maxextents)。
输入：TABLE，使用PCTINCREASE
CREATE TABLE tab1 (
col1 < datatype >
, col2 < datatype >
, ...
, colN < datatype > )
TABLESPACE testts
PCTFREE 10 INITRANS 1 MAXTRANS
255
/* STORAGE (
INITIAL 5 M NEXT 5 M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 );*/
输出：
CREATE TABLE tab1 (
col1 < datatype >
, col2 < datatype >
, ...
, colN < datatype > )
TABLESPACE testts
PCTFREE 10 INITRANS 1 MAXTRANS 255
/* STORAGE (
INITIAL 5 M NEXT 5 M MINEXTENTS 1 MAXEXTENTS
UNLIMITED );*/
外键
外键在Oracle数据库中用于强制保证引用的完整性。外键意味着一个表中的值必须同时存在另一个表中。被引用的表称为父表，而包含外键的表称为子表。子表中的外键通常会引用父表中的主键。可以在CREATE TABLE或ALTER TABLE语句中定义外键。
必须通过REFERENCE子句建立外键约束。内联约束子句是列定义子句或对象属性子句的一部分。外联约束是关系属性子句或对象属性子句中的一部分。
如果参数foreignKeyHandler设置为true(默认值)，工具将这些语句迁移为注释语句。
DSC支持内联和外联外键约束，如下所示。
输入：CREATE TABLE，使用外键和内联约束
CREATE TABLE orders (
order_no INT NOT NULL PRIMARY KEY,
order_date DATE NOT NULL,
cust_id INT
[CONSTRAINT fk_orders_cust]
REFERENCES customers(cust_id)
[ON DELETE SET NULL]
[INITIALLY DEFERRED]
[ENABLE NOVALIDATE]
);
输出：
CREATE TABLE orders (
order_no INT NOT NULL PRIMARY KEY,
order_date DATE NOT NULL,
cust_id INT
/*
[CONSTRAINT fk_orders_cust]
REFERENCES customers(cust_id)
[ON DELETE SET NULL]
[INITIALLY DEFERRED]
[ENABLE NOVALIDATE] */
);
输入：CREATE TABLE，使用外键和外联约束
CREATE TABLE customers (
cust_id INT NOT NULL,
cust_name VARCHAR(64) NOT NULL,
cust_addr VARCHAR(256),
cust_contact_no VARCHAR(16),
PRIMARY KEY (cust_id)
);
CREATE TABLE orders (
order_no INT NOT NULL,
order_date DATE NOT NULL,
cust_id INT NOT NULL,
PRIMARY KEY (order_no),
CONSTRAINT fk_orders_cust
FOREIGN KEY (cust_id)
REFERENCES customers(cust_id)
ON DELETE CASCADE
);
输出：
CREATE TABLE customers (
cust_id INT NOT NULL,
cust_name VARCHAR(64) NOT NULL,
cust_addr VARCHAR(256),
cust_contact_no VARCHAR(16),
PRIMARY KEY (cust_id)
);
CREATE TABLE orders (
order_no INT NOT NULL,
order_date DATE NOT NULL,
cust_id INT NOT NULL,
PRIMARY KEY (order_no) /*,
CONSTRAINT fk_orders_cust
FOREIGN KEY (cust_id)
REFERENCES customers(cust_id)
ON DELETE CASCADE */
);
LONG数据类型
定义为LONG的列可存储变长字符数据，最多可包含2GB信息。MT支持表结构和PL/SQL中的LONG数据类型。
输入：在表结构中使用LONG数据类型
CREATE TABLE project ( proj_cd INT
, proj_name VARCHAR2(32)
, dept_no INT
, proj_det LONG );
输出：
CREATE TABLE project ( proj_cd INT
, proj_name VARCHAR2(32)
, dept_no INT
, proj_det TEXT );
输入：在PL/SQL中使用LONG数据类型
CREATE OR REPLACE FUNCTION fn_proj_det
( i_proj_cd INT )
RETURN LONG
IS
v_proj_det LONG;
BEGIN
SELECT proj_det
INTO v_proj_det
FROM project
WHERE proj_cd = i_proj_cd;
RETURN v_proj_det;
END;
/
输出：
CREATE OR REPLACE FUNCTION fn_proj_det
( i_proj_cd INT )
RETURN TEXT
IS
v_proj_det TEXT;
BEGIN
SELECT proj_det
INTO v_proj_det
FROM project
WHERE proj_cd = i_proj_cd;
RETURN v_proj_det;
END;
/
TYPE
将“MDSYS.MBRCOORDLIST”替换为“CLOB”。
Oracle语法
迁移后语法
create table product_part
(
partid VARCHAR2(24),
mbrcoords MDSYS.MBRCOORDLIST
);
CREATE TABLE product_part
(
partid VARCHAR2(24),
mbrcoords CLOB
);
将“MDSYS.SDO_GEOMETRY”替换为“CLOB”。
Oracle语法
迁移后语法
create table product_part
(
partid VARCHAR2(24),
shape MDSYS.SDO_GEOMETRY
);
CREATE TABLE product_part
(
partid VARCHAR2(24),
shape CLOB
);
将“MDSYS.GEOMETRY”为“CLOB”。
Oracle语法
迁移后语法
create table product_part
(
partid VARCHAR2(24),
shape GEOMETRY
);
CREATE TABLE product_part
(
partid VARCHAR2(24),
shape CLOB
);
列
xmax、xmin、left、right、maxvalue为Gauss关键字，这些关键字应全字母大写并加英文双引号("")。
Oracle语法
迁移后语法
create table product
(
xmax VARCHAR2(20),
xmin VARCHAR2(50),
left VARCHAR2(50),
right VARCHAR2(50),
maxvalue VARCHAR2(50)
);
CREATE TABLE product1
(
"XMAX" VARCHAR2(20),
"XMIN" VARCHAR2(50),
"LEFT" VARCHAR2(50),
"RIGHT" VARCHAR2(50),
"MAXVALUE" VARCHAR2(50)
);
间隔分区
对于间隔分区，应该注释分区。
Oracle语法
迁移后语法
create table product
(
product_id VARCHAR2(20),
product_name VARCHAR2(50),
manufacture_month DATE
)
partition by range (manufacture_month) interval (NUMTODSINTERVAL (1, 'MONTH'))
(
partition T_PARTITION_2018_11_LESS values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')));
CREATE TABLE product
(
product_id VARCHAR2(20),
product_name VARCHAR2(50),
manufacture_month DATE
)
/*partition by range (manufacture_month) interval (NUMTODSINTERVAL (1, 'MONTH'))
(
partition T_PARTITION_2018_11_LESS values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')))*/;
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
0
0- 0
已为社区贡献3条内容
所有评论(0)