3.2.5.5. GaussDB-Oracle

3.2.5.5.1. 清空表

语法

TRUNCATE TABLE TABLE_NAME

示例

    CREATE TABLE unisql_truncate_test(id int, name varchar(10));

INSERT INTO unisql_truncate_test values(1,'aa');
INSERT INTO unisql_truncate_test values(2,'bb');
INSERT INTO unisql_truncate_test values(3,'cc');

-- 转换前Oracle SQL:
TRUNCATE TABLE unisql_truncate_test;

-- 转换后GaussDB-Oracle SQL:
TRUNCATE TABLE unisql_truncate_test

-- 查询表数据
SELECT * FROM unisql_truncate_test;

3.2.5.5.2. 创建索引

语法

CREATE [UNIQUE|BITMAP] INDEX [SCHEMA.] INDEX_NAME
ON TABLE_NAME ( COLUMN1, COLUMN2, ..., COLUMNN )
[{ LOGGING |  NOLOGGING}]
[COMPUTE STATISTICS]
[{ GLOBAL  |  LOCAL}]
[{ USABLE  |  UNUSABLE}]
[{ IMMEDIATE INVALIDATION }]
[tablespace tablespaceName] [local]

示例

--创建表
CREATE TABLE unisql_index_test(col1 int,col2 int,col3 int,col4 int,col5 varchar(10))

-- 删除存在的索引
DROP INDEX unisql_index_test_idx;

-- 转换前Oracle SQL:
-- 使用示例
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE UNIQUE INDEX unisql_index_test_idx ON unisql_index_test(col1);
CREATE BITMAP INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX test.unisql_index_test_idx ON unisql_index_test(col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1 asc);
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1 desc);
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1,col2);
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) LOGGING;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) USABLE;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) UNUSABLE;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) NOLOGGING;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) COMPUTE STATISTICS;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) NOLOGGING COMPUTE STATISTICS;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) COMPUTE STATISTICS NOLOGGING;


-- 转换后GaussDB-Oracle SQL:
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE UNIQUE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX test.unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1 DESC);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1, col2);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);


