11.3. SQL 语法

11.3.1. 查询和子查询

查询是指数据库中用来获取数据的方式，它可搭配条件限制子句（例如 WHERE），排列顺序子句（例如 ORDER BY）等语句来获取查询结果。

子查询是指嵌套在一个上层查询中的查询。上层的查询一般被称为父查询或外部查询。子查询的结果作为输入传递回父查询或外部查询。父查询将这个值结合到计算中，以便确定最后的输出。

SQL 语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。同时，子查询可以出现在 SQL 语句中的各种子句中，例如 SELECT 语句、FROM 语句和 WHERE 语句等。

SQL 语句中常见的查询类型如下：

• 简单查询

• 层次查询

• 复合查询

• 联接查询

• 子查询

• DUAL 表查询

• ORDER BY 子句

• AS作为别名

11.3.1.1. 简单查询

简单查询是指从数据库的一个或多个选择列表或视图中检索一个或多个列数据，列的数量以及它们的数据类型和长度由选择列表的元素确定。而选择列表指的是 SELECT 关键字之后和 FROM 子句之前的表达式列表。

如果两个或多个表有一些共同的列名，则必须用表名限定列名。您可以使用列别名 col_alias 来标记紧接在选择列表之前的表达式。别名可以在 ORDER BY 子句中使用，但不能在查询中的其他子句中使用。

查询的 SELECT 语法如下：

simple_select:
SELECT [ DISTINCT | ALL] select_expr_list
[FROM from_list ]
[WHERE condition]
[GROUP BY group_expression_list [HAVING condition]]
[ORDER BY order_expression_list]
[FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]
[row_limiting_clause]

select_expr_list:
    table_name.*
    | table_alias_name.*
    | expr [[AS] column_alias_name]

from_list:
    DUAL
    | table_reference [, table_reference...]

table_reference:
    simple_table
    | joined_table
    | pivot_clause
    | unpivot_clause
    | table_name@dblink_name

simple_table:
    table_name [partition_option] [table_alias_name]
    | (select_stmt) [table_alias_name]
    | (table_reference_list)

joined_table:
    table_reference [INNER] JOIN simple_table [join_condition]
    | table_reference outer_join_type JOIN simple_table join_condition

partition_option:
    PARTITION (partition_name)

outer_join_type:
    {LEFT | RIGHT | FULL} [OUTER]

join_condition:
    ON expression

condition:
    expression

group_expression_list:
    group_expression [, group_expression...]

group_expression:
    expression

order_expression_list:
    order_expression [, order_expression...]

order_expression:
    expression [ASC | DESC]

row_limiting_clause:
    [ OFFSET offset { ROW | ROWS } ]
    [ FETCH { FIRST | NEXT } [ { rowcount } ] { ROW | ROWS } { ONLY } ]

pivot_clause：
    PIVOT
    (aggregate_function ( expr ) [[AS] alias ]
        [, aggregate_function ( expr ) [[AS] alias ]... ]
        pivot_for_clause
        pivot_in_clause
    )

pivot_for_clause：
    FOR { column }

pivot_in_clause
    IN
    ( { { expr| ( expr [, expr...] ) } [ [ AS] alias]... }
        [, { { expr| ( expr [, expr...] ) } [ [ AS] alias] ...} ]
    )

unpivot_clause ：
    UNPIVOT
    ( { column }
      pivot_for_clause
      unpivot_in_clause
    )

unpivot_in_clause：
    IN
    ( { column | ( column [, column... ]) }
    )

CREATE TABLE unisql_simple_query_employee (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
manager_id INT,
salary NUMERIC
);
INSERT INTO unisql_simple_query_employee VALUES(1, 'DEL', 'FA BEN', 1, 1500);
INSERT INTO unisql_simple_query_employee VALUES(2, 'AXEL', 'BELL', 1, 1000);
INSERT INTO unisql_simple_query_employee VALUES(3, 'CRIS',  'RACHAR', 1, 1000);
INSERT INTO unisql_simple_query_employee VALUES(4, 'BlAIR',  'WALDORF', 1, 800);

-- 转换前Oracle SQL：
-- 查询表中部分列
SELECT first_name, last_name, salary FROM unisql_simple_query_employee;
-- 查询表中所有列
SELECT * FROM unisql_simple_query_employee;
-- 对表中的列进行数学运算
SELECT salary+100 FROM unisql_simple_query_employee;
-- 给表中的列取别名
SELECT salary*12 AS year_salary FROM unisql_simple_query_employee;
-- 将表中的数据去重
SELECT DISTINCT manager_id FROM unisql_simple_query_employee;
-- 使用 CASE WHEN 语句查询表中的数据
SELECT salary, CASE WHEN salary >= 1200 then 'high' WHEN salary >= 1000 THEN 'middle' ELSE 'low' END AS type FROM unisql_simple_query_employee;

