LightDB 23.2兼容oracle psql中使用with子句

前言

在适配过程中发现oracle可以在过程语言中使用with子句，因此在LightDB 23.2上做了兼容性开发。oracle的with_clause语法仅支持select子句之前使用。
其语法结构如下：


语法上不支持：
(1)不支持plsql_declarations
(2)不支持subav_factoring_clause
(3)不支持search_clause
(4)不支持cycle_clause

实现

在LightDB语法层实现创建匿名块、函数、存储过程、包对with_clause子句的解析，然后再插件plorasql语法解析部分加上with_clause的处理方式。
PLoraSQL支持公共表表达式（cte），用在匿名块、存储过程、函数和包中。cte后面可以跟随INSERT，UPDATE和DELETE子句，后面是SELECT语句的话必须包含into_clause，否则将报告错误。函数中不允许DML进行操作。with_clasue也可以用作子查询，但不支持递归。

plorasql with_clause测试

测试我们分别从4个方面进行，包括匿名块、函数、存储过程、包。
创建表并插入数据

CREATE TABLE company(
   ID INT      NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
INSERT INTO company VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO company VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO company VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO company VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO company VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO company VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO company VALUES (7, 'James', 24, 'Houston', 10000.00 );

（1）匿名块中包含with_clause

declare
v_count varchar(100);
begin
with tmp as (select * from company where id=2) select * into v_count from tmp;
DBMS_OUTPUT.PUT_LINE(v_count);
end;
/

（2）函数中包含with_clause

create or replace function hs_with_f
return int 
as
v_count INTEGER;
begin
with tmp as (select * from company where id=2) select id into v_count from tmp;
return v_count;
end;
/

select hs_with_f();
 hs_with_f 
-----------
         2
(1 row)

（3）存储过程中包含with_clause

create or replace procedure hs_with_p as
v_count varchar(100);
begin
with tmp as (select * from company where id=2) select id into v_count from tmp;
DBMS_OUTPUT.PUT_LINE(v_count);
end;
/

call hs_with_p();
2
CALL

（4）包中包含with_clause

create or replace package hs_with_pkg
 is
 v_count varchar(100);
 function hs_with_f return int;
 procedure hs_with_p;
end;
/

create or replace package body hs_with_pkg is
 function hs_with_f return int as
 begin
  with tmp as (select * from company where id=2) select id into v_count from tmp;
  return v_count;
 end;
 
 procedure hs_with_p as
 begin
  with tmp as (select * from company where id=3) select id into v_count from tmp;
  DBMS_OUTPUT.PUT_LINE(v_count);
 end;
 
 begin
  with tmp as (select * from company where id=4) select id into v_count from tmp;
  DBMS_OUTPUT.PUT_LINE(v_count);
end;
/

select hs_with_pkg.hs_with_f;
4
 hs_with_f 
-----------
         2
(1 row)

call hs_with_pkg.hs_with_p;
3
CALL