ORACLE集合学习总结

最新推荐文章于 2021-04-09 16:16:22 发布

Tiwen

最新推荐文章于 2021-04-09 16:16:22 发布

阅读量3.7k

点赞数

分类专栏：数据库文章标签： oracle table object nested insert exception

本文链接：https://blog.csdn.net/Tiwen818/article/details/7164652

版权

数据库专栏收录该内容

29 篇文章 0 订阅

订阅专栏

index-by table类型，这个类型只能在过程定义，因此不能在返回函数中使用。

declare

-- 定义

type t_a is table of number(4) index by pls_integer;

a t_a;

type t_b is table of number(10,2) index by varchar2(20);

b t_b;

begin

--赋值

a(1) := 123;

a(2) := 456;

--a(3) := 456;

a(4) := 888;

b('CHINA') := 13000;

b('AMERCIAN') := 300000;

--输出

fori in 1..a.count loop

--ifa(i).exists then --错误的写法

ifa.exists(i) then --正确的写法

dbms_output.put_line(a(i));

endif;

endloop;

dbms_output.put_line('------------------------------------------------------');

ifb.exists('CHINA') then --正确的写法

dbms_output.put_line(b('CHINA') );

endif;

end;

oracle数据库提供了类似的一种集合，这个集合可以在过程中定义，也可以在过程外定义，其方法与pl/sql table类型的集合相似。

DECLARE

TYPE NumList IS TABLE OF NUMBER;

--这里没有index by

n NumList := NumList(1,3,5,7);

---需要初始化

counter INTEGER;

BEGIN

n.DELETE(2); --删除第二条记录

counter := n.FIRST; --实际上是1

WHILE counter IS NOT NULL

LOOP

DBMS_OUTPUT.PUT_LINE

('Counting up: Element #' || counter || ' =' || n(counter));

counter := n.NEXT(counter);

END LOOP;

-- Run the same loop in reverse order.

counter := n.LAST;

WHILE counter IS NOT NULL

LOOP

DBMS_OUTPUT.PUT_LINE

('Counting down: Element #' || counter || '= ' || n(counter));

counter := n.PRIOR(counter);

END LOOP;

END;

还有一种集合类型 varry，但是运用很少。

集合的定义与方法很简单，了解其他编程语言的数组，很容易理解这些概念。

然而oracle的集合却是oracle提高效率，灵活实现功能的一种重要方法。

Oracle提高了表函数能够将一个集合映射为一个表，这个表可以与其他数据集合一些使用（仅限于select，不能通过DML修改集合类型。）

集合占用了内存，了解集合占用内存情况很有必要

集合内存释放情况

win xp oracle11g

DECLARE

TYPEdnames_tab IS TABLE OF char(2000);

dept_names dnames_tab := dnames_tab();

empty_set dnames_tab;

BEGIN

select rownum||'**' bulk collect into dept_names from dual connect by level<=100000;

ifdept_names is not null then

DBMS_OUTPUT.PUT_LINE(dept_names.count);

else

DBMS_OUTPUT.PUT_LINE('null');

end if;

dept_names := empty_set;

ifdept_names is not null then

DBMS_OUTPUT.PUT_LINE(dept_names.count);

else

DBMS_OUTPUT.PUT_LINE('null');

end if;

dbms_lock.sleep(10);

END;

运行上面的程序，观察系统内存比变化情况，发现只有程序运行时，使用内存在增加，dept_names :=empty_set;

后内存并没有立即释放，而是等到增个程序end的时候，才被释放。

说明：oracle的官方文档在这方面的介绍有一些问题：

oracle的官方文档说，一个集合trim后，会释放其占用的内存空间，但是我的测试结果并不支持这样的说法。

因此在程序中尽量避免大集合长期霸占内存，或者大集合多次拷贝

集合方法

集合的一些常用方法

窗体顶端方法窗体底端	描述	使用限制
Count	返回集合中元素的个数
Delete	删除集合中所有元素
Delete（x）	删除元素下标为x的元素，如果x为null，则集合保持不变	对varray非法
Delete（x，y）	删除元素下标从x到y的元素，如果x>y则集合保持不变	对varray非法
Exists（x）	如果集合元素x已经初始化，则返回ture否则返回false
Extend	在集合末尾添加一个元素	对index_by非法
Extend（x）	在集合末尾添加x个元素	对index_by非法
Extend（x，n）	在集合末尾添加元素n的x个副本	对index_by非法
First	返回集合第一个元素的下标号，对于varray集合始终返回1
Last	返回集合中最后一个元素的下标号，对于varray集合返回值始终等于count
Limit	返回varry集合的最大元素个数，对于嵌套表和index_by始终为null	Index_by和嵌套表无效
Next（）	返回元素x之后紧挨着他的元素的值，如果该元素是最后一个元素，则返回null
Prior（）	返回集合中元素在x之前紧挨着他的元素的值，如果该元素是第一个元素，则返回null
Trim	从集合末端开始删除一个元素
Trim（x）	从集合末端开始删除x个元素	窗体底端

上面提到的很多方法，在实际开发中较少使用到，无须记住它们。

似是而非的问题(trim|delete|count|first..last)

DECLARE

TYPE nested_typ IS TABLE OF NUMBER;

nt1nested_typ := nested_typ(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);

BEGIN

dbms_output.put_line('nt1.count=' || nt1.count);

for i in 1 .. nt1.count loop

if nt1(i) is not null then

dbms_output.put_line(nt1(i));

end if;

end loop;

dbms_output.put_line('************************************');

nt1.delete(4);

nt1.delete(6);

nt1.delete(8);

dbms_output.put_line('nt1.count=' || nt1.count);

for i in 1 .. nt1.count loop

if nt1.exists(i) then

dbms_output.put_line(nt1(i));

else

