PLSQL:oracle 集合类型

---

集  合

---

定义:

        集合是相同类型元素的组合。在集合中，使用唯一的下标来标识其中的每个元素

使用条件:

• 单行单列的数据，使用标量变量
  
• 单行多列数据，使用记录
  
• 多行单列数据，使用集合
  
• 多行多列数据 , 使用记录 + 集合

分类	语法	下标	元素个数	赋初始值	.extend	能否存在DB中
索引表	TYPE type_name IS TABLE OF element_type   INDEX BY index_type;	整数或字符 可以为负数	无限制	不用	不用	只能用在PLSQL中
嵌套表	TYPE type_name IS TABLE OF element_type;	整型 只能为整数	无限制	用同名构造函数赋值	用	可以使用create type 创建, 存在数据库中
变长数组	TYPE type_name IS VARRAY(n) OF element_type;		有限制

如何选择适用的集合类型?

• 索引表:通常来说，第一选择是索引表，因为它不需要初始化或者extend且无个数限制操作，并且是迄今为止最高效的集合类型。
  唯一不足的一点是它只能用于PL/SQL而不能直接用于数据库。
  如果你需要允许使用负数索引，应该选择索引表;
• 嵌套表: 如果你需要使用10g，11g中的那些集合操作，应该选择Nested table;
• 变长数组: 如果需要限制集合元素的个数
  

---

1、索引表

---

TYPE type_name IS TABLE OF element_type  INDEX BY index_type;  

table_name type_name ; 

element_type：集合中存放的元素的类型。如number、char, 也可以是记录

index_type：只能是整型或者字符串 pls_integer , binary_integer or char

存：集合变量名(下标) := 元素值; 

      或者

       使用游标赋值
取：集合变量名(下标)

例如:给集合赋值

FOR src IN 游标类型 LOOP

    --写法1:

    集合(下标).c1 := src.c1;

    集合(下标).c2 := src.c2;

    --写法2: 使用记录类型(下标) := 游标 i 直接给记录赋值, 屌屌的

    集合(下标) := src;

end loop;

 

示例表:

示例1：游标 + 集合
分别声明一个游标和一个索引表类型， 游标从student表中检索出前10个学生的姓名。遍历游标，打印结果

declare

  cursor name_cur is

    select last_name from student where rownum <= 10;

  type last_name_type is table of student.last_name%type index by pls_integer;

  last_name_tab last_name_type;

  

  v_idx pls_integer := 0;--index by pls_integer 需要一个整数变量做计数器

begin

  for i in name_cur loop --游标for循环

    v_idx := v_idx + 1; 

    last_name_tab(v_idx) := i.last_name; --索引表集合赋值

    dbms_output.put_line('插入 ' || last_name_tab(v_idx));

  end loop;

  

  for i in last_name_tab.first .. last_name_tab.last LOOP --数值for循环

    dbms_output.put_line('展示 : ' || last_name_tab(i));

  end loop;

end;

结果:

 

  注意：引用不存在的元素会抛出NO_DATA_FOUND异常。 
  例如， DBMS_OUTPUT.PUT_LINE (last_name_tab(11)); end; 

---

2、嵌套表

---

TYPE type_name IS TABLE OF element_type ;
table_name type_name ; 

和索引表区别:

• 嵌套表没有index of,其下标固定为整型
• 必须使用和其同名的构造器对其初始化
• 循环遍历时,必须持续给extend分配存储空间

例子:

将集合类型改为嵌套表类型 
因为当声明嵌套表时，嵌套表本身被自动设置为NULL. 为引用嵌套表中的单个元素，必须首先使用名为构造器的 系统定义函数对嵌套表进行初始化 
嵌套表的构造器函数() 与嵌套表类型完全同名 ，可以带参数也可以无参 

带参数初始化:
last_name_tab  :=  last_name_type('Rosenzweig' ,'Silvestrova'); 
不带参数初始化:
last_name_tab  :=  last_name_type();

修改示例1，对嵌套表进行初始化

declare

  cursor name_cur is select last_name from student where rownum <= 10;

  v_idx pls_integer := 0;

  type last_name_type is table of student.last_name%type; --定义嵌套表, 元素类型是学生姓名

  last_name_tab last_name_type := last_name_type(); --定义变量, 并初始化嵌套表!

begin

  for i in name_cur loop

    last_name_tab.extend; --必须先调用它的extend方法给集合添加存储空间

    v_idx := v_idx + 1;

    last_name_tab(v_idx) := i.last_name; --赋值,和取值语法是一样的!

    dbms_output.put_line('Insert ' || last_name_tab(v_idx));

  end loop;

end;

---

3、变长数组

---

定义:

         有最大长度限制的嵌套表, 编码上和嵌套表完全相同

语法:

TYPE type_name IS  VARRAY(最大值) OF element_type;

varray_name type_name ; 

例子:

修改上例，使用变长数组 

DECLARE 

    CURSOR name_cur IS 
      SELECT last_name 
      FROM   student 
      WHERE  ROWNUM <= 10; 
      v_idx         PLS_INTEGER := 0; 
    TYPE last_name_type IS varray(10) OF student.last_name%TYPE; 
    last_name_tab LAST_NAME_TYPE := Last_name_type(); 
