Bulk Collect的用法

--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;