dbms_output.put_line('位置在但是没有');

end if;

end loop;

dbms_output.put_line('************************************');

for i in nt1.first .. nt1.last loop

if nt1.exists(i) then

dbms_output.put_line(nt1(i));

else

dbms_output.put_line('位置在但是没有');

end if;

end loop;

dbms_output.put_line('************************************');

end;

输出结果

nt1.count=12

************************************

nt1.count=9

位置在但是没有

************************************

位置在但是没有

************************************

看到如果集合还是变得稀疏,使用count 就开始产生疑义，最好采用first last。

单字段集合

declare

--声明一个集合对象typ_number 用于存放number(10,2)的数组

type typ_number is table of number(10,2);

--声明一个对象typ_number的实例并初始化

sal typ_number := typ_number();

i_rowcounts integer;

begin

--扩展一个组员

sal.extend(1);

--赋予一个组员值

sal(sal.last) :=100.11;

--利用循环赋值

fori in 1..100 loop

sal.extend(1);

sal(sal.last) := sqrt(i);

endloop;

--输出

dbms_output.put_line(sal.count);

sal.extend(100);

for i in 100..200 loop

sal(i+1) := sqrt(i);

endloop;

dbms_output.put_line(sal(200));

--将select 的结果装入数组

select count(*) intoi_rowcounts from scott.emp;

dbms_output.put_line(i_rowcounts);

ifi_rowcounts>0 then

select sal s bulk collect intosal from scott.emp;

end if;

for k in 1.. sal.count loop

dbms_output.put_line(sal(k));

end loop;

--请问现在集合sal有多少个元素？

--注意如果采用bulk collect into 则这个集合从新初始化了

--动态语句sql的集合赋值

sal.delete;

dbms_output.put_line(sal.count);

sql_stmt := 'select sal from scott.emp';

execute immediate sql_stmt bulk collectinto sal;

dbms_output.put_line(sal.count);

end;

尽管可以用游标来赋值，但是我们还是力主用collect bulk into来赋值。

多个字段的集合的赋值方法

定义一个多字段的对象

create type myScalarType as object( x int, y date, z varchar2(30))；

定义一个基于该对象的集合

create type myArrayType as table of myScalarType；

declare

l_data myArrayType :=myArrayType();

begin

l_data.extend;

l_data(1).x := 42;

l_data(1).y := sysdate;

l_data(1).z := 'hello world';

l_data.extend;

l_data(2) := myScalarType( 1, sysdate, 'x' );

end;

如果在扩展后，l_data(l_data.count).x:= 42;操作成功

但是l_data(l_data.count).y:= sysdate-100; 却出现了错误

或者直接l_data(l_data.count).y:= sysdate-100;也出现了错误

正确的做法是

l_data(1):=myScalarType(1,sysdate,'ggg');

或者

l_data(1):=myScalarType(null,null,null);

再赋值

declare

l_data myArrayType := myArrayType();

begin

l_data.extend;

l_data(1):=myScalarType(1,sysdate,'ggg');

dbms_output.put_line(l_data(1).y);

end;

但是对于本地集合plsql index by (sparse) table

DECLARE

TYPE r_prods is RECORD (Prod_no number(1),

DAY_IN_WEEK number(1),

BRANCH_NO NUMBER(1),

QTY NUMBER(4));

TYPE t_prods IS TABLE OF r_prods INDEX BY BINARY_INTEGER;

v_Prods t_prods;

BEGIN

v_Prods(111).prod_no :=1;

v_Prods(111).day_in_week :=1;

v_Prods(111).branch_no :=1;

v_Prods(111).qty:=300;

v_Prods(121).prod_no :=1;

v_Prods(121).day_in_week :=2;

v_Prods(121).branch_no :=1;

v_Prods(121).qty:=400;

-- dbms_output.put_line(l_data(1).y);

end;

却成功

Limit

Limit是9i以后版本引进的

注意点：

1、limit 的参数不是越大越好

2、退出的写法与位置

注意游标退出的判断以及位置

DECLARE

TYPE numtab IS TABLEOF NUMBER INDEX BY PLS_INTEGER;

CURSOR c1 IS SELECT hr.employee_idFROM employees WHERE department_id = 80;

empids numtab;

rows PLS_INTEGER :=10;

BEGIN

OPEN c1;

LOOP -- the following statement fetches 10 rows or less ineach iteration

FETCH c1 BULKCOLLECT INTO empids LIMIT rows;

EXIT WHENempids.COUNT = 0;

-- EXIT WHENc1%NOTFOUND; --不正确, 这样写可能或略部分数据

DBMS_OUTPUT.PUT_LINE('-------Results from Each Bulk Fetch --------');

FOR i IN 1..empids.COUNT LOOP

DBMS_OUTPUT.PUT_LINE('Employee Id: ' || empids(i));

END LOOP;

CLOSE c1;

END;

集合的运算以及集合与表运算

drop type type_emp

create or replace type emp_t as object

(

empno number(4,0),

ename varchar2(20),

sal number(16,2)

)

测试

selectemp_t(empno,ename,sal) from emp;

drop type emp_t

create type table_emp is table of emp_t

declare

i integer;

e table_emp;

e1 table_emp;

v_sal number(16,0);

type c_sal is table of number(10,2);

v_c c_sal;

begin

select emp_t(empno,ename,sal) bulk collect into e from emp;

dbms_output.put_line(e.count);

for i in 1..e.count loop

dbms_output.put_line(e(i).empno||' '||e(i).sal);

end loop;

for i in1..e.count loop

e(i).sal:=(selectsum(sal) from emp where empno=e(i).empno);

end loop;

for i in 1..e.count loop

select sal into v_sal from emp where empno=e(i).empno;

e(i).sal:=e(i).sal+v_sal;

end loop;

