本人以下测试环境：oracle 10.2.0.1

先记录一下在sql中使用对象的使用。

SQL> create or replace type object_items as object (
  2  firstname varchar2(20),
  3  lastname  varchar2(30),
  4  birth     date,
  5  city      number
  6  )
  7  /

类型已创建。

SQL> create table x (person_id number,detail object_items);

表已创建。

SQL> col column_name format a20
SQL> col data_type format a20
SQL> select column_name,hidden_column,data_type
  2  from dba_tab_cols
  3  where table_name ='X'
  4  /

COLUMN_NAME          HID DATA_TYPE                                             
--------------------             ---     --------------------                                  
SYS_NC00005$         YES DATE                                                  
DETAIL                          NO  OBJECT_ITEMS                                          
SYS_NC00004$         YES VARCHAR2                                              
SYS_NC00006$         YES NUMBER                                                
SYS_NC00003$         YES VARCHAR2                                              
PERSON_ID                NO  NUMBER                                                

已选择6行。

以上代码是在一个表中使用对象作为1列。但在dba_tab_columns是看不到对象中具体信息的，因此遇到该问题时应该查看dba_tab_cols视图。

关于集合的使用，这里只是做一个介绍，分两部分说明 ：批量收集 与  批量绑定

1）批量收集

SQL> create or replace procedure explicit is
  2    cursor explicit_cur is
  3     select * from sec_group_tb ;
  4    type resultset is table of explicit_cur%rowtype;        --------在9.2.0.3之后可以这样使用
  5    r resultset;
  6 
  7  begin
  8 
  9      open explicit_cur;
 10      fetch explicit_cur bulk collect into r ;     ------使用bulk collect
 11      close explicit_cur;
 12  
 13      for i in 1..r.count loop      ---------此处是每次处理一行，若其后跟随sql语句时，会造成pl/sql与sql的上下文切换
 14        dbms_output.put_line('x='||r(i).x||',y='||r(i).y);    
 15      end loop;
 16  end;
 17  /

过程已创建。

已用时间:  00: 00: 00.04
SQL> exec explicit
x=1,y=padding                                                                
x=2,y=padding                                                                
x=3,y=padding                                                                
x=4,y=padding                                                                
x=5,y=padding      

。。。。。。

x=9999,y=padding                                                               
x=10000,y=padding                                                               

PL/SQL 过程已成功完成。

已用时间:  00: 00: 02.04

2) 批量绑定解决上下文切换

SQL> desc x
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               CHAR(100)

SQL> create or replace procedure rec_bind is
  2    type resultset2 is table of varchar2(100);
  3    type resultset is table of number;
  4    c resultset2;
  5    r resultset;
  6 
  7  begin
  8 
  9      for i in 1..10000 loop
 10        r(i) := i;
 11        c(i+10) := 'padding';                                              <-----------注意这里
 12      end loop;
 13 
 14      forall i in 1..10000
 15        insert into x(id,name) values(r(i),c(i));
 16 
 17  end;
 18  /

过程已创建。

已用时间:  00: 00: 00.03
SQL> exec rec_bind
BEGIN rec_bind; END;

*
第 1 行出现错误:
ORA-06531: 引用未初始化的收集                       <--------因为r,c未初始化
ORA-06512: 在 "SCOTT.REC_BIND", line 10
ORA-06512: 在 line 1

已用时间:  00: 00: 00.03
SQL> create or replace procedure rec_bind is
  2    type resultset2 is table of varchar2(100) index by binary_integer;    <------------注意这里
  3    type resultset is table of number index by binary_integer;                 <------------
  4    c resultset2;
  5    r resultset;
  6 
  7  begin
  8 
  9      for i in 1..10000 loop
 10        r(i) := i;                                                                                                <------------这样就可以直接使用r,c了
 11        c(i+10) := 'padding';
 12      end loop;
 13 
 14      forall i in 1..10000
 15        insert into x(id,name) values(r(i),c(i));
 16 
 17  end;
 18  /

过程已创建。

已用时间:  00: 00: 00.03

SQL> exec rec_bind
BEGIN rec_bind; END;

*
第 1 行出现错误:
ORA-22160: 下标 [1] 中的元素不存在        -----------注意这里，由于c的初始下标从11开始，所以报错
ORA-06512: 在 "SCOTT.REC_BIND", line 14
ORA-06512: 在 line 1

已用时间:  00: 00: 00.03
SQL> create or replace procedure rec_bind is
  2    type resultset2 is table of varchar2(100) index by binary_integer;
  3    type resultset is table of number index by binary_integer;
  4    c resultset2;
  5    r resultset;
  6 
  7  begin
  8 
  9      for i in 1..10000 loop
 10        r(i) := i;
 11        c(i) := 'padding';
 12      end loop;
 13 
 14        c(1) :='';                                           <----------将前3个数赋空
 15        c(2) :='';
 16        c(3) :='';
 17 
 18      forall i in 1..10000
 19        insert into x(id,name) values(r(i),c(i));
 20 
 21  end;
 22  /

过程已创建。

已用时间:  00: 00: 01.04
SQL> exec rec_bind

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.09
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00
SQL> col name for a20
SQL> select * from x order by id ;

        ID NAME                                                                
---------- --------------------                                                
         1                                                                     
         2                                                                     
         3                                                                     
         4 padding 

............

10000 padding                                                              
 已选择10000行。

已用时间:  00: 00: 12.87

如果表x中的name列不允许为null ,则此insert操作将rollback,不会有任何一条记录插入表中，但有一个解决办法，如下：

 forall i in 1..10000 save exceptions                       
      insert into x(id,name) values(r(i),c(i));
    exception
      when others then
        null;
    end;

注意上边代码中的粗体字，这样一来，就可以将不符合条件的记录抛出，而符合条件的将插入表中。