Bulk Collect的用法-优快云博客

本文链接：https://blog.youkuaiyun.com/olnathen/article/details/8845063

本文介绍Oracle数据库中的批量操作技巧，包括使用BULK COLLECT进行高效的数据检索与处理，通过FOR循环遍历集合进行批量插入、更新及删除操作，并展示了如何在RETURNING子句中结合BULK COLLECT进行数据备份。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--select col1,col2 bulk collect into ..
Declare
Type tv_object_name Is Table Of ou.Object_Name%Type Index By Binary_Integer;
var_object_name tv_object_name;
Type ti_object_id Is Table Of ou.Object_Id%Type Index By Binary_Integer;
i_object_id ti_object_id;
Begin
    Select object_name,object_id Bulk Collect Into var_object_name,i_object_id
      From ou Where Rownum <=100;
    Forall i In 1..i_object_id.count
      Insert Into h_ou Select * From ou Where object_name=var_object_name(i)
                                     And object_id = i_object_id(i);
    Commit;
End;
--Fetch cursorname bulk collect into ..
Declare
Type tv_rowid Is Table Of Varchar(20) Index By Binary_Integer;
var_rowid tv_rowid;

Cursor v_cur(pv_object_type Varchar2) Is Select Rowid From ou Where object_type=pv_object_type;
v_object_type Varchar2(20);
Begin
    v_object_type := 'TABLE PARTITION';
    Open v_cur(v_object_type);
    Loop
      Fetch v_cur Bulk Collect Into var_rowid Limit 1000;
      Forall i In 1..var_rowid.count
        Insert Into h_ou
          Select * From ou Where Rowid = var_rowid(i);
      Forall i In 1..var_rowid.count
        Delete From ou Where Rowid = var_rowid(i);
      Commit;
      Exit When v_cur%Notfound Or v_cur%Notfound Is Null;
    End Loop;
    Close v_cur;
End;
--在returning into中使用bulk collect
Create Table ou_list As Select object_name,object_id From Dba_Objects Where 1=2;
Declare
Type tv_object_name Is Table Of ou.Object_Name%Type Index By Binary_Integer;
var_object_name tv_object_name;
Type ti_object_id Is Table Of ou.Object_Id%Type Index By Binary_Integer;
i_object_id ti_object_id;
Begin
Delete From ou
    Returning object_name,object_id Bulk Collect Into var_object_name, i_object_id;
Forall i In 1..i_object_id.count
    Insert Into ou_list Values (var_object_name(i),i_object_id(i));
Commit;
End;