for i in 1..e.count loop

dbms_output.put_line(e(i).empno||' '||e(i).sal);

end loop;

select sal bulk collect into v_c from emp;

for i in 1..e.count loop

e(i).sal:=v_c(i);

end loop;

for i in 1..e.count loop

dbms_output.put_line(e(i).empno||' '||e(i).sal);

end loop;

select

emp_t(empno,ename,sal) bulk collect into e1

from table(e);

dbms_output.put_line('print e1');

for i in 1..e1.count loop

dbms_output.put_line(e1(i).empno||' '||e1(i).sal);

end loop;

e1.delete;

select

emp_t(a.empno,a.ename,a.sal+b.sal)bulk collect into e1

from table(e)a, emp b

where a.empno=b.empno;

dbms_output.put_line('print e1 withtable');

for i in 1..e1.count loop

dbms_output.put_line(e1(i).empno||' '||e1(i).sal);

end loop;

--对于表，我们可以

update emp a

set a.sal= 1+(select sal from emp wherea.empno=emp.empno);

--update table(e)a

--set a.sal= 1+(select sal from emp wherea.empno=emp.empno);

--但是对于集合则

--ora-00903错误表名无效

for i in 1..e.count loop

for j in 1..e1.count loop

if e1(j).empno=e(i).empnothen

e(i).sal:=nvl(e(i).sal,0)+nvl(e1(j).sal,0);

end if;

end loop;

dbms_output.put_line('col operate with other col');

for i in 1..e1.count loop

dbms_output.put_line(e1(i).empno||' '||e1(i).sal);

end loop;

end;

集合排序与拷贝

declare

i integer;

e table_emp;

e_order table_emp;

e1 table_emp;

v_sal number(16,0);

type c_sal is table of number(10,2);

v_c c_sal;

begin

select emp_t(empno,ename,sal) bulk collect into e from emp;

dbms_output.put_line(e.count);

for i in 1..e.count loop

dbms_output.put_line(e(i).empno||' '||e(i).sal);

end loop;

select count(*) into i from table(e);

dbms_output.put_line('i from e '||i);

select emp_t(empno,ename,sal)

bulk collect into e_order

from table(e) order by sal;

dbms_output.put_line('print e_order');

for i in 1..e.count loop

dbms_output.put_line(e_order(i).empno||' '||e_order(i).sal);

end loop;

end;

或者常用方法进行排序，这样可以节省内存空间

declare

-- Local variables here

type table_sal is table of scott.emp.sal%type;

c_sal table_sal;

v_sal scott.emp.sal%type :=0;

point integer;

begin

select sal bulk collect intoc_sal from emp;

for i in 1..c_sal.count loop

dbms_output.put_line(c_sal(i));

end loop;

dbms_output.put_line('----------------------------------');

for i in 2..c_sal.count loop

if c_sal(i) > c_sal(i-1) then

v_sal := c_sal(i-1);

c_sal(i-1) :=c_sal(i);

c_sal(i) := v_sal;

end if ;

end loop;

for i in 1..c_sal.count loop

dbms_output.put_line(c_sal(i));

end loop;

end;

输出

-----------------------------------------------------------

800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
950
3000
1300
----------------------------------
5000
3000
3000
2975
2850
2450
1600
1500
1300
1250
1250
1100
950
800

如果一个集合中的元素被删除，调用这个元素则会出错

declare

i integer;

e table_emp;

e1 table_emp;

e2 table_emp;

v_sal number(16,0);

type c_sal is table of number(10,2);

v_c c_sal;

begin

select emp_t(empno,ename,sal) bulk collect into e from emp;

dbms_output.put_line(e.count);

for i in 1..e.count loop

dbms_output.put_line(nvl(e(i).empno,0)||' '||nvl(e(i).sal,0));

end loop;

e.delete(1);

--e.trim(13);

--集合第一条数据被删除

--结果集合的条数从3变成2

--但是如果想输出集合则出现错误

-- for i in 1..e.count loop

--dbms_output.put_line(nvl(e1(i).empno,0)||' '||nvl(e1(i).sal,0));

--end loop;

--错误

--解决方法 1 作以下判断

for i in 1..e.count loop

if e1.exists(i) then

dbms_output.put_line(nvl(e1(i).empno,0)||' '||nvl(e1(i).sal,0));

end if;

end loop;

--解决方法2 将删除后的集合放到一个新的集合中

select emp_t(empno,ename,sal)bulk collect into e1 from table(e);

dbms_output.put_line(e1.count);

for i in 1..e.count loop

dbms_output.put_line(nvl(e1(i).empno,0)||' '||nvl(e1(i).sal,0));

end loop;

end;

DECLARE

TYPE NumList IS TABLE OF NUMBER;

n NumList := NumList(10,20,30,40,50,60,70,80,90,100);

BEGIN

dbms_output.put_line(n.count);

n.DELETE(2); -- deletes element 2

dbms_output.put_line(n.count);

for i in 1..n.count loop

dbms_output.put_line(n(i));

end loop;

end;

第 1 行出现错误:

ORA-01403: 未找到任何数据

ORA-06512: 在 line 12

修改

for i in 1..n.count loop
if n.exists(i) then
dbms_output.put_line(n(i));
end if;
end loop;

集合运算

IN | not IN

DECLARE

TYPE nested_typ IS TABLE OF NUMBER;

nt1 nested_typ := nested_typ(1,2, 3);

nt2 nested_typ := nested_typ(1,2, 3, 4);

flag boolean := true;

p integer := 0;

BEGIN

if nt1 IN (nt2) then

dbms_output.put_line('true');

else

dbms_output.put_line('false');

end if;

--输出为flase 这不是我们所需要的结果

for i in 1 .. nt1.count loop

for j in 1 .. nt2.count loop