BEGIN 
    FOR i IN name_cur LOOP 
        last_name_tab.extend; 
        v_idx := v_idx + 1; 
        Last_name_tab (v_idx) := i.last_name; 
        dbms_output.Put_line ('Insert ' || Last_name_tab (v_idx)); 
    END LOOP; 
END;

---

集合方法

---

定义:

        集合方法是集合对象的内置函数，可以使用"."标记来调用: 

exists(n) 索引处的元素是否存在, 返回TRUE|FALSE

count 当前集合中的元素总个数

limit 集合元素索引的最大值  (索引表和嵌套表是不限个数的，所以返回null，变长数组返回定义时的最大索引 )

first / last 返回集合第一个/最后一个元素的下标

prior / next 当前元素的前一个 / 后一个元素

extend 增加元素,扩展集合的容量,不能用于索引表

• x.extend    增加一个null元素  
  
• x.extend(n)    增加n个null元素
  
• x.extend(n,i)    增加n个元素，元素值与第i个元素相同

trim 从尾部删除, 不能用于索引表, 被删元素不保留占位符

• x.trim    删除一个元素
• x.trim(n)    删除n个元素

delete 按索引删除集合元素, 被删元素保留占位符

• delete    删除所有
  
• delete(n)    删除第n个
  
• delete(a,b)    删除a--b之间的所有元素
  

---

多层集合

---

元素类型为集合的集合

9i开始,可以创建多层集合,相当于其它语言中的二维数组

语法: 

varray_name(外层数组下标)(内下标) 

例子;:

DECLARE 
    TYPE varray_type1 IS varray(4) OF PLS_INTEGER;   --里面变长数组 元素类型是整形
    --二维数组类型 
    TYPE varray_type2 IS varray(3) OF VARRAY_TYPE1; --外面变长数组 元素类型是 变长数组
    varray1 VARRAY_TYPE1 := Varray_type1(2, 4, 6, 8);  --里面数组赋值
    varray2 VARRAY_TYPE2 := Varray_type2(varray1);     --外面数组赋值为里面数组
BEGIN 
    --初始化varray2(2) 
    varray2.extend;   --里面的数组为什么不用初始化? 明摆着...
    Varray2(2) := Varray_type1(1, 3, 5, 7);  --二维数组第二行赋值1 3 5 7
    -- 遍历varray2 
    FOR i IN 1..2 LOOP   --二维数组 2行 4列 遍历行1..2
        FOR j IN 1..4 LOOP   --二维数组 2行 4列 遍历列1..4
            dbms_output.Put_line('varray2(' ||i ||')(' || j ||')=' ||varray2(i)(j)); 
        END LOOP; 
    END LOOP; 
    -- 实际中遍历集合时，下标的取值范围由集合自己告诉你 
END;

declare 

    type varray_in is varray(4) of pls_integer; --内层相当于:4列

    type varray_out is varray(3) of varray_in; --外层相当于:3行

    varray_1 varray_in := varray_in(1,2,3,4); 

    varray_2 varray_out := varray_out(varray_1); 

begin

  varray_2.extend; 

  varray_2(2) := varray_in(5,6,7,8); 

  varray_2.extend; 

  varray_2(3) := varray_in(9,10,11,12);

  for i in varray_2.first..varray_2.last loop  --实际中遍历集合时，下标的取值范围由集合自己告诉你 

    for j in varray_1.first..varray_1.last loop

      dbms_output.put_line('array['||i||']['||j||']= '||varray_2(i)(j)); 

      if mod(j,4) = 0 then dbms_output.new_line(); end if;

    end loop; 

  end loop; 

end;

---

集合 + 记录

---

常表示多行多列数据

例子:

declare

  --声明游标类型,里面放4个学生的姓名

  cursor c_student is

    select first_name, last_name from student where rownum <= 4;

  type name_type is table of c_student%rowtype index by binary_integer; --声明索引表,里面的元素是基于上面游标的记录类型

  name_tab name_type; --初始化索引表

  v_index pls_integer := 0; --声明变量做下标 --声明做下标的变量，千万别忘记初值！

begin

  --遍历游标,把游标里的学生信息插入到集合中

  for i in c_student LOOP  --i是游标

    v_index := v_index + 1;

  

    --给集合赋值

    --写法1:

    name_tab(v_index).first_name := i.first_name;

    name_tab(v_index).last_name := i.last_name;

  

    --写法2: 使用记录类型(下标) := 游标 i 直接给记录赋值, 屌屌的

    name_tab(v_index) := i;

    dbms_output.put_line('遍历:' || name_tab(v_index).first_name || ' - ' || name_tab(v_index).last_name);

  end loop;

  --遍历集合, 注意和遍历游标的区别!

  --写法1: first/last方法返回集合上下标, 遇到被删除的元素会报错:找不到

  for i in name_tab.first .. name_tab.last LOOP --i是数值型的

    dbms_output.put_line('写法1:' || name_tab(i).first_name || ' 1 ' || name_tab(i).last_name);

  end loop;

  --写法2: count方法返回集合个数, 遇到被删除的元素会报错:找不到

  for i in 1 .. name_tab.count LOOP --i是数值型的

    dbms_output.put_line('写法2:' || name_tab(i).first_name || ' 2 ' || name_tab(i).last_name);

  end loop;

end;