Oracle 原理: 集合数组,集合属性,is table of 用法,bulk collect into,IS RECORD用法

数组分为固定长度数组和可变长度数组,都是集合,且数组的起始下标是从1开始的,这和其他高级程序语言不太一样。利用圆括号‘(下标)‘来获取值或者进行赋值。

一、固定长度的数组

固定长度的数组类型的声明 为 TYPE 类名 IS VARRAY(元素个数) OF  元素类型;

declare
type arry3 is varray(3) of varchar2(4);   ---固定数组类型声明
sexsList arry3 :=arry3('男','女','人妖'); --固定数组变量定义
usr_input number;                  
begin
  usr_input := &sexnum; --读取用户输入
  sexsList(3) :='未知';
  if usr_input <=sexsList.count and usr_input >0 then
  dbms_output.put_line('你好'||sexsList(usr_input));  --用变量名(下标) 的形式赋值 := 或者读取
  end if;
end ;
/

执行后输入:3 输出得

二、数组集合 count ,first ,last ,next() ,exists()的区别


declare
 type varr_num is table of number INDEX BY PLS_INTEGER;
 vnum varr_num;
 vn number ;
begin
 vnum(2) := 2000;
 vnum(3) 
<think>我们面对的是Oracle数据库,用户的需求是在存储过程中将两个表的查询结果合并存入一个数组变量。根据引用[1]和引用[3],我们可以使用Oracle集合类型(如嵌套表或VARRAY)来存储结果。但是,两个表的查询结果如何合并?这里需要明确:1.如果两个表的结构相同(即列的数量和类型一致),那么可以直接使用UNIONALL将两个查询合并,然后将整个结果存入一个数组变量。2.如果两个表结构不同,那么我们需要考虑如何将不同结构的数据存入同一个数组?通常,我们可以定义一个对象类型(OBJECTTYPE)来容纳两个表的不同列,或者只取每个表中我们需要的列(且类型兼容),然后使用这个对象类型的集合。然而,用户的问题描述中并没有明确两个表的结构是否相同。因此,我们需要提供两种情况的解决方案。另外,引用[1]提到了使用WMSYS.WM_CONCAT进行列合并,但这里我们是要将两个表的结果存入数组,而不是合并成一列。所以,我们考虑使用PL/SQL的集合类型。步骤:1.定义记录类型(RECORD)或对象类型(OBJECTTYPE)来存储一行数据。2.定义该类型的嵌套表(或VARRAY)。3.使用BULK COLLECT将查询结果直接存入嵌套表变量。但是,如果两个表结构不同,我们想要合并到一个数组中,那么我们必须保证这个数组的元素类型能够容纳两个表的数据。我们可以采取以下方法:-为两个表分别定义两个不同的数组,然后通过一个数组来组合(比如数组的元素是另一个记录类型,包含两个表的数据?这并不直接)。-或者,我们只选择两个表中我们关心的列,并且这些列的类型要一致,然后使用UNION ALL合并查询,再存入数组。这里,我们假设用户希望将两个表的所有行都放入同一个数组中,且两个表结构相同(这是常见情况)。如果结构不同,我们可能需要调整。因此,我们提供两种方案:方案一:两个表结构相同方案二:两个表结构不同,但我们可以将两个表的部分列(类型兼容)合并到一个数组中。如果用户需要将两个表的所有列都放入数组,且结构不同,那么可能需要使用两个数组变量,或者定义一个包含两个表所有列的对象类型(但这样每个元素中会有很多列可能为空),这通常不是好做法。我们重点解决结构相同的情况,并简要说明结构不同的处理。另外,引用[3]提到存储过程的性能优化,我们可以使用BULKCOLLECT来一次性获取所有数据,提高效率。具体步骤(以结构相同为例):1.定义一个与表结构匹配的记录类型。2.定义该记录类型的嵌套表类型。3.声明一个该嵌套表类型的变量。4.使用SELECT...BULK COLLECT INTO将两个表的合并查询结果存入该变量。示例代码(假设两个表都有id和name两列,且类型相同):```sqlCREATE ORREPLACE PROCEDURE merge_tables_into_arrayAS--定义记录类型TYPErec_typeIS RECORD (idtable1.id%TYPE,name table1.name%TYPE);--定义嵌套表类型TYPE tbl_typeIS TABLEOF rec_type;--声明嵌套表变量v_datatbl_type; BEGIN--使用UNION ALL合并两个表并批量收集到数组SELECT id, nameBULK COLLECT INTOv_dataFROM (SELECTid,name FROMtable1UNION ALLSELECT id, nameFROM table2);--示例:遍历数组并输出(可选)FOR iIN1..v_data.COUNTLOOPDBMS_OUTPUT.PUT_LINE('ID: '|| v_data(i).id|| ',Name:' ||v_data(i).name);ENDLOOP; END; /```如果两个表结构不同,但我们需要将两个表的不同列合并到一个数组中,那么我们需要重新定义一个记录类型来包含两个表的所有列(或者我们需要的列),然后分别查询两个表,分别收集到两个数组,再合并到一个数组?但PL/SQL中嵌套表不支持直接合并(可以使用集合操作函数,如MULTISET UNION)。例如:假设表1有id,name,表2有code, description,我们想合并到一个数组数组元素类型为包含id,name,code,description的记录类型。这样,每个元素中,来自表1的记录其code和description为空,来自表2的记录其id和name为空。示例:```sqlCREATEOR REPLACEPROCEDUREmerge_different_tables AS--定义记录类型,包含两个表的所有列TYPE rec_type ISRECORD(idtable1.id%TYPE,nametable1.name%TYPE,codetable2.code%TYPE,description table2.description%TYPE);TYPE tbl_type ISTABLE OFrec_type;v_datatbl_type :=tbl_type(); --初始化为空v_temp1 tbl_type;v_temp2tbl_type; BEGIN--查询第一个表SELECTid,name,CAST(NULLAS VARCHAR2(10))AS code, CAST(NULL ASVARCHAR2(100)) ASdescriptionBULKCOLLECTINTO v_temp1FROM table1;--查询第二个表SELECT CAST(NULL ASNUMBER)AS id, CAST(NULL ASVARCHAR2(50)) ASname,code,descriptionBULKCOLLECTINTO v_temp2FROM table2;--使用MULTISET UNION将两个集合合并v_data:= v_temp1MULTISET UNIONALL v_temp2;--遍历输出FOR iIN1..v_data.COUNTLOOPDBMS_OUTPUT.PUT_LINE('ID: '|| NVL(TO_CHAR(v_data(i).id), 'NULL')||', Name: '|| NVL(v_data(i).name, 'NULL')||', Code: '|| NVL(v_data(i).code, 'NULL')||', Description: '|| NVL(v_data(i).description, 'NULL'));ENDLOOP; END; /```注意:在第二个示例中,我们为每个查询添加了NULL值来填充另一个表的字段,并且使用CAST来确保类型匹配。然后使用MULTISET UNIONALL合并两个集合。但是,这种方法在表结构差异大时会导致记录类型变得庞大,且效率可能较低(需要两次查询和一次合并)。因此,如果不需要同时处理两个表的所有列,建议使用两个单独的数组变量。根据用户需求,我们提供了两种情况的解决方案。另外,引用[3]提到了存储过程的性能优化,使用BULK COLLECT可以减少上下文切换,提高性能。最后,注意数组的大小。如果两个表的数据量很大,可能会导致内存溢出。可以使用LIMIT子句分批次处理,但这里用户要求存入一个数组变量,所以如果数据量极大,可能需要考虑其他方案(如临时表)。引用[1]中提到的WMSYS.WM_CONCAT并不适用于此场景,因为它是将多行合并成一个字符串,而不是数组。因此,我们推荐使用BULKCOLLECT和嵌套表集合类型。总结步骤:1.根据需求定义记录类型(如果两个表结构相同,则记录类型与表结构一致;如果不同,则定义包含所有所需字段的记录类型)。2.定义该记录类型的嵌套表类型。3.声明嵌套表变量。4.使用BULKCOLLECT将查询结果直接存入嵌套表变量(结构相同时使用UNIONALL查询;结构不同时分别查询再合并)。注意:在结构不同的情况下,合并后的数组元素中,来自不同表的记录中,不属于该表的字段为NULL。根据用户需求,我们提供了以上解决方案。</think>在 Oracle 存储过程中,将两个表的查询结果合并并存储到数组变量中,可以通过以下步骤实现: ### 解决方案(使用集合类型和 BULK COLLECT) ```sql CREATE OR REPLACE PROCEDURE merge_tables_to_array AS -- 步骤1:定义记录类型和嵌套表类型 TYPE rec_type IS RECORD ( id table1.id%TYPE, name table1.name%TYPE, value table2.value%TYPE ); TYPE arr_type IS TABLE OF rec_type; -- 嵌套表作为"数组" -- 步骤2:声明数组变量 v_combined_data arr_type; BEGIN -- 步骤3:使用UNION ALL合并查询并批量收集到数组 SELECT t1.id, t1.name, t2.value BULK COLLECT INTO v_combined_data FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id UNION ALL SELECT t1.id, t1.name, t2.value FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id WHERE t2.status = 'ACTIVE'; -- 添加其他条件 -- 步骤4:使用数组内容(示例:遍历输出) FOR i IN 1..v_combined_data.COUNT LOOP DBMS_OUTPUT.PUT_LINE( 'ID: ' || v_combined_data(i).id || ', Name: ' || v_combined_data(i).name || ', Value: ' || v_combined_data(i).value ); END LOOP; -- 性能优化提示:大结果集处理 -- 若数据量过大,可添加LIMIT子句分批次处理 EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No data found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); END; / ``` ### 关键说明 1. **集合类型替代数组**: - Oracle 没有传统数组,但可用**嵌套表**(`TABLE OF`)模拟数组功能 - `BULK COLLECT INTO` 高效加载查询结果到集合[^3] 2. **结构合并方式**: - 通过 `UNION ALL` 合并多个查询结果(保持相同列结构) - 使用 `JOIN` 关联表数据(如示例中的 `ON t1.id = t2.table1_id`) 3. **数据类型处理**: - 使用 `%TYPE` 继承原表字段类型,确保类型兼容性 - 记录类型(`RECORD`)可容纳多列数据 4. **性能优化**: - `BULK COLLECT` 减少 SQL-PL/SQL 上下文切换,提升性能[^3] - 大结果集建议添加 `LIMIT` 子句分批处理(如 `LIMIT 1000`) ### 替代方案(使用临时表) 当结果集特别大或结构复杂时,推荐使用临时表代替数组: ```sql CREATE GLOBAL TEMPORARY TABLE temp_combined_data ( id NUMBER, name VARCHAR2(50), value NUMBER ) ON COMMIT PRESERVE ROWS; -- 在存储过程中 INSERT INTO temp_combined_data SELECT t1.id, t1.name, t2.value FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id; ``` 此方法避免内存溢出风险,更适合生产环境大数据量场景[^1]。 ### 注意事项 1. 确保两个查询结果的**列数量和数据类型兼容** 2. `UNION ALL` 比 `UNION` 性能更好(不自动去重) 3. 数组变量大小受 PGA 内存限制,超大结果集需分页处理 4. 使用 `DBMS_OUTPUT` 仅适用于调试,生产环境建议日志表
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值