1、创建表
==============================================================
create table scott.temp(id number(10) primary key, descr varchar2(20))
pctfree 20
pctused 40;
==============================================================

2、设置为nologging模式
======================================
alter table scott.temp nologging;
======================================

3、批量绑定

语法一
forall index in lower_bound .. upper_bound sql_statement;
========================================================================
declare
  type id_table_type is table of scott.temp.id%type;
  type descr_table_type is table of scott.temp.descr%type;
  id_table id_table_type := id_table_type();
  descr_table descr_table_type := descr_table_type();
begin
  for i in 1 .. 100000 loop
    id_table.extend;
    descr_table.extend;
    id_table(id_table.last) := i;
    descr_table(descr_table.last) := 'test'||to_char(i);
  end loop;
  forall i in id_table.first .. id_table.last
    insert /*+ APPEND */ into scott.temp values (id_table(i), descr_table(i));
  commit;
end;
/

declare
  type id_table_type is table of scott.temp.id%type index by binary_integer;
  type descr_table_type is table of scott.temp.descr%type index by binary_integer;
  id_table id_table_type;
  descr_table descr_table_type;
begin
  for i in 1 .. 100000 loop
    id_table(i) := i;
    descr_table(i) := 'Name'||to_char(i);
  end loop;
  forall i in 1 .. id_table.count
    insert /*+ APPEND */ into scott.temp values(id_table(i),descr_table(i));
  commit;
end;
/
========================================================================

语法二
forall index in indices of collection
  [between lower_bound.and.upper_bound] sql_statement;
========================================================================
declare
  type id_table_type is table of scott.temp.id%type;
  id_table id_table_type;
begin
  id_table:=id_table_type(1,null,3,null,5);
  forall i in indices of id_table
    delete from scott.temp where id=id_table(i);
  commit;
end;
/

declare
  type id_table_type is table of scott.temp.id%type index by binary_integer;
  id_table id_table_type;
begin
  select id bulk collect into id_table from scott.temp where mod(id,3)=0;
  forall i in indices of id_table
    delete from scott.temp where id=id_table(i);
  commit;
end;
/

declare
  type id_table_type is table of scott.temp.id%type index by binary_integer;
  id_table id_table_type;
begin
  for i in 1 .. 30000 loop
    id_table(i) := 3*i;
  end loop;
  forall i in 1 .. 30000
    delete from scott.temp where id = id_table(i);
  commit;
end;
/
========================================================================

语法三
forall index in values of index_collection sql_statement;
========================================================================
declare
  type id_table_type is table of scott.temp.id%type;
  type descr_table_type is table of scott.temp.descr%type;
  id_table id_table_type;
  descr_table descr_table_type;
  type index_no_type is table of pls_integer;
  index_no index_no_type;
begin
  select * bulk collect into id_table,descr_table from scott.temp order by id;
  index_no := index_no_type(6,8,10);
  forall i in values of index_no
    insert into scott.temp_new values(id_table(i),descr_table(i));
  commit;
end;
/
========================================================================

4、查询统计信息
================================================================
select table_name, tablespace_name,status, pct_free, pct_used,
num_rows, blocks, empty_blocks, avg_row_len from dba_tables
where wner='SCOTT'
================================================================
5、分析统计表
=============================================
begin
dbms_stats.gather_schema_stats(
 ownname => 'SCOTT',
 options => 'GATHER');
end;
/
=============================================
or
=============================================
analyze table scott.temp compute statistics;
=============================================

====================================================================================
declare
  type ename_table_type is table of emp.ename%type;
  ename_table ename_table_type;
begin
  delete from scott.emp where deptno=&dno returning ename bulk collect into ename_table;
  for i in 1 .. ename_table.count loop
    dbms_output.put_line(ename_table(i));
  end loop;
end;
/
====================================================================================

====================================================================================
select ceil(1.1),floor(1.8),trunc(1.5),round(1.7),round(1.4) from dual;

CEIL(1.1)   FLOOR(1.8)   TRUNC(1.5)   ROUND(1.7)   ROUND(1.4)            
----------- ------------ ------------ ------------ -----------
2           1            1            2            1                     
====================================================================================

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17012874/viewspace-693783/，如需转载，请注明出处，否则将追究法律责任。