1、定义一个集合,让它作为一个函数或过程的形式参数,这样就可以传递这个集合类型的参数。
看下面的例子:定义一个过程参数--内嵌表
create or replace package personnel --定义一个包
as
type staff_list is table of emp.empno%type; -- 定义了一个emp.empno 的内嵌表,表的类型为emp_empno。
procedure award_bonuses(emp_sal in staff_list); --定义了一个过程名字叫award_bonuses, 参数为 staff_list,也就是 -- 说 emp_sal的类型 是内嵌表的类型。
end personnel; --包头声明结束。
/
create or replace package body personnel --声明包体。
as
procedure award_bonuses(emp_sal staff_list) --声明过程体,具体的操作。 emp_sal的类型 是内嵌表的类型。
is
begin
for i in emp_sal.first .. emp_sal.last -- 因为emp_sal的类型是内嵌表类型,所以可以对它进行循环遍历操作。
loop
update emp set emp.sal=emp.sal+1000 where emp.empno = emp_sal(i); --把emp_sal用i循环赋值给empno。
end loop;
end;
end;
接下来,调用这个带有内嵌表参数的过程:
declare
good_employees personnel.staff_list; --定义一个staff_list 类型的内嵌表。
begin
good_employees:=personnel.staff_list(100,103,107); --对内嵌表赋值。
personnel.award_bonuses(good_employees); --将内嵌表作为参数传递给personnel.award_bonuses 过程。用这个过程来处理这个内嵌表,那么这个过程是怎么处理的呢?当然是看上面procedure 体声明部分咯。
end;
/
集合中的赋值和比较:
赋值:贴出几个例子 看完也就会了,有空自己复习复习...
declare --声明内嵌表nested_typ;
type nested_typ is table of number;
nt1 nested_typ:=nested_typ(1,2,3);
nt2 nested_typ:=nested_typ(3,2,1);
nt3 nested-typ:=nested_typ(2,3,1,3);
nt4 nested_typ:=nested_typ(1,2,4); --分别构造5个内嵌表,并给前四个赋值
answer nested_typ;
procedure print_nested_table(the_nt nested_typ) is --声明一个处理内嵌表的过程
output varchar2(128);
begin
if the_nt is null then
dbms_output.put_line('results: <null>');
return;
end if;
if the_nt.count =0 then
dbms_output.put_line('results: empty set');
return;
end if;
for i in the_nt.first .. the_nt.last
loop
output:= output||the_nt(i)||'':
end loop;
dbms_output.put_line('results:'|| output);
end; --过程结束。
begin --整个程序的开始,注意与上面的begin区分地位,上面的是一个过程的,
--这个是全局的
answer := nt1 MULTISET UNION nt4; -- (1,2,3,1,2,4)输出并集,包含重复值
print_nested_table(answer);
answer := nt1 MULTISET UNION nt3; -- (1,2,3,2,3,1,3)输出并集,包含重复值
print_nested_table(answer);
answer := nt1 MULTISET UNION DISTINCT nt3; -- (1,2,3) 输出并集,不包含重复值
print_nested_table(answer);
answer := nt2 MULTISET INTERSECT nt3; -- (3,2,1) 输出交集
print_nested_table(answer);
answer := nt2 MULTISET INTERSECT DISTINCT nt3; -- (3,2,1) 我估计跟上面一样,自己也不确定
print_nested_table(answer);
answer := SET(nt3); -- (2,3,1)估计是去掉重复值
print_nested_table(answer);
answer := nt3 MULTISET EXCEPT nt2; -- (3) 找出nt3中减去nt2中得值。
print_nested_table(answer);
answer := nt3 MULTISET EXCEPT DISTINCT nt2; -- () 找不nt3中不在nt2中的值。
print_nested_table(answer);
END;
-----------------------------------------------------------------------------------------------------------
用复合类型给可变数组赋值
declare
type emp_name_rec is record(
firstname employees.first_name%type,
lastname employees.last_name%type,
hiredate employees.hire_date%type
);
type emplist_arr is varray(10) of emp_name_rec;
seniorsalespeople emplist_arr;
cursor c1 is select first_name,last_name,hire_date from employees;
type nameset is table of c1%rowtype;
seniorten nameset;
endcounter number:=10;
begin
seniorsalespeople:=emplist_arr(); --可变数组是空的,随时在下面的语句中赋值。
select first_name,last_name,hire_date bulk collect into seniorten from employees where job_id='as_rep' order by hire_date;
if seniorten.last>0 then
if seniorten.last<10 then endcounter:=seniorten.last;
end if ;
for i in 1 .. endcounter loop
seniorsalespeople.extend(1); --在数组的末尾加一个空元素,注意:组合数组一定不能用extend方法。
seniorsalespeople(i):=seniorten(i); --接着给这个上一句的空元素赋值。
dbms_output.put_line(seniorsalespeople(i).lastname||','||seniorsalespeople(i).firstname||','||seniorsalespeople
(i).hiredate);
end loop;
end if;
end;
------------------------------------------------------------------------------------------------------------------------
用复合数据类型给表赋值
declare
type emp_name_rec is record(
firstname employees.first_name%type,
lastname employees.last_name%type,
hiredate employees.hire_date%type
);
type emplist_tab is table of emp_name_rec;
seniorsalespeople emplist_tab;
cursor c1 is select first_name,last_name,hire_date from employees;
endcounter number:=10;
type empcurtyp is ref cursor; --这是一种新的类型声明。引用游标类型。
emp_cv empcurtyp; --用这个新的游标类型声明一个形参。
begin
open emp_cv for select first_name,last_name,hire_date from employees where job_id='sa_rep' order by hire_date;
fetch emp_cv BULK COLLECT INTO seniorsalespeople;
close emp_cv;
if seniorsalespeople.last>0 then --判断为非空。
if seniorsalespeople.last <10 then endcounter:=seniorsalespeople.last; --求出endcounter的数量。DECLARE
TYPE emp_name_rec is RECORD (
firstname employees.first_name%TYPE,
lastname employees.last_name%TYPE,
hiredate employees.hire_date%TYPE
);
TYPE staff IS TABLE OF emp_name_rec;
members staff;
BEGIN
-- Condition yields TRUE because we have not used a constructor.
IF members IS NULL THEN
DBMS_OUTPUT.PUT_LINE('NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('Not NULL');
END IF;
END;
end if ;
for i in 1 ..endcounter loop
dbms_output.putline(seniorsalespeople(i).lastname||',' ||seniorsalespeople(i).firstname||','||seniorsalespeople
(i).hiredate);
end loop;
end if ;
end;
比较:DECLARETYPE nested_typ IS TABLE OF NUMBER;nt1 nested_typ := nested_typ(1,2,3);nt2 nested_typ := nested_typ(3,2,1);nt3 nested_typ := nested_typ(2,3,1,3);nt4 nested_typ := nested_typ(1,2,4);answer BOOLEAN;howmany NUMBER;PROCEDURE testify(truth BOOLEAN DEFAULT NULL, quantity NUMBER DEFAULT NULL) ISBEGINIF truth IS NOT NULL THENDBMS_OUTPUT.PUT_LINE(CASE truth WHEN TRUE THEN 'True' WHEN FALSE THEN'False' END);END IF;IF quantity IS NOT NULL THENDBMS_OUTPUT.PUT_LINE(quantity);END IF;END;BEGINanswer := nt1 IN (nt2,nt3,nt4); -- true, nt1 matches nt2testify(truth => answer);answer := nt1 SUBMULTISET OF nt3; -- true, all elements matchtestify(truth => answer);answer := nt1 NOT SUBMULTISET OF nt4; -- also truetestify(truth => answer);howmany := CARDINALITY(nt3); -- number of elements in nt3testify(quantity => howmany);howmany := CARDINALITY(SET(nt3)); -- number of distinct elementstestify(quantity => howmany);answer := 4 MEMBER OF nt1; -- false, no element matchestestify(truth => answer);answer := nt3 IS A SET; -- false, nt3 has duplicatestestify(truth => answer);answer := nt3 IS NOT A SET; -- true, nt3 has duplicatestestify(truth => answer);answer := nt1 IS EMPTY; -- false, nt1 has some memberstestify(truth => answer);
END;
集合函数:
这个早就学过了,不过系统的再看一遍,查查自己不懂的。
注意以下几点; 1、集合函数不能用在sql的声明中。
2、extend 和 trim 不能用在联合数组中,我估计是因为联合数组在声明的时候就确定了大小的缘故。
3、exists,count,limit,first,last,prior,和 next 都是函数; extend ,trim, 还有delete都属于过程。
4、exists,prior,next,trim,extend,和delete 的参数要和集合的标注一样,他们在联合数组中经常是inregeres 或者 strings
5、只有exists 可以被应用与空的集合,如果你用别的方法对空集合操作,将会提示:COLLECTION_IS_NULL.
你可以用exists函数来避免一个参照空值的错误,这样会抛出一个异常。当传入一个out-of-range的下标,exists函数会返回false而不会产生SUBSCRIPT_OUTSIDE_LIMIT异常。Example 5–28 Checking Whether a Collection Element EXISTS
Example 5–29 Counting Collection Elements With COUNTDECLARE
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(1,3,5,7);
BEGIN
n.DELETE(2); -- Delete the second element
IF n.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE('OK, element #1 exists.');
END IF;
IF n.EXISTS(2) = FALSE THEN
DBMS_OUTPUT.PUT_LINE('OK, element #2 has been deleted.');
END IF;
IF n.EXISTS(99) = FALSE THEN
DBMS_OUTPUT.PUT_LINE('OK, element #99 does not exist at all.');
END IF;
END
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(2,4,6,8); -- Collection starts with 4 elements.
BEGIN
DBMS_OUTPUT.PUT_LINE('There are ' || n.COUNT || ' elements in N.');
n.EXTEND(3); -- Add 3 new elements at the end.
DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');
n := NumList(86,99); -- Assign a completely new value with 2 elements.
DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');
n.TRIM(2); -- Remove the last 2 elements, leaving none.
DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');
END;
/
COUNT is useful because the current size of a collection is not always known. For
example, you can fetch a column of Oracle data into a nested table, where the number
of elements depends on the size of the result set.
For varrays, COUNT always equals LAST. You can increase or decrease the size of a
varray using the EXTEND and TRIM methods, so the value of COUNT can change, up to
the value of the LIMIT method.
For nested tables, COUNT normally equals LAST. But, if you delete elements from the
middle of a nested table, COUNT becomes smaller than LAST. When tallying elements,
COUNT ignores deleted elements. Using DELETE with no parameters sets COUNT to 0.