if nt1(i) = nt2(j) then

p := p + 1;

end if;

end loop;

if p > i then

p := i;

end if;

if p < i then

flag := false;

end if;

exit when p < i;

end loop;

if flag = true then

dbms_output.put_line('true');

else

dbms_output.put_line('false');

end if;

--这是我们所需要的结果

END;

可以将上述过程编写成函数。

Distinct

DECLARE

TYPE nested_typ IS TABLE OF NUMBER;

nt1 nested_typ :=nested_typ(100,1, 2, 3, 6,6,6, 19,9,7,11,3, 2, 12,1,14, 3,11,1,11,1,13,109);

collect_count integer;

BEGIN

dbms_output.put_line('nt1.count=' || nt1.count);

collect_count := nt1.count;

for i in nt1.first .. nt1.last loop

for j in nt1.first .. nt1.last loop

--注意不能写成 1.. nt1.count

if nt1.exists(i) and nt1.exists(j) then

if i <> j and nt1(i) = nt1(j)then

nt1.delete(j);

end if;

end loop;

dbms_output.put_line('nt1.count=' || nt1.count);

for i in nt1.first .. nt1.last loop

if nt1.exists(i) then

dbms_output.put_line(nt1(i));

end if;

end loop;

END;

-------------------------------------------

100
1
2
3
6
19
9
7
11
12
14
13
109

union all

思想找出较少元素的结合，通过loop将元素增加到较多元素的集合中，程序省略

union

union all + distinct

或者自己重新编写程序

intersect

自己重新编写程序

intersect

自己重新编写程序

利用oracle的集合运算符(unionall | union | minus | intersect) 对集合进行集合运算

利用表函数将集合转化为可以select的表，在做集合运算

好处是简化程序弊端为分配一个新的内存

create TYPE nested_typ IS TABLE OF NUMBER;

DECLARE

nt1 nested_typ := nested_typ(1,2,3,5);

nt2 nested_typ := nested_typ(3,2,1,4);

nt3 nested_typ := nested_typ(2,3,1,3);

nt4 nested_typ := nested_typ(1,2,4);

nt5 nested_typ := nested_typ();

BEGIN

for i in 1..nt1.count loop

for j in 1..nt2.count loop

if nt1(i)=nt2(j) then

dbms_output.put_line(nt1(i));

end if;

end loop;

select a

bulk collect into nt5

from

(

select column_value a from table(nt1)

union all

select column_value a from table(nt2)

);

for i in 1..nt5.count loop

dbms_output.put_line(nt5(i));

end loop;

END;

负集

create TYPE NumList IS TABLE OF NUMBER;

DECLARE

n2 NumList:= NumList(10,20,35,90);

n1NumList:= NumList(10,20,30,40,50,60,70,80,90,100);

n3 NumList := NumList();

BEGIN

select a

bulk collect into n3

from

(

select column_value a fromtable(n1)

minus

select column_value a fromtable(n2)

);

for i in 1..n3.count loop

dbms_output.put_line(n3(i));

end loop;

end;

集合的运算

注意集合类型一定在外定义

不能在程序内部定义

create or replace TYPE NumList IS TABLE OF integer;

DECLARE

不能在程序内部定义

--TYPE NumList IS TABLE OF NUMBER;

n NumList := NumList(10,20,35);

n1 NumList:= NumList(10,20,30,40,50,60,70,80,90,100);

n3 NumList;

i integer;

BEGIN

selectNumList(a) bulk_collect into n3

from

(

selectcolumn_value a from table(n)

minus

selectcolumn_value a from table(n1)

);

for i in 1..n3.count loop

dbms_output.put_line(n3(i));

end loop;

end;

集合与表的联合运算

declare

--不能在程序内部定义

--TYPE NumList IS TABLE OF NUMBER;

n NumList := NumList(10,20,35);

n1 NumList:= NumList(10,20,30,40,50,60,70,80,90,100);

n3 NumList;

i integer;

BEGIN

select a.empno bulk collect into n3 from

emp a,table(n) b

wherea.deptno=b.column_value;

for i in 1..n3.count loop

dbms_output.put_line(n3(i));

end loop;

end;

以上我们看到集合的方法以及自定义的集合运算方法，我们也了解到了从数据库的结果集批量取数到集合中的方法。

下面介绍更重要的集合数据的物化问题，就是将集合数据dml表中。

FORALL技术

先看一个基本的例子

create table TARGET_ALL_OBJECT
(
OWNER          VARCHAR2(30),
OBJECT_NAME    VARCHAR2(30),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID      NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE    VARCHAR2(19),
CREATED        DATE,
LAST_DDL_TIME DATE,
TIMESTAMP      VARCHAR2(19),
STATUS         VARCHAR2(7),
TEMPORARY      VARCHAR2(1),
GENERATED      VARCHAR2(1),
SECONDARY      VARCHAR2(1),
NAMESPACE      NUMBER,
EDITION_NAME   VARCHAR2(30)
)

declare

cursor c1 is select * from cur_all_object slow_by_slow;

cursor c2 is select * from cur_all_object bulk_collect;

type array is table of c1%rowtype index by binary_integer;

l_array array;

l_row c1%rowtype;

start_time int;

begin

start_time := dbms_utility.get_time;

open c1;

loop

fetch c1 into l_row;

exit when c1%notfound;

insert intotarget_all_object(object_id) values(l_row.object_id);

end loop;

close c1;

dbms_output.put_line(( dbms_utility.get_time-start_time)/100);

commit;

execute immediate 'truncate table target_all_object ';

start_time := dbms_utility.get_time;

open c2;

loop

fetch c2 bulk collect intol_array limit 10000;

-- process rows here --

exit when c2%notfound;

for m in 1..l_array.countloop

insert intotarget_all_object(object_id) values(l_array(m).object_id);

