本文主要是介绍LightDB24.1 存储过程支持inner和outer对变量的引用,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
背景
Oracle
oracle plsql支持如下场景:
在for循环中,将select查询的结果给一个record类型,这一操作也被称为隐式游标操作。record类型中一个字段用来接收查询结果中的一个select查询语句(update,delete,insert在这个语法中都会报错),这个字段被用作open for动态打开一个游标的对象。这个rec变量可以为关键字,在业务适配的过程中,我们发现了这一点,客户现场使用的关键字有两个outer和inner。
LIghtDB
在之前版本中,我们发现LightDB不支持上面描述的oracle plsql支持的场景,通常在内核语法解析就直接报错。经调查发现,
(1)inner和outer都是TYPE_FUNC_NAME_KEYWORD关键字,
(2)在gram.y没有对未保留关键字的解析。
因此我们需要在内核去实现这两步。但是由于inner还有一重含义,就是内联。如果表名、别名能够出现inner,会导致语法解析的时候产生已经冲突。异常在与oracle的表现上会呈现以下几点区别:
1、类型名(具体指联合数组、可变数组和嵌套表)和for循环变量的名字相同时,报错
declaretype outer is varray(1) of number;v_busin_array1 outer := outer();CURSOR c_a IS select * from record_filed;zqdm_p record_filed.id%TYPE;
begin
FOR outer IN c_a LOOPzqdm_p := outer.zqdm;
end loop;
end;
/declaretype outer is varray(1) of number;v_busin_array1 outer := outer();CURSOR c_a IS select * from record_filed;zqdm_p record_filed.id%TYPE;
begin
FOR outer IN c_a LOOPzqdm_p := outer.zqdm;
end loop;
end;
/
2、已创建outer同名的对象后,不允许再创建outer类型
create table outer(outer int);
create view outer as select * from dual;
create domain outer as char(100);
drop type outer;
3、OUTER为非保留关键字,可以作为表名、列名、函数名、类型名等,INNER为函数名类型名关键字,只能作为函数名;
测试
--------------------------lightdb add at 2024/03/19 for S202312144054----------------------------
--------------------------test of outer---------------------------------
create table outer(outer int);
select * from outer;
create table select_outer_test(id int);
select outer.id as outer from select_outer_test as outer where outer.id > 1;
update select_outer_test outer set outer.id = 1;
with outer as (select outer.id as outer from select_outer_test as outer) select outer from outer;
drop table outer;
drop table select_outer_test;--If an outbound table has already been created and the outer type is created, an error is reported.
create domain outer as char(100);
drop type outer;create type outer as (id int);
drop type outer;create table record_filed(id int, zqdm varchar(100));
insert into record_filed values(12,'hello world!');-- create a custom type...
-- error
CREATE TYPE outer AS object (i integer,i2 integer,member function outer() return varchar,member procedure outer()
);CREATE TYPE outer AS object (i integer,i2 integer,member function outer1() return varchar,member procedure outer2()
);drop type outer;CREATE TYPE test_object_type AS object (i integer,i2 integer,member function outer() return varchar
);--error
CREATE TYPE test_object_type AS object (i integer,i2 integer,member function outer() return varchar,member procedure outer()
);CREATE TYPE BODY test_object_type ASmember function outer() return varchar asbeginreturn '(' || self.i || ',' || self.i2 || ')';end;END;
/declareind test_object_type;ind_func int;
beginind_func := ind.outer();
end;
/CREATE or replace TYPE BODY test_object_type ASmember function outer() return varchar asCURSOR c_a ISselect * from record_filed;zqdm_p record_filed.id%TYPE;BEGINFOR outer IN c_a LOOPzqdm_p := outer.id;end loop;return 1;end;
END;
/declareind test_object_type;ind_func int;
beginind_func := ind.outer();
end;
/--error
CREATE TYPE BODY test_object_type ASmember function outer() return varchar asouter int;beginreturn '(' || self.i || ',' || self.i2 || ')';end;
END;
/drop type test_object_type;-- test of anonymous block, function, procedure, package
--outer
--anonymous block
DECLARECURSOR c_a ISselect * from record_filed;zqdm_p record_filed.id%TYPE;
BEGIN
FOR outer IN c_a LOOPzqdm_p := outer.id;
end loop;
end;
/DECLARECURSOR c_a ISselect * from record_filed;zqdm_p record_filed.id%TYPE;
BEGIN
execute immediate 'create table outer(outer int)';
FOR outer IN c_a LOOPzqdm_p := outer.id;
end loop;
end;
/DECLARECURSOR c_a ISselect * from record_filed;zqdm_p record_filed.id%TYPE;
BEGIN
execute immediate 'drop table outer';
execute immediate 'DECLARECURSOR c_a ISselect * from record_filed;zqdm_p record_filed.id%TYPE;
BEGIN
FOR outer IN c_a LOOPzqdm_p := outer.id;
end loop;
end;';
FOR outer IN c_a LOOPzqdm_p := outer.id;
end loop;
end;
/--record
select dbms_output.serveroutput(true);declare
type outer is record (a1 varchar2(1000) not null,a2 NVARCHAR2(1000),a3 VARCHAR(1000),a4 CHAR(1000),a5 NCHAR(1000),b1 NUMBER(8,2),b2 FLOAT,b3 BINARY_FLOAT,b4 BINARY_DOUBLE,c long,d1 rowid,d2 raw,e1 DATE,e2 TIMESTAMP,e3 TIMESTAMP WITH TIME ZONE,e5 INTERVAL YEAR TO MONTH,e6 INTERVAL DAY TO SECOND,f1 blob,f2 clob
);CURSOR c_a IS select * from record_filed;zqdm_p record_filed.id%TYPE;rec_pool outer;
begin
FOR outer IN c_a LOOPzqdm_p := outer.id;dbms_output.put_line(zqdm_p);
end loop;
end;
/
select dbms_output.serveroutput(false);
--varray
declaretype outer is varray(1) of number;v_busin_array1 outer := outer();CURSOR c_a IS select * from record_filed;zqdm_p record_filed.id%TYPE;
begin
FOR outer IN c_a LOOPzqdm_p := outer.id;
end loop;
end;
/--Associative Arrays
DECLAREtype outer is table of number index by varchar2(4);CURSOR c_a ISselect * from record_filed;zqdm_p record_filed.id%TYPE;
BEGIN
FOR outer IN c_a LOOPzqdm_p := outer.id;
end loop;
end;
/--Nested Tables
DECLARECURSOR c_a ISselect * from record_filed;type outer is table of number;zqdm_p record_filed.id%TYPE;
BEGIN
FOR outer IN c_a LOOPzqdm_p := outer.id;
end loop;
end;
/DECLAREouter int;CURSOR c_a ISselect * from record_filed;zqdm_p record_filed.id%TYPE;
BEGINouter := 12;FOR outer IN c_a LOOPzqdm_p := outer.id;end loop;
end;
/
---------------------------------------test of inner-----------------------------------
create table inner(inner int);
select * from inner;
create table select_inner_test(id int);
select inner.id as inner from select_inner_test as inner where inner.id > 1;
update select_inner_test inner set inner.id = 1;
with inner as (select inner.id as inner from select_inner_test as inner) select inner from inner;
drop table inner;
drop table select_inner_test;--If an outbound table has already been created and the inner type is created, an error is reported.
create domain inner as char(100);
drop type inner;create type inner as (id int);
drop type inner;create table record_filed(id int, zqdm varchar(100));
insert into record_filed values(12,'hello world!');-- create a custom type...
-- error
CREATE TYPE inner AS object (i integer,i2 integer,member function inner() return varchar,member procedure inner()
);CREATE TYPE inner AS object (i integer,i2 integer,member function inner1() return varchar,member procedure inner2()
);drop type inner;--error
CREATE TYPE test_object_type AS object (i integer,i2 integer,member function inner() return varchar,member procedure inner()
);CREATE TYPE test_object_type AS object (i integer,i2 integer,member function inner() return varchar
);CREATE TYPE BODY test_object_type ASmember function inner() return varchar asbeginreturn '(' || self.i || ',' || self.i2 || ')';end;END;
/--error
CREATE TYPE BODY test_object_type ASmember function inner() return varchar asinner int;beginreturn '(' || self.i || ',' || self.i2 || ')';end;
END;
/CREATE or replace TYPE BODY test_object_type ASmember function inner() return varchar asCURSOR c_a ISselect * from record_filed;zqdm_p record_filed.id%TYPE;BEGINFOR inner IN c_a LOOPzqdm_p := inner.id;end loop;return 1;end;
END;
/declareind test_object_type;ind_func int;
beginind_func := ind.inner();
end;
/drop type test_object_type;-- test of anonymous block, function, procedure, package
--inner
--anonymous block
DECLARECURSOR c_a ISselect * from record_filed;zqdm_p record_filed.id%TYPE;
BEGIN
FOR inner IN c_a LOOPzqdm_p := inner.id;
end loop;
end;
/--record
select dbms_output.serveroutput(true);declare
type inner is record (a1 varchar2(1000) not null,a2 NVARCHAR2(1000),a3 VARCHAR(1000),a4 CHAR(1000),a5 NCHAR(1000),b1 NUMBER(8,2),b2 FLOAT,b3 BINARY_FLOAT,b4 BINARY_DOUBLE,c long,d1 rowid,d2 raw,e1 DATE,e2 TIMESTAMP,e3 TIMESTAMP WITH TIME ZONE,e5 INTERVAL YEAR TO MONTH,e6 INTERVAL DAY TO SECOND,f1 blob,f2 clob
);CURSOR c_a IS select * from record_filed;zqdm_p record_filed.id%TYPE;rec_pool inner;
begin
FOR inner IN c_a LOOPzqdm_p := inner.id;dbms_output.put_line(zqdm_p);
end loop;
end;
/
select dbms_output.serveroutput(false);
--varray
declaretype inner is varray(1) of number;v_busin_array1 inner := inner();CURSOR c_a IS select * from record_filed;zqdm_p record_filed.id%TYPE;
begin
FOR inner IN c_a LOOPzqdm_p := inner.id;
end loop;
end;
/--Associative Arrays
DECLAREtype inner is table of number index by varchar2(4);CURSOR c_a ISselect * from record_filed;zqdm_p record_filed.id%TYPE;
BEGIN
FOR inner IN c_a LOOPzqdm_p := inner.id;
end loop;
end;
/--Nested Tables
DECLARECURSOR c_a ISselect * from record_filed;type inner is table of number;zqdm_p record_filed.id%TYPE;
BEGIN
FOR inner IN c_a LOOPzqdm_p := inner.id;
end loop;
end;
/DECLAREinner int;CURSOR c_a ISselect * from record_filed;zqdm_p record_filed.id%TYPE;
BEGINinner := 12;FOR inner IN c_a LOOPzqdm_p := inner.id;end loop;
end;
/
这篇关于LightDB24.1 存储过程支持inner和outer对变量的引用的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!