南大通用GBase 8s 嵌套表 返回方式简介

嵌套表是 GBase 8s 兼容模式下重要的集合数据类型，在业务迁移、集合类型开发中经常使用。创建嵌套表存储子表时，支持RETURN AS VALUE与RETURN AS LOCATOR两种返回模式，二者在数据传输、内存开销、查询表现、访问逻辑上差异明显，选型不当会带来性能或者使用上的问题。

本文结合实操样例，对比两种模式的核心差异，并提供完整可复现的测试 SQL，帮助开发人员理解、合理选择嵌套表返回方式。

嵌套表 return as value 与 return as locator 区别

默认行为：默认为 return as value return as value 返回实际值，return as locator 返回引用

在数据库中，嵌套表（nested table）是一种集合数据类型，可以存储零个或多个元素，这些元素可以是标量类型或对象类型。嵌套表可以通过两种主要方式返回给调用者：RETURN AS VALUE 和 RETURN AS LOCATOR。这两种方式在性能和功能上有一些重要的区别。

RETURN AS VALUE

• 内存占用：当使用 RETURN AS VALUE 时，嵌套表的数据会随调用返回的结果集一起传输。这意味着整个嵌套表的数据会被复制到调用者的内存空间中。
• 性能：由于数据需要在客户端和服务器之间传输，对于大型嵌套表，这种方式可能会消耗较多内存和带宽，影响性能。
• 用法：这种方式适用于嵌套表数据相对较小的情况，或者当嵌套表数据需要在客户端进行大量处理时。
• 访问方式：调用者可以直接访问和操作返回的嵌套表数据，无需额外的数据库访问。

RETURN AS LOCATOR

• 内存占用：RETURN AS LOCATOR 返回的是一个指向嵌套表数据的引用（locator），而不是实际数据本身。这意味着只有引用信息会被传输，而不是整个嵌套表的数据。
• 性能：由于只传输引用信息，这种方式通常比 RETURN AS VALUE 更节省内存和带宽。然而，当需要访问嵌套表数据时，数据库访问仍然需要发生，这可能引入额外的延迟。
• 用法：这种方式适用于嵌套表数据较大，或者当只需要访问嵌套表的一小部分数据时。通过引用，可以按需从数据库中检索数据。
• 访问方式：调用者需要通过引用（locator）来访问嵌套表数据，这通常涉及额外的数据库操作，如使用 DBMS_LOB 包或特定的数据库函数来检索数据。

总结

• RETURN AS VALUE：适用于小型嵌套表或需要在客户端进行大量处理的情况。数据随结果集一起返回，便于直接访问，但可能消耗较多内存和带宽。
• RETURN AS LOCATOR：适用于大型嵌套表或只需要访问部分数据的情况。返回的是数据引用，节省内存和带宽，但访问数据时需要额外的数据库操作。

实操用例

create database testdb with log;
set environment sqlmode 'oracle';

create type typ1 is table of int;

create table tmp_1(c1 typ1) nested table c1 store as ntmp_1;
create table tmp_2(c1 typ1) nested table c1 store as ntmp_2 return as locator;
info tables;

insert into tmp_1 values(typ1(1,3,5,7));
insert into tmp_2 values(typ1(1,3,5,7));

select * from tmp_1; --查询c1字段显示typ1(1,3,5,7)
select * from tmp_2; --查询c1字段显示（REFCURSOR）
select * from ntmp_1;
select * from ntmp_2;

drop table ntmp_1; --报错313
drop table tmp_1; --将tmp_1与ntmp_1全部删掉
info tables;
drop table ntmp_2; --报错313
info tables;
drop table tmp_2;

create table tmp_3(c1 typ1) nested table c1 store as ntmp_3 return as value; --同tmp_1
info tables;
insert into tmp_3 values(typ1(1,3,5,7));

select * from tmp_3;
select * from ntmp_3;

drop table tmp_3;
info tables;

drop type typ1;

执行现象说明：

1. RETURN AS VALUE：查询主表直接展示完整集合内容；
2. RETURN AS LOCATOR：查询主表字段返回(REFCURSOR)引用游标，不会直接吐出全部嵌套集合数据；
3. 嵌套存储子表（ntmp_1/ntmp_2/ntmp_3）不允许直接 drop，直接删除会报 313 错误；删除主表会连带清理对应的嵌套子表。

总结

1. 不写关键字时，嵌套表默认采用 RETURN AS VALUE；小集合场景优先使用默认模式，开发调试直观简单。
2. 嵌套表数据量很大，客户端不需要一次性读取全部集合内容，建议选用 RETURN AS LOCATOR，降低网络传输与客户端内存压力，但需要业务适配 locator 引用访问逻辑。
3. 嵌套生成的子存储表不能单独 drop，删除主表才会级联清理子表，运维不要尝试直接操作 ntmp 类嵌套子表。
4. 该特性依赖 Oracle 兼容模式，建库后需要执行set environment sqlmode 'oracle'开启。

大家在做 Oracle 迁移、嵌套表业务开发时，如果遇到查询返回 REFCURSOR、性能异常、313 删表报错等问题，欢迎在社区评论区交流讨论。