-- 转换后TDSQL-Oracle模式 SQL：
-- 查询表中部分列
SELECT first_name,last_name,salary FROM unisql_simple_query_employee
-- 查询表中所有列
SELECT * FROM unisql_simple_query_employee
-- 对表中的列进行数学运算
SELECT salary+100 FROM unisql_simple_query_employee
-- 给表中的列取别名
SELECT salary*12 AS year_salary FROM unisql_simple_query_employee
-- 将表中的数据去重
SELECT DISTINCT manager_id FROM unisql_simple_query_employee
-- 使用 CASE WHEN 语句查询表中的数据
SELECT salary,CASE WHEN salary>=1200 THEN 'high' WHEN salary>=1000 THEN 'middle' ELSE 'low' END AS type FROM unisql_simple_query_employee

11.3.1.2. 层次查询

层次查询（Hierarchical Query）是一种具有特殊功能的查询语句，通过它能够将分层数据按照层次关系展示出来。

语法

SELECT [level], column, expr... FROM table [WHERE condition] [ START WITH start_expression ]
CONNECT BY { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
[ GROUP BY ... ] [ HAVING ... ] [ ORDER BY ... ]

警告

1. 层次查询特有表达式不支持嵌套使用：如: sys_connect_by_root(prior column)、LTRIM(MAX(SYS_CONNECT_BY_PATH(column, ‘;’)), ‘;’)

2. 不支持和oracle(+)一起使用

3. 不支持和pivot, unpivot函数一起使用

4. 列名不支持带schema名和数据库名，只支持表名和字段名。

5. 不支持和with cte一起使用

6. 多表不支持*，需要列明字段；单表用*时，不能再查表中相同字段，如test中有id字段，不能用test.*,test.id，test.id

7. 不支持函数

8. level、CONNECT_BY_ROOT、SYS_CONNECT_BY_PATH 中不支持表达式和函数，只支持字段

9. 不支持rownum

11.3.1.3. 复合查询

使用集合运算符 UNION、UNION ALL、INTERSECT 和 MINUS 来组合多个查询，即复合查询。所有集合运算符都具有相同的优先级。

11.3.1.4. 联接查询

联接（Join）是将两个或多个表、视图的结合在一起的查询。

11.3.1.5. 子查询

子查询指的是 SELECT 查询语句中嵌套了另一个或者多个 SELECT 语句，可以返回单行结果、多行结果或不返回结果。SELECT 语句的 FROM 子句中的子查询也称为内联视图。可以在嵌入式视图中嵌套任意数量的子查询。SELECT 语句的 WHERE 子句中的子查询也称为嵌套子查询。

子查询可以分为相关子查询和非相关子查询。相关子查询指该子查询的执行依赖了外部查询的变量，这种子查询通常会执行多次。非相关子查询指该子查询的执行不依赖外部查询的变量，这种子查询一般只需要计算一次。对于非相关子查询与部分相关子查询，可以通过改写进行子查询消除，实现嵌套子查询的展开。

语法

SELECT [ hint ] [ { { DISTINCT | UNIQUE } | ALL } ] select_list
FROM { table_reference | join_clause | ( join_clause ) }
[ , { table_reference | join_clause | (join_clause) } ]
[ where_clause ]
[ hierarchical_query_clause ]
[ group_by_clause ]
| subquery { UNION [ALL] | INTERSECT | MINUS } subquery [ { UNION [ALL] | INTERSECT | MINUS } subquery ]
| ( subquery ) [ order_by_clause ]

示例

-- 创建表结构
CREATE TABLE unisql_table_a(PK INT, name VARCHAR(25));
INSERT INTO unisql_table_a VALUES(1,'福克斯');
INSERT INTO unisql_table_a VALUES(2,'警察');
INSERT INTO unisql_table_a VALUES(3,'的士');
INSERT INTO unisql_table_a VALUES(4,'林肯');
INSERT INTO unisql_table_a VALUES(5,'亚利桑那州');
INSERT INTO unisql_table_a VALUES(6,'华盛顿');
INSERT INTO unisql_table_a VALUES(7,'戴尔');
INSERT INTO unisql_table_a VALUES(10,'朗讯');

CREATE TABLE unisql_table_b(PK INT, name VARCHAR(25));
INSERT INTO unisql_table_b VALUES(1,'福克斯');
INSERT INTO unisql_table_b VALUES(2,'警察');
INSERT INTO unisql_table_b VALUES(3,'的士');
INSERT INTO unisql_table_b VALUES(6,'华盛顿');
INSERT INTO unisql_table_b VALUES(7,'戴尔');
INSERT INTO unisql_table_b VALUES(8,'微软');
INSERT INTO unisql_table_b VALUES(9,'苹果');
INSERT INTO unisql_table_b VALUES(11,'苏格兰威士忌');

-- 转换前Oracle SQL:
-- 没有依赖关系的子查询
SELECT * FROM unisql_table_a T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b T2);
PK|NAME|
--+----+
1|福克斯 |
2|警察  |
3|的士  |
6|华盛顿 |
7|戴尔  |

