oracle exit函数,oracle之集合函数、过程

PL/SQL集合方法、集合赋值、比较集合

集合方法

集合方法是oracle所提供的用于操纵集合变量的内置函数或过程，其中exists，count，limit，first，next，prior，next是函数，而extend，trim，delete则是过程。集合调用语法如下：

collection_name.method_name[(parameters)]

注意，集合方法只能在PL/SQL语句中使用，而不能在SQL语句中使用。另外集合方法extend和trim只适用于嵌套表和varray，而不适用于索引表。

--exists 用于确定集合元素是否存在

declare

type area_table_type is table

of emp.ename%type;

area_table

area_table_type;

begin

if

area_table.exists(1) then

area_table(1):='tt';

else

dbms_output.put_line('必须初始化元素');

end

if;

end;  注：嵌套表和VARRAY在引用前必须先初始化相应元素，而索引表可以直接在select语句中引用。

--count的使用(统计元素总数)如果集合元素存在数值，则统计结果会包含该元素，如果集合元素为NULL，则统计结果不含该元素。

declare

type area_table_type is table

of emp.ename%type

index by binary_integer;

area_table

area_table_type;

begin

area_table(-1):='tt';

area_table(3):='cc';

area_table(4):='gg';

dbms_output.put_line('集合元素总个数:'||area_table.count);

end;  --limit的使用(返回集合元素的最大个数)

该方法用于返回集合元素的最大个数。因为嵌套表和索引表的元素个数没有限制，所以调用该方法会返回NULL；而对于VARRAY来说，该方法会返回其所允许的最大元素个数。

declare

type area_table_type is

varray(30) of emp.ename%type;

area_table

area_table_type:=area_table_type('ff');

begin

dbms_output.put_line('集合元素的最大个数:'||area_table.limit);

end;

--first和last的使用

first方法用于返回集合变量第一个元素的下标，而last方法用于返回集合变量最后一个元素的下标。

declare

type area_table_type is table

of emp.ename%type

index by binary_integer;

area_table

area_table_type;

begin

area_table(-1):='tt';

area_table(3):='cc';

area_table(4):='gg';

dbms_output.put_line('第一个元素:'||area_table.first);

dbms_output.put_line('最后一个元素:'||area_table.last);

end;

--prior和next的使用

prior用于返回当前集合元素的前一个元素的下标，而next方法用于当前集合元素的后一个元素的下标。

declare

type area_table_type is table

of emp.ename%type

index by binary_integer;

area_table

area_table_type;

begin

area_table(-1):='tt';

area_table(3):='cc';

area_table(4):='gg';

dbms_output.put_line('元素3的前一个元素:'||area_table.prior(3));

dbms_output.put_line('元素3的下一个元素:'||area_table.next(3));

end;

--extend

用于扩展集合变量的尺寸，并为它们增加元素。该方法有extend，extend(n),extend(n,i)等三种调用格式，只适用于嵌套表和VARRAY。

其中:

extend用于为集合变量添加一个null元素;

extend(n)用于为集合变量添加n个null元素;

extend(n,i)用于为集合变量添加n个元素(元素值与第i个元素相同)

declare

type area_table_type is

varray(20) of varchar2(20);

area_table

area_table_type;

begin

area_table:=area_table_type('tt');

area_table.extend(5,1);

dbms_output.put_line('元素总个数:'||area_table.count);

end;  --trim 裁剪元素

该方法用于从集合尾部删除元素，它有TRIM和TRIM(n)两种调用格式。其中：

trim用于从集合尾部删除一个元素；

trim(n)则用于从集合尾部删除n个元素。

注：该方法只适用于嵌套表和VARRAY。

declare

type area_table_type is table

of varchar2(10);

area_table

area_table_type;

begin

area_table:=area_table_type('a','a','a','a');

area_table.trim(2); --裁掉2个元素

dbms_output.put_line('元素总个数:'||area_table.count); --总个数2个

end;  --delete 删掉某元素

该方法用于删除集合元素，但该方法只适用于嵌套表和索引表，而不适用于VARRAY。该方法有delete、delete(n)、delete(m,n)等三种调用格式。其中：

delete用于删除集合变量的所有元素；

delete(n)用于删除集合变量的第n个元素；

delete(m，n)用于删除集合变量从m到n之间的所有元素。

declare

type area_table_type is table

of emp.ename%type

index by binary_integer;

area_table

area_table_type;

begin

area_table(-1):='tt';

area_table(3):='cc';

area_table(4):='gg';

area_table.delete(3); --删掉元素3

dbms_output.put_line('元素总个数:'||area_table.count); --总个数2

end;

集合赋值

--将一个集合的数据赋值给另一个集合

DECLARE

TYPE name_varray_type IS VARRAY(4) OF

VARCHAR2(10);

name_array1 name_varray_type;

