3.3. SQL 语法
3.3.1. 查询和子查询
查询是指数据库中用来获取数据的方式，它可搭配条件限制子句（例如 WHERE），排列顺序子句（例如 ORDER BY）等语句来获取查询结果。
子查询是指嵌套在一个上层查询中的查询。上层的查询一般被称为父查询或外部查询。子查询的结果作为输入传递回父查询或外部查询。父查询将这个值结合到计算中，以便确定最后的输出。
SQL 语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。同时，子查询可以出现在 SQL 语句中的各种子句中，例如 SELECT 语句、FROM 语句和 WHERE 语句等。
SQL 语句中常见的查询类型如下：
3.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;
-- 转换后PostgreSQL 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
3.3.1.1.1. 分区表查询
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('2024-01-01', 'YYYY-MM-DD')),
PARTITION unisql_partition_p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (1, 'John', 5000, TO_DATE('2023-01-01','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (2, 'Mary', 6000, TO_DATE( '2023-02-15','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (3, 'Tom', 7000, TO_DATE( '2023-03-20','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (4, 'Alice', 8000, TO_DATE('2023-04-10','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (5, 'Bob', 9000, TO_DATE('2023-05-01','YYYY-MM-DD'));
-- 转换前Oracle SQL：
SELECT unisql_id,unisql_name,unisql_salary, unisql_hire_date FROM unisql_partition PARTITION (unisql_partition_p1);
UNISQL_ID|UNISQL_NAME|UNISQL_SALARY|UNISQL_HIRE_DATE |
---------+-----------+-------------+-----------------------+
1|John | 5000|2023-01-01 00:00:00.000|
2|Mary | 6000|2023-02-15 00:00:00.000|
3|Tom | 7000|2023-03-20 00:00:00.000|
4|Alice | 8000|2023-04-10 00:00:00.000|
5|Bob | 9000|2023-05-01 00:00:00.000|
-- 转换后PostgreSQL SQL：
SELECT unisql_id,unisql_name,unisql_salary,unisql_hire_date FROM unisql_partition_1_prt_unisql_partition_p1
unisql_id|unisql_name|unisql_salary|unisql_hire_date |
---------+-----------+-------------+-----------------------+
1|John | 5000.00|2023-01-01 00:00:00.000|
2|Mary | 6000.00|2023-02-15 00:00:00.000|
3|Tom | 7000.00|2023-03-20 00:00:00.000|
4|Alice | 8000.00|2023-04-10 00:00:00.000|
5|Bob | 9000.00|2023-05-01 00:00:00.000|
create table unisql_partition_by_list_test
(
id varchar2(15) not null,
city varchar2(20),
city_number NUMBER(10,2)
)
partition by list (city)(
partition "P1" values ('beijing'),
partition p2 values ('shanghai'),
partition p3 values ('changsha'),
partition p4 values (default)
);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (1, 'beijing', 5000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (2, 'beijing', 9000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (3, 'beijing', 2000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (4, 'shanghai', 3000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (5, 'hangzhou', 6000);
-- 转换前Oracle SQL：
SELECT id, city, city_number FROM unisql_partition_by_list_test PARTITION ("P1");
ID|CITY |CITY_NUMBER|
--+-------+-----------+
1 |beijing| 5000|
2 |beijing| 9000|
3 |beijing| 2000|
-- 转换后PostgreSQL SQL：
SELECT id, city,city_number FROM "unisql_partition_by_list_test_1_prt_P1"
id|city |city_number|
--+-------+-----------+
1 |beijing| 5000.00|
2 |beijing| 9000.00|
3 |beijing| 2000.00|
3.3.1.1.2. pivot行转列
CREATE TABLE unisql_pivot(id int ,name varchar(64),score NUMBER ,subject varchar2(64));
INSERT INTO unisql_pivot VALUES(1,'张三',70,'CHINESE');
INSERT INTO unisql_pivot VALUES(1,'张三',90,'MATH');
INSERT INTO unisql_pivot VALUES(1,'张三',95,'ENGLISH');
INSERT INTO unisql_pivot VALUES(2,'李四',75,'CHINESE');
INSERT INTO unisql_pivot VALUES(2,'李四',85,'MATH');
INSERT INTO unisql_pivot VALUES(2,'李四',90,'ENGLISH');
INSERT INTO unisql_pivot VALUES(3,'王五',90,'CHINESE');
INSERT INTO unisql_pivot VALUES(3,'王五',90,'MATH');
INSERT INTO unisql_pivot VALUES(3,'王五',90,'ENGLISH');
-- 转换前Oracle SQL：
SELECT id,name,chinese_score,match_score,english_score
FROM unisql_pivot
pivot(
min(score) FOR subject IN('CHINESE' AS chinese_score,'MATH' AS match_score,'ENGLISH' AS english_score)
)
WHERE id IN (1, 2, 3) ORDER BY id;
ID|NAME|CHINESE_SCORE|MATCH_SCORE|ENGLISH_SCORE|
--+----+-------------+-----------+-------------+
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
3|王五 | 90| 90| 90|
-- 转换后PostgreSQL SQL：
SELECT id,name,min(score) FILTER (WHERE subject='CHINESE') AS chinese_score,min(score) FILTER (WHERE subject='MATH') AS match_score,min(score) FILTER (WHERE subject='ENGLISH') AS english_score FROM unisql_pivot WHERE id IN (1,2,3) GROUP BY id,name ORDER BY id
id|name|chinese_score|match_score|english_score|
--+----+-------------+-----------+-------------+
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
3|王五 | 90| 90| 90|
3.3.1.1.3. unpivot列转行
-- 建表语句
CREATE TABLE unisql_unpivot(id INT,name VARCHAR(64),chinese NUMBER,math INT,english INT);
INSERT INTO unisql_unpivot VALUES(1,'张三',70,90,95);
INSERT INTO unisql_unpivot VALUES(2,'李四',75,85,90);
INSERT INTO unisql_unpivot VALUES(3,'张三',90,90,90);
-- 转换前Oracle SQL:
SELECT
id,
name,
score ,
subject
FROM
unisql_unpivot UNPIVOT(score FOR subject IN(chinese, math, english))
WHERE
id IN (1, 2, 3) ORDER BY id;
ID|NAME|SCORE|SUBJECT|
--+----+-----+-------+
1|张三 | 70|CHINESE|
1|张三 | 90|MATH |
1|张三 | 95|ENGLISH|
2|李四 | 75|CHINESE|
2|李四 | 85|MATH |
2|李四 | 90|ENGLISH|
3|张三 | 90|CHINESE|
3|张三 | 90|MATH |
3|张三 | 90|ENGLISH|
-- 转换后PostgreSQL SQL:
SELECT id,name,score,subject FROM unisql_unpivot, LATERAL(VALUES(chinese, 'CHINESE'), (math, 'MATH'), (english, 'ENGLISH')) AS un_pivot(score, subject) WHERE id IN (1,2,3) ORDER BY id
id|name|score|subject|
--+----+-----+-------+
1|张三 | 70|CHINESE|
1|张三 | 90|MATH |
1|张三 | 95|ENGLISH|
2|李四 | 75|CHINESE|
2|李四 | 85|MATH |
2|李四 | 90|ENGLISH|
3|张三 | 90|CHINESE|
3|张三 | 90|MATH |
3|张三 | 90|ENGLISH|
3.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 ... ]
警告
层次查询特有表达式不支持嵌套使用：如: sys_connect_by_root(prior column)、LTRIM(MAX(SYS_CONNECT_BY_PATH(column, ‘;’)), ‘;’)
不支持和oracle(+)一起使用
不支持和pivot, unpivot函数一起使用
列名不支持带schema名和数据库名，只支持表名和字段名。
不支持和with cte一起使用
多表不支持*，需要列明字段；单表用*时，不能再查表中相同字段，如test中有id字段，不能用test.*,test.id，test.id
不支持函数
level、CONNECT_BY_ROOT、SYS_CONNECT_BY_PATH 中不支持表达式和函数，只支持字段
不支持rownum
3.3.1.2.1. connect by
DROP TABLE unisql_connect_by_emp;
CREATE TABLE unisql_connect_by_emp (empno int NOT NULL,ename VARCHAR2(10),job VARCHAR2(9),mgr int,sal int,comm int,deptno int);
INSERT INTO unisql_connect_by_emp VALUES (7369,'SMITH','CLERK',7902,800,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7499,'ALLEN','SALESMAN',7698,1600,300,30);
INSERT INTO unisql_connect_by_emp VALUES (7521,'WARD','SALESMAN',7698,1250,500,30);
INSERT INTO unisql_connect_by_emp VALUES (7566,'JONES','MANAGER',7839,2975,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7654,'MARTIN','SALESMAN',7698,1250,1400,30);
INSERT INTO unisql_connect_by_emp VALUES (7698,'BLAKE','MANAGER',7839,2850,NULL,30);
INSERT INTO unisql_connect_by_emp VALUES (7782,'CLARK','MANAGER',7839,2450,NULL,10);
INSERT INTO unisql_connect_by_emp VALUES (7788,'SCOTT','ANALYST',7566,3000,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7839,'KING','PRESIDENT',NULL,5000,NULL,30);
INSERT INTO unisql_connect_by_emp VALUES (7844,'TURNER','SALESMAN',7698,1500,0,30);
INSERT INTO unisql_connect_by_emp VALUES (7876,'ADAMS','CLERK',7788,1100,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7900,'JAMES','CLERK',7698,950,NULL,30);
INSERT INTO unisql_connect_by_emp VALUES (7902,'FORD','ANALYST',7566,3000,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7934,'MILLER','CLERK',7782,1300,NULL,10);
-- 转换前Oracle SQL:
-- 单表
SELECT LEVEL, empno, ename, mgr, sal,connect_by_root(DEPTNO) root,
SYS_CONNECT_BY_PATH(LEVEL,'/') FROM unisql_connect_by_emp START WITH ename = 'BLAKE' CONNECT BY PRIOR empno = mgr ;
LEVEL|EMPNO|ENAME |MGR |SAL |ROOT|SYS_CONNECT_BY_PATH(LEVEL,'/')|
-----+-----+------+----+----+----+------------------------------+
1| 7698|BLAKE |7839|2850| 30|/1 |
2| 7499|ALLEN |7698|1600| 30|/1/2 |
2| 7521|WARD |7698|1250| 30|/1/2 |
2| 7654|MARTIN|7698|1250| 30|/1/2 |
2| 7844|TURNER|7698|1500| 30|/1/2 |
2| 7900|JAMES |7698| 950| 30|/1/2 |
-- 转换后PostgreSQL SQL:
WITH RECURSIVE tmp AS (SELECT 1 AS "$level",empno AS "$empno",ename AS "$ename",mgr AS "$mgr",sal AS "$sal",DEPTNO AS root,concat('/', 1) AS sys_connect_by_path_level,NULL AS "prior$empno" FROM unisql_connect_by_emp WHERE ename='BLAKE' UNION ALL SELECT "$level"+1 AS "$level",unisql_connect_by_emp.empno AS "$empno",unisql_connect_by_emp.ename AS "$ename",unisql_connect_by_emp.mgr AS "$mgr",unisql_connect_by_emp.sal AS "$sal",root AS root,concat(concat(sys_connect_by_path_level, '/'), "$level"+1) AS sys_connect_by_path_level,CAST("$empno" AS text) AS "prior$empno" FROM unisql_connect_by_emp , tmp WHERE tmp."$empno"=unisql_connect_by_emp.mgr) SELECT tmp."$level" AS LEVEL,tmp."$empno" AS empno,tmp."$ename" AS ename,tmp."$mgr" AS mgr,tmp."$sal" AS sal,tmp.root AS root,tmp.sys_connect_by_path_level AS sys_connect_by_path_level FROM tmp
level|empno|ename |mgr |sal |root|sys_connect_by_path_level|
-----+-----+------+----+----+----+-------------------------+
1| 7698|BLAKE |7839|2850| 30|/1 |
2| 7499|ALLEN |7698|1600| 30|/1/2 |
2| 7521|WARD |7698|1250| 30|/1/2 |
2| 7654|MARTIN|7698|1250| 30|/1/2 |
2| 7844|TURNER|7698|1500| 30|/1/2 |
2| 7900|JAMES |7698| 950| 30|/1/2 |
-- 转换前Oracle SQL:
-- 表关联
select
emp_.empno,
emp_.ename,
emp_.job as jobnew,
emp_.mgr,
emp_.sal,
emp_.comm,
LEVEL,
connect_by_root(emp_.DEPTNO) AS root,
SYS_CONNECT_BY_PATH(emp_.deptno,'/') path,
SYS_CONNECT_BY_PATH(level,'/')
from unisql_connect_by_emp emp_
left join unisql_connect_by_emp emp2 on emp_.empno=emp2.empno
start with emp_.mgr is null connect by PRIOR emp_.empno = emp_.mgr;
EMPNO|ENAME |JOBNEW |MGR |SAL |COMM|LEVEL|ROOT|PATH |SYS_CONNECT_BY_PATH(LEVEL,'/')|
-----+------+---------+----+----+----+-----+----+------------+------------------------------+
7839|KING |PRESIDENT| |5000| | 1| 30|/30 |/1 |
7566|JONES |MANAGER |7839|2975| | 2| 30|/30/20 |/1/2 |
7788|SCOTT |ANALYST |7566|3000| | 3| 30|/30/20/20 |/1/2/3 |
7876|ADAMS |CLERK |7788|1100| | 4| 30|/30/20/20/20|/1/2/3/4 |
7902|FORD |ANALYST |7566|3000| | 3| 30|/30/20/20 |/1/2/3 |
7369|SMITH |CLERK |7902| 800| | 4| 30|/30/20/20/20|/1/2/3/4 |
7698|BLAKE |MANAGER |7839|2850| | 2| 30|/30/30 |/1/2 |
7499|ALLEN |SALESMAN |7698|1600| 300| 3| 30|/30/30/30 |/1/2/3 |
7521|WARD |SALESMAN |7698|1250| 500| 3| 30|/30/30/30 |/1/2/3 |
7654|MARTIN|SALESMAN |7698|1250|1400| 3| 30|/30/30/30 |/1/2/3 |
7844|TURNER|SALESMAN |7698|1500| 0| 3| 30|/30/30/30 |/1/2/3 |
7900|JAMES |CLERK |7698| 950| | 3| 30|/30/30/30 |/1/2/3 |
7782|CLARK |MANAGER |7839|2450| | 2| 30|/30/10 |/1/2 |
7934|MILLER|CLERK |7782|1300| | 3| 30|/30/10/10 |/1/2/3 |
-- 转换后PostgreSQL SQL:
WITH RECURSIVE tmp AS (SELECT emp_.empno AS "emp_$empno",emp_.ename AS "emp_$ename",emp_.job AS jobnew,emp_.mgr AS "emp_$mgr",emp_.sal AS "emp_$sal",emp_.comm AS "emp_$comm",1 AS "$level",emp_.DEPTNO AS root,concat('/', emp_.deptno) AS path,concat('/', 1) AS sys_connect_by_path_level,NULL AS "prioremp_$empno" FROM unisql_connect_by_emp AS emp_ LEFT JOIN unisql_connect_by_emp AS emp2 ON emp_.empno=emp2.empno WHERE emp_.mgr IS NULL UNION ALL SELECT tmp_tmp."emp_$empno" AS "emp_$empno",tmp_tmp."emp_$ename" AS "emp_$ename",tmp_tmp.jobnew AS jobnew,tmp_tmp."emp_$mgr" AS "emp_$mgr",tmp_tmp."emp_$sal" AS "emp_$sal",tmp_tmp."emp_$comm" AS "emp_$comm","$level"+1 AS "$level",root AS root,concat(concat(path, '/'), tmp_tmp."emp_$deptno") AS path,concat(concat(sys_connect_by_path_level, '/'), "$level"+1) AS sys_connect_by_path_level,CAST(tmp_tmp."emp_$empno" AS text) AS "prioremp_$empno" FROM (SELECT emp_.empno AS "emp_$empno",emp_.ename AS "emp_$ename",emp_.job AS jobnew,emp_.mgr AS "emp_$mgr",emp_.sal AS "emp_$sal",emp_.comm AS "emp_$comm",emp_.deptno AS "emp_$deptno",emp_.empno AS "prioremp_$empno" FROM unisql_connect_by_emp AS emp_ LEFT JOIN unisql_connect_by_emp AS emp2 ON emp_.empno=emp2.empno) AS tmp_tmp , tmp WHERE tmp."emp_$empno"=tmp_tmp."emp_$mgr") SELECT tmp."emp_$empno" AS empno,tmp."emp_$ename" AS ename,tmp.jobnew AS jobnew,tmp."emp_$mgr" AS mgr,tmp."emp_$sal" AS sal,tmp."emp_$comm" AS comm,tmp."$level" AS LEVEL,tmp.root AS root,tmp.path AS path,tmp.sys_connect_by_path_level AS sys_connect_by_path_level FROM tmp
empno|ename |jobnew |mgr |sal |comm|level|root|path |sys_connect_by_path_level|
-----+------+---------+----+----+----+-----+----+------------+-------------------------+
7839|KING |PRESIDENT| |5000| | 1| 30|/30 |/1 |
7566|JONES |MANAGER |7839|2975| | 2| 30|/30/20 |/1/2 |
7698|BLAKE |MANAGER |7839|2850| | 2| 30|/30/30 |/1/2 |
7782|CLARK |MANAGER |7839|2450| | 2| 30|/30/10 |/1/2 |
7499|ALLEN |SALESMAN |7698|1600| 300| 3| 30|/30/30/30 |/1/2/3 |
7521|WARD |SALESMAN |7698|1250| 500| 3| 30|/30/30/30 |/1/2/3 |
7654|MARTIN|SALESMAN |7698|1250|1400| 3| 30|/30/30/30 |/1/2/3 |
7788|SCOTT |ANALYST |7566|3000| | 3| 30|/30/20/20 |/1/2/3 |
7844|TURNER|SALESMAN |7698|1500| 0| 3| 30|/30/30/30 |/1/2/3 |
7900|JAMES |CLERK |7698| 950| | 3| 30|/30/30/30 |/1/2/3 |
7902|FORD |ANALYST |7566|3000| | 3| 30|/30/20/20 |/1/2/3 |
7934|MILLER|CLERK |7782|1300| | 3| 30|/30/10/10 |/1/2/3 |
7369|SMITH |CLERK |7902| 800| | 4| 30|/30/20/20/20|/1/2/3/4 |
7876|ADAMS |CLERK |7788|1100| | 4| 30|/30/20/20/20|/1/2/3/4 |
-- 转换前Oracle SQL:
-- 构造列数
select level from dual connect by level<=10;
LEVEL|
-----+
1|
2|
3|
4|
5|
6|
7|
8|
9|
10|
-- 转换后PostgreSQL SQL:
WITH RECURSIVE tmp AS (SELECT 1 AS "$level" UNION ALL SELECT "$level"+1 AS "$level" FROM tmp WHERE "$level"+1<=10) SELECT tmp."$level" AS level FROM tmp
level|
-----+
1|
2|
3|
4|
5|
6|
7|
8|
9|
10|
3.3.1.3. 复合查询
使用集合运算符 UNION、UNION ALL、INTERSECT 和 MINUS 来组合多个查询，即复合查询。所有集合运算符都具有相同的优先级。
3.3.1.3.1. UNION 唯一并集
{ (< SQL- 查询语句 1>) }
UNION
{ (< SQL- 查询语句 2>) }
CREATE TABLE unisql_collect_1(
id INT,
name VARCHAR(32),
chinese NUMBER,
math NUMBER,
english NUMBER);
CREATE TABLE unisql_collect_2(
id INT,
name VARCHAR(32),
chinese NUMBER,
math NUMBER,
english NUMBER);
INSERT INTO unisql_collect_1 VALUES(1,'张三',70,90,95);
INSERT INTO unisql_collect_1 VALUES(2,'李四',75,85,90);
INSERT INTO unisql_collect_1 VALUES(3,'王五',90,90,90);
INSERT INTO unisql_collect_1 VALUES(4,'秦六',65,65,60);
INSERT INTO unisql_collect_2 VALUES(1,'张三',70,90,95);
INSERT INTO unisql_collect_2 VALUES(2,'李四',75,85,90);
INSERT INTO unisql_collect_2 VALUES(4,'Bob',78,88,65);
INSERT INTO unisql_collect_2 VALUES(5,'Li',90,93,93);
-- 转换前Oracle SQL:
SELECT * FROM unisql_collect_1
UNION
SELECT * FROM unisql_collect_2;
ID|NAME|CHINESE|MATH|ENGLISH|
--+----+-------+----+-------+
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
3|王五 | 90| 90| 90|
4|Bob | 78| 88| 65|
4|秦六 | 65| 65| 60|
5|Li | 90| 93| 93|
-- 转换后PostgreSQL SQL:
SELECT * FROM unisql_collect_1 UNION SELECT * FROM unisql_collect_2
id|name|chinese|math|english|
--+----+-------+----+-------+
4|Bob | 78| 88| 65|
5|Li | 90| 93| 93|
4|秦六 | 65| 65| 60|
3|王五 | 90| 90| 90|
2|李四 | 75| 85| 90|
1|张三 | 70| 90| 95|
3.3.1.3.2. UNION ALL 并集
{ (< SQL- 查询语句 1>) }
UNION ALL
{ (< SQL- 查询语句 2>) }
-- 转换前Oracle SQL:
SELECT * FROM unisql_collect_1
UNION ALL
SELECT * FROM unisql_collect_2;
ID|NAME|CHINESE|MATH|ENGLISH|
--+----+-------+----+-------+
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
3|王五 | 90| 90| 90|
4|秦六 | 65| 65| 60|
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
4|Bob | 78| 88| 65|
5|Li | 90| 93| 93|
-- 转换后PostgreSQL SQL:
SELECT * FROM unisql_collect_1 UNION ALL SELECT * FROM unisql_collect_2
id|name|chinese|math|english|
--+----+-------+----+-------+
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
3|王五 | 90| 90| 90|
4|秦六 | 65| 65| 60|
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
4|Bob | 78| 88| 65|
5|Li | 90| 93| 93|
3.3.1.3.3. INTERSECT 交集
{ (< SQL- 查询语句 1>) }
INTERSECT
{ (< SQL- 查询语句 2>) }
-- 转换前Oracle SQL:
SELECT * FROM unisql_collect_1
INTERSECT
SELECT * FROM unisql_collect_2;
ID|NAME|CHINESE|MATH|ENGLISH|
--+----+-------+----+-------+
1|张三 | 70| 90| 95|
2|李四 | 75| 85| 90|
-- 转换后PostgreSQL SQL:
SELECT * FROM unisql_collect_1 INTERSECT SELECT * FROM unisql_collect_2
id|name|chinese|math|english|
--+----+-------+----+-------+
2|李四 | 75| 85| 90|
1|张三 | 70| 90| 95|
3.3.1.3.4. MINUS 差集
{ (< SQL- 查询语句 1>) }
MINUS
{ (< SQL- 查询语句 2>) }
-- 转换前Oracle SQL:
SELECT * FROM unisql_collect_1
MINUS
SELECT * FROM unisql_collect_2;
ID|NAME|CHINESE|MATH|ENGLISH|
--+----+-------+----+-------+
3|王五 | 90| 90| 90|
4|秦六 | 65| 65| 60|
-- 转换后PostgreSQL SQL:
SELECT * FROM unisql_collect_1 EXCEPT SELECT * FROM unisql_collect_2
id|name|chinese|math|english|
--+----+-------+----+-------+
4|秦六 | 65| 65| 60|
3|王五 | 90| 90| 90|
3.3.1.4. 联接查询
联接（Join）是将两个或多个表、视图的结合在一起的查询。
3.3.1.4.1. 等值联接
等值联接是包含等值运算符的联接。在进行等值联接时将指定列满足等值条件的行进行组合输出。
示例
-- 创建 unisql_orders 表
CREATE TABLE unisql_orders (
order_id NUMBER,
order_date DATE,
customer_id NUMBER,
amount NUMBER
);
-- 创建 unisql_customers 表
CREATE TABLE unisql_customers (
customer_id NUMBER,
customer_name VARCHAR2(100),
city VARCHAR2(100)
);
-- 插入示例数据
INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (1, TO_DATE('2022-01-01', 'YYYY-MM-DD'), 1001, 1000);
INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (2, TO_DATE('2022-02-01', 'YYYY-MM-DD'), 1002, 1500);
INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1001, 'John Smith', 'New York');
INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1002, 'Jane Doe', 'Los Angeles');
-- 转换前Oracle SQL:
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT|
--------+-----------------------+-------------+-----------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
-- 转换后PostgreSQL SQL:
SELECT o.order_id,o.order_date,c.customer_name,c.city,o.amount FROM unisql_orders AS o JOIN unisql_customers AS c ON o.customer_id=c.customer_id
order_id|order_date |customer_name|city |amount|
--------+-----------------------+-------------+-----------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
3.3.1.4.2. 自联接
自联接是表与其自身的联接。该表在 FROM 子句中出现两次，后跟表别名，这些别名限定联接条件中的列名。
示例
-- 创建 employees 表
CREATE TABLE unisql_employees (
employee_id NUMBER,
employee_name VARCHAR2(100),
manager_id NUMBER
);
-- 插入示例数据
INSERT INTO unisql_employees (employee_id, employee_name, manager_id)
VALUES (1001, 'John Smith', 1003);
INSERT INTO unisql_employees (employee_id, employee_name, manager_id)
VALUES (1002, 'Jane Doe', 1003);
INSERT INTO unisql_employees (employee_id, employee_name, manager_id)
VALUES (1003, 'Mike Johnson', NULL);
-- 转换前Oracle SQL:
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM unisql_employees e, unisql_employees m WHERE e.manager_id = m.employee_id;
EMPLOYEE |MANAGER |
----------+------------+
John Smith|Mike Johnson|
Jane Doe |Mike Johnson|
-- 转换后PostgreSQL SQL:
SELECT e.employee_name AS employee,m.employee_name AS manager FROM unisql_employees AS e CROSS JOIN unisql_employees AS m WHERE e.manager_id=m.employee_id
employee |manager |
----------+------------+
John Smith|Mike Johnson|
Jane Doe |Mike Johnson|
3.3.1.4.3. 笛卡尔积
如果联接查询中的两个表没有联接条件，返回其笛卡尔乘积，使用第一个表的每一行与另一表每一个行进行组合输出。笛卡尔乘积会生成许多行，但是大多是无用数据。例如，两个都有 100 行的表的笛卡尔积有 10,000 行，所以除非特别需要笛卡尔乘积，建议查询中始终包括一个联接条件以避免返回笛卡尔乘积。
示例
-- 创建 colors 表
CREATE TABLE unisql_colors (
color_id NUMBER,
color_name VARCHAR2(100)
);
-- 创建 sizes 表
CREATE TABLE unisql_sizes (
size_id NUMBER,
size_name VARCHAR2(100)
);
-- 插入示例数据
INSERT INTO unisql_colors (color_id, color_name)
VALUES (1, 'Red');
INSERT INTO unisql_colors (color_id, color_name)
VALUES (2, 'Blue');
INSERT INTO unisql_sizes (size_id, size_name)
VALUES (1, 'Small');
INSERT INTO unisql_sizes (size_id, size_name)
VALUES (2, 'Medium');
-- 转换前Oracle SQL:
SELECT c.color_id, c.color_name, s.size_id, s.size_name
FROM unisql_colors c, unisql_sizes s;
COLOR_ID|COLOR_NAME|SIZE_ID|SIZE_NAME|
--------+----------+-------+---------+
1|Red | 1|Small |
1|Red | 2|Medium |
2|Blue | 1|Small |
2|Blue | 2|Medium |
-- 转换后PostgreSQL SQL:
SELECT c.color_id,c.color_name,s.size_id,s.size_name FROM unisql_colors AS c CROSS JOIN unisql_sizes AS s
color_id|color_name|size_id|size_name|
--------+----------+-------+---------+
1|Red | 1|Small |
1|Red | 2|Medium |
2|Blue | 1|Small |
2|Blue | 2|Medium |
3.3.1.4.4. INNER JOIN 内联接查询
内联接基于联接条件将两张表（例如 A 和 B）的列组合在一起，产生新的结果存放在表中。查询会将 A 表的每一行和 B 表的每一行进行比较，并找出满足联接条件的组合。当满足联接条件时，表 A 和 B 中匹配的行会按列组合（并排组合）成结果集中的一行。联接产生的结果集等于首先对两张表做笛卡尔积，将表 A 中的每一行和表 B 中的每一行组合，然后返回满足联接条件的记录。
示例
-- 创建 unisql_orders 表
CREATE TABLE unisql_orders (
order_id NUMBER,
order_date DATE,
customer_id NUMBER,
amount NUMBER
);
-- 创建 unisql_customers 表
CREATE TABLE unisql_customers (
customer_id NUMBER,
customer_name VARCHAR2(100),
city VARCHAR2(100)
);
-- 插入示例数据
INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (1, TO_DATE('2022-01-01', 'YYYY-MM-DD'), 1001, 1000);
INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (2, TO_DATE('2022-02-01', 'YYYY-MM-DD'), 1002, 1500);
INSERT INTO unisql_orders (order_id, order_date, customer_id, amount)
VALUES (3, TO_DATE('2022-04-03', 'YYYY-MM-DD'), 1003, 1600);
INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1001, 'John Smith', 'New York');
INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1002, 'Jane Doe', 'Los Angeles');
INSERT INTO unisql_customers (customer_id, customer_name, city)
VALUES (1004, 'Grace Miller', 'San Francisco');
-- 转换前Oracle SQL：
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
INNER JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT|
--------+-----------------------+-------------+-----------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
-- 转换后PostgreSQL SQL:
SELECT o.order_id,o.order_date,c.customer_name,c.city,o.amount FROM unisql_orders AS o JOIN unisql_customers AS c ON o.customer_id=c.customer_id
order_id|order_date |customer_name|city |amount|
--------+-----------------------+-------------+-----------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
3.3.1.4.5. LEFT JOIN 左外联接查询
左联接（LEFT [OUTER] JOIN）中左表行未在右表匹配到行时，在右表自动填充 NULL。
示例
-- 转换前Oracle SQL:
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
LEFT JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT|
--------+-----------------------+-------------+-----------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
3|2022-04-03 00:00:00.000| | | 1600|
-- 转换后PostgreSQL SQL:
SELECT o.order_id,o.order_date,c.customer_name,c.city,o.amount FROM unisql_orders AS o LEFT JOIN unisql_customers AS c ON o.customer_id=c.customer_id
order_id|order_date |customer_name|city |amount|
--------+-----------------------+-------------+-----------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles| 1500|
3|2022-04-03 00:00:00.000| | | 1600|
3.3.1.4.6. RIGHT JOIN 右外联接查询
右联接（RIGHT [OUTER] JOIN）中右表行未在左表匹配到行时，在左表自动填充 NULL。
示例
-- 转换前Oracle SQL:
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
RIGHT JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT|
--------+-----------------------+-------------+-------------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles | 1500|
| |Grace Miller |San Francisco| |
-- 转换后PostgreSQL SQL:
SELECT o.order_id,o.order_date,c.customer_name,c.city,o.amount FROM unisql_orders AS o RIGHT JOIN unisql_customers AS c ON o.customer_id=c.customer_id
order_id|order_date |customer_name|city |amount|
--------+-----------------------+-------------+-------------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles | 1500|
| |Grace Miller |San Francisco| |
3.3.1.4.7. FULL JOIN 全外联接查询
全联接（FULL [OUTER] JOIN）左表或者右表未在其它表匹配到行时均会填充NULL。
示例
-- 转换前Oracle SQL:
SELECT o.order_id, o.order_date, c.customer_name, c.city, o.amount
FROM unisql_orders o
FULL JOIN unisql_customers c ON o.customer_id = c.customer_id;
ORDER_ID|ORDER_DATE |CUSTOMER_NAME|CITY |AMOUNT|
--------+-----------------------+-------------+-------------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles | 1500|
| |Grace Miller |San Francisco| |
3|2022-04-03 00:00:00.000| | | 1600|
-- 转换后PostgreSQL SQL:
SELECT o.order_id,o.order_date,c.customer_name,c.city,o.amount FROM unisql_orders AS o FULL JOIN unisql_customers AS c ON o.customer_id=c.customer_id
order_id|order_date |customer_name|city |amount|
--------+-----------------------+-------------+-------------+------+
1|2022-01-01 00:00:00.000|John Smith |New York | 1000|
2|2022-02-01 00:00:00.000|Jane Doe |Los Angeles | 1500|
3|2022-04-03 00:00:00.000| | | 1600|
| |Grace Miller |San Francisco| |
3.3.1.4.8. 外联接符号（+）
对于左联接，在 WHERE 子句的联接条件中对右表的所有列应用外联接符号（+）；对于右联接，在 WHERE 子句的联接条件中对左表的所有列应用外联接符号（+）。
示例
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');
CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');
-- 转换前Oracle SQL:
SELECT
UNISQL_TABLE1.*,
UNISQL_TABLE2.*
FROM
UNISQL_TABLE1,UNISQL_TABLE2
WHERE
UNISQL_TABLE1.c1 = UNISQL_TABLE2.c1(+);
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |
2|B |BB| | | |
3|C |CC| | | |
-- 转换后PostgreSQL SQL:
SELECT UNISQL_TABLE1.*,UNISQL_TABLE2.* FROM UNISQL_TABLE2 RIGHT JOIN UNISQL_TABLE1 ON UNISQL_TABLE1.c1=UNISQL_TABLE2.c1 WHERE 1=1
c1|c2|c3|c1|c2|c3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |
2|B |BB| | | |
3|C |CC| | | |
-- (+)左外连接多个条件
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');
CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');
-- 转换前Oracle SQL:
SELECT
UNISQL_TABLE1.*,
UNISQL_TABLE2.*
FROM
UNISQL_TABLE1,
UNISQL_TABLE2
WHERE
UNISQL_TABLE1.c1 = UNISQL_TABLE2.c1(+)
AND UNISQL_TABLE1.c2 = UNISQL_TABLE2.c2(+);
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
2|B |BB| | | |
1|A |AA| | | |
3|C |CC| | | |
-- 转换后PostgreSQL SQL:
SELECT UNISQL_TABLE1.*,UNISQL_TABLE2.* FROM UNISQL_TABLE2 RIGHT JOIN UNISQL_TABLE1 ON UNISQL_TABLE1.c1=UNISQL_TABLE2.c1 AND UNISQL_TABLE1.c2=UNISQL_TABLE2.c2 WHERE 1=1 AND 1=1
c1|c2|c3|c1|c2|c3|
--+--+--+--+--+--+
1|A |AA| | | |
2|B |BB| | | |
3|C |CC| | | |
-- (+)右外连接
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');
CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');
-- 转换前Oracle SQL:
SELECT
*
FROM
UNISQL_TABLE1,UNISQL_TABLE2
WHERE
UNISQL_TABLE1.c1(+) = UNISQL_TABLE2.c1;
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |
-- 转换后PostgreSQL SQL:
SELECT * FROM UNISQL_TABLE1 RIGHT JOIN UNISQL_TABLE2 ON UNISQL_TABLE1.c1=UNISQL_TABLE2.c1 WHERE 1=1
c1|c2|c3|c1|c2|c3|
--+--+--+--+--+--+
1|A |AA| 1|a |b |
-- (+)右外连接多个条件
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');
CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');
-- 转换前Oracle SQL:
SELECT
*
FROM
UNISQL_TABLE1,UNISQL_TABLE2
WHERE
UNISQL_TABLE1.c1(+) = UNISQL_TABLE2.c1
AND UNISQL_TABLE1.c2(+) = UNISQL_TABLE2.c2;
C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+
| | | 1|a |b |
-- 转换后PostgreSQL SQL:
SELECT * FROM UNISQL_TABLE1 RIGHT JOIN UNISQL_TABLE2 ON UNISQL_TABLE1.c1=UNISQL_TABLE2.c1 AND UNISQL_TABLE1.c2=UNISQL_TABLE2.c2 WHERE 1=1 AND 1=1
c1|c2|c3|c1|c2|c3|
--+--+--+--+--+--+
| | | 1|a |b |
-- (+)外连接在子查询中
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');
CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');
CREATE TABLE UNISQL_TABLE3(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE3 (C1, C2, C3) VALUES ('1', 'C', 'D');
-- 转换前Oracle SQL:
SELECT
*
FROM
(
SELECT t1.c1, t2.c2,t3.c3
FROM
UNISQL_TABLE1 t1,
UNISQL_TABLE2 t2,
UNISQL_TABLE3 t3
WHERE
t1.c1 = t2.c1(+)
AND t1.c1 = t3.c1(+)
);
C1|C2|C3|
--+--+--+
1|a |D |
2| | |
3| | |
-- 转换后PostgreSQL SQL:
SELECT * FROM (SELECT t1.c1,t2.c2,t3.c3 FROM UNISQL_TABLE3 AS t3 RIGHT JOIN (UNISQL_TABLE2 AS t2 RIGHT JOIN UNISQL_TABLE1 AS t1 ON t1.c1=t2.c1) ON t1.c1=t3.c1 WHERE 1=1 AND 1=1) AS uni_sub
c1|c2|c3|
--+--+--+
1|a |D |
2| | |
3| | |
-- (+)外连接加笛卡尔积
CREATE TABLE UNISQL_TABLE1(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('1', 'A', 'AA');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('2', 'B', 'BB');
INSERT INTO UNISQL_TABLE1 (C1, C2, C3) VALUES ('3', 'C', 'CC');
CREATE TABLE UNISQL_TABLE2(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE2 (C1, C2, C3) VALUES ('1', 'a', 'b');
CREATE TABLE UNISQL_TABLE3(C1 NUMBER, C2 VARCHAR2(50), C3 VARCHAR2(50));
INSERT INTO UNISQL_TABLE3 (C1, C2, C3) VALUES ('1', 'C', 'D');
-- 转换前Oracle SQL:
SELECT
t1.*,
t2.*,
t3.*
FROM
UNISQL_TABLE1 t1,
UNISQL_TABLE2 t2,
UNISQL_TABLE3 t3
WHERE
t1.c1(+) = t2.c1
AND t1.c1 = t3.c1;
C1|C2|C3|C1|C2|C3|C1|C2|C3|
--+--+--+--+--+--+--+--+--+
1|A |AA| 1|a |b | 1|C |D |
-- 转换后PostgreSQL SQL:
SELECT t1.*,t2.*,t3.* FROM (UNISQL_TABLE1 AS t1 RIGHT JOIN UNISQL_TABLE2 AS t2 ON t1.c1=t2.c1) CROSS JOIN UNISQL_TABLE3 AS t3 WHERE 1=1 AND t1.c1=t3.c1
c1|c2|c3|c1|c2|c3|c1|c2|c3|
--+--+--+--+--+--+--+--+--+
1|A |AA| 1|a |b | 1|C |D |
3.3.1.5. WITH CLAUSE
如果查询语句中有多个相同的子查询，可以把相同的子查询放在 WITH CLAUSE 中作为公共表达式，在主体查询中直接引用即可。
语法
with_clause_select:
with_clause simple_select
with_clause:
WITH query_table_name [opt_column_alias_name_list] AS ( select_clause )
select_clause:
simple_select | select_clause_set
opt_column_alias_name_list:
(column_name_list)
column_name_list:
column_name [,column_name...]
示例
CREATE TABLE unisql_tbl1(col1 INT,col2 INT,col3 INT);
INSERT INTO unisql_tbl1 VALUES(1,1,1);
INSERT INTO unisql_tbl1 VALUES(2,2,2);
INSERT INTO unisql_tbl1 VALUES(3,3,3);
-- 转换前Oracle SQL:
-- 直接使用表 unisql_tbl1 的列名作为 u_tbl1 的列名
WITH u_tbl1 AS(SELECT * FROM unisql_tbl1) SELECT * FROM u_tbl1;
COL1|COL2|COL3|
----+----+----+
1| 1| 1|
2| 2| 2|
3| 3| 3|
-- 转换后PostgreSQL SQL:
WITH u_tbl1 AS (SELECT * FROM unisql_tbl1) SELECT * FROM u_tbl1
col1|col2|col3|
----+----+----+
1| 1| 1|
2| 2| 2|
3| 3| 3|
-- 转换前Oracle SQL:
-- 直接给 w_tbl1 命名列名
WITH w_tbl1(c1,c2,c3) AS(SELECT * FROM unisql_tbl1) SELECT * FROM w_tbl1;
C1|C2|C3|
--+--+--+
1| 1| 1|
2| 2| 2|
3| 3| 3|
-- 转换后PostgreSQL SQL:
WITH w_tbl1 (c1, c2, c3) AS (SELECT * FROM unisql_tbl1) SELECT * FROM w_tbl1
c1|c2|c3|
--+--+--+
1| 1| 1|
2| 2| 2|
3| 3| 3|
3.3.1.6. 子查询
子查询指的是 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|戴尔 |
-- 转换后PostgreSQL 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|戴尔 |
-- 转换后PostgreSQL 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|戴尔 |
3.3.1.7. DUAL 表查询
DUAL 表有一个列 DUMMY，定义为 VARCHAR2(1)，并包含一个值为 X 的行。在使用 SELECT 语句计算常量表达式时，可以从 DUAL 表中选择数据。
-- 转换前Oracle SQL:
SELECT
1,
2 + 1,
NULL,
''
FROM
dual;
1|2+1|NULL|''|
-+---+----+--+
1| 3| | |
-- 转换后PostgreSQL SQL:
SELECT 1,2+1,NULL,''
?column?|?column?|?column?|?column?|
--------+--------+--------+--------+
1| 3| | |
3.3.1.8. 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;
-- 转换后PostgreSQL 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
3.3.1.9. 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|
-- 转换后PostgreSQL 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|
3.3.2. 伪列
3.3.2.1. 层次查询伪列
层次查询伪列仅在层次查询中有效，要在查询中定义层次结构关系，必须使用 CONNECT BY 子句。
3.3.2.1.1. LEVEL 伪列
LEVEL 伪列用来协助标记节点的层次。
在层次结构中，根为第 1 层，根的子结点为第 2 层，之后以此类推。例如，根节点的 LEVEL 值会返回 1，根节点的子节点的 LEVEL 值会返回 2，之后以此类推。
以四层级倒置树结构为例，Root Row 是倒置树中最高的行，LEVEL 值一般为 1。Child Row 是任何非 Root Row，LEVEL 值一般为 2、3 或 4。Parent Row 是任何有 Child Row 的行（Root Row 除外），LEVEL 值一般为 2 或 3。Leaf Row 是任何没有孩子节点的行，LEVEL 值一般为 4。
-- 转换前Oracle SQL: -- 构造列数 select level from dual connect by level<=10; LEVEL| -----+ 1| 2| 3| 4| 5| 6| 7| 8| 9| 10| -- 转换后PostgreSQL SQL: WITH RECURSIVE tmp AS (SELECT 1 AS "$level" UNION ALL SELECT "$level"+1 AS "$level" FROM tmp WHERE "$level"+1<=10) SELECT tmp."$level" AS level FROM tmp level| -----+ 1| 2| 3| 4| 5| 6| 7| 8| 9| 10|
3.3.2.2. 序列伪列
序列（Sequence）是数据库按照一定规则生成的自增数字序列。因其自增的特性，通常被用作主键和唯一键。本节主要介绍序列的取值方法和应用场景。
3.3.2.2.1. 序列的取值方法
可以使用如下伪列引用 SQL 语句中的序列值：
CURRVAL：返回序列的当前值。
NEXTVAL：返回序列的下一个自增值。
使用序列伪列时，必须在 CURRVAL 和 NEXTVAL 前带上序列的名称，并用句点（.）引用。例如，序列的名称为 SEQ_FOO，则可以通过 SEQ_FOO.CURRVAL 获取 SEQ_FOO 序列的当前值。同样，可以通过 SEQ_FOO.NEXTVAL 获取 SEQ_FOO 序列的下一个自增值。
3.3.2.2.2. 序列值的应用场景
通过 CURRVAL 和 NEXTVAL 引用的序列值可以用于以下位置：
非子查询或者视图中的 SELECT 语句的选择列表中。
INSERT 语句中子查询的选择列表中。
INSERT 语句中的 VALUE 子句中。
UPDATE 语句中的 SET 子句中。
序列 CURRVAL 和 NEXTVAL 的值不能用于以下位置：
DELETE、SELECT 或者 UPDATE 语句的子查询中。
视图的查询中。
带 DISTINCT 运算符的 SELECT 语句中。
带 GROUP BY 子句或者 ORDER BY 子句的 SELECT 语句中。
与另一个 SELECT 语句通过 UNION、INTERSECT 或者 MINUS 集合运算符进行联合的 SELECT 语句中。
SELECT 语句的 WHERE 子句中。
CREATE TABLE 或者 ALTER TABLE 语句中列的 DEFAULT 值。
CHECK 约束的条件中。
-- 创建sequence create sequence uni_seq increment by 1 start with 1; -- 转换前Oracle SQL： -- 获取下一个sequence的值 SELECT uni_seq.nextval FROM unisql_employee ke; NEXTVAL| -------+ 1| 2| 3| 4| 5| 6| -- 转换后PostgreSQL 创建sequence SQL： CREATE SEQUENCE uni_seq INCREMENT BY 1 START WITH 1 -- 转换后PostgreSQL SQL： SELECT nextval('uni_seq') FROM unisql_employee AS ke nextval| -------+ 1| 2| 3| 4| 5| 6|
3.3.2.2.3. 使用注意事项
当currval,nextval作为表字段和sequence_name.currval,sequence_name.nextval出现在同一个SQL语句时,建议表字段使用双引号引起来,否则转换后可能得到和预期不一致的结果。 同时注意表的别名和sequence_name不要相同,会造成一定的歧义。
-- 如果表结构定义如下：
DROP TABLE unisql_key_test_2;
CREATE TABLE unisql_key_test_2(
id NUMBER,
currval varchar(10),
nextval varchar(10)
);
INSERT INTO unisql_key_test_2(currval,nextval) values('currval1','nextval1');
-- 同时使用不支持,转换结果可能和预期不一致。
SELECT unisql_seq_1.nextval,nextval,unisql_seq_1.currval,currval FROM unisql_key_test_2;
-- 推荐使用方式：
-- 如果需要同时使用，表字段请用双引号包裹起来
CREATE TABLE unisql_key_test_3(
id NUMBER,
"currval" varchar(10),
"nextval" varchar(10)
);
INSERT INTO unisql_key_test_3("currval","nextval") values('currval1','nextval1');
SELECT unisql_seq_1.nextval,"nextval",unisql_seq_1.currval,"currval" FROM unisql_key_test_3;
-- 注意以下语句转换会得到和预期不一样的结果
SELECT unisql_seq_1.nextval,unisql_seq_1.lnextval,unisql_seq_1.currval,unisql_seq_1.currval FROM unisql_key_test_1 unisql_seq_1;
3.3.2.3. ROWID 伪列
ROWID 伪列提供了快速定位表中某一行的能力，ROWID 值由主键编码得到，不会进行实际的存储。
-- 转换前Oracle SQL： SELECT ROWID,ke.* FROM unisql_employee ke; ROWID |EMPLOYEE_ID|NAME |SALARY|DEPARTMENT_ID|HIRE_DATE |COMMISSION_PCT| ------------------+-----------+-----+------+-------------+----------+--------------+ AAA2fQAAMAAIbD0AAA| 1|JACK | 5000| 1|2023-01-01| 0.05| AAA2fQAAMAAIbD0AAB| 2|TOM | 10000| 1|2023-02-01| 0.15| AAA2fQAAMAAIbD0AAC| 3|LINDA| 15000| 1|2023-03-01| 0.2| AAA2fQAAMAAIbD0AAD| 4|ADA | 20000| 2|2023-04-01| 0.1| AAA2fQAAMAAIbD0AAE| 5|TINA | 30000| 2|2023-05-01| 0.2| AAA2fQAAMAAIbD0AAF| 6|KATE | 50000| 3|2023-06-01| 0.3| -- 转换后PostgreSQL SQL： SELECT CTID,ke.* FROM unisql_employee AS ke rowid|employee_id|name |salary|department_id|hire_date |commission_pct| -----+-----------+-----+------+-------------+----------+--------------+ (0,1)| 1|JACK | 5000| 1|2023-01-01| 0.05| (0,2)| 2|TOM | 10000| 1|2023-02-01| 0.15| (0,3)| 3|LINDA| 15000| 1|2023-03-01| 0.2| (0,4)| 4|ADA | 20000| 2|2023-04-01| 0.1| (0,5)| 5|TINA | 30000| 2|2023-05-01| 0.2| (0,6)| 6|KATE | 50000| 3|2023-06-01| 0.3|
3.3.2.4. ROWNUM 伪列
ROWNUM 伪列会对查询结果中的每一行进行编号，其值为该行在查询结果集中的具体位置。例如，第一行返回值 1，第二行返回值 2，之后以此类推。
统一SQL 支持以下ROWNUM的用法：
-- 转换前Oracle SQL: SELECT ke.*, ROWNUM AS row_num FROM unisql_employee ke; EMPLOYEE_ID|NAME |SALARY|DEPARTMENT_ID|HIRE_DATE |COMMISSION_PCT|ROW_NUM| -----------+-----+------+-------------+----------+--------------+-------+ 1|JACK | 5000| 1|2023-01-01| 0.05| 1| 2|TOM | 10000| 1|2023-02-01| 0.15| 2| 3|LINDA| 15000| 1|2023-03-01| 0.2| 3| 4|ADA | 20000| 2|2023-04-01| 0.1| 4| 5|TINA | 30000| 2|2023-05-01| 0.2| 5| 6|KATE | 50000| 3|2023-06-01| 0.3| 6| -- 转换后PostgreSQL SQL: SELECT ke.*,row_number() OVER () AS row_num FROM unisql_employee AS ke employee_id|name |salary|department_id|hire_date |commission_pct|row_num| -----------+-----+------+-------------+----------+--------------+-------+ 1|JACK | 5000| 1|2023-01-01| 0.05| 1| 2|TOM | 10000| 1|2023-02-01| 0.15| 2| 3|LINDA| 15000| 1|2023-03-01| 0.2| 3| 4|ADA | 20000| 2|2023-04-01| 0.1| 4| 5|TINA | 30000| 2|2023-05-01| 0.2| 5| 6|KATE | 50000| 3|2023-06-01| 0.3| 6| -- 转换前Oracle SQL: SELECT rownum,employee_id,SALARY FROM unisql_employee ke WHERE rownum < 5; ROWNUM|EMPLOYEE_ID|SALARY| ------+-----------+------+ 1| 1| 5000| 2| 2| 10000| 3| 3| 15000| 4| 4| 20000| -- 转换后PostgreSQL SQL: SELECT row_number() OVER () AS "ROWNUM",employee_id,SALARY FROM unisql_employee AS ke LIMIT 5-1 rownum|employee_id|salary| ------+-----------+------+ 1| 1| 5000| 2| 2| 10000| 3| 3| 15000| 4| 4| 20000| -- 转换前Oracle SQL: SELECT rownum,employee_id,SALARY FROM unisql_employee ke WHERE rownum <= 5; ROWNUM|EMPLOYEE_ID|SALARY| ------+-----------+------+ 1| 1| 5000| 2| 2| 10000| 3| 3| 15000| 4| 4| 20000| 5| 5| 30000| -- 转换后PostgreSQL SQL: SELECT row_number() OVER () AS "ROWNUM",employee_id,SALARY FROM unisql_employee AS ke LIMIT 5 rownum|employee_id|salary| ------+-----------+------+ 1| 1| 5000| 2| 2| 10000| 3| 3| 15000| 4| 4| 20000| 5| 5| 30000| -- 转换前Oracle SQL: SELECT employee_id,SALARY FROM unisql_employee ke WHERE rownum = 1; EMPLOYEE_ID|SALARY| -----------+------+ 1| 5000| -- 转换后PostgreSQL SQL: SELECT employee_id,SALARY FROM unisql_employee AS ke LIMIT 1 employee_id|salary| -----------+------+ 1| 5000| -- 转换前Oracle SQL: SELECT * FROM (SELECT row_.*,rownum rownum_ FROM unisql_employee row_) WHERE rownum_ > 2 AND rownum_ <= 4; EMPLOYEE_ID|NAME |SALARY|DEPARTMENT_ID|HIRE_DATE |COMMISSION_PCT|ROWNUM_| -----------+-----+------+-------------+----------+--------------+-------+ 3|LINDA| 15000| 1|2023-03-01| 0.2| 3| 4|ADA | 20000| 2|2023-04-01| 0.1| 4| -- 转换后PostgreSQL SQL: SELECT * FROM (SELECT row_.*,row_number() OVER () AS rownum_ FROM unisql_employee AS row_) AS uni_sub WHERE rownum_>2 AND rownum_<=4 employee_id|name |salary|department_id|hire_date |commission_pct|rownum_| -----------+-----+------+-------------+----------+--------------+-------+ 3|LINDA| 15000| 1|2023-03-01| 0.2| 3| 4|ADA | 20000| 2|2023-04-01| 0.1| 4| -- 转换前Oracle SQL: SELECT * FROM (SELECT row_.*,rownum rownum_ FROM (select employee_id,salary,name from unisql_employee) row_) WHERE rownum_ > 2 AND rownum_ <= 4; EMPLOYEE_ID|SALARY|NAME |ROWNUM_| -----------+------+-----+-------+ 3| 15000|LINDA| 3| 4| 20000|ADA | 4| -- 转换后PostgreSQL SQL: SELECT * FROM (SELECT row_.*,row_number() OVER () AS rownum_ FROM (SELECT employee_id,salary,name FROM unisql_employee) AS row_) AS uni_sub WHERE rownum_>2 AND rownum_<=4 employee_id|salary|name |rownum_| -----------+------+-----+-------+ 3| 15000|LINDA| 3| 4| 20000|ADA | 4|
3.3.3. 运算符
运算符一般用于连接运算数或参数等单个数据项并返回结果。从语法上讲，运算符出现在操作数之前、操作数之后或两个操作数之间均可。本节主要介绍 统一SQL 所支持的常见运算符及使用说明。
当前支持常见运算符类型：
3.3.3.1. 串联运算符
串联运算符 || 用于连接字符串和 CLOB 数据类型的数据。使用串联运算符连接两个字符串后会得到另一个字符串。
-- 转换前Oracle SQL: select '成交价格高于前一日收盘价;成交价格：' || 2 * 1000.00 || '元，收盘价：' || null || '' || ' '|| 'end'|| '' FROM DUAL; '成交价格高于前一日收盘价;成交价格：'||2*1000.00||'元，收盘价：'||NULL||''||''||'END'||''| ------------------------------------------------------------------+ 成交价格高于前一日收盘价;成交价格：2000元，收盘价： end | -- 转换后PostgreSQL SQL: SELECT concat(concat(concat(concat(concat(concat(concat('成交价格高于前一日收盘价;成交价格：', 2*1000.00), '元，收盘价：'), NULL), ''), ' '), 'end'), ''); concat | -------------------------------------+ 成交价格高于前一日收盘价;成交价格：2000.00元，收盘价： end|
警告
本方案中串联运算符会转化成concat函数，转化的结果是字符串，字符串去和其他数据类型进行运算、比较时，因为源库和目标库的隐式转化不同，出现转出的SQL无法在目标库中执行的情况。
3.3.4. 字面量
字面量用于表达一个固定的数值，与常量值是同义词。许多函数和 SQL 语句都需要指定字面量，字面量也可以作为表达式和条件的一部分。
3.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|
-- 转换后PostgreSQL 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|
-- 转换后PostgreSQL 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，所以目标数据库字面量值超过该精度会自动进行处理后截断，出现与源数据库数据不一致的情况。
3.3.5. DML
3.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);
-- 转换后PostgreSQL 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');
-- 转换后PostgreSQL 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);
-- 转换后PostgreSQL SQL:
INSERT INTO unisql_special_orders VALUES (3,1,1,'a1',1,1)
-- 查询表数据
SELECT * FROM unisql_special_orders;
3.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;
-- 转换后PostgreSQL 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|
3.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;
-- 转换后PostgreSQL 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|
3.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;
-- 转换后PostgreSQL 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;
-- 转换后PostgreSQL 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;
-- 转换后PostgreSQL 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
3.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_;
-- 转换后PostgreSQL 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 | | |
3.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);
-- 转换后PostgreSQL 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 |
3.3.5.7. 更新
-- 转换前Oracle SQL:
UPDATE unisql_people_target SET FIRST_NAME = 'Bob', LAST_NAME = 'Matthew' WHERE person_id=1;
-- 转换后PostgreSQL SQL:
UPDATE unisql_people_target SET FIRST_NAME='Bob', LAST_NAME='Matthew' WHERE person_id=1
-- 查询表数据
select * from unisql_people_target;
3.3.5.8. 更新带表别名
-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = 'Ashley', u.LAST_NAME = 'Jessica' WHERE u.person_id=2;
-- 转换后PostgreSQL SQL:
UPDATE unisql_people_target AS u SET FIRST_NAME='Ashley', LAST_NAME='Jessica' WHERE u.person_id=2
-- 查询表数据
select * from unisql_people_target;
3.3.5.9. 更新带子查询
-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = (SELECT b.FIRST_NAME FROM unisql_people_target b WHERE b.person_id = 2), u.LAST_NAME = 'Nicholas' WHERE u.person_id=3;
-- 转换后PostgreSQL SQL:
UPDATE unisql_people_target AS u SET FIRST_NAME=(SELECT b.FIRST_NAME FROM unisql_people_target AS b WHERE b.person_id=2), LAST_NAME='Nicholas' WHERE u.person_id=3
-- 查询表数据
select * from unisql_people_target;
3.3.5.10. 更新带函数
-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = UPPER(u.FIRST_NAME),u.LAST_NAME = LOWER(u.LAST_NAME) WHERE u.person_id=3;
-- 转换后PostgreSQL SQL:
UPDATE unisql_people_target AS u SET FIRST_NAME=UPPER(u.FIRST_NAME), LAST_NAME=LOWER(u.LAST_NAME) WHERE u.person_id=3
-- 查询表数据
select * from unisql_people_target;
3.3.5.11. 条件删除
-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID =1 AND FIRST_NAME = 'Bob';
-- 转换后PostgreSQL SQL:
DELETE FROM unisql_people_target AS u WHERE u.PERSON_ID=1 AND FIRST_NAME='Bob'
-- 查询表数据
select * from unisql_people_target;
3.3.5.12. 条件子查询删除
-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID > (SELECT PERSON_ID FROM unisql_people_target WHERE FIRST_NAME = 'ASHLEY');
-- 转换后PostgreSQL SQL:
DELETE FROM unisql_people_target AS u WHERE u.PERSON_ID>(SELECT PERSON_ID FROM unisql_people_target WHERE FIRST_NAME='ASHLEY')
-- 查询表数据
select * from unisql_people_target;
3.3.5.13. exists函数删除
-- 转换前Oracle SQL:
DELETE FROM unisql_people_source
WHERE EXISTS(SELECT 1 FROM unisql_people_target WHERE unisql_people_target.PERSON_ID = unisql_people_source.PERSON_ID);
-- 转换后PostgreSQL SQL:
DELETE FROM unisql_people_source WHERE EXISTS (SELECT 1 FROM unisql_people_target WHERE unisql_people_target.PERSON_ID=unisql_people_source.PERSON_ID)
-- 查询表数据
select * from unisql_people_source;
3.3.6. DDL
本节主要介绍统一SQL支持原生Oracle数据库中的SQL的DDL操作。
使用场景中一些注意点示例：
-- 在建表时使用比如"user","USER","rowid","value","random"等单词作为字段时；
-- 在Oracle数据库中以下的建表语句虽然可以正常执行,但是不建议如此使用,表字段名称尽量根据业务特性来定
DROP TABLE unisql_key_test;
CREATE TABLE unisql_key_test(
"user" varchar(10),
"USER" varchar(10),
"SYSDATE" varchar(10),
"rowid" varchar(10),
value varchar(10),
"value" varchar(10),
random varchar(10),
"random" varchar(10),
currval varchar(10),
"currval" varchar(10),
nextval varchar(10),
"nextval" varchar(10)
);
-- 统一SQL虽然可以对上述SQL进行转换,但是由于目标库特性支持度不同,可能会在执行时报错。
-- 比如在PostgreSQL中value和"value"会被视为重复定义。
3.3.6.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;
-- 转换后PostgreSQL SQL:
TRUNCATE TABLE unisql_truncate_test
-- 查询表数据
SELECT * FROM unisql_truncate_test;
3.3.6.2. 创建索引
语法
CREATE [UNIQUE|BITMAP] INDEX [SCHEMA.] INDEX_NAME
ON TABLE_NAME ( COLUMN1, COLUMN2, ..., COLUMNN )
[{ LOGGING | NOLOGGING}]
[{ GLOBAL | LOCAL}]
[{ USABLE | UNUSABLE}]
[{ IMMEDIATE INVALIDATION | UNUSABLE}]
[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) NOLOGGING;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) GLOBAL;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) USABLE;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) UNUSABLE;
-- 转换后PostgreSQL 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 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);
-- 创建表，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;
-- 转换后PostgreSQL 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.3.6.3. 删除索引
语法
DROP INDEX INDEX_NAME
示例
-- 转换前Oracle SQL:
DROP INDEX unisql_index_test_idx;
-- 转换后PostgreSQL SQL:
DROP INDEX unisql_index_test_idx;
3.3.6.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;
-- 转换后PostgreSQL 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.3.6.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_)
);
-- 转换后PostgreSQL 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;
-- 转换后PostgreSQL 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
)
-- 转换后PostgreSQL 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.3.6.6. 创建分区表
分区键仅支持列，分区范围支持使用函数、字符串、常数表达，统一SQL会根据表名、分区名在转化库中创建分区表，分区表名为：表名_1_prt_分区名， Oracle 源语句HASH分区未指定分区名和分区数目时，转化到PostgreSQL表分区名默认为p0，指定多个分区数目，转化后的分区名依次递增（p0,p1,p2,…）。Oracle 和 PostgreSQL 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'))
);
-- 转换后PostgreSQL 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'))
);
-- 转换后PostgreSQL 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)
);
-- 转换后PostgreSQL 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);
-- 转换后PostgreSQL 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;
-- 转换后PostgreSQL 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)
-- 转换后PostgreSQL SQL:
不支持
3.3.6.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;
-- 转换后PostgreSQL SQL:
TRUNCATE TABLE unisql_partition_by_range_test_1_prt_q1_2023
DROP TABLE unisql_partition_by_range_test_1_prt_q1_2023
3.3.6.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);
-- 转换后PostgreSQL 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.3.6.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;
-- 转换后PostgreSQL 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);
-- 转换后PostgreSQL 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);
-- 转换后PostgreSQL SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a FOREIGN KEY (a, b) REFERENCES unisql_cn_ref_tab(col1, col2)
3.3.6.10. 删除表约束
语法
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;
-- 转换后PostgreSQL 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.3.6.11. 修改列的属性
警告
PostgreSQL内核暂不支持使用ALTER MODIFY
3.3.6.12. 修改表名
语法
RENAME TABLE_NAME TO NEW_TABLE_NAME;
ALTER TABLE TABLE_NAME RENAME TO NEW_TABLE_NAME;
示例
CREATE TABLE unisql_rename_table_test(id int,name varchar(10));
-- 转换前Oracle SQL:
RENAME unisql_rename_table_test TO unisql_rename_table_test_new;
ALTER TABLE unisql_rename_table_test_new RENAME TO unisql_rename_table_test;
-- 转换后PostgreSQL SQL:
ALTER TABLE unisql_rename_table_test RENAME TO unisql_rename_table_test_new
ALTER TABLE unisql_rename_table_test_new RENAME TO unisql_rename_table_test
3.3.6.13. 注释表名,列名
语法
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';
-- 转换后PostgreSQL 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.3.6.15. 创建视图
语法
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;
-- 转换后PostgreSQL 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.3.6.16. 创建序列
语法
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;
-- 转换后PostgreSQL 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