-- 转换后TDSQL-Oracle模式 SQL:
SELECT * FROM unisql_table_a AS T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b AS T2)
pk|name|
--+----+
 1|福克斯 |
 2|警察  |
 3|的士  |
 6|华盛顿 |
 7|戴尔  |


-- 转换前Oracle SQL:
-- 有依赖关系的子查询，子查询中用到了外层查询变量 T1.PK
SELECT * FROM unisql_table_a T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b T2 WHERE T2.PK = T1.PK);
PK|NAME|
--+----+
 1|福克斯 |
 2|警察  |
 3|的士  |
 6|华盛顿 |
 7|戴尔  |

-- 转换后TDSQL-Oracle模式 SQL:
SELECT * FROM unisql_table_a AS T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b AS T2 WHERE T2.PK=T1.PK)
pk|name|
--+----+
 1|福克斯 |
 2|警察  |
 3|的士  |
 6|华盛顿 |
 7|戴尔  |

11.3.1.6. DUAL 表查询

DUAL 表有一个列 DUMMY，定义为 VARCHAR2(1)，并包含一个值为 X 的行。在使用 SELECT 语句计算常量表达式时，可以从 DUAL 表中选择数据。

-- 转换前Oracle SQL:
SELECT
    1,
    2 + 1,
    NULL,
    ''
FROM
dual;
1|2+1|NULL|''|
-+---+----+--+
1|  3|    |  |

-- 转换后TDSQL-Oracle模式 SQL:
SELECT 1,2+1,NULL,''
?column?|?column?|?column?|?column?|
--------+--------+--------+--------+
       1|       3|        |        |

11.3.1.7. ORDER BY 子句

ORDER BY 子句用于对查询结果按照一个或多个属性列进行升序（ASC）或降序（DESC）排列，默认为升序。排序查询的 SQL 语句格式如下：

ORDER BY expr
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, expr
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]
]

示例

DROP TABLE orderby_nulls_test;
CREATE TABLE orderby_nulls_test(id int,name varchar(10));

INSERT INTO orderby_nulls_test values(1,'jack');
INSERT INTO orderby_nulls_test values(2,'tom');
INSERT INTO orderby_nulls_test values(3,'cacy');
INSERT INTO orderby_nulls_test values(4,'bob');
INSERT INTO orderby_nulls_test values(null,'nina');


-- 转换前Oracle SQL:
SELECT * FROM orderby_nulls_test ORDER BY id ASC;
SELECT * FROM orderby_nulls_test ORDER BY id DESC;
SELECT * FROM orderby_nulls_test ORDER BY 1 NULLS FIRST;
SELECT * FROM orderby_nulls_test ORDER BY id DESC NULLS FIRST;
SELECT id,name FROM orderby_nulls_test UNION SELECT id,name FROM orderby_nulls_test ORDER BY id NULLS FIRST;
SELECT e.id, e.name FROM orderby_nulls_test e LEFT JOIN orderby_nulls_test d ON e.id = d.id ORDER BY d.id ASC NULLS FIRST;
SELECT id,name,ROW_NUMBER() over(PARTITION BY id ORDER BY name NULLS last) FROM orderby_nulls_test;
SELECT id,name,MAX(id) over(PARTITION BY id ORDER BY name NULLS last) FROM orderby_nulls_test;
SELECT id, COUNT(*) AS ct FROM orderby_nulls_test GROUP BY id ORDER BY COUNT(*) NULLS FIRST;