-- 创建表，LOCAL索引需要创建在分区表上
CREATE TABLE unisql_local_test_orders (
    order_id   int,
    order_num int,
    customer   varchar(50)
)
PARTITION BY RANGE (order_num)
(
    PARTITION p1 VALUES LESS THAN (100),
    PARTITION p2 VALUES LESS THAN (200),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

-- 删除存在的索引
DROP INDEX unisql_local_test_orders_idx;

-- 转换前Oracle SQL:
-- 使用示例
CREATE INDEX unisql_local_test_orders_idx ON unisql_local_test_orders(order_num) LOCAL;
CREATE INDEX unisql_local_test_orders_idx ON unisql_local_test_orders(order_num) tablespace test LOCAL;

-- 转换后GaussDB-Oracle SQL:
CREATE INDEX unisql_local_test_orders_idx ON unisql_local_test_orders (order_num);
CREATE INDEX unisql_local_test_orders_idx ON unisql_local_test_orders (order_num) TABLESPACE test;

3.2.5.5.3. 删除索引

语法

DROP INDEX INDEX_NAME

示例

-- 转换前Oracle SQL:
DROP INDEX unisql_index_test_idx;

-- 转换后GaussDB-Oracle SQL:
DROP INDEX unisql_index_test_idx;

3.2.5.5.4. 复制表

语法

CREATE [GLOBAL TEMPORARY] TABLE TABLE_NAME AS (select_list)

示例

CREATE TABLE unisql_copy_table_test(id int, name varchar(10));
INSERT INTO unisql_copy_table_test values(1,'TOM');
INSERT INTO unisql_copy_table_test values(2,'JIMMY');
INSERT INTO unisql_copy_table_test values(3,'NANCY');

-- 转换前Oracle SQL:
CREATE TABLE unisql_copy_table_test_new AS SELECT id,name FROM unisql_copy_table_test WHERE id > 1;
CREATE GLOBAL TEMPORARY TABLE unisql_copy_table_test_global AS SELECT id,name FROM unisql_copy_table_test WHERE id > 1;
CREATE GLOBAL TEMPORARY TABLE unisql_copy_table_test_global_1 AS SELECT * FROM duaL;

-- 转换后GaussDB-Oracle SQL:
CREATE TABLE unisql_copy_table_test_new AS SELECT id,name FROM unisql_copy_table_test WHERE id>1;
CREATE TEMPORARY TABLE unisql_copy_table_test_global AS SELECT id,name FROM unisql_copy_table_test WHERE id>1;
CREATE TEMPORARY TABLE unisql_copy_table_test_global_1 AS SELECT 'X' AS DUMMY;

3.2.5.5.5. 创建表,包含临时表等

语法

CREATE [GLOBAL TEMPORARY] TABLE TABLE_NAME (COLUMN1 DATATYPE [NOT NULL | NULL] [DEFAULT] [PRIMARY KEY],[table_constraint]...)
[ ON COMMIT { DELETE | PRESERVE } ROWS ]

table_constraint:
[ CONSTRAINT constraint_name ]
{UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters}

DEFAULT 后面表达式可支持使用部分函数、关键字函数、字符串、常数、序列、字符串连接符。

示例

-- 转换前Oracle SQL:
CREATE table unisql_create_table_test(
    DBID_   NUMERIC(19)      NOT NULL,
    PROCESS_KEY     VARCHAR2(128)    NOT NULL,
    BIZ_VERSION     VARCHAR2(32)     NOT NULL,
    PROCESS_NAME    VARCHAR2(128)    NOT NULL,
    PROCESS_FLAG    VARCHAR2(1)         NOT NULL,
    PROCESS_CLASS   VARCHAR2(128)    NOT NULL,
    IGNORE_EXIST_USER       VARCHAR2(1)     default 0 NOT NULL,
    FORM_NAME       VARCHAR2(128),
    FORM_TYPE       VARCHAR2(128),
    FORM_URL        VARCHAR2(128),
    FORM_UUID       VARCHAR2(32),
    IMPORT_TIME     TIMESTAMP       default CURRENT_TIMESTAMP,
    HANDLE_TIME     TIMESTAMP,
    STATE_      VARCHAR2(20)        default 0,
    ERRORMSG        CLOB,
    constraint UK_IMPORT_PRO UNIQUE (PROCESS_KEY,BIZ_VERSION,PROCESS_NAME),
    constraint UK_PROCESS_BIZ UNIQUE (PROCESS_KEY,BIZ_VERSION),
    constraint UK_IMPORT_PRO_NAME UNIQUE (PROCESS_NAME,BIZ_VERSION),
    constraint PK_IMPORT_PRO primary key (DBID_)
);

-- 转换后GaussDB-Oracle SQL:
CREATE TABLE unisql_create_table_test (DBID_ decimal(19) NOT NULL,PROCESS_KEY varchar(128) NOT NULL,BIZ_VERSION varchar(32) NOT NULL,PROCESS_NAME varchar(128) NOT NULL,PROCESS_FLAG varchar(1) NOT NULL,PROCESS_CLASS varchar(128) NOT NULL,IGNORE_EXIST_USER varchar(1) DEFAULT 0 NOT NULL,FORM_NAME varchar(128),FORM_TYPE varchar(128),FORM_URL varchar(128),FORM_UUID varchar(32),IMPORT_TIME timestamp DEFAULT CURRENT_TIMESTAMP(0),HANDLE_TIME timestamp,STATE_ varchar(20) DEFAULT 0,ERRORMSG text,CONSTRAINT UK_IMPORT_PRO UNIQUE(PROCESS_KEY, BIZ_VERSION, PROCESS_NAME),CONSTRAINT UK_PROCESS_BIZ UNIQUE(PROCESS_KEY, BIZ_VERSION),CONSTRAINT UK_IMPORT_PRO_NAME UNIQUE(PROCESS_NAME, BIZ_VERSION),CONSTRAINT PK_IMPORT_PRO PRIMARY KEY(DBID_))


-- 转换前Oracle SQL:
CREATE GLOBAL TEMPORARY TABLE unisql_create_table_on_option_test(
    d_SMALLINT SMALLINT ,
    d_INTEGER INTEGER,
    d_INT INT ,
    d_LONG LONG ,
    d_NUMBER NUMBER(10,2),
    d_DECIMAL DECIMAL(10,2),
    d_CHAR CHAR(60),
    d_CHARACTER CHARACTER(60) ,
    d_VARCHAR VARCHAR(60),
    d_VARCHAR2 VARCHAR2(60),
    d_DATE DATE,
    d_TIMESTAMP TIMESTAMP(6),
    d_TIMESTAMP2 TIMESTAMP(6) WITH TIME ZONE,
    d_BLOB BLOB,
    d_CLOB CLOB,
    d_XMLTYPE XMLTYPE
    ) ON COMMIT PRESERVE ROWS;

-- 转换后GaussDB-Oracle SQL:
CREATE TEMPORARY TABLE unisql_create_table_on_option_test (d_SMALLINT bigint,d_INTEGER bigint,d_INT bigint,d_LONG text,d_NUMBER decimal(10,2),d_DECIMAL decimal(10,2),d_CHAR char(60),d_CHARACTER char(60),d_VARCHAR varchar(60),d_VARCHAR2 varchar(60),d_DATE timestamp(0),d_TIMESTAMP timestamp(6),d_TIMESTAMP2 timestamp(6) with time zone,d_BLOB bytea,d_CLOB text,d_XMLTYPE xml)


CREATE SEQUENCE unisql_default_expr_seq

-- 转换前Oracle SQL:
CREATE TABLE unisql_default_expr_table (
    col_not_null VARCHAR2 ( 1 ) DEFAULT 1 NOT NULL,
    col_current_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    col_current_localstamp DATE DEFAULT LOCALTIMESTAMP,
    col_sysdate DATE DEFAULT SYSDATE,
    col_systimestamp DATE DEFAULT SYSTIMESTAMP,
    col_name VARCHAR2(50) DEFAULT 'John Doe',
    col_to_date DATE DEFAULT TO_DATE('2000-01-01', 'YYYY-MM-DD'),
    col_nextval NUMBER DEFAULT unisql_default_expr_seq.NEXTVAL,
    col_currval NUMBER DEFAULT unisql_default_expr_seq.CURRVAL
)

-- 转换后GaussDB-Oracle SQL:
CREATE TABLE unisql_default_expr_table (
    col_not_null VARCHAR ( 1 ) DEFAULT 1 NOT NULL,
    col_current_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ( 0 ),
    col_current_localstamp TIMESTAMP ( 0 ) DEFAULT CURRENT_TIMESTAMP ( 0 ),
    col_sysdate TIMESTAMP ( 0 ) DEFAULT statement_timestamp (),
    col_systimestamp TIMESTAMP ( 0 ) DEFAULT statement_timestamp (),
    col_name VARCHAR ( 50 ) DEFAULT 'John Doe',
    col_to_date TIMESTAMP ( 0 ) DEFAULT CAST( to_timestamp ( '2000-01-01', 'YYYY-MM-DD' ) AS TIMESTAMP ),
    col_nextval DECIMAL DEFAULT nextval ( 'unisql_default_expr_seq' ),
    col_currval DECIMAL DEFAULT currval ( 'unisql_default_expr_seq' )
)

3.2.5.5.6. 创建分区表

分区键仅支持列，分区范围支持使用函数、字符串、常数表达，统一SQL会根据表名、分区名在转化库中创建分区表，分区表名为：表名_1_prt_分区名， Oracle 源语句HASH分区未指定分区名和分区数目时，转化到GaussDB-Oracle表分区名默认为p0，指定多个分区数目，转化后的分区名依次递增（p0,p1,p2,…）。Oracle 和 GaussDB-Oracle HASH分区算法不同，所以插入的数据分布到分区表表现也不一致。 分区表的唯一、主键的表级约束，必须与分区字段一致（或包含分区字段），才能保证全局的唯一性，否则无法实现转化，唯一、主键的列级约束的这个限制暂不考虑。

语法

CREATE TABLE TABLE_NAME …PARTITION BY[RANGE|LIST] (column) (partition_list);

CREATE TABLE TABLE_NAME …PARTITION BY HASH (column[,column]) [PARTITIONS number];

示例

-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_range_test
(
product_id   NUMBER(6),
sale_time    TIMESTAMP(6),
sale_price   NUMBER(10,2)
)
PARTITION BY RANGE (sale_time)
(
PARTITION q1_2023 VALUES LESS THAN (TO_DATE('2023/04/01', 'yyyy/mm/dd')),
PARTITION q2_2023 VALUES LESS THAN (TO_DATE('2023/07/01', 'yyyy/mm/dd')),
PARTITION q3_2023 VALUES LESS THAN (TO_DATE('2023/10/01', 'yyyy/mm/dd')),
PARTITION q4_2023 VALUES LESS THAN (TO_DATE('2024/01/01', 'yyyy/mm/dd'))
);

-- 转换后GaussDB-Oracle SQL:
CREATE TABLE unisql_partition_by_range_test (product_id int,sale_time timestamp(6),sale_price decimal(10,2)) PARTITION BY RANGE (sale_time);
CREATE TABLE unisql_partition_by_range_test_1_prt_q1_2023 PARTITION OF unisql_partition_by_range_test for values from  (MINVALUE)  to (CAST(to_timestamp('2023/04/01', 'yyyy/mm/dd') AS timestamp));
CREATE TABLE unisql_partition_by_range_test_1_prt_q2_2023 PARTITION OF unisql_partition_by_range_test for values from (CAST(to_timestamp('2023/04/01', 'yyyy/mm/dd') AS timestamp)) to (CAST(to_timestamp('2023/07/01', 'yyyy/mm/dd') AS timestamp));
CREATE TABLE unisql_partition_by_range_test_1_prt_q3_2023 PARTITION OF unisql_partition_by_range_test for values from (CAST(to_timestamp('2023/07/01', 'yyyy/mm/dd') AS timestamp)) to (CAST(to_timestamp('2023/10/01', 'yyyy/mm/dd') AS timestamp));
CREATE TABLE unisql_partition_by_range_test_1_prt_q4_2023 PARTITION OF unisql_partition_by_range_test for values from (CAST(to_timestamp('2023/10/01', 'yyyy/mm/dd') AS timestamp)) to (CAST(to_timestamp('2024/01/01', 'yyyy/mm/dd') AS timestamp));

-- 转换前Oracle SQL:
CREATE TABLE unisql_partition (
unisql_id NUMBER,
unisql_name VARCHAR2(50),
unisql_salary NUMBER(10,2),
unisql_hire_date DATE,
CONSTRAINT unisql_partition_pk PRIMARY KEY (unisql_id, unisql_hire_date)
)PARTITION BY RANGE (unisql_hire_date) (
PARTITION unisql_partition_p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
PARTITION unisql_partition_p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION unisql_partition_p3 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
PARTITION unisql_partition_p4 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);

-- 转换后GaussDB-Oracle SQL:
CREATE TABLE unisql_partition (unisql_id decimal,unisql_name varchar(50),unisql_salary decimal(10,2),unisql_hire_date timestamp(0),CONSTRAINT unisql_partition_pk PRIMARY KEY(unisql_id, unisql_hire_date)) PARTITION BY RANGE (unisql_hire_date);
CREATE TABLE unisql_partition_1_prt_unisql_partition_p1 PARTITION OF unisql_partition for values from  (MINVALUE)  to (CAST(to_timestamp('2023-01-01', 'YYYY-MM-DD') AS timestamp));
CREATE TABLE unisql_partition_1_prt_unisql_partition_p2 PARTITION OF unisql_partition for values from (CAST(to_timestamp('2023-01-01', 'YYYY-MM-DD') AS timestamp)) to (CAST(to_timestamp('2024-01-01', 'YYYY-MM-DD') AS timestamp));
CREATE TABLE unisql_partition_1_prt_unisql_partition_p3 PARTITION OF unisql_partition for values from (CAST(to_timestamp('2024-01-01', 'YYYY-MM-DD') AS timestamp)) to (CAST(to_timestamp('2025-01-01', 'YYYY-MM-DD') AS timestamp));
CREATE TABLE unisql_partition_1_prt_unisql_partition_p4 PARTITION OF unisql_partition for values from (CAST(to_timestamp('2025-01-01', 'YYYY-MM-DD') AS timestamp)) to (CAST(to_timestamp('2026-01-01', 'YYYY-MM-DD') AS timestamp));


-- 转换前Oracle SQL:
create table unisql_partition_by_list_test
(
id varchar2(15) not null,
city varchar2(20)
)
partition by list (city)
(
partition p1 values ('beijing'),
partition p2 values ('shanghai'),
partition p3 values ('changsha'),
partition p4 values (default)
);

-- 转换后GaussDB-Oracle SQL:
CREATE TABLE unisql_partition_by_list_test (id varchar(15) NOT NULL,city varchar(20)) PARTITION BY LIST (city);
CREATE TABLE unisql_partition_by_list_test_1_prt_p1 PARTITION OF unisql_partition_by_list_test for values in ('beijing');
CREATE TABLE unisql_partition_by_list_test_1_prt_p2 PARTITION OF unisql_partition_by_list_test for values in ('shanghai');
CREATE TABLE unisql_partition_by_list_test_1_prt_p3 PARTITION OF unisql_partition_by_list_test for values in ('changsha');
CREATE TABLE unisql_partition_by_list_test_1_prt_p4 PARTITION OF unisql_partition_by_list_test DEFAULT;

-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 INT, col2 VARCHAR(50), col3 TIMESTAMP )
PARTITION BY HASH (col1);

