4.3. SQL 语法
4.3.1. 查询和子查询
查询是指数据库中用来获取数据的方式,它可搭配条件限制子句(例如 WHERE),排列顺序子句(例如 ORDER BY)等语句来获取查询结果。
子查询是指嵌套在一个上层查询中的查询。上层的查询一般被称为父查询或外部查询。子查询的结果作为输入传递回父查询或外部查询。父查询将这个值结合到计算中,以便确定最后的输出。
SQL 语言允许多层嵌套查询,即一个子查询中还可以嵌套其他子查询。同时,子查询可以出现在 SQL 语句中的各种子句中,例如 SELECT 语句、FROM 语句和 WHERE 语句等。
SQL 语句中常见的查询类型如下:
4.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;
-- 转换后LightDB-Oracle SQL:
-- 查询表中部分列
SELECT first_name,last_name,salary FROM unisql_simple_query_employee
-- 查询表中所有列
SELECT * FROM unisql_simple_query_employee
-- 对表中的列进行数学运算
SELECT salary+100 FROM unisql_simple_query_employee
-- 给表中的列取别名
SELECT salary*12 AS year_salary FROM unisql_simple_query_employee
-- 将表中的数据去重
SELECT DISTINCT manager_id FROM unisql_simple_query_employee
-- 使用 CASE WHEN 语句查询表中的数据
SELECT salary,CASE WHEN salary>=1200 THEN 'high' WHEN salary>=1000 THEN 'middle' ELSE 'low' END AS type FROM unisql_simple_query_employee
4.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|
-- 转换后LightDB-Oracle SQL:
SELECT unisql_id,unisql_name,unisql_salary,unisql_hire_date FROM unisql_partition$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|
4.3.1.1.2. pivot行转列
警告
pivot前不支持使用子查询,如:select … from (子查询语句) t 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|
-- 转换后LightDB-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|
4.3.1.1.3. unpivot列转行
警告
unpivot前不支持使用子查询,如:select … from (子查询语句) t 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|
-- 转换后LightDB-Oracle 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|
4.3.1.2. 层次查询
层次查询(Hierarchical Query)是一种具有特殊功能的查询语句,通过它能够将分层数据按照层次关系展示出来。
语法
SELECT [level,] [column,] expression...
FROM from_list
[where_clause]
[hierarchical_query_clause]
[group_by_clause]
hierarchical_query_clause::=
start_with_clause connect_by_clause
| connect_by_clause start_with_clause
start_with_clause::=
START WITH expression
connect_by_clause::=
CONNECT BY [NOCYCLE] expression
警告
层次查询特有表达式不支持嵌套使用:如: 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
4.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 |
-- 转换后LightDB-Oracle SQL:
SELECT LEVEL,empno,ename,mgr,sal,connect_by_root(DEPTNO) AS 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|sysconnectpath|
-----+-----+------+----+----+----+--------------+
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 |
-- 转换后LightDB-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, '/') AS 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 |sysconnectpath|
-----+------+---------+----+----+----+-----+----+------------+--------------+
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|
-- 转换后LightDB-Oracle SQL:
SELECT level FROM dual CONNECT BY level<=10
level|
-----+
1|
2|
3|
4|
5|
6|
7|
8|
9|
10|
4.3.1.3. 复合查询
使用集合运算符 UNION、UNION ALL、INTERSECT 和 MINUS 来组合多个查询,即复合查询。所有集合运算符都具有相同的优先级。
4.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|
-- 转换后LightDB-Oracle 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|
4.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|
-- 转换后LightDB-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|
4.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|
-- 转换后LightDB-Oracle 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|
4.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|
-- 转换后LightDB-Oracle SQL:
SELECT * FROM unisql_collect_1 MINUS SELECT * FROM unisql_collect_2
id|name|chinese|math|english|
--+----+-------+----+-------+
4|秦六 | 65| 65| 60|
3|王五 | 90| 90| 90|
4.3.1.4. 联接查询
联接(Join)是将两个或多个表、视图的结合在一起的查询。
4.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|
-- 转换后LightDB-Oracle 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|
4.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|
-- 转换后LightDB-Oracle SQL:
SELECT e.employee_name AS employee,m.employee_name AS manager FROM unisql_employees AS e , unisql_employees AS m WHERE e.manager_id=m.employee_id
employee |manager |
----------+------------+
John Smith|Mike Johnson|
Jane Doe |Mike Johnson|
4.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 |
-- 转换后LightDB-Oracle SQL:
SELECT c.color_id,c.color_name,s.size_id,s.size_name FROM unisql_colors AS c , 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 |
4.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|
-- 转换后LightDB-Oracle 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|
4.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|
-- 转换后LightDB-Oracle 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|
4.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| |
-- 转换后LightDB-Oracle 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| |
4.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|
-- 转换后LightDB-Oracle 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|
| |Grace Miller |San Francisco| |
3|2022-04-03 00:00:00.000| | | 1600|
4.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| | | |
-- 转换后LightDB-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| | | |
-- (+)左外连接多个条件
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| | | |
-- 转换后LightDB-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|
--+--+--+--+--+--+
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 |
-- 转换后LightDB-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 |
-- (+)右外连接多个条件
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 |
-- 转换后LightDB-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 |
-- (+)外连接在子查询中
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| | |
-- 转换后LightDB-Oracle SQL:
SELECT * FROM (SELECT t1.c1,t2.c2,t3.c3 FROM UNISQL_TABLE1 AS t1 , UNISQL_TABLE2 AS t2 , UNISQL_TABLE3 AS t3 WHERE t1.c1=t2.c1(+) AND t1.c1=t3.c1(+)) 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 |
-- 转换后LightDB-Oracle SQL:
SELECT t1.*,t2.*,t3.* FROM UNISQL_TABLE1 AS t1 , UNISQL_TABLE2 AS t2 , UNISQL_TABLE3 AS 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 |
4.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|
-- 转换后LightDB-Oracle 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|
-- 转换后LightDB-Oracle 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|
4.3.1.6. 子查询
子查询指的是 SELECT 查询语句中嵌套了另一个或者多个 SELECT 语句,可以返回单行结果、多行结果或不返回结果。SELECT 语句的 FROM 子句中的子查询也称为内联视图。可以在嵌入式视图中嵌套任意数量的子查询。SELECT 语句的 WHERE 子句中的子查询也称为嵌套子查询。
子查询可以分为相关子查询和非相关子查询。相关子查询指该子查询的执行依赖了外部查询的变量,这种子查询通常会执行多次。非相关子查询指该子查询的执行不依赖外部查询的变量,这种子查询一般只需要计算一次。对于非相关子查询与部分相关子查询,可以通过改写进行子查询消除,实现嵌套子查询的展开。
示例:
-- 创建表结构
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|戴尔 |
-- 转换后LightDB-Oracle SQL:
SELECT * FROM unisql_table_a AS T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b AS T2)
pk|name|
--+----+
1|福克斯 |
2|警察 |
3|的士 |
6|华盛顿 |
7|戴尔 |
-- 转换前Oracle SQL:
-- 有依赖关系的子查询,子查询中用到了外层查询变量 T1.PK
SELECT * FROM unisql_table_a T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b T2 WHERE T2.PK = T1.PK);
PK|NAME|
--+----+
1|福克斯 |
2|警察 |
3|的士 |
6|华盛顿 |
7|戴尔 |
-- 转换后LightDB-Oracle SQL:
SELECT * FROM unisql_table_a AS T1 WHERE T1.PK IN (SELECT T2.PK FROM unisql_table_b AS T2 WHERE T2.PK=T1.PK)
pk|name|
--+----+
1|福克斯 |
2|警察 |
3|的士 |
6|华盛顿 |
7|戴尔 |
4.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| | |
-- 转换后LightDB-Oracle SQL:
SELECT 1,2+1,NULL,'' FROM dual
?column?|?column?|?column?|?column?|
--------+--------+--------+--------+
1| 3| | |
4.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;
-- 转换后LightDB-Oracle SQL:
SELECT * FROM orderby_nulls_test ORDER BY id
SELECT * FROM orderby_nulls_test ORDER BY id DESC
SELECT * FROM orderby_nulls_test ORDER BY 1 NULLS FIRST
SELECT * FROM orderby_nulls_test ORDER BY id DESC NULLS FIRST
SELECT id,name FROM orderby_nulls_test UNION SELECT id,name FROM orderby_nulls_test ORDER BY id NULLS FIRST
SELECT e.id,e.name FROM orderby_nulls_test AS e LEFT JOIN orderby_nulls_test AS d ON e.id=d.id ORDER BY d.id NULLS FIRST
SELECT id,name,ROW_NUMBER() OVER (PARTITION BY id ORDER BY name NULLS LAST) FROM orderby_nulls_test
SELECT id,name,MAX(id) OVER (PARTITION BY id ORDER BY name NULLS LAST) FROM orderby_nulls_test
SELECT id,COUNT(1) AS ct FROM orderby_nulls_test GROUP BY id ORDER BY COUNT(1) NULLS FIRST
4.3.1.9. AS作为别名
警告
LightDB-Oracle暂不支持在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|
-- 建议使用修改上述语句为
SELECT 0 as "as", 1 as "as",2 as "as", 3 a, 4 as b, count(*) as "as" FROM dual;
4.3.2. 伪列
伪列(Pseudocolumn)的行为与表中的列相同,但并未存储具体数值。因此,伪列只具备读属性,不可以对伪列进行插入、更新、删除的等行为。本节主要介绍 统一SQL 所支持的伪列及使用说明。
当前支持如下伪列类型:
4.3.2.1. 层次查询伪列
层次查询伪列仅在层次查询中有效,要在查询中定义层次结构关系,必须使用 CONNECT BY 子句。
4.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| -- 转换后LightDB-Oracle SQL: SELECT level FROM dual CONNECT BY level<=10 level| -----+ 1| 2| 3| 4| 5| 6| 7| 8| 9| 10|
4.3.2.2. 序列伪列
序列(Sequence)是数据库按照一定规则生成的自增数字序列。因其自增的特性,通常被用作主键和唯一键。本节主要介绍序列的取值方法和应用场景。
4.3.2.2.1. 序列的取值方法
可以使用如下伪列引用 SQL 语句中的序列值:
CURRVAL:返回序列的当前值。
NEXTVAL:返回序列的下一个自增值。
使用序列伪列时,必须在 CURRVAL 和 NEXTVAL 前带上序列的名称,并用句点(.)引用。例如,序列的名称为 SEQ_FOO,则可以通过 SEQ_FOO.CURRVAL 获取 SEQ_FOO 序列的当前值。同样,可以通过 SEQ_FOO.NEXTVAL 获取 SEQ_FOO 序列的下一个自增值。
4.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|
-- 转换后LightDB-Oracle 创建sequence SQL:
CREATE SEQUENCE uni_seq INCREMENT BY 1 START WITH 1
-- 转换后LightDB-Oracle SQL:
SELECT uni_seq.nextval FROM unisql_employee AS ke
nextval|
-------+
1|
2|
3|
4|
5|
6|
4.3.2.2.3. 使用注意事项
当currval,nextval作为表字段和sequence_name.currval,sequence_name.nextval出现在同一个SQL语句时,建议表字段使用双引号引起来,否则转换后可能得到和预期不一致的结果。 同时注意表的别名和sequence_name不要相同,会造成一定的歧义。
-- 注意以下SQL语句为原始的Oracle语句,非转换后目标库的SQL语句
-- 如果表结构定义如下:
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;
4.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| -- 转换后LightDB-Oracle SQL: SELECT ROWID,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|
4.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| -- 转换后LightDB-Oracle SQL: SELECT ke.*,ROWNUM 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| -- 转换后LightDB-Oracle SQL: SELECT rownum,employee_id,SALARY FROM unisql_employee AS ke WHERE rownum<5 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| -- 转换后LightDB-Oracle SQL: SELECT rownum,employee_id,SALARY FROM unisql_employee AS ke WHERE rownum<=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| -- 转换后LightDB-Oracle SQL: SELECT employee_id,SALARY FROM unisql_employee AS ke WHERE rownum=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| -- 转换后LightDB-Oracle SQL: SELECT * FROM (SELECT row_.*,rownum 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| -- 转换后LightDB-Oracle SQL: SELECT * FROM (SELECT row_.*,rownum 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|
4.3.3. 运算符
运算符一般用于连接运算数或参数等单个数据项并返回结果。从语法上讲,运算符出现在操作数之前、操作数之后或两个操作数之间均可。本节主要介绍 统一SQL 所支持的常见运算符及使用说明。
当前支持常见运算符类型:
4.3.3.1. 串联运算符
串联运算符 || 用于连接字符串和 CLOB 数据类型的数据。使用串联运算符连接两个字符串后会得到另一个字符串。
-- 转换前Oracle SQL: select '成交价格高于前一日收盘价;成交价格:' || 2 * 1000.00 || '元,收盘价:' || null || '' || ' '|| 'end'|| '' FROM DUAL; '成交价格高于前一日收盘价;成交价格:'||2*1000.00||'元,收盘价:'||NULL||''||''||'END'||''| ------------------------------------------------------------------+ 成交价格高于前一日收盘价;成交价格:2000元,收盘价: end | -- 转换后LightDB-Oracle SQL: SELECT ((((((('成交价格高于前一日收盘价;成交价格:'||2*1000.00)||'元,收盘价:')||NULL)||'')||' ')||'end')||'') FROM DUAL; ?column? | ----------------------------------+ 成交价格高于前一日收盘价;成交价格:2000元,收盘价: end|
4.3.4. 字面量
字面量用于表达一个固定的数值,与常量值是同义词。许多函数和 SQL 语句都需要指定字面量,字面量也可以作为表达式和条件的一部分。
4.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|
-- 转换后LightDB-Oracle SQL:
INSERT INTO unisql_interval_ym_table (col1,col2,col3) VALUES (INTERVAL '2-3' YEAR TO MONTH,INTERVAL '-21-3' YEAR TO MONTH,INTERVAL 'P3Y11M366DT23H59M59.1234S' YEAR TO MONTH);
SELECT col1,col2,col3 FROM unisql_interval_ym_table;
col1 |col2 |col3 |
--------------+-----------------+---------------+
2 years 3 mons|-21 years -3 mons|3 years 11 mons|
INTERVAL DAY TO SECOND
语法
INTERVAL '[+ | -] days hours:minutes:seconds[.frac_secs]' DAY [(precision)] TO SECOND [(fractional_seconds_precision)]
描述
用来指定以天和具体时间为单位的一段时间。
参数解释
参数 |
说明 |
---|---|
days hours:minutes:seconds[.frac_secs] |
SQL日期格式,依次表示天数、时、分、秒。注意 值中时、分、秒不允许有空格。 |
precision |
表示 DAY 元素的精度,默认值为 2,取值范围为 [0,9] |
fractional_seconds_precision |
表示 SECOND 元素小数部分的精度,默认值为 6,取值范围为 [0,9] |
days 表示天,取整数值,范围为 [0,999999999]。
hours 表示小时,取整数值,范围为 [0,23]。
minutes 表示分钟,取整数值,范围为 [0,59]。
seconds 表示秒,取整数值,范围为 [0,59]。
示例
CREATE TABLE unisql_interval_ds_table (
col1 INTERVAL DAY(3) TO SECOND(9),
col2 INTERVAL DAY(1) TO SECOND(9)
);
-- 转换前Oracle SQL:
INSERT INTO unisql_interval_ds_table(col1,col2)
VALUES
(
INTERVAL '1 12:30:15.123456789' DAY(2) TO SECOND(9),
INTERVAL '-4 05:45:30.987654' DAY TO SECOND(6)
);
SELECT col1,col2 FROM unisql_interval_ds_table;
COL1 |COL2 |
--------------------+-----------------+
1 12:30:15.123456789|-4 5:45:30.987654|
-- 转换后LightDB-Oracle SQL:
INSERT INTO unisql_interval_ds_table (col1,col2) VALUES (INTERVAL '1 12:30:15.123456789' DAY TO SECOND(6),INTERVAL '-4 05:45:30.987654' DAY TO SECOND(6));
SELECT col1,col2 FROM unisql_interval_ds_table;
col1 |col2 |
---------------------+------------------------+
1 day 12:30:15.123457|-4 days +05:45:30.987654|
警告
下面列举的是一些在使用间隔字面量过程中会遇到的部分疑问点,在使用数据库时,如果对数据完全一致要求较高,现有的函数处理能力不能满足业务需求,建议进行业务SQL改写或者对结果进行进一步处理
目标数据库对显示间隔字面量与源库Oracle不一致,但是语义保持一致,不考虑时区、数据库设置的影响;
精度、时间格式暂时不做范围限制,具体是否错误由执行层保证;
因为需求原因,时间间隔字面量只限制在插入表中值去使用;
Oracle的INTERVAL DAY TO SECOND字面量的SECOND 元素小数部分的精度,默认值为 6,取值范围为 [0,9],但是目标库相关SECOND 元素小数部分的精度的最大值为6,所以目标数据库字面量值超过该精度会自动进行处理后截断,出现与源数据库数据不一致的情况。
4.3.5. DML
DML(Data Manipulation Language,数据操作语言)语句用于操作现有 Schema 对象中的数据,如增加、修改、删除等。
4.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);
-- 转换后LightDB-Oracle SQL:
INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id,credit_limit,cust_email) VALUES (1,1,1,'a1',1,1)
-- 查询表数据
SELECT * FROM unisql_special_orders;
指定某些字段,不指的有默认值的插入时带上默认值。
-- 创建表
drop table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders(sales_rep_id) VALUES('a1');
-- 转换后LightDB-Oracle SQL:
INSERT INTO unisql_special_orders (sales_rep_id) VALUES ('a1')
-- 查询表数据
SELECT * FROM unisql_special_orders;
不指定字段,则默认为所有字段与建表时一致。
-- 创建表
drop table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders VALUES(3,1,1,'a1',1,1);
-- 转换后LightDB-Oracle SQL:
INSERT INTO unisql_special_orders VALUES (3,1,1,'a1',1,1)
-- 查询表数据
SELECT * FROM unisql_special_orders;
4.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;
-- 转换后LightDB-Oracle SQL:
INSERT INTO unisql_special_orders SELECT order_,customer_,order_total,sales_rep_id,credit_limit,cust_email FROM unisql_special_orders
-- 查询表数据
SELECT * FROM unisql_special_orders;
ORDER_|CUSTOMER_|ORDER_TOTAL|SALES_REP_ID|CREDIT_LIMIT|CUST_EMAIL|
------+---------+-----------+------------+------------+----------+
1| 1| 1|a1 | 1| 1|
0| | |a1 | | |
3| 1| 1|a1 | 1| 1|
1| 1| 1|a1 | 1| 1|
0| | |a1 | | |
3| 1| 1|a1 | 1| 1|
4.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;
-- 转换后LightDB-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
-- 查询表数据
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|
4| 4| 4|a4 | 4| 4|
5| 5| 5|a5 | 5| 5|
4.3.5.4. 多表插入
备注
LightDB-Oracle暂不支持在INSERT ALL 的条件句中使用子查询
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;
-- 转换后LightDB-Oracle SQL:
-- INSERT ALL 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) 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) 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 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders
INSERT ALL
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)
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)
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 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders
-- 转换前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;
-- 转换后LightDB-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 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders
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 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders
-- 说明,LightDB-Oracle暂不支持在WHEN条件中使用子查询,统一SQL支持转换,但是在数据库执行会报错提示:
-- SQL 错误 [XX000]: ERROR: cannot handle unplanned sub-select
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;
4.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_;
-- 转换后LightDB-Oracle SQL:
-- INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id) WITH tmp AS (SELECT order_,customer_,order_total,sales_rep_id FROM unisql_special_orders) SELECT t.order_,t.customer_,y.order_total,y.sales_rep_id FROM unisql_special_orders AS t LEFT JOIN tmp AS y ON t.order_=y.order_
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 | | |
4.3.5.6. 合并插入
备注
支持 merge_insert_clause 在 merge_update_clause 顺序之前用法。还原后 merge_update_clause 还是在 merge_insert_clause 顺序之前。
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);
-- 转换后LightDB-Oracle SQL:
-- MERGE INTO unisql_people_target AS pt USING unisql_people_source AS 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)
MERGE INTO unisql_people_target AS pt
USING unisql_people_source AS 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)
-- 查询表数据
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 |
-- merge_insert_clause 在 merge_update_clause 顺序之前用法
-- 转换前Oracle SQL:
MERGE INTO unisql_people_target pt
USING unisql_people_source ps
ON (pt.person_id = ps.person_id)
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)
WHEN MATCHED THEN
UPDATE SET pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title;
-- 转换后LightDB-Oracle SQL:
-- MERGE INTO unisql_people_target AS pt USING unisql_people_source AS 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)
MERGE INTO unisql_people_target AS pt
USING unisql_people_source AS 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)
4.3.5.7. 更新
-- 转换前Oracle SQL:
UPDATE unisql_people_target SET FIRST_NAME = 'Bob', LAST_NAME = 'Matthew' WHERE person_id=1;
-- 转换后LightDB-Oracle SQL:
UPDATE unisql_people_target SET FIRST_NAME='Bob', LAST_NAME='Matthew' WHERE person_id=1
-- 查询表数据
select * from unisql_people_target;
4.3.5.8. 更新带表别名
-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = 'Ashley', u.LAST_NAME = 'Jessica' WHERE u.person_id=2;
-- 转换后LightDB-Oracle SQL:
UPDATE unisql_people_target AS u SET u.FIRST_NAME='Ashley', u.LAST_NAME='Jessica' WHERE u.person_id=2
-- 查询表数据
select * from unisql_people_target;
4.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;
-- 转换后LightDB-Oracle SQL:
UPDATE unisql_people_target AS u SET u.FIRST_NAME=(SELECT b.FIRST_NAME FROM unisql_people_target AS b WHERE b.person_id=2), u.LAST_NAME='Nicholas' WHERE u.person_id=3
-- 查询表数据
select * from unisql_people_target;
4.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;
-- 转换后LightDB-Oracle SQL:
UPDATE unisql_people_target AS u SET u.FIRST_NAME=UPPER(u.FIRST_NAME), u.LAST_NAME=LOWER(u.LAST_NAME) WHERE u.person_id=3
-- 查询表数据
select * from unisql_people_target;
4.3.5.11. 条件删除
-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID =1 AND FIRST_NAME = 'Bob';
-- 转换后LightDB-Oracle SQL:
DELETE FROM unisql_people_target AS u WHERE u.PERSON_ID=1 AND FIRST_NAME='Bob'
-- 查询表数据
select * from unisql_people_target;
4.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');
-- 转换后LightDB-Oracle 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;
4.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);
-- 转换后LightDB-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)
-- 查询表数据
select * from unisql_people_source;
4.3.6. DDL
DDL(Data Definition Language,数据定义语言)语句用来创建、修改和删除数据库对象。
备注
在 DDL 开始之前和之后,数据库会发出一个隐式的 COMMIT 语句,所以 DDL 语句执行后不可以回滚,建议在 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进行转换,但是由于目标库特性支持度不同,可能会在执行时报错。
-- 比如在LightDB-Oracle中rowid不可以作为字段,value和"value"会被视为重复定义。
4.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;
-- 转换后LightDB-Oracle SQL:
TRUNCATE TABLE unisql_truncate_test
-- 查询表数据
SELECT * FROM unisql_truncate_test;
4.3.6.2. 创建索引
语法
CREATE [UNIQUE|BITMAP] INDEX [SCHEMA.] INDEX_NAME
ON TABLE_NAME ( COLUMN1, COLUMN2, ..., COLUMNN )
[{ LOGGING | NOLOGGING}]
[{ GLOBAL | LOCAL}]
[{ 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;
-- 转换后LightDB-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 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);
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;
-- 创建表,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;
-- 转换后LightDB-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) LOCAL TABLESPACE test;
4.3.6.3. 删除索引
语法
DROP INDEX INDEX_NAME
示例
-- 转换前Oracle SQL:
DROP INDEX unisql_index_test_idx;
-- 转换后LightDB-Oracle SQL:
DROP INDEX unisql_index_test_idx
4.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;
-- 转换后LightDB-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
4.3.6.5. 创建表,包含临时表等
语法
CREATE [GLOBAL TEMPORARY] TABLE TABLE_NAME (COLUMN1 DATATYPE [NOT NULL | NULL] [DEFAULT_EXPR] [PRIMARY KEY],[table_constraint]...)
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
table_constraint:
CONSTRAINT constraint_name
{UNIQUE ( column_name [, ... ] ) constraint_state | PRIMARY KEY ( column_name [, ... ] ) constraint_state}
constraint_state::=
disable
| enable
DEFAULT_EXPR 后面表达式可支持使用部分函数、关键字函数、字符串、常数、序列、字符串连接符。
示例
-- 转换前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_)
);
-- 转换后LightDB-Oracle SQL:
-- CREATE TABLE unisql_create_table_test (DBID_ decimal(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(0),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_))
CREATE TABLE unisql_create_table_test (
DBID_ decimal(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(0),
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_))
-- 转换前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;
-- 转换后LightDB-Oracle SQL:
-- CREATE GLOBAL TEMPORARY TABLE unisql_create_table_on_option_test (d_SMALLINT bigint,d_INTEGER bigint,d_INT bigint,d_LONG text,d_NUMBER number(10,2),d_DECIMAL decimal(10,2),d_CHAR char(60),d_CHARACTER char(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 xml) ON COMMIT PRESERVE ROWS
CREATE GLOBAL TEMPORARY TABLE unisql_create_table_on_option_test (
d_SMALLINT bigint,
d_INTEGER bigint,
d_INT bigint,
d_LONG text,
d_NUMBER number(10,2),
d_DECIMAL decimal(10,2),
d_CHAR char(60),
d_CHARACTER char(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 xml) ON COMMIT PRESERVE ROWS
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
)
-- 转换后LightDB-Oracle SQL:
CREATE TABLE unisql_default_expr_table (
col_not_null varchar2 ( 1 ) DEFAULT 1 NOT NULL,
col_current_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ( 0 ),
col_current_localstamp date DEFAULT CURRENT_TIMESTAMP ( 0 ),
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
)
4.3.6.6. 创建分区表
分区范围支持使用函数、字符串、常数表达,统一SQL会根据表名、分区名在转化库中创建分区表,分区表名为:表名_1_prt_分区名,分区表的唯一、主键约束,必须与分区字段一致(或包含分区字段),才能保证全局的唯一性,否则无法实现转化。
语法
CREATE TABLE TABLE_NAME … PARTITION BY[HASH|RANGE|LIST] (partition_list)
示例
-- 转换前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'))
);
-- 转换后LightDB-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')))
-- 转换前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'))
);
-- 转换后LightDB-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')))
-- 转换前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)
);
-- 转换后LightDB-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))
-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 INT, col2 VARCHAR(50), col3 TIMESTAMP )
PARTITION BY HASH (col1) PARTITIONS 4;
-- 转换后LightDB-Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 bigint,col2 varchar(50),col3 timestamp) PARTITION BY HASH (col1) PARTITIONS 4
4.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 TRUNCATE PARTITION unisql_partition_p1,unisql_partition_p2;
-- 转换后LightDB-Oracle SQL:
ALTER TABLE unisql_partition_by_range_test TRUNCATE PARTITION q1_2023;
ALTER TABLE unisql_partition TRUNCATE PARTITION unisql_partition_p1;ALTER TABLE unisql_partition TRUNCATE PARTITION unisql_partition_p2;
-- 转换前Oracle SQL:
ALTER TABLE unisql_partition_by_range_test DROP PARTITION q1_2023;
ALTER TABLE unisql_partition DROP PARTITION unisql_partition_p1,unisql_partition_p2;
-- 转换后LightDB-Oracle SQL:
ALTER TABLE unisql_partition_by_range_test DROP PARTITION q1_2023;
ALTER TABLE unisql_partition DROP PARTITION unisql_partition_p1;ALTER TABLE unisql_partition DROP PARTITION unisql_partition_p2;
4.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]
[NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]]
示例
-- 创建表
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);
ALTER TABLE unisql_add_column_test ADD (col7 NUMBER(10,2) CONSTRAINT cn1_col7 NOT NULL,col8 varchar(10) DEFAULT 'a');
ALTER TABLE unisql_add_column_test ADD (col9 NUMBER(10,2) CONSTRAINT cn1_col9 NOT NULL,col10 varchar(10) CONSTRAINT cn1_col10 NOT NULL);
-- 转换后LightDB-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 bigint)
ALTER TABLE unisql_add_column_test ADD (col7 number(10,2) CONSTRAINT cn1_col7 NOT NULL, col8 varchar(10) DEFAULT 'a')
ALTER TABLE unisql_add_column_test ADD (col9 number(10,2) CONSTRAINT cn1_col9 NOT NULL, col10 varchar(10) CONSTRAINT cn1_col10 NOT NULL)
备注
-- 注意下面的用例场景,LightDB-Oracle暂未支持,如有需要,请联系LightDB团队提需求
-- 转换前Oracle SQL:
ALTER TABLE unisql_add_column_test ADD (i NUMBER(10,2), CONSTRAINT cn1_i UNIQUE(i));
-- 使用UNISQL转换时会有以下报错提示:
[57017]CONVERT ERROR: [00002]not support error: not support to convert constraint clause in alter table add column clause
4.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;
-- 转换后LightDB-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
-- 测试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);
-- 转换后LightDB-Oracle 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);
-- 转换后LightDB-Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a FOREIGN KEY (a,b) REFERENCES unisql_cn_ref_tab(col1,col2)
4.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;
-- 转换后LightDB-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
4.3.6.11. 修改列的属性
警告
- LightDB-Oracle内核暂不支持在修改字段时使用DEFAULT指定默认值,比如:
ALTER TABLE unisql_modify_column_test MODIFY (d number(10) DEFAULT ‘10’)
语法
alter_table_stmt:
ALTER TABLE table_name alter_table_action_list;
alter_table_action_list:
alter_table_action [, alter_table_action ...]
alter_table_action:
MODIFY [COLUMN] column_definition
示例
DROP TABLE unisql_modify_column_test;
CREATE TABLE unisql_modify_column_test(a int,d number);
SELECT * FROM unisql_modify_column_test;
-- 转换前Oracle SQL:
ALTER TABLE unisql_modify_column_test MODIFY (d number(10));
ALTER TABLE unisql_modify_column_test MODIFY (d number(10,2));
ALTER TABLE unisql_modify_column_test MODIFY d number(8);
ALTER TABLE unisql_modify_column_test MODIFY (a varchar(10),d number(10));
ALTER TABLE unisql_modify_column_test MODIFY (d varchar(10));
-- 转换后LightDB-Oracle SQL:
ALTER TABLE unisql_modify_column_test ALTER COLUMN d TYPE number(10)
ALTER TABLE unisql_modify_column_test ALTER COLUMN d TYPE number(10,2)
ALTER TABLE unisql_modify_column_test ALTER COLUMN d TYPE number(8)
ALTER TABLE unisql_modify_column_test MODIFY(a varchar(10),d number(10))
ALTER TABLE unisql_modify_column_test ALTER COLUMN d TYPE varchar(10)
4.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;
-- 转换后LightDB-Oracle 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
4.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';
-- 转换后LightDB-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'
4.3.6.14. 修改表
支持普通表修改为分区表的转换。
语法
alter_table_stmt:
ALTER TABLE table_name alter_table_action_list;
alter_table_action_list:
alter_table_action [, alter_table_action ...]
alter_table_action:
MODIFY PARTITION (range_partition_list)
| MODIFY PARTITION (list_partition_list)
range_partition_list:
range_partition [, range_partition ...]
range_partition:
PARTITION [partition_name]
VALUES LESS THAN {(expression_list) | (MAXVALUE)}
list_partition_list:
list_partition [, list_partition] ...
list_partition:
PARTITION [partition_name]
VALUES (DEFAULT|expression_list)
hash_partition_list:
hash_partition [, hash_partition, ...]
hash_partition:
partition [partition_name]
示例
-- 创建普通表
CREATE TABLE unisql_partition_test_list(id int,code varchar(10));
-- 修改成list分区表
ALTER TABLE unisql_partition_test_list MODIFY PARTITION BY list(id) (
PARTITION even values(2,4,6,8),
PARTITION odd values(1,3,5,7),
PARTITION rest values(DEFAULT)
);
-- 转换后LightDB-Oracle SQL:
ALTER TABLE unisql_partition_test_list MODIFY PARTITION BY LIST (id) (PARTITION even VALUES (2, 4, 6, 8),PARTITION odd VALUES (1, 3, 5, 7),PARTITION rest VALUES (DEFAULT))
-- 创建普通表
CREATE TABLE unisql_partition_test_range(id int,code varchar(10));
-- 转换前Oracle SQL:
-- 修改成range分区表
ALTER TABLE unisql_partition_test_range
MODIFY PARTITION BY RANGE (id)(
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (MAXVALUE)
);
-- 转换后LightDB-Oracle SQL:
ALTER TABLE unisql_partition_test_range MODIFY PARTITION BY RANGE (id) (PARTITION p1 VALUES LESS THAN (100),PARTITION p2 VALUES LESS THAN (MAXVALUE))
-- 创建普通表
CREATE TABLE unisql_partition_test_range_1(id int,code varchar(10));
-- 转换前Oracle SQL:
ALTER TABLE unisql_partition_test_range_1
MODIFY PARTITION BY RANGE (id)(
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200)
);
-- 转换后LightDB-Oracle SQL:
ALTER TABLE unisql_partition_test_range_1 MODIFY PARTITION BY RANGE (id) (PARTITION p1 VALUES LESS THAN (100),PARTITION p2 VALUES LESS THAN (200))
-- 创建普通表
CREATE TABLE unisql_partition_test_range_2(id int,code varchar(10));
-- 转换前Oracle SQL:
ALTER TABLE unisql_partition_test_range_2
MODIFY PARTITION BY RANGE (code)(
PARTITION p1 VALUES LESS THAN ('a'),
PARTITION p2 VALUES LESS THAN ('b')
);
-- 转换后LightDB-Oracle SQL:
ALTER TABLE unisql_partition_test_range_2 MODIFY PARTITION BY RANGE (code) (PARTITION p1 VALUES LESS THAN ('a'),PARTITION p2 VALUES LESS THAN ('b'))
4.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;
-- 转换后LightDB-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 ' ' AS a,' ' AS b FROM dual
CREATE VIEW unisql_view_6 AS SELECT 1 AS a,2 AS b FROM dual
CREATE OR REPLACE VIEW unisql_view_7 AS SELECT 1 AS a,2 AS b FROM dual
CREATE OR REPLACE VIEW unisql_view_8 AS SELECT 1 AS a,2 AS b,'3' AS c FROM dual
CREATE VIEW unisql_view_9 AS SELECT 1 AS a,2 AS b,'3' AS c FROM dual
CREATE VIEW unisql_view_10 AS SELECT 1 AS a,2 AS b,'3' AS c FROM dual
CREATE VIEW unisql_view_11 (a1,b1) AS SELECT a,b FROM unisql_view_test
4.3.6.16. 删除视图
语法
DROP VIEW [schema.] view_name [CASCADE CONSTRAINTS];
警告
暂不支持CASCADE CONSTRAINTS参数。如果给出,将被视为语法糖而被忽略
- 描述
- 该语句用于删除视图语句,该视图必须在您自己的 Schema 中,或者您必须有 DROP ANY VIEW 权限。
示例
-- 创建表
CREATE TABLE unisql_view_employees_test (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER
);
-- 创建视图
CREATE VIEW unisql_view_employee_names_salaries AS
SELECT first_name, salary
FROM unisql_view_employees_test;
-- 删除视图
-- 转换前Oracle SQL:
DROP VIEW unisql_view_employee_names_salaries;
-- 转换后LightDB-Oracle SQL:
DROP VIEW unisql_view_employee_names_salaries
4.3.6.17. 创建序列
语法
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;
-- 转换后LightDB-Oracle 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
4.3.6.18. 修改索引名
语法
ALTER TABLE [SCHEMA.] INDEX_NAME RENAME TO NEW_INDEX_NAME;
示例
CREATE TABLE unisql_rename_index_test(id int,name varchar(10));
CREATE INDEX idx_id ON unisql_rename_index_test(id);
-- 转换前Oracle SQL:
ALTER INDEX idx_id RENAME TO idx_id_new;
ALTER INDEX test.idx_id_new RENAME TO idx_id;
-- 转换后LightDB-Oracle SQL:
ALTER INDEX idx_id RENAME TO idx_id_new;
ALTER INDEX test.idx_id_new RENAME TO idx_id;
4.3.6.19. 同义词
- 描述
- 同义词 (Synonym)是 Oracle 租户中表、视图、物化视图、序列、存储过程、函数、包、类型、用户自定义类型,或是其他的同义词的别名。由于其只是一个别名,所以除了在数据字典中的定义不占任何空间。本文主要介绍同义词的分类、基本特性和权限要求。
- 分类
同义词有两种类型,Public 同义词和 Private 同义词。
Public 同义词属于 PUBLIC 组,每个用户都可以访问。Private 同义词属于对象所有者,只有其显式授权后其他用户才可访问。
公有同义词一般由 DBA 创建,普通用户如果希望创建公有同义词,则需要 CREATE PUBLIC SYNONYM 系统权限。
- 基本特性
- 同义词扩展了数据库的使用范围,能够在不同的数据库用户之间实现无缝交互。经常用于简化对象访问和提高对象访问的安全性。
4.3.6.19.1. 创建同义词
语法
CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ]
[ PUBLIC ] SYNONYM
[ schema. ] synonym
[ SHARING = { METADATA | NONE } ]
FOR [ schema. ] object [ @ dblink ] ;
备注
[ EDITIONABLE | NONEDITIONABLE ]和[ SHARING = { METADATA | NONE } ] 在LightDB-Oracle中将被作为语法糖处理,[ @ dblink ]暂不支持!
参数解释
参数 |
说明 |
---|---|
OR REPLACE |
表示如果要创建的同义词名称已存在,则使用新的定义替换同义词。 |
EDITIONABLE/NONEDITIONABLE |
如果在模式中为模式对象类型SYNONYM启用了编辑,请使用这些子句来指定同义词是已编辑对象还是非版本对象。对于私人同义词,默认是EDITABLE。对于公共同义词,默认值为NONEDITIONAL。 |
PUBLIC |
指定 PUBLIC 来创建公共同义词,所有用户都可以使用。用户必须对基础对象具有相应的权限才能使用该同义词。 在解析对象的引用时,仅当对象没有指定 Schema 时,才会使用公共同义词。 如果不指定 PUBLIC,则同义词是私有的,只能由当前 Schema 访问,并且同义词名称在当前 Schema 中必须唯一。 |
[schema. ]synonym |
Schema 指定当前同义词属于哪个用户。如果指定了 PUBLIC,则对同义词不能指定用户。 synonym 表示同义词的名称。 |
[ SHARING = { METADATA/NONE } ] |
此子句仅在应用程序根目录中创建同义词时适用。这种类型的同义词称为应用程序公共对象,它可以与属于应用程序根的应用程序 PDB 共享。要确定同义词的共享方式,请指定以下共享属性之一:METADATA - 元数据链接共享同义词的元数据,但其数据对于每个容器都是唯一的。这种类型的同义词被称为元数据链接的应用程序公共对象。NONE - 不共享同义词。如果省略此子句,则数据库将使用 DEFAULT_SHARING 初始化参数的值来确定同义词的共享属性。如果 DEFAULT_SHARING 初始化参数没有值,则默认值为 METADATA。 |
[schema. ]object |
表示同义词对应对象的名称。 |
[ @ dblink ] |
暂不支持 |
示例
-- 转换前Oracle SQL:
CREATE OR REPLACE EDITIONABLE SYNONYM employee SHARING = METADATA FOR user2.emp;
-- 转换后LightDB-Oracle SQL:
CREATE OR REPLACE SYNONYM employee FOR user2.emp;
4.3.6.19.2. 删除同义词
语法
DROP [PUBLIC] SYNONYM [ schema. ] synonym [FORCE] ;
备注
[FORCE] 在LightDB-Oracle中将被作为语法糖处理!
参数解释
参数 |
说明 |
---|---|
PUBLIC |
指定 PUBLIC 来删除公共同义词。如果不指定 PUBLIC,则删除私有同义词。 |
[schema. ]synonym |
Schema 指定当前同义词属于哪个用户。如果指定了 PUBLIC,则对同义词不能指定用户。 synonym 表示同义词的名称。 |
FORCE |
指定 FORCE 来删除同义词,即使它具有依赖表或用户定义类型。 |
示例
-- 转换前Oracle SQL:
DROP PUBLIC SYNONYM user1.employee FORCE;
-- 转换后LightDB-Oracle SQL:
DROP PUBLIC SYNONYM user1.employee;