end loop;

close c2;

dbms_output.put_line(( dbms_utility.get_time-start_time)/100);

commit;

execute immediate 'truncate table target_all_object ';

start_time := dbms_utility.get_time;

open c2;

loop

fetch c2 bulk collectinto l_array limit 10000;

-- process rows here --

exit when c2%notfound;

forall m in 1..l_array.count

insert into target_all_object(object_id) values (l_array(m).object_id);

end loop;

close c2;

dbms_output.put_line(( dbms_utility.get_time-start_time)/100);

commit;

execute immediate 'truncate table target_all_object ';

end;

--------

4.22
2.93
0.34

limit=100

--------

4.23
2.86
0.36

插入全表的数据

declare

cursor c1 is select * fromcur_all_object slow_by_slow;

cursor c2 is select * fromcur_all_object bulk_collect;

type array is table of tiwen.target_all_object%rowtype index by binary_integer;

l_array array;

l_row tiwen.target_all_object%rowtype;

start_time int;

begin

start_time := dbms_utility.get_time;

open c1;

loop

fetch c1 into l_row;

exit when c1%notfound;

insert into target_all_object values l_row;

end loop;

close c1;

dbms_output.put_line((dbms_utility.get_time-start_time)/100);

commit;

execute immediate 'truncate tabletarget_all_object ';

start_time := dbms_utility.get_time;

open c2;

loop

fetch c2 bulk collect intol_array limit 200;

-- process rows here --

exit when c2%notfound;

for m in 1..l_array.countloop

insert into target_all_object values l_array(m);

end loop;

close c2;

dbms_output.put_line((dbms_utility.get_time-start_time)/100);

commit;

execute immediate 'truncate tabletarget_all_object ';

start_time := dbms_utility.get_time;

open c2;

loop

fetch c2 bulk collect into l_array limit10000;

-- process rows here --

exit when c2%notfound;

forall m in 1..l_array.count

insert into target_all_object valuesl_array(m);

end loop;

close c2;

dbms_output.put_line((dbms_utility.get_time-start_time)/100);

commit;

execute immediate 'truncate tabletarget_all_object ';

end;

或者

declare

cursor c1 is select * from cur_all_object slow_by_slow;

cursor c2 is select * from cur_all_object bulk_collect;

type array is table of c1%rowtypeindex by binary_integer;

l_array array;

l_row c1%rowtype;

start_time int;

begin

start_time := dbms_utility.get_time;

open c1;

loop

fetch c1 into l_row;

exit when c1%notfound;

insert into target_all_object values l_row;

end loop;

close c1;

dbms_output.put_line(( dbms_utility.get_time-start_time)/100);

commit;

execute immediate 'truncate table target_all_object ';

start_time := dbms_utility.get_time;

open c2;

loop

fetch c2 bulk collect intol_array limit 10000;

-- process rows here --

exit when c2%notfound;

for m in 1..l_array.countloop

insert into target_all_object values l_array(m);

end loop;

close c2;

dbms_output.put_line(( dbms_utility.get_time-start_time)/100);

commit;

execute immediate 'truncate table target_all_object ';

start_time := dbms_utility.get_time;

open c2;

loop

fetch c2 bulk collect intol_array limit 10000;

-- process rows here --

exit when c2%notfound;

forall m in 1..l_array.count

insert into target_all_object values l_array(m);

end loop;

close c2;

dbms_output.put_line(( dbms_utility.get_time-start_time)/100);

commit;

execute immediate 'truncate table target_all_object ';

end;

看到 FORALL 极大地提高了效率，与取数一样,forall避免了工作引擎之间的切换。

注意写法上的差别

for m in 1..l_array.countloop

insert intotarget_all_object values l_array(m);

end loop;

forall m in 1..l_array.count

insert into target_all_object values l_array(m);

FORALL后面直接跟上dml语句，中间不能有其他如判断过程。如

forall m in 1..l_array.count

if mod(object_id,2)=1 then

insert into target_all_object values l_array(m);

end if;

结束时候没有END LOOP这句话

forall m in 1..l_array.count

insert intotarget_all_object values l_array(m);

不支持稀疏集合

如

declare

cursor c1 is select * from cur_all_object slow_by_slow;

cursor c2 is select * from cur_all_object bulk_collect;

type array is table of c1%rowtype index by binary_integer;

l_array array;

l_row c1%rowtype;

start_time int;

begin

start_time := dbms_utility.get_time;

open c2;

loop

fetch c2 bulk collect intol_array limit 10000;

-- process rows here --

exit when c2%notfound;

l_array.delete(10);

forall m in 1..l_array.count

insert intotarget_all_object values l_array(m);

end loop;

close c2;

dbms_output.put_line(( dbms_utility.get_time-start_time)/100);

commit;

execute immediate 'truncate table target_all_object ';

end;

ORA-22160: 下标 [10] 中的元素不存在

ORA-06512: 在 line 18

为解决这个问题，在oracle10个中提供了新的语法

declare

cursor c1 is select * from cur_all_object slow_by_slow;

cursor c2 is select * from cur_all_object bulk_collect;

type array is table of c1%rowtype index by binary_integer;

l_array array;

l_row c1%rowtype;

start_time int;

begin

start_time := dbms_utility.get_time;

open c2;

loop

fetch c2 bulk collect intol_array limit 10000;

-- process rows here --

exit when c2%notfound;

l_array.delete(10);

forall m in INDICES OFl_array

insert intotarget_all_object values l_array(m);

end loop;

close c2;

dbms_output.put_line(( dbms_utility.get_time-start_time)/100);

commit;

execute immediate 'truncate table target_all_object ';

end;

如果只想插入新数据的一部分如第2，7，10个，oracle 10g中又提供了 in value的语法

declare

