oracle 集合

 属性:

 

I.表类型

--1.索引组织表,存放在内存中的表.
  DECLARE
    TYPE t_indexTable IS TABLE OF emp%ROWTYPE NOT NULL INDEX BY BINARY_INTEGER;
    v_indexTable t_indexTable;
    CURSOR v_cur IS
      SELECT * FROM emp;
  BEGIN
    FOR c IN v_cur LOOP
      v_indexTable(c.empno):=c;
      IF v_indexTable.EXISTS(c.empno) THEN
        DBMS_OUTPUT.PUT_LINE('v_indexTable('||c.empno||').empno=' || c.empno
                         ||';v_indexTable('||c.empno||').ename='||c.ename);
      ELSE
        DBMS_OUTPUT.PUT_LINE('v_indexTable('||c.empno||'):不存在');
      END IF;
    END LOOP;
  END;

注:INDEX BY BINARY_INTEGER表示索引表,否则为嵌套表.索引表类型的下标不一定是连续的,而嵌套表的下标是连续的,表类型可以删除某个下标值,数组类型不行.

 

--2.嵌套表.可以存放在数据库中

a.表必须先覆值才能引用,否则会抛出ora-06531异常.
b.初使化用构造函数来初使化.
如:
  v_mytable my_nestedtable:=my_nestedtable(1,2);
  可以用不带参数的构造函数初使化.
c.可以用is null判断是否已初使化
d.可以用extend扩展表元素

例子1:

DECLARE
  TYPE my_nestedtable IS TABLE OF number(6);
  v_mytable my_nestedtable;
BEGIN
    v_mytable:=my_nestedtable();
    v_mytable:=my_nestedtable(1,2,3);
    DBMS_OUTPUT.PUT_LINE(v_mytable(3)||' '||v_mytable(1));
    v_mytable.DELETE(2);
    --当2元素被删除时,抛出ora-01403:no data found异常,元素3还存在
    --DBMS_OUTPUT.PUT_LINE(v_mytable(2)||' '||v_mytable(1));
    --会抛出ora-06433:subscript beyond count异常
    --v_mytable(4):=4;
    --扩展表元素
    v_mytable.EXTEND;
    v_mytable(4):=4;
    --可以重新为2下标覆值
    v_mytable(2):=2;
    DBMS_OUTPUT.PUT_LINE(v_mytable(4)||' '||v_mytable(1)||' '||v_mytable(2));
END;

例子2:

1).结构

CREATE TYPE student AS OBJECT(
 vid number(4),
 vname varchar2(10),
 vsex varchar2(2),
 vaddress varchar2(40)
);

CREATE TYPE studentlist AS TABLE OF student;

CREATE TABLE nestedtable_test
(
  vid number(4),
  vbj varchar2(14),
  students studentlist
)
NESTED TABLE students STORE AS students_tab;

 

2).操作

a).INSERT
DECLARE
/*声明并初始化一个studentlist类型的嵌套表*/
  v_student studentlist:=studentlist(student(11,'刘德华','男','广州市共和大街46号'));
BEGIN
 /* 用嵌套表变量v_student给数据库表赋值*/
 INSERT INTO nestedtable_test VALUES(1001,'计算机01',v_student);
 /*也可以像下面这样直接给数据库表赋值*/
 INSERT INTO nestedtable_test VALUES(1002,'计算机02',
    studentlist(student(2001,'恢复的','男','北京市王府井大街105号'),
            student(2002,'于小姐','女','重庆市钩子巷88号')));
 COMMIT;
END;

b).UPDATE
DECLARE
  V_TABLEVAR studentlist:=studentlist(student(11,'刘德华','男','广州市共和大街46号'),
    student(21,'周小姐','女','广州市北京路846号'));
BEGIN
 /*用定义好的嵌套表变量来更新NESTEDTABLE_TEST中的vid=1001中存储的嵌套表*/
 UPDATE nestedtable_test set students=v_tablevar
 WHERE vid=1001;
 COMMIT;
END;

c).DELETE
DELETE FROM nestedtable_test WHERE vid=1001;
COMMIT;
/*上面语句删除表中对应的行,并把对应的嵌套表也删除了*/

d).SELECT
/*可以把嵌套表检索到一个PL/SQL变量中.再对变量进行操作.
注意:数据库中的嵌套表不能用在WHERE子句中,并且在隐含需要进行比较的子句中都不能使用
嵌套表,如ORDER BY/GROUP BY/DISTINCT等子句中.*/
DECLARE
  v_vid nestedtable_test.vid%TYPE;
  v_vbj nestedtable_test.vbj%TYPE;
  v_students nestedtable_test.students%TYPE;
  CURSOR mycursor IS SELECT vid,vbj,students FROM nestedtable_test;