name_array2 name_varray_type;

BEGIN

name_array1:=name_varray_type('SCOTT','SMITH');

name_array2:=name_varray_type('a','a','a','a');

dbms_output.put('name_array2的原数据:');

FOR i IN 1..name_array2.count LOOP

dbms_output.put(' '||name_array2(i));

END LOOP;

dbms_output.new_line;

name_array2:=name_array1;

dbms_output.put('name_array2的新数据:');

for i IN 1..name_array2.count LOOP

dbms_output.put(' '||name_array2(i));

END LOOP;

dbms_output.new_line;

END;

输出结果 name_array2的原数据:'a','a','a','a'；

name_array2的新数据:'SCOTT','SMITH'

备注：当进行集合赋值时，源集合和目标集合的数据类型必须完全一致。如果集合元素数据类型一致，但集合类型不一致，也不能进行赋值。

如下错误的例子：

DECLARE

TYPE name_varray1_type IS

VARRAY(4) OF VARCHAR2(10);

TYPE name_varray2_type IS

VARRAY(4) OF VARCHAR2(10);

name_array1

name_varray1_type;

name_array2

name_varray2_type;

BEGIN

name_array1:=name_varray1_type('scott','smith');

name_array2:=name_array1;

END;

ERROR 位于第8行：

ORA-06550: 第8行第17列：

表达式类型错误

ORA-06550: 第8行第4列：

PL/SQL：Statement ignored

--给集合赋NULL值

当需要清空集合变量的所有数据时，既可以使用集合方法delete和trim，也可以使用一个null集合变量赋值给目标集合变量。

DECLARE

TYPE name_varray_type IS VARRAY(4) OF

VARCHAR2(10);

name_array name_varray_type;

name_empty name_varray_type;

BEGIN

name_array:=name_varray_type('SCOTT','SMITH');

dbms_output.put_line('name_array的原有元素个数:'||name_array.count);

name_array:=name_empty;

IF name_array IS NULL THEN

dbms_output.put_line('name_array的现有元素个数:0');

END IF;

END;

输出结果 name_array的原有元素个数:2

name_array的现有元素个数:0

--使用集合操作符给嵌套表赋值

--使用set操作符

set操作符用于取消特定嵌套表中的重复值。

DECLARE

TYPE nt_table_type IS table OF number;

nt_table

nt_table_type:=nt_table_type(2,4,3,1,2);

result nt_table_type;

BEGIN

result:=set(nt_table);

dbms_output.put('result:');

FOR i in 1..result.count LOOP

dbms_output.put_line(' '||result(i));

END LOOP;

dbms_output.new_line;

END;

result:

2 4

3

1

--multiset union操作符的使用 (两个几何合并)

DECLARE

TYPE nt_table_type IS table OF number;

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

nt2 nt_table_type:=nt_table_type(3,4,5);

result nt_table_type;

BEGIN

result:=nt1 multiset union nt2;

dbms_output.put('result:');

FOR i in 1..result.count LOOP

dbms_output.put_line(' '||result(i));

END LOOP;

dbms_output.new_line;

END;

result:

1

2

3

3

4

5

--multiset union distinct操作符的使用 (去掉合并后的重复值)

DECLARE

TYPE nt_table_type IS table OF number;

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

nt2 nt_table_type:=nt_table_type(3,4,5);

result nt_table_type;

BEGIN

result:=nt1 multiset union distinct nt2;

dbms_output.put('result:');

FOR i in 1..result.count LOOP

dbms_output.put_line(' '||result(i));

END LOOP;

dbms_output.new_line;

END;

result: 1

2

3

4

5

--multiset intersect操作符的使用

DECLARE

TYPE nt_table_type IS table OF number;

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

nt2 nt_table_type:=nt_table_type(3,4,5);

result nt_table_type;

BEGIN

result:=nt1 multiset intersect nt2;

dbms_output.put('result:');

FOR i in 1..result.count LOOP

dbms_output.put_line(' '||result(i));

END LOOP;

dbms_output.new_line;

END;

result: 3

--multiset except操作符的使用

DECLARE

TYPE nt_table_type IS table OF number;

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

nt2 nt_table_type:=nt_table_type(3,4,5);

result nt_table_type;

BEGIN

result:=nt1 multiset except nt2;

dbms_output.put('result:');

FOR i in 1..result.count LOOP

dbms_output.put_line(' '||result(i));

END LOOP;

dbms_output.new_line;

END;

result: 1 2(nt1表在nt2表中不存在的)

比较集合

在oracle10g之前，当使用嵌套表类型和varray类型的集合变量时，开发人员可以检测集合变量是否为null。从oracle10g开始，开发人员还可以比较两个集合变量是否相同，另外还可以在嵌套表上使用cardinality，submultiset

of，member of，is a set，isempty等集合操作符。其中

