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。

相关文章

pycharm远程连接服务器运行pytorch的过程详解

pycharm远程连接服务器运行pytorch的过程详解

《pycharm远程连接服务器运行pytorch的过程详解》：本文主要介绍在Linux环境下使用Anaconda管理不同版本的Python环境,并通过PyCharm远程连接服务器来运行PyTorc... 目录linux部署pytorch背景介绍Anaconda安装Linux安装pytorch虚拟环境安装cu
阅读更多...
SpringBoot项目注入 traceId 追踪整个请求的日志链路(过程详解)

SpringBoot项目注入 traceId 追踪整个请求的日志链路(过程详解)

《SpringBoot项目注入traceId追踪整个请求的日志链路(过程详解)》本文介绍了如何在单体SpringBoot项目中通过手动实现过滤器或拦截器来注入traceId,以追踪整个请求的日志链... SpringBoot项目注入 traceId 来追踪整个请求的日志链路，有了 traceId， 我们在排
阅读更多...
Spring Boot 3 整合 Spring Cloud Gateway实践过程

Spring Boot 3 整合 Spring Cloud Gateway实践过程

《SpringBoot3整合SpringCloudGateway实践过程》本文介绍了如何使用SpringCloudAlibaba2023.0.0.0版本构建一个微服务网关,包括统一路由、限... 目录引子为什么需要微服务网关实践1.统一路由2.限流防刷3.登录鉴权小结引子当前微服务架构已成为中大型系统的标
阅读更多...
Java中对象的创建和销毁过程详析

Java中对象的创建和销毁过程详析

《Java中对象的创建和销毁过程详析》：本文主要介绍Java中对象的创建和销毁过程,对象的创建过程包括类加载检查、内存分配、初始化零值内存、设置对象头和执行init方法,对象的销毁过程由垃圾回收机... 目录前言对象的创建过程1. 类加载检查2China编程. 分配内存3. 初始化零值4. 设置对象头5. 执行
阅读更多...
SpringBoot整合easy-es的详细过程

SpringBoot整合easy-es的详细过程

《SpringBoot整合easy-es的详细过程》本文介绍了EasyES,一个基于Elasticsearch的ORM框架,旨在简化开发流程并提高效率,EasyES支持SpringBoot框架,并提供... 目录一、easy-es简介二、实现基于Spring Boot框架的应用程序代码1.添加相关依赖2.添
阅读更多...
SpringBoot中整合RabbitMQ(测试+部署上线最新完整)的过程

SpringBoot中整合RabbitMQ(测试+部署上线最新完整)的过程

《SpringBoot中整合RabbitMQ(测试+部署上线最新完整)的过程》本文详细介绍了如何在虚拟机和宝塔面板中安装RabbitMQ,并使用Java代码实现消息的发送和接收,通过异步通讯,可以优化... 目录一、RabbitMQ安装二、启动RabbitMQ三、javascript编写Java代码1、引入
阅读更多...
JavaScript中的reduce方法执行过程、使用场景及进阶用法

JavaScript中的reduce方法执行过程、使用场景及进阶用法

《JavaScript中的reduce方法执行过程、使用场景及进阶用法》：本文主要介绍JavaScript中的reduce方法执行过程、使用场景及进阶用法的相关资料,reduce是JavaScri... 目录1. 什么是reduce2. reduce语法2.1 语法2.2 参数说明3. reduce执行过程
阅读更多...
定价129元!支持双频 Wi-Fi 5的华为AX1路由器发布

定价129元!支持双频 Wi-Fi 5的华为AX1路由器发布

《定价129元!支持双频Wi-Fi5的华为AX1路由器发布》华为上周推出了其最新的入门级Wi-Fi5路由器——华为路由AX1，建议零售价129元，这款路由器配置如何？详细请看下文介... 华为 Wi-Fi 5 路由 AX1 已正式开售，新品支持双频 1200 兆、配有四个千兆网口、提供可视化智能诊断功能，建
阅读更多...
redis群集简单部署过程

redis群集简单部署过程

《redis群集简单部署过程》文章介绍了Redis,一个高性能的键值存储系统,其支持多种数据结构和命令,它还讨论了Redis的服务器端架构、数据存储和获取、协议和命令、高可用性方案、缓存机制以及监控和... 目录Redis介绍1. 基本概念2. 服务器端3. 存储和获取数据4. 协议和命令5. 高可用性6.
阅读更多...
PLsql Oracle 下载安装图文过程详解

PLsql Oracle 下载安装图文过程详解

《PLsqlOracle下载安装图文过程详解》PL/SQLDeveloper是一款用于开发Oracle数据库的集成开发环境,可以通过官网下载安装配置,并通过配置tnsnames.ora文件及环境变... 目录一、PL/SQL Developer 简介二、PL/SQL Developer 安装及配置详解1.下
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2