-- 转换后TDSQL-Oracle模式 SQL:
SELECT * FROM orderby_nulls_test ORDER BY id
SELECT * FROM orderby_nulls_test ORDER BY id DESC
SELECT * FROM orderby_nulls_test ORDER BY 1 NULLS FIRST
SELECT * FROM orderby_nulls_test ORDER BY id DESC NULLS FIRST
SELECT id,name FROM orderby_nulls_test UNION SELECT id,name FROM orderby_nulls_test ORDER BY id NULLS FIRST
SELECT e.id,e.name FROM orderby_nulls_test AS e LEFT JOIN orderby_nulls_test AS d ON e.id=d.id ORDER BY d.id NULLS FIRST
SELECT id,name,ROW_NUMBER() OVER (PARTITION BY id ORDER BY name NULLS LAST) FROM orderby_nulls_test
SELECT id,name,MAX(id) OVER (PARTITION BY id ORDER BY name NULLS LAST) FROM orderby_nulls_test
SELECT id,COUNT(1) AS ct FROM orderby_nulls_test GROUP BY id ORDER BY COUNT(1) NULLS FIRST

11.3.1.8. AS作为别名

通常AS在数据库中作为保留关键字，一般不建议作为字段的别名，但是有时候开发人员在编写SQL语句时，存在使用AS作为别名的情况。 统一SQL支持AS作为别名时到目标库的转换。

示例

-- 转换前Oracle SQL:
SELECT 0 as, 1 as,2 "as", 3 a, 4 as b, count(*) as FROM dual;
0AS|1AS|as|A|B|COUNT(*)AS|
---+---+--+-+-+----------+
  0|  1| 2|3|4|         1|

-- 转换后TDSQL-Oracle模式 SQL:
SELECT 0 AS as,1 AS as,2 AS "as",3 AS a,4 AS b,count(1) AS as
as|as|as|a|b|as|
--+--+--+-+-+--+
 0| 1| 2|3|4| 1|

11.3.2. 伪列

伪列（Pseudocolumn）的行为与表中的列相同，但并未存储具体数值。因此，伪列只具备读属性，不可以对伪列进行插入、更新、删除的等行为。本节主要介绍 统一SQL 所支持的伪列及使用说明。

当前支持如下伪列类型：

• 层次查询伪列

11.3.2.1. 层次查询伪列

层次查询伪列仅在层次查询中有效，要在查询中定义层次结构关系，必须使用 CONNECT BY 子句。

11.3.3. 运算符

运算符一般用于连接运算数或参数等单个数据项并返回结果。从语法上讲，运算符出现在操作数之前、操作数之后或两个操作数之间均可。本节主要介绍 统一SQL 所支持的常见运算符及使用说明。

当前支持常见运算符类型：

• 串联运算符

11.3.3.1. 串联运算符

串联运算符 || 用于连接字符串和 CLOB 数据类型的数据。使用串联运算符连接两个字符串后会得到另一个字符串。

-- 转换前Oracle SQL:
select '成交价格高于前一日收盘价;成交价格：' || 2 * 1000.00 || '元，收盘价：' || null || '' || '   '|| 'end'|| ''  FROM DUAL;
'成交价格高于前一日收盘价;成交价格：'||2*1000.00||'元，收盘价：'||NULL||''||''||'END'||''|
------------------------------------------------------------------+
成交价格高于前一日收盘价;成交价格：2000元，收盘价：   end                                |

-- 转换后TDSQL-Oracle模式 SQL:
SELECT concat(concat(concat(concat(concat(concat(concat('成交价格高于前一日收盘价;成交价格：', 2*1000.00), '元，收盘价：'), NULL), ''), '   '), 'end'), '');
concat                               |
-------------------------------------+
成交价格高于前一日收盘价;成交价格：2000.00元，收盘价：   end|

警告

本方案中串联运算符会转化成concat函数，转化的结果是字符串，字符串去和其他数据类型进行运算、比较时，因为源库和目标库的隐式转化不同，出现转出的SQL无法在目标库中执行的情况。

11.3.4. 字面量

字面量用于表达一个固定的数值，与常量值是同义词。许多函数和 SQL 语句都需要指定字面量，字面量也可以作为表达式和条件的一部分。

11.3.4.1. 间隔字面量

间隔字面量用来指定一段时间。统一SQL支持两种类型的间隔字面量：INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND。

• INTERVAL YEAR TO MONTH

语法

/*SQL 日期格式*/
INTERVAL '[+|-] years-months' YEAR [(precision)] TO MONTH

/*ISO 日期格式*/
INTERVAL 'P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]]' YEAR [(precision)] TO MONTH

描述

用来指定以年和月为单位的一段时间。

参数解释