BEGIN
 OPEN mycursor;
 LOOP
  FETCH mycursor INTO v_vid,v_vbj,v_students;
  exit WHEN mycursor%NOTFOUND;
  dbms_output.put_line('vid is '||v_vid||' vbj is '||v_vbj);
  IF v_students IS NOT NULL THEN
    FOR key_num IN 1..v_students.count LOOP
      dbms_output.put_line('students('||key_num||').vname is'||v_students(key_num).vname);
      dbms_output.put_line('students('||key_num||').vaddress is'||v_students(key_num).vaddress);
    END LOOP;
  END IF;
 END LOOP;
 CLOSE mycursor;
EXCEPTION
  WHEN others THEN
    CLOSE mycursor;
END;

e).THE
/*THE 可以直接操纵数据库中的嵌套表.THE将一个子查询作为一个参数,其返回
数据库表中的嵌套表,这样我们就可以对嵌套表进行操作.*/
  UPDATE THE(SELECT students FROM nestedtable_test WHERE vid=1001)
  set vid=vid+1008 WHERE vid=11;
/*注意:SET子句中的VID不是数据库表中的列,而是嵌套表中元素的一个字段,
上例是将嵌套表中元素字段VID为11的字段值改为1019.*/

 

附:

SELECT *
  FROM THE (SELECT T.STUDENTS FROM NESTEDTABLE_TEST T WHERE VID = 1001)

子查询必须是单行的

 

--3.数组(varray)

  type varry_name is {varray|varying array}(max_size) of varray_type[not null]

 

DECLARE
  TYPE my_varray IS VARRAY(18) OF varchar2(8);
  myvarray1 my_varray;
  myvarray2 my_varray;
  myvarray3 my_varray;
BEGIN
  myvarray1:=my_varray('张学友','李连杰');
  /*用带NULL参数的构造函数初始化myvarray2,使之只有一个含NULL的元素*/
  myvarray2:=my_varray(NULL);
  IF myvarray2(1) IS NULL THEN
    dbms_output.put_line('myvarray2(1) is null');
  END IF; 
  IF myvarray3 IS NULL THEN
    dbms_output.put_line('myvarray3 is null');
  END IF;
  IF myvarray2 IS NULL THEN
    --2数组不为空,这句不输出
    dbms_output.put_line('myvarray2 is null');
  END IF;
  dbms_output.put_line('myvarray1(1) is '||myvarray1(1)||' myvarray1(2) is '||myvarray1(2));
  --不能引用未初始化的元素
  --myvarray1(4):='aa';
  --可以用extend扩展
  myvarray1.EXTEND;
  /*不能引用未实例化的数组
  myvarray3.EXTEND;
  myvarray3(1):='aa';*/
  myvarray3:=my_varray(NULL);
  myvarray2(1):='aa';
  dbms_output.put_line(myvarray2(1));
END;

 

--b).库中的数组.

CREATE TYPE namelist AS VARRAY(10) OF student;

CREATE TABLE varray_test
(
  vid number(4),
  names namelist
);

注:和嵌套表不同的是,数组的存储组织和数据库行是完全相同的,并且数组数据是作为数据库表数据进行存储的,它不需要指明一个存储表的名字来存储.

不能直接更新数据库中数组的值,要在pl/sql中,取到一个变量里面,再来更新,即不能直接:

UPDATE THE(SELECT NAMES FROM varray_test WHERE vid=1001)
  set vid=vid+1008 WHERE vid=11;

 

from：http://blog.itpub.net/post/35477/425956

集合方法
pl/sql预定义了在varray 和嵌套表实例上进行调用的方法。这些方法能在集合上执行一定的功能。

EXISTS	该函数返回   集合中第一个元素的索引，如果集合为空，返回NULLNULLNULL	Collection.EXISTS(index)
COUNT	该函数集合   元素的数目	Collection.COUNT
DELETE	该过程从嵌套表中删除一个或多个或合部元素	Table_name.DELETE 删除所有元素 Table_name.delete(index)删除指定索引的记录 Table_name.delete(start_index,end_index)删除区间内元素
FIRST	返回集合第一个元素索引，如果集合为空，返回NULL	Collection.FIRST
LAST	返回集合中最后一个元素索引，如果集合为空，返回NULL	Collection. LAST
NEXT	返回集合当前元素的下一个元素的索引，如果它不存在就返回NULL	Collection. NEXT
PRIOR	返回集合当前元素的上一个元素的索引，如果它不存在就返回NULL	Collection. PRIOR
LIMIT	返回varray中创建元素的最大个数	Collection. LIMIT
EXTENDS	该过程在集合的末尾添加新的元素	Collection.EXTEND添加一个NULL元素；Collection.extends(n)添加N个NULL元素，Collection.extend(n,index)添加由index指定位置上元素的n个副表
TRIM	从集合末尾处删除元素	Collection.TRIM 删除最后一个元素 Collection.TRIM(n)删除最后N个元素

 

数据加中的集合
与index-by表不同，varray和嵌套表可以作为对象-关系表中的珍存储在数据库中。它们也可以作为对象关系表中的列使用。为了表示出作为数据库列的数据类型，集合类型必须是在pl/sql和sql中可见。这需要使用create or replace type 语句定义而不能仅在pl/sql块中进行局部定义。语法如下：
create of replace type table_name is table of data_type

 

------from to me