1.3.2.5.2. Tdsql-Oracle

1.3.2.5.2.1. 创建分区表

分区键仅支持列，分区范围支持使用字符串、常数表达，统一SQL会根据表名、分区名在转化库中创建分区表，分区表名为：表名$分区名。

语法

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

示例

-- 转换前Oracle SQL:
CREATE TABLE sales (
    sale_id NUMBER,
    sale_amount NUMBER,
    sale_date DATE
)
PARTITION BY RANGE (sale_amount) (
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (5000),
    PARTITION p3 VALUES LESS THAN (10000),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

-- 转换后TDSQL-Oracle模式 SQL:
CREATE TABLE sales (sale_id number, sale_amount number, sale_date date) PARTITION BY RANGE (sale_amount);
CREATE TABLE sales$p1 PARTITION OF sales for values from (MINVALUE) to (1000);
CREATE TABLE sales$p2 PARTITION OF sales for values from (1000) to (5000);
CREATE TABLE sales $ p3 PARTITION OF sales for values from (5000) to (10000);
CREATE TABLE sales $ p4 PARTITION OF sales for values from (10000) to (MAXVALUE);


-- 转换前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)
);

-- 转换后TDSQL-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_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'))
);

-- 转换后TDSQL-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));
-- 实际分区范围使用函数，源库TDSQL-Oracle模式无法执行。

1.3.2.5.2.2. 清空分区表

清空分区，实际转化成清空分区表，分区表名为：表名$分区名，暂不支持表和分区名上有双引号的清空。

语法

ALTER TABLE TABLE_NAME TRUNCATE PARTITION PARTITION_NAMES

示例

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

-- 转换后TDSQL-Oracle模式 SQL:
TRUNCATE TABLE unisql_partition_by_range_test$q1_2023;

1.3.2.5.2.3. 清空表

语法

TRUNCATE TABLE [schema.] table

示例

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;

-- 转换后TDSQL-Oracle模式 SQL:
TRUNCATE TABLE unisql_truncate_test;

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

1.3.2.5.2.4. 删除表

语法

DROP TABLE [ schema. ] table
[ CASCADE CONSTRAINTS ]

说明

schema: 表所在的模式

table: 要删除的表名称

cascade constraints: 如果当前表的主键或唯一键被其他表引用,删除当前表的同时会删除其他表引用的约束。

示例

--创建测试表unisql_refed
CREATE TABLE unisql_refed(refed_id int PRIMARY KEY ,refedcol char(10),refedcol2 char(10));
--创建测试表,添加外键引用
CREATE TABLE unisql_ref_test(id int, refed_id int,col char(10),col2 char(10),CONSTRAINT id_ref FOREIGN KEY(refed_id) REFERENCES unisql_refed(refed_id));

-- 转换前Oracle SQL:
-- 删除表unisql_refed,同时删除unisql_ref_test中的约束
DROP TABLE unisql_refed CASCADE CONSTRAINTS;

-- 转换后TDSQL-Oracle模式 SQL:
DROP TABLE unisql_refed CASCADE

1.3.2.5.2.5. 修改列的属性

语法

ALTER TABLE [ schema. ] table MODIFY
{ ( column [ datatype ] [ DEFAULT expr] [[NOT] NULL] [, column [ datatype ] [ DEFAULT expr] [[NOT] NULL]]... )
}

注意

• 数据类型相关请参考数据类型章节

示例

-- 创建测试表
DROP TABLE unisql_alter_modify_test;
CREATE TABLE unisql_alter_modify_test(id int, name varchar2(10),col varchar2(10));
SELECT * FROM unisql_alter_modify_test;

-- 转换前Oracle SQL:
ALTER TABLE unisql_alter_modify_test modify (name varchar(100),col number);
ALTER TABLE unisql_alter_modify_test modify (name DEFAULT '开始',col DEFAULT 0);
ALTER TABLE unisql_alter_modify_test modify (name NULL,col NULL);
ALTER TABLE unisql_alter_modify_test modify (name varchar(100) DEFAULT '开始' NOT NULL,col number default 0 NOT NULL);


-- 转换后TDSQL-Oracle模式 SQL:
ALTER TABLE unisql_alter_modify_test ALTER COLUMN name TYPE varchar(100),ALTER COLUMN col TYPE number
ALTER TABLE unisql_alter_modify_test ALTER COLUMN name SET DEFAULT '开始',ALTER COLUMN col SET DEFAULT 0
ALTER TABLE unisql_alter_modify_test ALTER COLUMN name DROP NOT NULL,ALTER COLUMN col DROP NOT NULL
ALTER TABLE unisql_alter_modify_test ALTER COLUMN name TYPE varchar(100),ALTER COLUMN name SET DEFAULT '开始',ALTER COLUMN name SET NOT NULL,ALTER COLUMN col TYPE number(10),ALTER COLUMN col SET DEFAULT 0,ALTER COLUMN col SET NOT NULL

1.3.2.5.2.6. 创建索引

语法

CREATE [UNIQUE] INDEX [SCHEMA.] INDEX_NAME
ON TABLE_NAME ( COLUMN1, COLUMN2, ..., COLUMNN )

警告

• 索引的schema语法糖为处理。

示例

-- 转换前Oracle SQL:
CREATE INDEX unisql.unisql_index_test_idx ON unisql_index_test (col1);

-- 转换后TDSQL-Oracle模式 SQL:
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1)6;

1.3.2.5.2.7. 新增表约束

语法

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] [validate | novalidate]

警告

• constraint_option 语法糖实现，无实际功能。

示例

-- 创建表
CREATE TABLE unisql_cn_test(a int,b int,c int,d varchar2(10),e varchar2(10));
CREATE TABLE unisql_cn_ref_tab(col1 int,col2 int,col3 int,col4 varchar2(10),col5 varchar2(10));

-- 转换前Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_1 PRIMARY KEY (a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_2 PRIMARY KEY (a,b);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_3 UNIQUE (a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_4 UNIQUE (a,b);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_5 PRIMARY KEY (a) disable;
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_6 PRIMARY KEY (a) enable;
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_7 PRIMARY KEY (a) enable validate;
ALTER TABLE unisql_cn_ref_tab ADD CONSTRAINT ref_tab_col1 PRIMARY KEY (col1);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_8 FOREIGN KEY (a) REFERENCES unisql_cn_ref_tab(col1);

-- 转换后TDSQL-Oracle模式 SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_1 PRIMARY KEY(a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_2 PRIMARY KEY(a, b);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_3 UNIQUE(a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_4 UNIQUE(a, b);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_5 PRIMARY KEY(a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_6 PRIMARY KEY(a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_7 PRIMARY KEY(a);
ALTER TABLE unisql_cn_ref_tab ADD CONSTRAINT ref_tab_col1 PRIMARY KEY(col1);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a_8 FOREIGN KEY (a) REFERENCES unisql_cn_ref_tab(col1);