CONNECT BY支持识别外层ROWID

介绍

Vastbase G100新增兼容Oracle的用法：CONNECT BY支持ROWID在外层查询被识别。具体用法参考示例。

Vastbase数据库表中的每一行数据都有一个唯一的标识符，也可以称为ROWID，ROWID是一个伪列，更多内容参考ROWID数据类型。

示例

示例1： ROWID出现在SELECT的目标列中。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建测试表并插入数据。

CREATE TABLE EMP_1140719
(ID INT PRIMARY KEY,
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP_1140719 VALUES (1,7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP_1140719 VALUES (2,7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP_1140719 VALUES (3,7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP_1140719 VALUES (4,7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP_1140719 VALUES (5,7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP_1140719 VALUES (6,7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP_1140719 VALUES (7,7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP_1140719 VALUES (8,7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP_1140719 VALUES (9,7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP_1140719 VALUES (10,7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP_1140719 VALUES (11,7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP_1140719 VALUES (12,7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP_1140719 VALUES (13,7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP_1140719 VALUES (14,7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

3、执行查询语句。

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || t.ename "employee", t.empno, t.mgr,
CONNECT_BY_ROOT t.empno "mgr empno",
CONNECT_BY_ROOT t.ename "mgr ename",rowid
FROM EMP_1140719 t
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

返回结果如下：

 LEVEL | employee  | empno | mgr  | mgr empno | mgr ename |        rowid
-------+-----------+-------+------+-----------+-----------+----------------------
     1 | BLAKE     |  7698 | 7839 | 7698      | BLAKE     | ClYAAA==AAAAAA==BgA=
     2 |   ALLEN   |  7499 | 7698 | 7698      | BLAKE     | ClYAAA==AAAAAA==AgA=
     2 |   JAMES   |  7900 | 7698 | 7698      | BLAKE     | ClYAAA==AAAAAA==DAA=
     2 |   MARTIN  |  7654 | 7698 | 7698      | BLAKE     | ClYAAA==AAAAAA==BQA=
     2 |   TURNER  |  7844 | 7698 | 7698      | BLAKE     | ClYAAA==AAAAAA==CgA=
     2 |   WARD    |  7521 | 7698 | 7698      | BLAKE     | ClYAAA==AAAAAA==AwA=
     1 | CLARK     |  7782 | 7839 | 7782      | CLARK     | ClYAAA==AAAAAA==BwA=
     2 |   MILLER  |  7934 | 7782 | 7782      | CLARK     | ClYAAA==AAAAAA==DgA=
     1 | JONES     |  7566 | 7839 | 7566      | JONES     | ClYAAA==AAAAAA==BAA=
     2 |   FORD    |  7902 | 7566 | 7566      | JONES     | ClYAAA==AAAAAA==DQA=
     3 |     SMITH |  7369 | 7902 | 7566      | JONES     | ClYAAA==AAAAAA==AQA=
     2 |   SCOTT   |  7788 | 7566 | 7566      | JONES     | ClYAAA==AAAAAA==CAA=
     3 |     ADAMS |  7876 | 7788 | 7566      | JONES     | ClYAAA==AAAAAA==CwA=
(13 rows)

示例2： 子查询的外层查询中存在ROWID。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建测试表并插入数据。

CREATE TABLE EMP_1140768
(ID INT PRIMARY KEY,
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP_1140768 VALUES (1,7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP_1140768 VALUES (2,7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP_1140768 VALUES (3,7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP_1140768 VALUES (4,7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP_1140768 VALUES (5,7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP_1140768 VALUES (6,7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP_1140768 VALUES (7,7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP_1140768 VALUES (8,7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP_1140768 VALUES (9,7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP_1140768 VALUES (10,7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP_1140768 VALUES (11,7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP_1140768 VALUES (12,7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP_1140768 VALUES (13,7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP_1140768 VALUES (14,7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

3、执行查询语句。

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || t.ename "employee", t.empno, t.mgr,
CONNECT_BY_ROOT t.empno "mgr empno",
CONNECT_BY_ROOT t.ename "mgr ename",rowid
FROM EMP_1140768 t
WHERE EMPNO NOT IN(
SELECT EMPNO FROM
EMP_1140768 
WHERE DEPTNO <=20
)  
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

返回结果如下：

 LEVEL | employee | empno | mgr  | mgr empno | mgr ename |        rowid
-------+----------+-------+------+-----------+-----------+----------------------
     1 | BLAKE    |  7698 | 7839 | 7698      | BLAKE     | 8VUAAA==AAAAAA==BgA=
     2 |   ALLEN  |  7499 | 7698 | 7698      | BLAKE     | 8VUAAA==AAAAAA==AgA=
     2 |   JAMES  |  7900 | 7698 | 7698      | BLAKE     | 8VUAAA==AAAAAA==DAA=
     2 |   MARTIN |  7654 | 7698 | 7698      | BLAKE     | 8VUAAA==AAAAAA==BQA=
     2 |   TURNER |  7844 | 7698 | 7698      | BLAKE     | 8VUAAA==AAAAAA==CgA=
     2 |   WARD   |  7521 | 7698 | 7698      | BLAKE     | 8VUAAA==AAAAAA==AwA=
(6 rows)

VastbaseG100

CONNECT BY支持识别外层ROWID

介绍

示例

弹框头部