3.2.9.1. Lightdb-Oracle

3.2.9.1.1. 层次查询伪列

层次查询伪列仅在层次查询中有效,要在查询中定义层次结构关系,必须使用 CONNECT BY 子句。

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

3.2.9.1.2. 序列伪列

序列(Sequence)是数据库按照一定规则生成的自增数字序列。因其自增的特性,通常被用作主键和唯一键。本节主要介绍序列的取值方法和应用场景。

3.2.9.1.2.1. 序列的取值方法

可以使用如下伪列引用 SQL 语句中的序列值:

  • CURRVAL:返回序列的当前值。

  • NEXTVAL:返回序列的下一个自增值。

使用序列伪列时,必须在 CURRVAL 和 NEXTVAL 前带上序列的名称,并用句点(.)引用。例如,序列的名称为 SEQ_FOO,则可以通过 SEQ_FOO.CURRVAL 获取 SEQ_FOO 序列的当前值。同样,可以通过 SEQ_FOO.NEXTVAL 获取 SEQ_FOO 序列的下一个自增值。

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

3.2.9.1.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;

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

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