参数	说明
years-months	years 表示年，取整数值，范围为 [0，178000000]。months 表示月，取整数值，范围为 [0 ， 11]。注意 值中不允许有空格。
precision	表示 YEAR 元素的精度，默认值为 2，取值范围为 [0,9]
P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]]	ISO 日期格式，范围见下列，注意 值中不允许有空格。

• years 表示年数，取整数值，范围为 [0,178000000]。

• months 表示月数，取整数值，范围为 [0,11]。

• days 表示日数，取整数值，范围为 [0,366]。

• hours 表示秒数，取整数值，范围为 [0,59]。

• minutes 表示分钟数，取整数值，范围为 [0,59]。

• seconds 表示秒数，取整数值，范围为 [0,59]。

• frac_secs 表示秒的精度，取整数值，范围为 [0,999999999]。

示例

CREATE TABLE unisql_interval_ym_table (
    col1 INTERVAL YEAR(1) TO MONTH,
    col2 INTERVAL YEAR(2) TO MONTH,
    col3 INTERVAL YEAR(3) TO MONTH
);

-- 转换前Oracle SQL:
INSERT INTO unisql_interval_ym_table(col1,col2,col3) VALUES
(
    INTERVAL '2-3' YEAR TO MONTH,
    INTERVAL '-21-3' YEAR(2) TO MONTH,
    INTERVAL 'P3Y11M366DT23H59M59.1234S' YEAR(2) TO MONTH
);
SELECT col1,col2,col3 FROM unisql_interval_ym_table;
COL1|COL2 |COL3|
----+-----+----+
2-3 |-21-3|3-11|

-- 转换后TDSQL-Oracle模式 SQL:
INSERT INTO unisql_interval_ym_table (col1,col2,col3) VALUES (INTERVAL '2-3' YEAR TO MONTH,INTERVAL '-21-3' YEAR TO MONTH,INTERVAL 'P3Y11M366DT23H59M59.1234S' YEAR TO MONTH);
SELECT col1,col2,col3 FROM unisql_interval_ym_table;
col1          |col2             |col3           |
--------------+-----------------+---------------+
2 years 3 mons|-21 years -3 mons|3 years 11 mons|

• INTERVAL DAY TO SECOND

语法

INTERVAL '[+ | -] days hours:minutes:seconds[.frac_secs]' DAY [(precision)] TO SECOND [(fractional_seconds_precision)]

描述

用来指定以天和具体时间为单位的一段时间。

参数解释

参数	说明
days hours：minutes：seconds[.frac_secs]	SQL日期格式，依次表示天数、时、分、秒。注意 值中时、分、秒不允许有空格。
precision	表示 DAY 元素的精度，默认值为 2，取值范围为 [0,9]
fractional_seconds_precision	表示 SECOND 元素小数部分的精度，默认值为 6，取值范围为 [0,9]

• days 表示天，取整数值，范围为 [0,999999999]。

• hours 表示小时，取整数值，范围为 [0,23]。

• minutes 表示分钟，取整数值，范围为 [0,59]。

• seconds 表示秒，取整数值，范围为 [0,59]。

示例

CREATE TABLE unisql_interval_ds_table (
    col1 INTERVAL DAY(3) TO SECOND(9),
    col2 INTERVAL DAY(1) TO SECOND(9)
);
-- 转换前Oracle SQL:
INSERT INTO unisql_interval_ds_table(col1,col2)
VALUES
(
    INTERVAL '1 12:30:15.123456789' DAY(2) TO SECOND(9),
    INTERVAL '-4 05:45:30.987654' DAY TO SECOND(6)
);
SELECT col1,col2 FROM unisql_interval_ds_table;
COL1                |COL2             |
--------------------+-----------------+
1 12:30:15.123456789|-4 5:45:30.987654|

-- 转换后TDSQL-Oracle模式 SQL:
INSERT INTO unisql_interval_ds_table (col1,col2) VALUES (INTERVAL '1 12:30:15.123456789' DAY TO SECOND(6),INTERVAL '-4 05:45:30.987654' DAY TO SECOND(6));
SELECT col1,col2 FROM unisql_interval_ds_table;
col1                 |col2                    |
---------------------+------------------------+
1 day 12:30:15.123457|-4 days +05:45:30.987654|

警告

下面列举的是一些在使用间隔字面量过程中会遇到的部分疑问点，在使用数据库时，如果对数据完全一致要求较高，现有的函数处理能力不能满足业务需求，建议进行业务SQL改写或者对结果进行进一步处理

• 目标数据库对显示间隔字面量与源库Oracle不一致，但是语义保持一致，不考虑时区、数据库设置的影响；

• 精度、时间格式暂时不做范围限制，具体是否错误由执行层保证；

