3.2.5.2. Tdsql-Oracle

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模式无法执行。

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;

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;

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;

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