『PLSQL』plsql编程 一 集合

 1.Table（索引表）
     索引表是将数据保存在内存中！！！

 1.1 定义索引表
 

  -- 定义记录集
  TYPE my_rec IS RECORD( ename varchar2(30), eid NUMBER );
  -- 定义索引表类型
  TYPE my_tab IS TABLE OF yang_rec INDEX BY BINARY_INTEGER;
  -- 定义索引表对象的实例
  test_tab my_tab;

 1.2 将条目插入到索引表中

  索引表中的每个元素都由一个唯一的整型值(索引)标识。引用表中的值时，必须提供该值的索引。
  索引的范围：1 ---- 2147483647，索引值可以不连续，同时PL/SQL并不为没有使用的条目预留空间。

例如：

---利用Cursor填写索引表

DECLARE

  CURSOR All_Emps IS

    SELECT * FROM Employee ORDER BY Emp_Id;

  TYPE Emp_Table IS TABLE OF Employee%ROWTYPE INDEX BY BINARY_INTEGER;

  Emps     Emp_Table;

  Emps_Max BINARY_INTEGER;

BEGIN

  Emps_Max := 0;

  FOR Emp IN All_Emps

  LOOP

    Emps_Max := Emps_Max + 1;

    Emps(Emps_Max).Emp_Id := Emp.Emp_Id;

    Emps(Emps_Max).Emp_Name := Emp.Emp_Name;

  END LOOP;

END;

/

 1.3 对索引表中进行操作

1) 插入：见上例。

2) 引用：

    IF emps.EXIST(10) THEN
          DBMS_OUTPUT.PUT_LINE('存在第10条记录。');
    END IF;

3) 修改：
 ---修改emps 表中的第100个条目：
    emps(100).emp_name := 'Chris Jian';

4) 删除：
-- 删除emps 表中的第100个条目：
    emps.DELETE(100);

-- 删除emps 表中的从1到100的条目：
    emps.DELETE(1, 100);

-- 删除emps 表中的的所有条目：
   emps.DELETE;

1.4 索引表中的内建函数

1) count：返回表的条目数量：
      num_rows := emps.COUNT;

2) EXISTS：如果指定的条目存在，则返回为真；否则为假。
     IF emps.EXIST(10) THEN
         DBMS_OUTPUT.PUT_LINE('存在第10条记录。');
     END IF;

3) LIMIT：该方法返回集合可以包含的最大元素数目。只有变长数组才有上限。将LIMIT 用于嵌套表和索引
表时，其返回为NULL。

4) FRIST：该方法返回集合中使用的最小的索引值。

5) LAST：该方法返回集合中使用的最大的索引值。

6) NEXT：该方法返回集合中当前使用的下一个索引值。

7) PRIOR：该方法返回集合中当前使用的上一个索引值。

8) DELETE：删除集合中的条目，见前例。

9) TRIM：从集合的尾部删除一个或多个条目，无返回值，只适用于变长数组和嵌套表。

     emps.TRIM(1); -- 从集合的尾部删除一个条目
     emps.TRIM(3); -- 从集合的尾部删除三个条目

10) EXTEND：在集合的尾部添加条目或复制已有的条目，只适用于变长数组和嵌套表。
    emps.EXTEND(1); -- 从集合的尾部添加一个条目
    emps.EXTEND(3); -- 从集合的尾部添加三个条目
    emps.EXTEND(1, 3);-- 复制集合的第三个条目，并将其添加到表的末尾。

---------------------------

2 嵌套表

将数据保存在内存中！！！
嵌套表是一个无序记录集合。
检索数据库中的嵌套表时，条目的索引是连续的，不能象索引表那样随意跳过索引值。
需要使用构造函数初始化嵌套表。
嵌套表不能是以下数据类型：
   BOOLEAN, NCHAR, NCLOB, NVARCHAR2, REF CURSOR, TABLE, VARRAY, NOT NULL。
嵌套表的定义和索引表类似，但不能使用INDEX BY 子句。

2.1 初始化嵌套表
必须使用构造函数初始化嵌套表后，才能给它添加条目！
-- 定义索引表类型
    TYPE emp_tab IS TABLE OF emp%ROWTYPE;
-- 定义索引表对象的实例
    emps emp_tab;
-- 初始化嵌套表
   emps := emp_tab();

2.2 扩展嵌套表
利用 EXTEND 来扩展嵌套表的数据于内存。

DECLARE

  CURSOR All_Emps IS

    SELECT * FROM Emp ORDER BY Empno;

  TYPE Emp_Table IS TABLE OF Emp%ROWTYPE;

  Emps    Emp_Table;

  i       PLS_INTEGER;

  l_Count PLS_INTEGER;