cursor c1 is select * from cur_all_object slow_by_slow;

cursor c2 is select * from cur_all_object bulk_collect;

type array is table of c1%rowtype index by binary_integer;

l_array array;

l_row c1%rowtype;

start_time int;

TYPE aat_id IS TABLE OF PLS_INTEGERINDEX BY PLS_INTEGER;

legal_ids aat_id;

begin

start_time := dbms_utility.get_time;

open c2;

loop

fetch c2 bulk collect intol_array limit 10000;

-- process rows here --

exit when c2%notfound;

legal_ids(1) := 2;

legal_ids(2) := 7;

legal_ids(3) := 10;

forall m IN VALUES OFlegal_ids

insert intotarget_all_object values l_array(m);

end loop;

close c2;

dbms_output.put_line(( dbms_utility.get_time-start_time)/100);

commit;

execute immediate 'truncate table target_all_object ';

end;

FORALL是oracle的一个很重要的技术选项，上面只是介绍了 FORALL insert操作，FORALL支持oracle的所有DML操作。

FORALL update

在我们讲到用一个数据集更新另一个数据集进行update操作的时候，update隐含着一个规则，即用于更新的数据源在关联条件上一定是唯一的。但是很多情况下，这个隐含的规则并不满足。

如

create table source_update

(

seq int,

id int,

name varchar2(10)

)

SEQ	ID	NAME
1	10	aaaa
2	20	bbbb
3	10	cccc
4	30	pppp

create table target_update

(

id int,

name varchar2(10)

)

ID	NAME
10	mmmmmmmm
20	dddd
30	sss
40	aaavvv

merge into target_update a

using (select * from source_update) b

on (a.id=b.id)

when matched then

update set name=b.name

ORA-30926: 无法在源表中获得一组稳定的行

可以取得最新的更新数据源

merge into target_update a
using (
select * from source_update
where seq in
(
select max(seq) ms from source_update group by id
)
) b
on (a.id=b.id)
when matched then
update set name=b.name

成功

当然对于

select * from source_update
where seq in
(
select max(seq) ms from source_update group by id
)

我们可以有更加有效的写法

select id,name
from
(
select seq, max(seq) over (partition by id ) s, id, name from source_update
)
where seq=s

但是不是所有特征的数据情况下，这些写法具有高效率。说明

但是由于不能确认id具有唯一性，又不能直接用update(merge)的方法，游标集合提供了很好的方法

declare

cursor c2 is select id,name from source_update orderby seq;

type array is table of c2%rowtype index by binary_integer;

l_array array;

start_time int;

begin

start_time := dbms_utility.get_time;

open c2;

loop

fetch c2 bulk collect intol_array limit 10000;

exit when c2%notfound;

forall m IN l_array.first..l_array.last

update target_update

set name=l_array(m).name

where id= l_array(m).id;

end loop;

close c2;

dbms_output.put_line(( dbms_utility.get_time-start_time)/100);

commit;

end;

讨论：你能根据已有的知识，做相关表的物理设计，使上述过程效率得到更大提高吗？

FORALL的部分更新

CREATE TABLEemp_temp (deptno NUMBER(2), job VARCHAR2(18));

--INSERT INTO emp_temp VALUES (10, 'Clerk');

--Lengthening this job title causes an exception

--INSERT INTO emp_temp VALUES (20, 'Bookkeeper');

--INSERT INTO emp_temp VALUES (30, 'Analyst');

--COMMIT;

DECLARE

TYPE NumList IS TABLE OF NUMBER;

depts NumList := NumList(10, 20, 30);

BEGIN

FORALL j IN depts.FIRST .. depts.LAST -- Run 3 UPDATE statements.

UPDATE emp_temp SET job = job || ' (Senior)' WHERE deptno = depts(j);

--raises a "value too large" exception

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Problem in the FORALL statement.');

COMMIT; -- Commit results of successfulupdates.

END;

FORALL影响的行数

(%BULK_ROWCOUNTAttribute)

游标具有以下属性

SQL%FOUND,

SQL%ISOPEN,

SQL%NOTFOUND,

SQL%ROWCOUNT,

这些属性记录了最近执行的DML语句的有关信息。

在FORALL中，引进了一个新的属性, %BULK_ROWCOUNT,这个属性实际上是一个数组，存放了第i次执行DNM操作的记录数。

CREATE TABLE emp_tempAS SELECT * FROM scott.emp;

select count(*) , deptno from emp_temp group by deptno

*COUNT()**	DEPTNO
6	30
5	20
3	10

DECLARE

TYPE NumList IS TABLE OF NUMBER;

depts NumList := NumList(10, 20, 30);

BEGIN

FORALL j INdepts.FIRST..depts.LAST

DELETE FROM emp_temp WHERE deptno = depts(j);

-- How many rows were affected by each DELETE statement?

FOR i IN depts.FIRST..depts.LAST

LOOP

DBMS_OUTPUT.PUT_LINE('Iteration #' || i || ' deleted '||SQL%BULK_ROWCOUNT(i) || ' rows.');

END LOOP;

END;

输出结果

Iteration #1 deleted 3 rows.
Iteration #2 deleted 5 rows.
Iteration #3 deleted 6 rows.

如果FORALL采用语法INDICES OF处理稀疏性集合%BULK_ROWCOUNT有同样的稀疏性；

如果FORALL采用语法VALUES OF处理一个子集合%BULK_ROWCOUNT则保持子集合相关的值。如果子集合包含重复的数据，则 has subscripts corresponding to the values ofthe elements in the index collection. If the index collection containsduplicate elements, so that some DML statements are issued multiple times usingthe same subscript, then the corresponding elements of %BULK_ROWCOUNT represent the sum of all rowsaffected by the DML statement using that

subscript.

DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 10,null,1001);
BEGIN
FORALL j in INDICES OF depts
DELETE FROM emp_temp WHERE deptno = depts(j);
-- How many rows were affected by each DELETE statement?
FOR i IN depts.FIRST..depts.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Iteration #' || i ||' deleted ' ||SQL%BULK_ROWCOUNT(i)|| ' rows.');
END LOOP;
END;

-------------------------------------------------------------

Iteration #1 deleted 3 rows.
Iteration #2 deleted 5 rows.
Iteration #3 deleted 0 rows.
Iteration #4 deleted 0 rows.
Iteration #5 deleted 0 rows.

-----------------------------------------------------------------------------

DECLARE

TYPE NumList IS TABLE OF NUMBER;

depts NumList :=NumList(10, 20, 30,1001);

BEGIN

FORALL j in INDICES OF depts between 1 and 3

DELETE FROM emp_temp WHERE department_id = depts(j);

-- Howmany rows were affected by each DELETE statement?

FOR i IN 1..3

LOOP

DBMS_OUTPUT.PUT_LINE('Iteration #' || i || ' deleted' ||SQL%BULK_ROWCOUNT(i) || ' rows.');

END LOOP;

END;

--------------------------------------

Iteration #1 deleted0 rows.

Iteration #2 deleted0 rows.

Iteration #3 deleted5 rows.

通常对于insert %BULK_ROWCOUNT = 1, 因为一般情况下一次插入一条数据。但也可以一次插入多条数据。

CREATE TABLE emp_by_dept AS SELECTemployee_id, department_id

FROM hr.employeesWHERE 1 = 0;

DECLARE
TYPE dept_tab IS TABLEOF hr.departments.department_id%TYPE;
deptnums dept_tab;
BEGIN
SELECT department_id BULKCOLLECT INTO deptnums FROM hr.departments;
FORALL i IN1 .. deptnums.COUNT
    INSERT INTO emp_by_dept
      SELECT employee_id,department_id
        FROM hr.employees
       WHERE department_id =deptnums(i);
FOR i IN 1 ..deptnums.COUNT LOOP
    -- Counthow many rows were inserted for each department; that is,
    -- howmany employees are in each department.
    DBMS_OUTPUT.PUT_LINE('Dept' || deptnums(i) || ':inserted ' ||
                         SQL%BULK_ROWCOUNT(i)|| ' records');
END LOOP;
DBMS_OUTPUT.PUT_LINE('Totalrecords inserted: ' || SQL%ROWCOUNT);
END;

Dept 10: inserted 1 records
Dept 20: inserted 2 records
Dept 30: inserted 6 records
Dept 40: inserted 1 records
Dept 50: inserted 45 records
Dept 60: inserted 5 records
Dept 70: inserted 1 records
Dept 80: inserted 34 records
Dept 90: inserted 3 records
Dept 100: inserted 6 records
Dept 110: inserted 2 records
Dept 120: inserted 0 records
Dept 130: inserted 0 records
Dept 140: inserted 0 records
Dept 150: inserted 0 records
Dept 160: inserted 0 records
Dept 170: inserted 0 records
Dept 180: inserted 0 records
Dept 190: inserted 0 records
Dept 200: inserted 0 records
Dept 210: inserted 0 records
Dept 220: inserted 0 records
Dept 230: inserted 0 records
Dept 240: inserted 0 records
Dept 250: inserted 0 records
Dept 260: inserted 0 records
Dept 270: inserted 0 records
Total records inserted: 106

SQL%ROWCOUNT返回了最近FORALL的DML的影响的条数

%FOUND and %NOTFOUND refer only to the lastexecution of the SQL statement. You can use %BULK_ROWCOUNT to infer their values for individualexecutions. For example, when %BULK_ROWCOUNT(i) is zero, %FOUND and %NOTFOUND are FALSE and TRUE, respectively.

FORALL exception

处理FORALLExceptions (%BULK_EXCEPTIONS Attribute)

PL/SQL 提供了一种机制用来处理在FORALL　DML过程中的例外信息。　这种机制开启了集合绑定操作中的例外信息，在遇到例外的时候，程序并没有退出而并作进一步处理。

%BULK_EXCEPTIONS 是一个数组记录了例外信息，它有两个字段组成：

１、%BULK_EXCEPTIONS(i).ERROR_INDEX

２、%BULK_EXCEPTIONS(i).ERROR_CODE

%BULK_EXCEPTIONS.COUNT.记录了例外的个数

You might need to work backward to determine whichcollection element was used in　theiteration that caused an exception. For example, if you use the INDICES OF clause　to process a sparse collection, you must stepthrough the elements one by one to find　the onecorresponding to %BULK_EXCEPTIONS(i).ERROR_INDEX. If you use the　VALUES OF clause to process a subset of elements, you must find theelement in the　index collection whose subscript matches %BULK_EXCEPTIONS(i).ERROR_INDEX,　and then use that element's value as thesubscript to find the erroneous element in the　originalcollection.

If you omit the keywords SAVE EXCEPTIONS, execution of the FORALL statement　stops when an exception is raised. In thatcase, SQL%BULK_EXCEPTIONS.COUNT　returns1, and SQL%BULK_EXCEPTIONScontainsjust one record. If no exception is　raisedduring execution, SQL%BULK_EXCEPTIONS.COUNT returns 0.

Drop tableemp_temp purge;
CREATE TABLE emp_temp AS SELECT * FROMhr.employees;

