LightDB24.1 plorasql支持open dynamic_cur for rec.vc_dynamic_sql(rec record变量中包含vc_dynamic_sql变量）

背景

Oracle

oracle plsql支持如下场景：
在for循环中，将select查询的结果给一个record类型，这一操作也被称为隐式游标操作。record类型中一个字段用来接收查询结果中的一个select查询语句（update,delete,insert在这个语法中都会报错），这个字段被用作open for动态打开一个游标的对象。

这里需要注意oracle plsql在使用这个语法时有以下限制：
（1）FOR和IN中间的字段只能是A格式（即不包含A.B,A.B.C等以上格式）；
（2）单单是从OPEN FOR语法来考虑的话，FOR之后的动态语句的格式，支持多个类型进行嵌套；如下：

LIghtDB

在之前版本中，我们发现LightDB不支持上面描述的oracle plsql支持的场景。因此我们在plorasql中添加了for in语法对a.b格式解析的逻辑，在实现之后，支持只用一个record类型的调用逻辑，不能支持类型嵌套类型的这种调用方式，原因在于LightDB对a.b.c的解析方式的原因 。
针对oracle上面提示的内容，LightDB给出了相应的回答：
（1）FOR和IN和oracle不同，Oracle只支持a的格式，lightdb支持a.b的格式。
如下：

create or replace function f_rec_with_sql_for_cursor(id int) return int
    as
    type t_dynamic_cursor is ref cursor;
    dynamic_cur t_dynamic_cursor;
    type t_inter_code_table is table of varchar2(128);
    rec_all_code t_inter_code_table;
    type emp_rec_type is record
    (ename varchar2(25),
     sql_text varchar2(10),
     sal number(7,2)
    );
    type emp_record_type is record
    (ename varchar2(25),
     job varchar2(10),
     sal number(7,2),
     rec emp_rec_type
    );
    sys emp_record_type;
BEGIN
   FOR sys.rec IN (
      SELECT object_name,object_id,sql_text FROM V2_text
   ) LOOP
      OPEN dynamic_cur FOR sys.rec.sql_text;    --error
      -- Perform cursor operations
       fetch dynamic_cur bulk collect into rec_all_code;
        for i in 1..rec_all_code.count loop
           DBMS_OUTPUT.PUT_LINE('OBJECTNAME: ' ||rec_all_code(i));
        end loop;
      close dynamic_cur;
   END LOOP;
   return id;
END;
/

但是open 打开时受限于record（object类型相同）的使用，会报错。
FOR和IN中使用A.B.C格式及以上时，受限于目前对变量的解析方式，报错。

DECLARE
    type t_dynamic_cursor is ref cursor;
    dynamic_cur t_dynamic_cursor;
    type t_inter_code_table is table of varchar2(128);
    rec_all_code t_inter_code_table;
    type emp_rec_type is record
    (ename varchar2(25),
     sql_text varchar2(100),
     sal number(7,2)
    );

    type emp_record_type is record
    (ename varchar2(25),
     job varchar2(10),
     sal number(7,2),
     rec emp_rec_type
    );

    type empl_record_type is record
    (ename varchar2(25),
     job varchar2(10),
     sal number(7,2),
     rec_emp emp_record_type
    );
    sys empl_record_type;
BEGIN
   FOR sys.rec_emp.rec IN (
      SELECT object_name,object_id,sql_text FROM V2_text
   ) LOOP
    NULL;
   END LOOP;
END;
/

(2) 支持a.b类型的调用，类型（包括record、object类型相同）中涉及到嵌套的使用场景不支持；

1.1 Record类型如下：

DECLARE
    type t_dynamic_cursor is ref cursor;
    dynamic_cur t_dynamic_cursor;
    type t_inter_code_table is table of varchar2(128);
    rec_all_code t_inter_code_table;
    type emp_rec_type is record
    (ename varchar2(25),
     sql_text varchar2(100),
     sal number(7,2)
    );

    type emp_record_type is record
    (ename varchar2(25),
     job varchar2(10),
     sal number(7,2),
     rec emp_rec_type
    );

    type empl_record_type is record
    (ename varchar2(25),
     job varchar2(10),
     sal number(7,2),
     rec_emp emp_record_type
    );
    sys empl_record_type;
BEGIN
    sys.rec_emp.rec.sql_text := 'SELECT job_id FROM employees WHERE department_id = 10';
    OPEN dynamic_cur FOR sys.rec_emp.rec.sql_text;
    close dynamic_cur;
END;
/

这个测试用例会报错，sys.rec_emp.rec.sql_text不支持，open for动态操作只支持格式a.b。

1.2 Object类型如下：

CREATE TYPE Book_t1 AS OBJECT (
    sql_text varchar(100)
);

DECLARE
    type t_dynamic_cursor is ref cursor;
    dynamic_cur t_dynamic_cursor;
    type t_inter_code_table is table of varchar2(128);
    rec_all_code t_inter_code_table;
    type emp_record_type is record
    (ename varchar2(25),
     job varchar2(10),
     sal number(7,2),
     rec Book_t1
    );
    sys1 emp_record_type;
BEGIN
	sys1.rec := Book_t1('SELECT job_id FROM employees WHERE department_id = 10');
	OPEN dynamic_cur FOR sys1.rec.sql_text;
END;
/

上面使用的表信息详见测试部分。

测试

---------------- When testing an open cursor, a field of record is dynamically executed when the SQL is executed. ----------------
select dbms_output.serveroutput('t');

create table employees(salary int,
    job_id varchar2(100), employee_id int,
    last_name varchar(100), first_name varchar(100),
    department_id int, hire_date date);

create table V2_text (
    object_name VARCHAR2(40),
    object_id VARCHAR2(8),
    sql_text VARCHAR2(1000)
);

create table orders (id int,org_id int,user_id int
    ,order_id int,customer_id int, order_date date, price int
    ,primary key (org_id, id),status varchar(20));

insert into employees values (12308, 'SA_REP', 100, 'huaxiang', 'zhao', 10, TO_DATE('31-DEC-2005', 'DD-MON-YYYY'));
insert into employees values (12301, 'AD_FG', 12301, 'huahai', 'zhao', 10, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
insert into employees values (12302, 'SA_REP', 12302, 'huaduo', 'zhao', 10, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));

insert into orders values (12301, 100, 2346, 456043, 23490, TO_DATE('31-DEC-2005', 'DD-MON-YYYY'), 2000, 'Completed');

---------------- Normal test ----------------
-- select
insert into V2_text (object_name, object_id, sql_text)
values ('EmployeeTable', 'OBJ0001', 'SELECT job_id FROM employees WHERE department_id = 10');

DECLARE
    type t_dynamic_cursor is ref cursor;
    dynamic_cur t_dynamic_cursor;
    type t_inter_code_table is table of varchar2(128);
    rec_all_code t_inter_code_table;
BEGIN
   FOR rec IN (
      SELECT object_name,object_id,sql_text FROM V2_text
   ) LOOP
      OPEN dynamic_cur FOR rec.sql_text;
      -- Perform cursor operations
       fetch dynamic_cur bulk collect into rec_all_code;
        for i in 1..rec_all_code.count loop
           DBMS_OUTPUT.PUT_LINE('OBJECTNAME: ' ||rec_all_code(i));
        end loop;
      close dynamic_cur;
   END LOOP;
END;
/

delete from V2_text;

-- with selete
insert into V2_text (object_name, object_id, sql_text)
values ('OrderTable', 'OBJ0002', 'with empl as (SELECT job_id FROM employees WHERE department_id = 10) select * from empl');

DECLARE
    type t_dynamic_cursor is ref cursor;
    dynamic_cur t_dynamic_cursor;
    type t_inter_code_table is table of varchar2(128);
    rec_all_code t_inter_code_table;
BEGIN
   FOR rec IN (
      SELECT object_name,object_id,sql_text FROM V2_text
   ) LOOP
      OPEN dynamic_cur FOR rec.sql_text;
      -- Perform cursor operations
       fetch dynamic_cur bulk collect into rec_all_code;
        for i in 1..rec_all_code.count loop
           DBMS_OUTPUT.PUT_LINE('OBJECTNAME: ' ||rec_all_code(i));
        end loop;
      close dynamic_cur;
   END LOOP;
END;
/

-- function
create or replace function f_rec_with_sql_for_cursor(id int) return int
as
    type t_dynamic_cursor is ref cursor;
    dynamic_cur t_dynamic_cursor;
    type t_inter_code_table is table of varchar2(128);
    rec_all_code t_inter_code_table;
BEGIN
   FOR rec IN (
      SELECT object_name,object_id,sql_text FROM V2_text
   ) LOOP
      OPEN dynamic_cur FOR rec.sql_text;
      -- Perform cursor operations
       fetch dynamic_cur bulk collect into rec_all_code;
        for i in 1..rec_all_code.count loop
           DBMS_OUTPUT.PUT_LINE('OBJECTNAME: ' ||rec_all_code(i));
        end loop;
      close dynamic_cur;
   END LOOP;
   return id;
END;
/

select f_rec_with_sql_for_cursor(1) from dual;

drop function f_rec_with_sql_for_cursor;
-- procedure
create or replace procedure p_rec_with_sql_for_cursor(id int)
as
    type t_dynamic_cursor is ref cursor;
    dynamic_cur t_dynamic_cursor;
    type t_inter_code_table is table of varchar2(128);
    rec_all_code t_inter_code_table;
BEGIN
   FOR rec IN (
      SELECT object_name,object_id,sql_text FROM V2_text
   ) LOOP
      OPEN dynamic_cur FOR rec.sql_text;
      -- Perform cursor operations
       fetch dynamic_cur bulk collect into rec_all_code;
        for i in 1..rec_all_code.count loop
           DBMS_OUTPUT.PUT_LINE('OBJECTNAME: ' ||rec_all_code(i));
        end loop;
      close dynamic_cur;
   END LOOP;
END;
/

call p_rec_with_sql_for_cursor(1);

drop procedure p_rec_with_sql_for_cursor;
-- package
create or replace package pkg_rec_with_sql_for_cursor is
    procedure p_rec_with_sql_for_cursor(id int);
    function f_rec_with_sql_for_cursor(id int) return int;
end;
/

-- package body
create or replace package body pkg_rec_with_sql_for_cursor
    is
    procedure p_rec_with_sql_for_cursor(id int) as
        type t_dynamic_cursor is ref cursor;
        dynamic_cur t_dynamic_cursor;
        type t_inter_code_table is table of varchar2(128);
        rec_all_code t_inter_code_table;
    BEGIN
        FOR rec IN (
            SELECT object_name,object_id,sql_text FROM V2_text
        ) LOOP
            OPEN dynamic_cur FOR rec.sql_text;
            -- Perform cursor operations
            fetch dynamic_cur bulk collect into rec_all_code;
                for i in 1..rec_all_code.count loop
                DBMS_OUTPUT.PUT_LINE('OBJECTNAME: ' ||rec_all_code(i));
                end loop;
            close dynamic_cur;
        END LOOP;
    END;

    function f_rec_with_sql_for_cursor(id int) return int
    as
        type t_dynamic_cursor is ref cursor;
        dynamic_cur t_dynamic_cursor;
        type t_inter_code_table is table of varchar2(128);
        rec_all_code t_inter_code_table;
    BEGIN
        FOR rec IN (
            SELECT object_name,object_id,sql_text FROM V2_text
        ) LOOP
            OPEN dynamic_cur FOR rec.sql_text;
            -- Perform cursor operations
            fetch dynamic_cur bulk collect into rec_all_code;
                for i in 1..rec_all_code.count loop
                DBMS_OUTPUT.PUT_LINE('OBJECTNAME: ' ||rec_all_code(i));
                end loop;
            close dynamic_cur;
        END LOOP;
        return id;
    END;
END;
/

select pkg_rec_with_sql_for_cursor.f_rec_with_sql_for_cursor(1) from dual;
call pkg_rec_with_sql_for_cursor.p_rec_with_sql_for_cursor(1);
drop package body pkg_rec_with_sql_for_cursor;
drop package pkg_rec_with_sql_for_cursor;