-- 转换后GaussDB-Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 bigint,col2 varchar(50),col3 timestamp) PARTITION BY HASH (col1);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p0 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 1, REMAINDER 0);

-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 int,col2 varchar(50),col3 timestamp,CONSTRAINT unisql_partition_uk UNIQUE (col1, col2))
PARTITION BY HASH (col1,col2) PARTITIONS 4;

-- 转换后GaussDB-Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 bigint,col2 varchar(50),col3 timestamp,CONSTRAINT unisql_partition_uk UNIQUE(col1, col2)) PARTITION BY HASH (col1,col2);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p0 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p1 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p2 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p3 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 转换前Oracle SQL:
create table unisql_hash_part(
id        number,
pro_num   varchar2(40),
app_type  varchar2(40)
)
partition by hash(pro_num)
(
partition part_01,
partition part_02,
partition part_03)

-- 转换后GaussDB-Oracle SQL:
不支持

3.2.5.5.7. 清空、删除分区表

语法

ALTER TABLE TABLE_NAME [TRUNCATE|DROP] PARTITION PARTITION_NAMES

示例

-- 转换前Oracle SQL:
ALTER TABLE unisql_partition_by_range_test TRUNCATE  PARTITION q1_2023;
ALTER TABLE unisql_partition_by_range_test DROP  PARTITION q1_2023;

