4.2.3.4. Tdsql-Oracle

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

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

4.2.3.4.3. 复合查询

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

4.2.3.4.4. 联接查询

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

4.2.3.4.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|戴尔  |

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

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

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