• 因为需求原因，时间间隔字面量只限制在插入表中值去使用；

• Oracle的INTERVAL DAY TO SECOND字面量的SECOND 元素小数部分的精度，默认值为 6，取值范围为 [0,9]，但是目标库相关SECOND 元素小数部分的精度的最大值为6，所以目标数据库字面量值超过该精度会自动进行处理后截断，出现与源数据库数据不一致的情况。

11.3.5. DML

11.3.5.1. 插入单条记录

指定所有字段

-- 创建表
drop table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders(order_,customer_,order_total,sales_rep_id,credit_limit,cust_email) VALUES(1,1,1,'a1',1,1);

-- 转换后TDSQL-Oracle模式 SQL:
INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id,credit_limit,cust_email) VALUES (1,1,1,'a1',1,1)

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

指定某些字段，不指的有默认值的插入时带上默认值。

-- 创建表
drop table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders(sales_rep_id) VALUES('a1');

-- 转换后TDSQL-Oracle模式 SQL:
INSERT INTO unisql_special_orders (sales_rep_id) VALUES ('a1')

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

不指定字段，则默认为所有字段与建表时一致。

-- 创建表
drop table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders VALUES(3,1,1,'a1',1,1);

-- 转换后TDSQL-Oracle模式 SQL:
INSERT INTO unisql_special_orders VALUES (3,1,1,'a1',1,1)

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

11.3.5.2. 从另外一个表取数据进行批量插入

-- 创建表
drop table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 插入数据
INSERT INTO unisql_special_orders(order_,customer_,order_total,sales_rep_id,credit_limit,cust_email) VALUES(1,1,1,'a1',1,1);
INSERT INTO unisql_special_orders(sales_rep_id) VALUES('a1');
INSERT INTO unisql_special_orders VALUES(3,1,1,'a1',1,1);

-- 从另外一个表读取数据进行数据插入
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders SELECT order_,customer_,order_total,sales_rep_id,credit_limit,cust_email FROM unisql_special_orders;

-- 转换后TDSQL-Oracle模式 SQL:
INSERT INTO unisql_special_orders SELECT order_,customer_,order_total,sales_rep_id,credit_limit,cust_email FROM unisql_special_orders

-- 查询表数据
SELECT * FROM unisql_special_orders;
order_|customer_|order_total|sales_rep_id|credit_limit|cust_email|
------+---------+-----------+------------+------------+----------+
    1|        1|          1|a1          |           1|         1|
    0|         |           |a1          |            |          |
    3|        1|          1|a1          |           1|         1|
    1|        1|          1|a1          |           1|         1|
    0|         |           |a1          |            |          |
    3|        1|          1|a1          |           1|         1|

11.3.5.3. 批量插入

-- 创建表
drop table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 转换前Oracle SQL:
INSERT ALL
INTO unisql_special_orders VALUES(1,1,1,'a1',1,1)
INTO unisql_special_orders VALUES(2,2,2,'a2',2,2)
INTO unisql_special_orders VALUES(3,3,3,'a3',3,3)
INTO unisql_special_orders VALUES(4,4,4,'a4',4,4)
INTO unisql_special_orders VALUES(5,5,5,'a5',5,5)
SELECT * FROM dual;

-- 转换后TDSQL-Oracle模式 SQL:
WITH tmp AS (SELECT 'X' AS DUMMY),
unisql_special_orders_tmp_0 AS (INSERT INTO unisql_special_orders SELECT 1,1,1,'a1',1,1 FROM tmp),
unisql_special_orders_tmp_1 AS (INSERT INTO unisql_special_orders SELECT 2,2,2,'a2',2,2 FROM tmp),
unisql_special_orders_tmp_2 AS (INSERT INTO unisql_special_orders SELECT 3,3,3,'a3',3,3 FROM tmp),
unisql_special_orders_tmp_3 AS (INSERT INTO unisql_special_orders SELECT 4,4,4,'a4',4,4 FROM tmp),
unisql_special_orders_tmp_4 AS (INSERT INTO unisql_special_orders SELECT 5,5,5,'a5',5,5 FROM tmp) SELECT 1


-- 查询表数据
SELECT * FROM unisql_special_orders;
order_|customer_|order_total|sales_rep_id|credit_limit|cust_email|
------+---------+-----------+------------+------------+----------+
    5|        5|          5|a5          |           5|         5|
    4|        4|          4|a4          |           4|         4|
    3|        3|          3|a3          |           3|         3|
    2|        2|          2|a2          |           2|         2|
    1|        1|          1|a1          |           1|         1|