-- 转换后GaussDB-Oracle SQL:
TRUNCATE TABLE unisql_partition_by_range_test_1_prt_q1_2023
DROP TABLE unisql_partition_by_range_test_1_prt_q1_2023

3.2.5.5.8. 新增列

语法

ALTER TABLE ADD COLUMN_NAME {column_definition | (column_definition_list)}


column_definition_list:
column_definition [, column_definition ...]

column_definition:
column_name data_type
[DEFAULT const_value] [AUTO_INCREMENT]
[NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment

示例

-- 创建表
CREATE TABLE unisql_add_column_test(col1 int);

-- 转换前Oracle SQL:
ALTER TABLE unisql_add_column_test ADD col2 NUMBER(4);
ALTER TABLE unisql_add_column_test ADD col3 varchar(10) NOT NULL ;
ALTER TABLE unisql_add_column_test ADD col4 NUMBER(4) DEFAULT 0;
ALTER TABLE unisql_add_column_test ADD (col5 NUMBER(4), col6 INT);

-- 转换后GaussDB-Oracle SQL:
ALTER TABLE unisql_add_column_test ADD COLUMN col2 smallint
ALTER TABLE unisql_add_column_test ADD COLUMN col3 varchar(10) NOT NULL
ALTER TABLE unisql_add_column_test ADD COLUMN col4 smallint DEFAULT 0
ALTER TABLE unisql_add_column_test ADD COLUMN col5 smallint, ADD COLUMN col6 bigint

3.2.5.5.9. 新增表约束

语法

ALTER TABLE table_name alter_table_action_list;

alter_table_action_list:
    alter_table_action [, alter_table_action ...]

alter_table_action:
    ADD [CONSTRAINT [constraint_name]] UNIQUE (column_name [, column_name ]...)
    | ADD [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name_list) references_clause
    | ADD [CONSTRAINT [constraint_name]] CHECK (expr)
    | ADD CONSTRAINT constraint_name PRIMARY KEY (column_name)
    | ADD CONSTRAINT constraint_name FOREIGN KEY(foreign_col_name) REFERENCES
        reference_tbl_name(column_name)
    constraint_option

constraint_option:
    [disable | enable]

示例

-- 注意下面的示例中，因为约束名都相同，所以在测试时，注意在执行时出现冲突错误，请先删除存在的约束。再进行后续测试

-- 创建表
CREATE TABLE unisql_cn_test(a int,b int,c int,d varchar2(10),e varchar2(10));

-- 删除约束
ALTER TABLE unisql_cn_test DROP CONSTRAINT cn_name_a;

-- 测试主键或者唯一约束
-- 转换前Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a,b);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a UNIQUE (a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a UNIQUE (a,b);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a) disable;
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a) enable;

