PL/SQL的复合数据类型

 

 

 

/*
 * not use var bind
 */
 
 declare
  type id_table_type is table of demo.id% TYPE
  index by BINARY_INTEGER;
  type name_table_type is table of demo.name% TYPE
  index by BINARY_INTEGER;
  id_table id_table_type;
  name_table name_table_type;
  start_time NUMBER(10);
  end_time NUMBER(10);
  BEGIN
    for i in 1..5000 LOOP
      id_table(i):=i;
      name_table(i):='NAME'||to_char(i);
    END LOOP;
    start_time:= dbms_utility.get_time;
    /*
      批量绑定删除数据
    */
-- FORALL i in 1..id_table.count

-- delete from demo where id= id_table(i);

-- commit;


    /*批量绑定添加数据*/
    forall i in 1..id_table.count
      insert into demo values(id_table(i),name_table(i));
    commit;
    end_time:= dbms_utility.get_time;
    dbms_output.put_line('用批量绑定的执行时间（秒）：'||(end_time-start_time)/1000);
  END;
    

 

 

 

/*
  使用select into，fetch into和dml返回子句，可以将批量数据存放到集合变量中
  可以将select的结果检索到集合变量中
*/
declare
  type emp_table_type is table of emp%ROWTYPE
    INDEX BY BINARY_INTEGER;
  emp_table emp_table_type;
  
  begin
    select * BULK COLLECT INTO emp_table
      from emp where deptno=&no;
    for i in 1..emp_table.count loop
      dbms_output.put_line('雇员名：'||emp_table(i).ename);
    END LOOP;
  end;