LightDB24.1 存储过程支持inner和outer对变量的引用

2024-03-21 04:12
文章标签 引用 支持 过程 存储 变量 inner outer lightdb24.1

本文主要是介绍LightDB24.1 存储过程支持inner和outer对变量的引用，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

背景

Oracle

oracle plsql支持如下场景：
在for循环中，将select查询的结果给一个record类型，这一操作也被称为隐式游标操作。record类型中一个字段用来接收查询结果中的一个select查询语句（update,delete,insert在这个语法中都会报错），这个字段被用作open for动态打开一个游标的对象。这个rec变量可以为关键字，在业务适配的过程中，我们发现了这一点，客户现场使用的关键字有两个outer和inner。
test1

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对变量的引用的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/831728。

相关文章

使用SpringBoot+InfluxDB实现高效数据存储与查询

使用SpringBoot+InfluxDB实现高效数据存储与查询

《使用SpringBoot+InfluxDB实现高效数据存储与查询》InfluxDB是一个开源的时间序列数据库,特别适合处理带有时间戳的监控数据、指标数据等,下面详细介绍如何在SpringBoot项目... 目录1、项目介绍2、 InfluxDB 介绍3、Spring Boot 配置 InfluxDB4、I
阅读更多...
AOP编程的基本概念与idea编辑器的配合体验过程

AOP编程的基本概念与idea编辑器的配合体验过程

《AOP编程的基本概念与idea编辑器的配合体验过程》文章简要介绍了AOP基础概念,包括Before/Around通知、PointCut切入点、Advice通知体、JoinPoint连接点等,说明它们... 目录BeforeAroundAdvise — 通知PointCut — 切入点Acpect — 切面
阅读更多...
C++ STL-string类底层实现过程

C++ STL-string类底层实现过程

《C++STL-string类底层实现过程》本文实现了一个简易的string类,涵盖动态数组存储、深拷贝机制、迭代器支持、容量调整、字符串修改、运算符重载等功能,模拟标准string核心特性,重点强... 目录实现框架一、默认成员函数1.默认构造函数2.构造函数3.拷贝构造函数(重点)4.赋值运算符重载函数
阅读更多...
MySQ中出现幻读问题的解决过程

MySQ中出现幻读问题的解决过程

《MySQ中出现幻读问题的解决过程》文章解析MySQLInnoDB通过MVCC与间隙锁机制在可重复读隔离级别下解决幻读,确保事务一致性,同时指出性能影响及乐观锁等替代方案,帮助开发者优化数据库应用... 目录一、幻读的准确定义与核心特征幻读 vs 不可重复读二、mysql隔离级别深度解析各隔离级别的实现差异
阅读更多...
Nginx添加内置模块过程

Nginx添加内置模块过程

《Nginx添加内置模块过程》文章指导如何检查并添加Nginx的with-http_gzip_static模块：确认该模块未默认安装后,需下载同版本源码重新编译,备份替换原有二进制文件,最后重启服务验... 目录1、查看Nginx已编辑的模块2、Nginx官网查看内置模块3、停止Nginx服务4、Nginx
阅读更多...
Jenkins的安装与简单配置过程

Jenkins的安装与简单配置过程

《Jenkins的安装与简单配置过程》本文简述Jenkins在CentOS7.3上安装流程,包括Java环境配置、RPM包安装、修改JENKINS_HOME路径及权限、启动服务、插件安装与系统管理设置... 目录www.chinasem.cnJenkins安装访问并配置JenkinsJenkins配置邮件通知
阅读更多...
Conda国内镜像源及配置过程

Conda国内镜像源及配置过程

《Conda国内镜像源及配置过程》文章介绍Conda镜像源使用方法,涵盖临时指定单个/多个源、永久配置及恢复默认设置,同时说明main（官方稳定）、free（逐渐弃用）、conda-forge（社区更... 目录一、Conda国内镜像源二、Conda临时使用镜像源指定单个源临时指定多个源创建环境时临时指定源
阅读更多...
mybatisplus的逻辑删除过程

mybatisplus的逻辑删除过程

《mybatisplus的逻辑删除过程》：本文主要介绍mybatisplus的逻辑删除过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录myBATisplus的逻辑删除1、在配置文件中添加逻辑删除的字段2、在实体类上加上@TableLogic3、业务层正常删除即
阅读更多...
通过配置nginx访问服务器静态资源的过程

通过配置nginx访问服务器静态资源的过程

《通过配置nginx访问服务器静态资源的过程》文章介绍了图片存储路径设置、Nginx服务器配置及通过http://192.168.206.170:8007/a.png访问图片的方法,涵盖图片管理与服务... 目录1.图片存储路径2.nginx配置3.访问图片方式总结1.图片存储路径2.nginx配置
阅读更多...
MySQL连表查询之笛卡尔积查询的详细过程讲解

MySQL连表查询之笛卡尔积查询的详细过程讲解

《MySQL连表查询之笛卡尔积查询的详细过程讲解》在使用MySQL或任何关系型数据库进行多表查询时,如果连接条件设置不当,就可能发生所谓的笛卡尔积现象,：本文主要介绍MySQL连表查询之笛卡尔积查... 目录一、笛卡尔积的数学本质二、mysql中的实现机制1. 显式语法2. 隐式语法3. 执行原理（以Nes
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2