-- 转换后GaussDB-Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY(a)
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY(a, b)
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a UNIQUE(a)
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a UNIQUE(a, b)
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY(a)
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY(a)


-- 测试reference约束
-- 创建一张关联表
CREATE TABLE unisql_cn_ref_tab(col1 int,col2 int,col3 int,col4 varchar2(10),col5 varchar2(10));


-- 删除约束
ALTER TABLE unisql_cn_ref_tab DROP CONSTRAINT ref_tab_col1;
ALTER TABLE unisql_cn_ref_tab DROP CONSTRAINT ref_tab_col1_col2;

-- 添加一个外键约束(指定一个列)
ALTER TABLE unisql_cn_ref_tab ADD CONSTRAINT ref_tab_col1 PRIMARY KEY (col1);

-- 转换前Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a FOREIGN KEY (a) REFERENCES unisql_cn_ref_tab(col1);

-- 转换后GaussDB-Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a FOREIGN KEY (a) REFERENCES unisql_cn_ref_tab(col1)


-- 添加一个外键约束(指定两个列)
ALTER TABLE unisql_cn_ref_tab ADD CONSTRAINT ref_tab_col1_col2 PRIMARY KEY (col1,col2);

-- 添加两个列约束
-- 转换前Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a FOREIGN KEY (a,b) REFERENCES unisql_cn_ref_tab(col1,col2);

-- 转换后GaussDB-Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a FOREIGN KEY (a, b) REFERENCES unisql_cn_ref_tab(col1, col2)

3.2.5.5.10. 重命名表约束

语法

ALTER TABLE TABLE_NAME RENAME CONSTRAINT OLD_CONSTRAINT_NAME TO NEW_CONSTRAINT_NAME

示例

CREATE TABLE unisql_rename_constraint_test (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100),
department_id NUMBER,
salary NUMBER,
hire_date DATE DEFAULT SYSDATE,
CONSTRAINT EMP_EMAIL_PK PRIMARY KEY (employee_id),
CONSTRAINT EMP_EMAIL_UNIQUE UNIQUE (email),
CONSTRAINT EMP_SALARY_CHECK CHECK (salary > 0)
);

-- 转换前Oracle SQL:
ALTER TABLE unisql_rename_constraint_test RENAME CONSTRAINT EMP_EMAIL_PK TO "emp_email_pk_new";
ALTER TABLE unisql_rename_constraint_test RENAME CONSTRAINT EMP_EMAIL_UNIQUE TO EMP_EMAIL_uNiqueU;
ALTER TABLE unisql_rename_constraint_test RENAME CONSTRAINT EMP_SALARY_CHECK TO "EMP_EMAIL_op";


-- 转换后GaussDB-Oracle SQL:
ALTER TABLE unisql_rename_constraint_test RENAME CONSTRAINT EMP_EMAIL_PK TO "emp_email_pk_new";
ALTER TABLE unisql_rename_constraint_test RENAME CONSTRAINT EMP_EMAIL_UNIQUE TO EMP_EMAIL_uNiqueU;
ALTER TABLE unisql_rename_constraint_test RENAME CONSTRAINT EMP_SALARY_CHECK TO "EMP_EMAIL_op"

3.2.5.5.11. 删除表约束

语法

ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME

示例

-- 转换前Oracle SQL:
    -- 删除约束
ALTER TABLE unisql_cn_test DROP CONSTRAINT cn_name_a;
ALTER TABLE unisql_cn_ref_tab DROP CONSTRAINT ref_tab_col1;
ALTER TABLE unisql_cn_ref_tab DROP CONSTRAINT ref_tab_col1_col2;

