LightDB 22.4 新特性之兼容Oracle树形查询

支持树形查询

Oracle：

LightDB：

支持sys_connect_by_path语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43 lightdb@test=#
lightdb@test=# SELECT empno,ename,SUBSTR(sys_connect_by_path(ename,'->'),3) AS sys_connect_by_path_col
lightdb@test-# FROM EMP
lightdb@test-# START WITH ENAME = 'KING' /*mgr is null*/
lightdb@test-# CONNECT BY MGR = (PRIOR EMPNO);
empno | ename | sys_connect_by_path_col
-------+--------+---------------------------
7839 | KING | KING
7566 | JONES | KING->JONES
7698 | BLAKE | KING->BLAKE
7782 | CLARK | KING->CLARK
7499 | ALLEN | KING->BLAKE->ALLEN
7521 | WARD | KING->BLAKE->WARD
7654 | MARTIN | KING->BLAKE->MARTIN
7788 | SCOTT | KING->JONES->SCOTT
7844 | TURNER | KING->BLAKE->TURNER
7900 | JAMES | KING->BLAKE->JAMES
7902 | FORD | KING->JONES->FORD
7934 | MILLER | KING->CLARK->MILLER
7369 | SMITH | KING->JONES->FORD->SMITH
7876 | ADAMS | KING->JONES->SCOTT->ADAMS
(14 rows)
lightdb@test=# SELECT LEVEL,empno,ename,mgr -- , (PRIOR ename) AS mgr_name
lightdb@test-# FROM EMP
lightdb@test-# START WITH ENAME = 'KING'
lightdb@test-# CONNECT BY MGR = (PRIOR EMPNO);
level | empno | ename | mgr
-------+-------+--------+------
1 | 7839 | KING |
2 | 7566 | JONES | 7839
2 | 7698 | BLAKE | 7839
2 | 7782 | CLARK | 7839
3 | 7499 | ALLEN | 7698
3 | 7521 | WARD | 7698
3 | 7654 | MARTIN | 7698
3 | 7788 | SCOTT | 7566
3 | 7844 | TURNER | 7698
3 | 7900 | JAMES | 7698
3 | 7902 | FORD | 7566
3 | 7934 | MILLER | 7782
4 | 7369 | SMITH | 7902
4 | 7876 | ADAMS | 7788
(14 rows)

lt_restore支持--table_exists_action

如下使用

1	`lt_dump -U lightdb -d` `test` `-n public -Fd -f` `test` `-v`

导出完后使用lt_restore恢复数据，默认情况下，会报表冲突

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 [lightdb@node1 ~]$ lt_restore test -U lightdb -d test -n public -v
lt_restore: connecting to database for restore
lt_restore: creating TABLE "public.dept"
lt_restore: while PROCESSING TOC:
lt_restore: from TOC entry 571; 1259 24124 TABLE dept lightdb
lt_restore: error: could not execute query: ERROR: relation "dept" already exists
Command was: CREATE TABLE public.dept (
deptno numeric(2,0) NOT NULL,
dname varchar2(14),
loc varchar2(13)
);
lt_restore: creating TABLE "public.emp"
lt_restore: from TOC entry 570; 1259 24116 TABLE emp lightdb
lt_restore: error: could not execute query: ERROR: relation "emp" already exists
Command was: CREATE TABLE public.emp (
empno numeric(4,0) NOT NULL,
ename varchar2(10),

添加--table-exists-action参数之后

1
2
3
4
5
6
7
8
9
10 [lightdb@node1 ~]$ lt_restore test -U lightdb -d test -n public --table_exists_action=replace -v
lt_restore: connecting to database for restore
lt_restore: table public.dept exists, drop table before restore due to table_exists_action of replace.
lt_restore: creating TABLE "public.dept"
lt_restore: table public.emp exists, drop table before restore due to table_exists_action of replace.
lt_restore: creating TABLE "public.emp"
lt_restore: processing data for table "public.dept"
lt_restore: processing data for table "public.emp"
lt_restore: creating CONSTRAINT "public.dept pk_dept"
lt_restore: creating CONSTRAINT "public.emp pk_emp"

q语句语法支持

1
2
3
4
5
6 lightdb@test=# select q'[alter table ]' || table_name from user_tables;
?column?
------------------
alter table EMP
alter table DEPT
(2 rows)

标签：Oracle

时间:2022-12-26 01:40:55

声明：本站部分作品是由网友自主投稿和发布、编辑整理上传，对此类作品本站仅提供交流平台，转载的目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，不为其版权负责。如果您发现网站上有侵犯您的知识产权的作品，请与我们取得联系，我们会及时修改或删除。

上一篇：返回列表
下一篇：PG数据库运维中的操作系统关注点

网友评论：

LightDB 22.4 新特性之兼容Oracle树形查询

支持树形查询

lt_restore支持--table_exists_action

q语句语法支持

时间:2022-12-26 01:40:55

客服QQ