11.3.5.4. 多表插入

CREATE TABLE unisql_multi_table_small_orders (
order_id INT,
customer_id INT,
order_total INT,
sales_rep_id INT
);

CREATE TABLE unisql_multi_table_medium_orders (
    order_id INT,
    customer_id INT,
    order_total INT,
    sales_rep_id INT
);

CREATE TABLE unisql_multi_table_large_orders (
    order_id INT,
    customer_id INT,
    order_total INT,
    sales_rep_id INT
);


-- 转换前Oracle SQL:
INSERT ALL
-- 插入到 unisql_multi_table_small_orders 表中
INTO unisql_multi_table_small_orders (order_id,customer_id,order_total,sales_rep_id )
    VALUES((order_id + customer_id + 1)*2,customer_id,order_total+1,credit_limit)
-- 插入到 unisql_multi_table_medium_orders 表中
INTO unisql_multi_table_medium_orders(order_id,customer_id,order_total,sales_rep_id )
    VALUES(order_id,customer_id,(order_total + customer_id + 1)*2+2,credit_limit)
-- 插入到 unisql_multi_table_large_orders 表中
INTO unisql_multi_table_large_orders(order_id,customer_id,order_total,sales_rep_id )
    VALUES(order_id,customer_id,order_total+3,credit_limit)
SELECT (order_ +1 )*2 order_id ,(customer_ +1 )*2 customer_id,order_total,credit_limit FROM unisql_special_orders;