-- 转换后GaussDB-Oracle SQL:
ALTER TABLE unisql_cn_test DROP CONSTRAINT cn_name_a
ALTER TABLE unisql_cn_ref_tab DROP CONSTRAINT ref_tab_col1
ALTER TABLE unisql_cn_ref_tab DROP CONSTRAINT ref_tab_col1_col2

3.2.5.5.12. 修改列的默认值属性

语法

ALTER TABLE [ schema. ] table_name modify [(] column_name default expression [,column_name default expression[,…])];

注意

• default中语法关键字作为列名时，需要加双引号使用。

• default中数据类型包括（int, number, numeric, varchar, varchar2, char, CHARACTER, timestamp, date）;

• 不支持函数;

• 表达式只针对数值类型的加减乘除；

• 关键字只包括（sysdate、current_timestamp、current_date）

示例

-- 创建测试表
create table unisql_alter_modify_default_test(
                                     id int primary key,
                                     INT_ INT,
                                     NUMBER_                NUMBER,
                                     numeric_               numeric,
                                     CHAR_ CHAR(10),
                                     CHARACTER_ CHARACTER(10),
                                     VARCHAR_ VARCHAR(4000),
                                     VARCHAR2_ VARCHAR2(4000),
TIMESTAMP_ TIMESTAMP,
date_ date
);
INSERT INTO unisql_alter_modify_default_test (id,INT_,NUMBER_,numeric_,CHAR_,CHARACTER_,VARCHAR_,VARCHAR2_,TIMESTAMP_,date_) VALUES(1,1,1.01,2.020,'1','1','1','1',TIMESTAMP '2024-01-01 00:00:00', DATE '2024-06-26');

-- 转换前Oracle SQL:
ALTER TABLE unisql_alter_modify_default_test MODIFY (INT_ DEFAULT 1+2-1*1/1,NUMBER_ DEFAULT 12.060/3*2,numeric_ DEFAULT 4.7090,CHAR_ DEFAULT 1+2-1*1/1,CHARACTER_ DEFAULT 1+2-1*1/1,VARCHAR_ DEFAULT 1+2-1*1/1,VARCHAR2_ DEFAULT 1+2-1*1/1);

-- 转换后GaussDB-Oracle SQL:
ALTER TABLE unisql_alter_modify_default_test ALTER COLUMN INT_ SET DEFAULT 1+2-1*1/1;
ALTER TABLE unisql_alter_modify_default_test ALTER COLUMN NUMBER_ SET DEFAULT 12.060/3*2;
ALTER TABLE unisql_alter_modify_default_test ALTER COLUMN numeric_ SET DEFAULT 4.7090;
ALTER TABLE unisql_alter_modify_default_test ALTER COLUMN CHAR_ SET DEFAULT 1+2-1*1/1;
ALTER TABLE unisql_alter_modify_default_test ALTER COLUMN CHARACTER_ SET DEFAULT 1+2-1*1/1;
ALTER TABLE unisql_alter_modify_default_test ALTER COLUMN VARCHAR_ SET DEFAULT 1+2-1*1/1;
ALTER TABLE unisql_alter_modify_default_test ALTER COLUMN VARCHAR2_ SET DEFAULT 1+2-1*1/1;


-- 转换前Oracle SQL:
ALTER TABLE unisql_alter_modify_default_test MODIFY (TIMESTAMP_ DEFAULT current_timestamp,date_ DEFAULT sysdate);

-- 转换后GaussDB-Oracle SQL:
ALTER TABLE unisql_alter_modify_default_test ALTER COLUMN TIMESTAMP_ SET DEFAULT current_timestamp(0);
ALTER TABLE unisql_alter_modify_default_test ALTER COLUMN date_ SET DEFAULT sysdate;

3.2.5.5.13. 修改表名、视图名称

语法

RENAME TABLE_NAME TO NEW_TABLE_NAME;

示例

-- 转换前Oracle SQL:
CREATE TABLE unisql_rename_table_test(id int,name varchar(10));

RENAME unisql_rename_table_test TO unisql_rename_table_test_new;

CREATE VIEW view_unisql_rename_table AS SELECT id,name FROM unisql_rename_table_test_new;

RENAME view_unisql_rename_table TO view_unisql_rename_table_new;

-- 转换后GaussDB-Oracle SQL:
CREATE TABLE unisql_rename_table_test (id bigint,name varchar(10));

ALTER TABLE unisql_rename_table_test RENAME TO unisql_rename_table_test_new;

CREATE VIEW view_unisql_rename_table AS SELECT id,name FROM unisql_rename_table_test_new;

ALTER TABLE view_unisql_rename_table RENAME TO view_unisql_rename_table_new;

3.2.5.5.14. 注释表名,列名

语法

COMMENT ON TABLE {object_name|COLUMN relation_name.column_name} IS string_literal;

示例

CREATE TABLE unisql_comment_table_test(id int,name varchar(10));

-- 转换前Oracle SQL:
COMMENT ON COLUMN unisql_comment_table_test.id IS 'unisql_comment_table_test id column';
COMMENT ON TABLE unisql_comment_table_test IS 'unisql_comment_table_test table';

