oracle 中的集合（联合数组 内嵌表 可变数组）

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;

比较：

DECLARE

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);

answer BOOLEAN;

howmany NUMBER;

PROCEDURE testify(truth BOOLEAN DEFAULT NULL, quantity NUMBER DEFAULT NULL) IS

BEGIN

IF truth IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(CASE truth WHEN TRUE THEN 'True' WHEN FALSE THEN

'False' END);

END IF;

IF quantity IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(quantity);

END IF;

END;

BEGIN

answer := nt1 IN (nt2,nt3,nt4); -- true, nt1 matches nt2

testify(truth => answer);

answer := nt1 SUBMULTISET OF nt3; -- true, all elements match

testify(truth => answer);

answer := nt1 NOT SUBMULTISET OF nt4; -- also true

testify(truth => answer);

howmany := CARDINALITY(nt3); -- number of elements in nt3

testify(quantity => howmany);

howmany := CARDINALITY(SET(nt3)); -- number of distinct elements

testify(quantity => howmany);

answer := 4 MEMBER OF nt1; -- false, no element matches

testify(truth => answer);

answer := nt3 IS A SET; -- false, nt3 has duplicates

testify(truth => answer);

answer := nt3 IS NOT A SET; -- true, nt3 has duplicates

testify(truth => answer);

answer := nt1 IS EMPTY; -- false, nt1 has some members

testify(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

DECLARE

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

Example 5–29 Counting Collection Elements With COUNT

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.