-- 转换后TDSQL-Oracle模式 SQL:
WITH tmp AS (SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders),
unisql_multi_table_small_orders_tmp_0 AS (INSERT INTO unisql_multi_table_small_orders (order_id,customer_id,order_total,sales_rep_id) SELECT (order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit FROM tmp),
unisql_multi_table_medium_orders_tmp_1 AS (INSERT INTO unisql_multi_table_medium_orders (order_id,customer_id,order_total,sales_rep_id) SELECT order_id,customer_id,(order_total+customer_id+1)*2+2,credit_limit FROM tmp),
unisql_multi_table_large_orders_tmp_2 AS (INSERT INTO unisql_multi_table_large_orders (order_id,customer_id,order_total,sales_rep_id) SELECT order_id,customer_id,order_total+3,credit_limit FROM tmp) SELECT 1


-- 转换前Oracle SQL:
INSERT ALL
WHEN order_id = 4 THEN
    INTO unisql_multi_table_small_orders VALUES((order_id + customer_id + 1)*2,customer_id,order_total+1,credit_limit)
WHEN order_id = 6 THEN
    INTO unisql_multi_table_medium_orders VALUES(order_id,customer_id,(order_total + customer_id + 1)*2+2,credit_limit)
ELSE
    INTO unisql_multi_table_large_orders VALUES(order_id,customer_id,order_total+3,credit_limit)
SELECT (order_ +1 )*2 order_id ,(customer_ +1 )*2 customer_id,order_total,credit_limit FROM unisql_special_orders;

-- 转换后TDSQL-Oracle模式 SQL:
WITH tmp AS (SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders),
unisql_multi_table_small_orders_tmp_0_0 AS (INSERT INTO unisql_multi_table_small_orders SELECT (order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit FROM tmp WHERE order_id=4),
unisql_multi_table_medium_orders_tmp_1_0 AS (INSERT INTO unisql_multi_table_medium_orders SELECT order_id,customer_id,(order_total+customer_id+1)*2+2,credit_limit FROM tmp WHERE order_id=6),
unisql_multi_table_large_orders_tmp_2_0 AS (INSERT INTO unisql_multi_table_large_orders SELECT order_id,customer_id,order_total+3,credit_limit FROM (SELECT * FROM tmp EXCEPT ALL (SELECT * FROM tmp WHERE order_id=4 UNION ALL SELECT * FROM tmp WHERE order_id=6)) AS tmp_except_0) SELECT 1


-- 转换前Oracle SQL:
INSERT ALL
WHEN order_id = 4 and order_id in (select order_id FROM unisql_multi_table_small_orders WHERE order_id>1) THEN
    INTO unisql_multi_table_small_orders VALUES((order_id + customer_id + 1)*2,customer_id,order_total+1,credit_limit)
ELSE
    INTO unisql_multi_table_large_orders VALUES(order_id,customer_id,order_total+3,credit_limit)
SELECT (order_ +1 )*2 order_id ,(customer_ +1 )*2 customer_id,order_total,credit_limit FROM unisql_special_orders;

-- 转换后TDSQL-Oracle模式 SQL:
WITH tmp AS (SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders),
unisql_multi_table_small_orders_tmp_0_0 AS (INSERT INTO unisql_multi_table_small_orders SELECT (order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit FROM tmp WHERE order_id=4 AND order_id IN (SELECT order_id FROM unisql_multi_table_small_orders WHERE order_id>1)),
unisql_multi_table_large_orders_tmp_1_0 AS (INSERT INTO unisql_multi_table_large_orders SELECT order_id,customer_id,order_total+3,credit_limit FROM (SELECT * FROM tmp EXCEPT ALL (SELECT * FROM tmp WHERE order_id=4 AND order_id IN (SELECT order_id FROM unisql_multi_table_small_orders WHERE order_id>1))) AS tmp_except_0) SELECT 1

11.3.5.5. WITH临时表查询插入

-- 创建表
drop table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 插入数据
INSERT INTO unisql_special_orders VALUES(1,1,1,'a1',1,1);
INSERT INTO unisql_special_orders VALUES(2,2,2,'a2',2,2);
INSERT INTO unisql_special_orders VALUES(3,3,3,'a3',3,3);

-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id)
WITH tmp AS (SELECT order_,customer_,order_total,sales_rep_id FROM unisql_special_orders)
SELECT t.order_,t.customer_,y.order_total,y.sales_rep_id FROM unisql_special_orders t LEFT JOIN tmp y ON t.order_ = y.order_;

-- 转换后TDSQL-Oracle模式 SQL:
INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id)
WITH tmp AS (SELECT order_,customer_,order_total,sales_rep_id FROM unisql_special_orders)
SELECT t.order_,t.customer_,y.order_total,y.sales_rep_id FROM unisql_special_orders AS t LEFT JOIN tmp AS y ON t.order_=y.order_

-- 查询表数据
SELECT * FROM unisql_special_orders;
ORDER_|CUSTOMER_|ORDER_TOTAL|SALES_REP_ID|CREDIT_LIMIT|CUST_EMAIL|
------+---------+-----------+------------+------------+----------+
    1|        1|          1|a1          |           1|         1|
    2|        2|          2|a2          |           2|         2|
    3|        3|          3|a3          |           3|         3|
    1|        1|          1|a1          |            |          |
    2|        2|          2|a2          |            |          |
    3|        3|          3|a3          |            |          |

11.3.5.6. 合并插入

警告

merge into的on条件中不能有绑定变量占位，涉及到占位相关的条件写到外层

DROP TABLE unisql_people_source;
CREATE TABLE unisql_people_source (
person_id  INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name  VARCHAR2(20) NOT NULL,
title      VARCHAR2(10) NOT NULL
);

DROP TABLE unisql_people_target;
CREATE TABLE unisql_people_target (
person_id  INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name  VARCHAR2(20) NOT NULL,
title      VARCHAR2(10) NOT NULL
);

INSERT INTO unisql_people_target VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO unisql_people_target VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO unisql_people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO unisql_people_source VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO unisql_people_source VALUES (4, 'Dave', 'Brown', 'Mr');

-- 转换前Oracle SQL:
MERGE INTO unisql_people_target pt
USING unisql_people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN
UPDATE SET pt.first_name = ps.first_name,
    pt.last_name = ps.last_name,
    pt.title = ps.title
WHEN NOT MATCHED THEN
INSERT (pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);

-- 转换后TDSQL-Oracle模式 SQL:
WITH ps AS (SELECT * FROM unisql_people_source),
upsert AS (UPDATE unisql_people_target AS pt SET first_name=ps.first_name, last_name=ps.last_name, title=ps.title FROM unisql_people_source AS ps WHERE pt.person_id=ps.person_id RETURNING pt.*)
INSERT INTO unisql_people_target AS pt (person_id,first_name,last_name,title) SELECT ps.person_id,ps.first_name,ps.last_name,ps.title FROM unisql_people_source AS ps WHERE NOT EXISTS (SELECT 1 FROM upsert AS pt WHERE pt.person_id=ps.person_id)

-- 查询表数据
SELECT * FROM unisql_people_target;
PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
        1|John      |Smith    |Mr   |
        2|Alice     |Jones    |Mrs. |
        3|Jane      |Doe      |Miss |
        4|Dave      |Brown    |Mr   |

11.3.6. DDL

DDL（Data Definition Language，数据定义语言）语句用来创建、修改和删除数据库对象。

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

11.3.6.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;

11.3.6.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;

11.3.6.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;

11.3.6.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