-- 转换后GaussDB-Oracle SQL:
COMMENT ON COLUMN unisql_comment_table_test.id IS 'unisql_comment_table_test id column'
COMMENT ON TABLE unisql_comment_table_test IS 'unisql_comment_table_test table'

3.2.5.5.15. 修改表

支持普通表修改为分区表的转换。参考示例如下：

语法

GaussDB-Oracle不支持

3.2.5.5.16. 创建视图

语法

CREATE [OR REPLACE] [[NO] FORCE] VIEW view_name [(column_name_list)] AS select_stmt;

 column_name_list:
     column_name [, column_name ...]

描述

该语句用于创建视图语句，如果指定了 OR REPLACE 子句，该语句能够替换已有的视图

示例

-- 创建表
CREATE TABLE unisql_view_test(a int,b int,c varchar(10));

-- 创建视图示例
-- 转换前Oracle SQL:
CREATE VIEW unisql_view_1 AS SELECT * FROM unisql_view_test;
CREATE VIEW unisql_view_2 AS SELECT a,b FROM unisql_view_test;
CREATE VIEW public.unisql_view_3 AS SELECT a,b FROM unisql_view_test;
CREATE VIEW unisql_view_4 AS SELECT a,b FROM unisql_view_test;
CREATE VIEW unisql_view_5 AS SELECT ' ' a, ' ' b FROM dual;
CREATE VIEW unisql_view_6 AS SELECT 1 a, 2 b FROM dual;
CREATE OR REPLACE VIEW unisql_view_7 AS SELECT 1 a, 2 b FROM dual;
CREATE OR REPLACE VIEW unisql_view_8 AS SELECT 1 a, 2 b, '3' c FROM dual;
CREATE FORCE VIEW unisql_view_9 AS SELECT 1 a, 2 b, '3' c FROM dual;
CREATE NOFORCE VIEW unisql_view_10 AS SELECT 1 a, 2 b, '3' c FROM dual;
CREATE VIEW unisql_view_11(a1,b1) AS SELECT a,b FROM unisql_view_test;

-- 转换后GaussDB-Oracle SQL:
CREATE VIEW unisql_view_1 AS SELECT * FROM unisql_view_test
CREATE VIEW unisql_view_2 AS SELECT a,b FROM unisql_view_test
CREATE VIEW public.unisql_view_3 AS SELECT a,b FROM unisql_view_test
CREATE VIEW unisql_view_4 AS SELECT a,b FROM unisql_view_test
CREATE VIEW unisql_view_5 AS SELECT ' ' AS a,' ' AS b
CREATE VIEW unisql_view_6 AS SELECT 1 AS a,2 AS b
CREATE OR REPLACE VIEW unisql_view_7 AS SELECT 1 AS a,2 AS b
CREATE OR REPLACE VIEW unisql_view_8 AS SELECT 1 AS a,2 AS b,'3' AS c
CREATE VIEW unisql_view_9 AS SELECT 1 AS a,2 AS b,'3' AS c
CREATE VIEW unisql_view_10 AS SELECT 1 AS a,2 AS b,'3' AS c
CREATE VIEW unisql_view_11 (a1,b1) AS SELECT a,b FROM unisql_view_test

3.2.5.5.17. 创建序列

语法

CREATE SEQUENCE [ schema. ] sequence_name
 { START WITH int_value
 |[ INCREMENT BY int_value ]
 |[ MINVALUE int_value | NOMINVALUE ]
 |[ MAXVALUE int_value | NOMAXVALUE ]
 |[ CACHE int_value | NOCACHE ]
 |[ ORDER | NOORDER ]
 |[ CYCLE | NOCYCLE ]
 }
 ;

描述

该语句用来创建序列。序列属于数据库对象，用户可以从序列中生成唯一整数。

参数解释

字段	描述
schema.	指定包含序列的 Schema。如果省略 Schema，则数据库会在用户当前所在的 Schema 中创建序列。
sequence_name	要创建的序列名称。
MINVALUE int_value	指定序列的最小值，int_value 的取值范围是 [-9223372036854775808，9223372036854775807]。
NOMINVALUE	NOMINVALUE 为默认值。 如果指定为 NOMINVALUE，则升序时最小值为 1，降序时最小值为 -9223372036854775808。
MAXVALUE int_value	指定序列的最大值，int_value 的取值范围是 [-9223372036854775808，9223372036854775807]。注意:同时指定 MINVALUE 和 MAXVALUE 时，MINVALUE 必须小于 MAXVALUE。MAXVALUE 和 MINVALUE 之差必须大于等于 INCREMENT BY 中 的 int_value。
NOMAXVALUE	NOMAXVALUE 为默认值。 如果指定为 NOMAXVALUE，则升序时最大值为9223372036854775807，降序时最大值为 -1。
START WITH int_value	指定序列的起始值。int_value 必须小于等于 MAXVALUE，且大于等于 MINVALUE。 如果不指定，则升序时默认取值为最小值，降序时默认取值为最大值。
INCREMENT BY int_value	指定序列的自增步数。int_value 不可以为 0。 如果指定为正数，则序列为升序；如果指定为负数，则序列为降序。 不指定时缺省值为 1。
CACHE int_value	指定在内存中预分配的自增值个数，int_value 默认为 20。CACHE int_value 的值必须大于 1，如果 CACHE INT_VALUE 的值为 1，则等价于 NOCACHE。
NOCACHE	指定序列的值为未预先分配。 如果同时省略 CACHE 和 NOCACHE，则数据库默认缓存 20 个序列号。
ORDER	指定序列的值按顺序生成。
NOORDER	NOORDER 为默认值，即不保证序列的值按顺序生成。
CYCLE	指定序列的值循环生成，即序列在达到其最大值或最小值后继续生成值。在升序达到最大值后，它会生成最小值。在降序序列达到其最小值后，它会生成其最大值。注意: 如果指定了 CYCLE，而且 INCREMENT BY int_value 小于 0 时，必须指定 MINVALUE。如果指定了 CYCLE，CACHE 的数量不能超过一个 CYCLE。
NOCYCLE	NOCYCLE 为默认值，指定序列在达到其最大值或最小值后无法生成更多值。