DECLARE
TYPE empid_tab IS TABLE OFhr.employees.employee_id%TYPE;
emp_sr empid_tab;
-- create an exception handler for ORA-24381
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
SELECT employee_id
BULK COLLECT INTO emp_sr FROM emp_temp
WHERE hire_date < to_date('30-12-94', 'dd-mm-yy');
-- add '_SR' to the job_id of the most senior employees
FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS
UPDATE emp_temp SET job_id = job_id || '_SR'
WHERE emp_sr(i) = emp_temp.employee_id;
-- If any errors occurred during the FORALL SAVEEXCEPTIONS,
-- a single exception is raised when the statementcompletes.
EXCEPTION
-- Figure out what failed and why
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' ||errors);

FOR i IN 1..errorsLOOP
DBMS_OUTPUT.PUT_LINE('Error #' || i ||' occurred during '||'iteration#' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
DBMS_OUTPUT.PUT_LINE('Error message is ' ||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;

Number of statements that failed: 51

Error #1occurred during iteration #1

Errormessage is ORA-12899: 列的值太大 (实际值: , 最大值: )

Error #2occurred during iteration #2

Errormessage is ORA-12899: 列的值太大 (实际值: , 最大值: )

Error #3occurred during iteration #9

Errormessage is ORA-12899: 列的值太大 (实际值: , 最大值: )

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

PL/SQL raises predefinedexceptions because updated values were

too large to insert into the job_id column. After the FORALL statement, SQL%BULK_

EXCEPTIONS.COUNT returned 2, and the contentsof SQL%BULK_EXCEPTIONSwere

(7,12899) and (13,12899).

To get the Oracle Database error message (which includesthe code), the value of

SQL%BULK_EXCEPTIONS(i).ERROR_CODE was negated and then passed tothe

error-reportingfunction SQLERRM, whichexpects a negative number.

我们要看那些数据引起了例外

增加

DBMS_OUTPUT.PUT_LINE('Error(' || i || ') happens when employee_id='|| emp_sr(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX));

Error (41) happens when employee_id=187

createtable EMP_TEMP

(

EMPLOYEE_ID NUMBER(6),

FIRST_NAME VARCHAR2(20),

LAST_NAME VARCHAR2(25) not null,

EMAIL VARCHAR2(25) not null,

PHONE_NUMBER VARCHAR2(20),

HIRE_DATE DATE not null,

JOB_ID VARCHAR2(10) notnull,

SALARY NUMBER(8,2),

COMMISSION_PCT NUMBER(2,2),

MANAGER_ID NUMBER(6),

DEPARTMENT_ID NUMBER(4)

)

selectlength(job_id), length( job_id || '_SR') from emp_temp whereemp_temp.employee_id=187

―――――――

8 11

return 与　集合

Drop tableemp_temp purge;
CREATE TABLE emp_temp AS SELECT * FROMhr.employees;

DECLARE
TYPE NumList IS TABLE OFhr.employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OFhr.employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM emp_temp
WHERE department_id = 30

　　RETURNING employee_id, last_name

　　BULK COLLECT INTO
enums, names;
DBMS_OUTPUT.PUT_LINE('Deleted ' ||SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee #' ||enums(i) || ': ' || names(i));
END LOOP;
END;

FORALL 与 return …　bulk colect

Droptable emp_temp purge;

CREATETABLE emp_temp AS SELECT * FROM hr.employees;

DECLARE
TYPE NumList IS TABLEOF NUMBER;
depts NumList := NumList(10, 20, 30);
TYPE enum_t IS TABLEOF hr.employees.employee_id%TYPE;
TYPE dept_t IS TABLEOF hr.employees.department_id%TYPE;
e_ids enum_t;
d_ids dept_t;
BEGIN
FORALL j INdepts.FIRST .. depts.LAST
DELETE FROM emp_temp
WHERE department_id =depts(j)

RETURNINGemployee_id, department_id

BULK COLLECTINTO e_ids, d_ids;
DBMS_OUTPUT.PUT_LINE('Deleted' || SQL%ROWCOUNT || 'rows:');
FOR i IN e_ids.FIRST.. e_ids.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee#' || e_ids(i) || ' from dept #' ||d_ids(i));
END LOOP;
END;

那些例外的数据是否包含在return中

DECLARE

TYPE empid_tab IS TABLE OFhr.employees.employee_id%TYPE;

emp_sr empid_tab;

-- create an exception handler for ORA-24381

errors NUMBER;

dml_errors EXCEPTION;

PRAGMA EXCEPTION_INIT(dml_errors, -24381);

type typ_emp_id is table ofhr.employees.employee_id%type;

c_emp typ_emp_id;

BEGIN

SELECTemployee_id

BULK COLLECT INTO emp_sr FROM emp_temp

WHERE hire_date < to_date('30-12-94', 'dd-mm-yy')and rownum<=2;

-- add '_SR' to the job_id of the most senioremployees

FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS

UPDATE emp_temp SET job_id = job_id || '_S'

WHERE emp_sr(i) = emp_temp.employee_id

RETURNING employee_id bulk collect into c_emp;

dbms_output.put_line('______________________________');

for i in 1.. c_emp.count loop

dbms_output.put_line('ppp'||c_emp(i));

end loop;

EXCEPTION

WHENdml_errors THEN

errors := SQL%BULK_EXCEPTIONS.COUNT;

DBMS_OUTPUT.PUT_LINE('Number of statements thatfailed: ' || errors);

FOR i IN1..errors LOOP

DBMS_OUTPUT.PUT_LINE('Error (' || i || ') happens when employee_id=' ||emp_sr(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX));

END LOOP;

dbms_output.put_line('______________________________');

for i in 1.. c_emp.count loop

dbms_output.put_line('those records ofsuccess update is '||c_emp(i));

end loop;

END;

注意红色部分代码的位置

Number of statements that failed: 1
Error (1) happens when employee_id=206
______________________________
those records of success update is   198
those records of success update is   199
those records of success update is   200
those records of success update is   201
those records of success update is   202
those records of success update is   203
those records of success update is   204
those records of success update is   205
those records of success update is   100