BEGIN

  l_Count := 0;

  Emps    := Emp_Table(); -- 初始化嵌套表并产生一条空记录

  FOR C1 IN All_Emps

  LOOP

    l_Count := l_Count + 1;

    Emps.EXTEND;

    Emps(l_Count).Empno := C1.Empno;

    Emps(l_Count).Ename := C1.Ename;

    Emps(l_Count).Job := C1.Job;

    Emps(l_Count).Mgr := C1.Mgr;

    Emps(l_Count).Hiredate := C1.Hiredate;

    Emps(l_Count).Sal := C1.Sal;

    Emps(l_Count).Comm := C1.Comm;

    Emps(l_Count).Deptno := C1.Deptno;

  END LOOP;

  -- clone the first entry five times

  Emps.EXTEND(5, 1);

  FOR i IN 1 .. l_Count + 5

  LOOP

    Dbms_Output.Put_Line(To_Char(Emps(i).Empno) || ' ' || Mps(i).Ename);

  END LOOP;

END;

/

2.3 删除嵌套表中的条目
1) DELETE 方法：
     emps.DELETE(10); -- 删除嵌套表中的第10个条目。
     注意：在删除嵌套表中的条目后，嵌套表中的条目并没有重新编号，还可以继续使用。

2) TRIM 方法：
     TRIM方法是在表的末尾删除指定数目的条目。
     TRIM方法只能用于嵌套表和变长数组。

DECLARE

  CURSOR All_Emps IS

    SELECT * FROM Emp ORDER BY Empno;

  TYPE Emp_Table IS TABLE OF Emp%ROWTYPE;

  Emps    Emp_Table;

  i       PLS_INTEGER;

  l_Count PLS_INTEGER;

BEGIN

  l_Count := 0;

  -- 初始化嵌套表并产生一条空记录

  Emps := Emp_Table();

  FOR C1 IN All_Emps

  LOOP

    l_Count := l_Count + 1;

    Emps.EXTEND;

    Emps(l_Count).Empno := C1.Empno;

    Emps(l_Count).Ename := C1.Ename;

    Emps(l_Count).Job := C1.Job;

    Emps(l_Count).Mgr := C1.Mgr;

    Emps(l_Count).Hiredate := C1.Hiredate;

    Emps(l_Count).Sal := C1.Sal;

    Emps(l_Count).Comm := C1.Comm;

    Emps(l_Count).Deptno := C1.Deptno;

  END LOOP;

  -- clone the first entry five times

  Emps.EXTEND(5, 1);

  -- Trim off the five clones of entry #1

  Emps.TRIM(5);

  -- Delete the first entry

  Emps.DELETE(1);

  FOR i IN 1 .. l_Count + 5

  LOOP

    Dbms_Output.Put_Line(To_Char(Emps(i).Empno) || ' ' || Emps(i).Ename);

  END LOOP;

END;

/

注意：调试以上代码，并注意错误表达!!!

3 变长数组
   变长数组与嵌套表类似，但变长数组的最大长度是固定的。
   变长数组与嵌套表一样需要初始化。

3.1 定义变长数组
-- 定义最大长度为100 的变长数组
   TYPE type_name IS VARRAY(100) OF VARCHAR2(20);

3.2 扩展变长数组
类似于嵌套表，但不能超过最大长度。例程类似于嵌套表的例程。

4 批绑定
PL/SQL 批绑定是Oracle8i中的新功能。
1) 使用 BULK COLLECT
2) 使用 FORALL

例程：

DECLARE

  CURSOR C1 IS

    SELECT Empno, Ename FROM Emp;

  TYPE Eno_Tab IS TABLE OF Emp.Empno%TYPE;

  TYPE Ename_Tab IS TABLE OF Emp.Ename%TYPE;

  l_Enos   Eno_Tab;

  l_Enames Ename_Tab;

BEGIN

  OPEN C1;

  FETCH C1 BULK COLLECT

    INTO l_Enos, l_Enames;

  CLOSE C1;

  FOR i IN 1 .. l_Enos.COUNT

  LOOP

    Dbms_Output.Put_Line(To_Char(l_Enos(i)) || ' ' || l_Enames(i));

  END LOOP;

  FORALL i IN l_Enos.FIRST .. l_Enos.LAST

    UPDATE Emp SET Ename = l_Enames(i) WHERE Empno = l_Enos(i);

END;

/

 

5 集合的异常处理
异 常 原 因
COLLECTION_IS_NULL 在构造函数初始化集合之前试图使用它
NO_DATA_FOUND 试图访问集合中不存在的条目
SUBCRIPT_BEYOND_COUNT 使用的下标超过了集合当前的元素数目
SUBCRIPT_OUTSIDE_LIMIT 变长数组中使用的下标大于该变长数组声明中规定的最大值
VALUE_ERROR 使用一个不能转换为整形的下标