示例

-- 创建序列示例
-- 转换前Oracle SQL:
CREATE SEQUENCE SE_NAME MAXVALUE 100000 CYCLE;
CREATE SEQUENCE SE_NAME_1 NOCYCLE;
CREATE SEQUENCE SE_NAME_2 CACHE 20;
CREATE SEQUENCE SE_NAME_3 START WITH 1 NOCACHE;
CREATE SEQUENCE SE_NAME_4 START WITH 1 ORDER;
CREATE SEQUENCE SE_NAME_5 START WITH 1 NOORDER;
CREATE SEQUENCE SE_NAME_6 START WITH 1 KEEP;
CREATE SEQUENCE SE_NAME_7 NOKEEP START WITH 1;
CREATE SEQUENCE SE_NAME_8 NOMINVALUE MAXVALUE 9223372036854775808;
CREATE SEQUENCE SE_NAME_9 MAXVALUE 9223372036854775806;
CREATE SEQUENCE SE_NAME_10 MINVALUE -9223372036854775808 NOMAXVALUE;
CREATE SEQUENCE SE_NAME_11 MINVALUE -9223372036854775809;
CREATE SEQUENCE SE_NAME_12 MINVALUE -9999999999999999999999999999;
CREATE SEQUENCE SE_NAME_13 INCREMENT BY 9223372036854775808;
CREATE SEQUENCE SE_NAME_14 INCREMENT BY -9223372036854775809;
CREATE SEQUENCE SE_NAME_15 MINVALUE -9999999999999999999999999999;
CREATE SEQUENCE SE_NAME_16 INCREMENT BY 9223372036854775808;
CREATE SEQUENCE SE_NAME_17 INCREMENT BY -9223372036854775809;
CREATE SEQUENCE SE_NAME_18 START WITH 9223372036854775808;

-- 转换后GaussDB-Oracle SQL:
CREATE SEQUENCE SE_NAME MAXVALUE 100000 CYCLE
CREATE SEQUENCE SE_NAME_1 NO CYCLE
CREATE SEQUENCE SE_NAME_2 CACHE 20
CREATE SEQUENCE SE_NAME_3 START WITH 1
CREATE SEQUENCE SE_NAME_4 START WITH 1
CREATE SEQUENCE SE_NAME_5 START WITH 1
CREATE SEQUENCE SE_NAME_6 START WITH 1
CREATE SEQUENCE SE_NAME_7 START WITH 1
CREATE SEQUENCE SE_NAME_8 NO MINVALUE MAXVALUE 9223372036854775807
CREATE SEQUENCE SE_NAME_9 MAXVALUE 9223372036854775806
CREATE SEQUENCE SE_NAME_10 MINVALUE -9223372036854775808 NO MAXVALUE
CREATE SEQUENCE SE_NAME_11 MINVALUE -9223372036854775808
CREATE SEQUENCE SE_NAME_12 MINVALUE -9223372036854775808
CREATE SEQUENCE SE_NAME_13 INCREMENT BY 9223372036854775807
CREATE SEQUENCE SE_NAME_14 INCREMENT BY -9223372036854775808
CREATE SEQUENCE SE_NAME_15 MINVALUE -9223372036854775808
CREATE SEQUENCE SE_NAME_16 INCREMENT BY 9223372036854775807
CREATE SEQUENCE SE_NAME_17 INCREMENT BY -9223372036854775808
CREATE SEQUENCE SE_NAME_18 START WITH 9223372036854775807

3.2.5.5.18. 迁移表空间

语法

alter table [schema.]table_name move tablespace tablespace_name;

描述

该语法用于迁移表空间

注意

由于GaussDB-Oracle中的表空间功能不完善,统一SQL在进行转换时,将该语句默认转换成select 1

同时统一SQL支持参数可配置项,用户可选择配置所要转换的sql进行替换,参见快速开始=》配置说明=》配置文件=》unisql.conf文件说明一节(unisql.conf文件说明)

示例

-- 创建表
CREATE TABLE test(a int,b int,c varchar(10));


-- 转换前Oracle SQL:
alter table test move tablespace tb_space;

-- 转换后GaussDB-Oracle:
select 1