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 intastype 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;
BEGINFOR sys.rec IN (SELECT object_name,object_id,sql_text FROM V2_text) LOOPOPEN dynamic_cur FOR sys.rec.sql_text;    --error-- Perform cursor operationsfetch dynamic_cur bulk collect into rec_all_code;for i in 1..rec_all_code.count loopDBMS_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格式及以上时，受限于目前对变量的解析方式，报错。

DECLAREtype 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;
BEGINFOR sys.rec_emp.rec IN (SELECT object_name,object_id,sql_text FROM V2_text) LOOPNULL;END LOOP;
END;
/

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

1.1 Record类型如下：

DECLAREtype 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;
BEGINsys.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)
);DECLAREtype 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;
BEGINsys1.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');DECLAREtype 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;
BEGINFOR rec IN (SELECT object_name,object_id,sql_text FROM V2_text) LOOPOPEN dynamic_cur FOR rec.sql_text;-- Perform cursor operationsfetch dynamic_cur bulk collect into rec_all_code;for i in 1..rec_all_code.count loopDBMS_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');DECLAREtype 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;
BEGINFOR rec IN (SELECT object_name,object_id,sql_text FROM V2_text) LOOPOPEN dynamic_cur FOR rec.sql_text;-- Perform cursor operationsfetch dynamic_cur bulk collect into rec_all_code;for i in 1..rec_all_code.count loopDBMS_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
astype 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;
BEGINFOR rec IN (SELECT object_name,object_id,sql_text FROM V2_text) LOOPOPEN dynamic_cur FOR rec.sql_text;-- Perform cursor operationsfetch dynamic_cur bulk collect into rec_all_code;for i in 1..rec_all_code.count loopDBMS_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)
astype 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;
BEGINFOR rec IN (SELECT object_name,object_id,sql_text FROM V2_text) LOOPOPEN dynamic_cur FOR rec.sql_text;-- Perform cursor operationsfetch dynamic_cur bulk collect into rec_all_code;for i in 1..rec_all_code.count loopDBMS_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 isprocedure 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_cursorisprocedure p_rec_with_sql_for_cursor(id int) astype 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;BEGINFOR rec IN (SELECT object_name,object_id,sql_text FROM V2_text) LOOPOPEN dynamic_cur FOR rec.sql_text;-- Perform cursor operationsfetch dynamic_cur bulk collect into rec_all_code;for i in 1..rec_all_code.count loopDBMS_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 intastype 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;BEGINFOR rec IN (SELECT object_name,object_id,sql_text FROM V2_text) LOOPOPEN dynamic_cur FOR rec.sql_text;-- Perform cursor operationsfetch dynamic_cur bulk collect into rec_all_code;for i in 1..rec_all_code.count loopDBMS_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;