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/,如需转载,请注明出处,否则将追究法律责任。