函数cardinality用于返回嵌套表变量的元素个数，操作符submultiset of用于检测特定数据是否为嵌套表元素，操作符is

a set用于检测嵌套表是否包含重复的元素值，操作符is empty用于加你侧嵌套表是否为null。

--检测集合是否为null

当编写复杂的pl/sql应用程序时，经常需要检测集合变量是否为null，在oracle10g之前，使用is

null操作符检测嵌套表或varray是否为null。

DECLARE

TYPE name_array_type IS varray(3) OF

varchar2(10);

name_array name_array_type;

BEGIN

IF name_array IS NULL THEN

dbms_output.put_line('name_array未初始化');

END IF;

END;

输出：name_array未初始化

oracle10g开始，当检测嵌套表是否为null时，不仅可以使用is null操作符，也可以使用 is empty操作符。注意，is

empty操作符只适用于嵌套表，而不适用于varray。

DECLARE

TYPE name_table_type IS TABLE OF

varchar2(10);

name_table name_table_type;

BEGIN

IF name_table IS empty THEN

dbms_output.put_line('name_table未初始化');

END IF;

END;

问题：没有输出结果，奇怪？

--比较嵌套表是否相同

在oracle10g之前，不能直接比较两个嵌套表是否相同。但从oracle10g开始，允许使用比较操作符=和！=检测两个嵌套表变量是否相同。注意，使用这两个比较符只能比较嵌套表，不能比较varray和索引表。

DECLARE

TYPE nt_table_type IS table OF

varchar2(10);

nt1 nt_table_type;

nt2 nt_table_type;

BEGIN

nt1:=nt_table_type('scott');

nt2:=nt_table_type('smith');

IF nt1=nt2 THEN

dbms_output.put_line('两个嵌套表完全相同');

else

dbms_output.put_line('两个嵌套表值不同');

END IF;

END;

输出结果：两个嵌套表值不同

--在嵌套表上使用集合操作符

从oracle10g开始，开发人员可以在嵌套表上使用ANSI集合操作符CARDINALITY,MEMBEROF,IS A

SET。注意，这些操作符只适用于嵌套表，不适用于varray和索引表。

--使用cardinality(返回嵌套表变量的元素个数)

DECLARE

TYPE nt_table_type IS table OF number;

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

BEGIN

dbms_output.put_line('元素个数:'||cardinality(nt1));

END;

元素个数:4

--SUBMULTISET OF 用于确定一个嵌套表是否是另一个嵌套表的子集

DECLARE

TYPE nt_table_type IS table OF number;

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

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

BEGIN

IF nt1 SUBMULTISET OF nt2 THEN

dbms_output.put_line('nt1是nt2的子集');

END IF;

END;

nt1是nt2的子集

--MEMBER OF 检测特定数据是否是嵌套表的元素

DECLARE

TYPE nt_table_type IS table OF number;

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

v1 NUMBER:=&V1;

BEGIN

IF v1 MEMBER OF nt1 THEN

dbms_output.put_line('v1是nt1的元素');

END IF;

END;

输入的值：1

v1是nt1的元素

--IS A SET 检测嵌套表是否包含重复的元素值

DECLARE

TYPE nt_table_type IS table OF number;

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

BEGIN

IF nt1 IS A SET THEN

dbms_output.put_line('嵌套表nt1无重复值');

END IF;

END;

嵌套表nt1无重复值

2010年12月2日 补充

对于index_by 不存在的元素

index_by表中的元素i如果不存在，实际上会创建一个元素i，类似于对表的insert

操作；对元素的引用类似于select操作。但是，如果元素i还没创建就被引用，pl\

sql会报错，提示“ORA-1403：no data found ”。如下语句：

DECLARE

TYPE NumberTab IS TABLE OF NUMBER

INDEX BY BINARY_INTEGER;

v_Numbers NumberTab;

BEGIN

FOR v_count IN 1..10 LOOP

v_Numbers(v_count) := v_count * 10;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Table elements: ');

FOR v_count IN 1..10 LOOP

DBMS_OUTPUT.put_line('v_Numbers('||v_count||'): '

||v_numbers(v_count));

END LOOP;

BEGIN

DBMS_OUTPUT.put_line('v_numbers(11): '||

v_numbers(11));

EXCEPTION

WHEN

NO_DATA_FOUND THEN

DBMS_OUTPUT.put_line('No data found reading v_numbers(11)!');

END;

END;

输出结果如下：

Table elements:

v_Numbers(1): 10

v_Numbers(2): 20

v_Numbers(3): 30

v_Numbers(4): 40

v_Numbers(5): 50

v_Numbers(6): 60

v_Numbers(7): 70

v_Numbers(8): 80

v_Numbers(9): 90

v_Numbers(10): 100

No data found reading v_numbers(11)!