第一章集合

1.索引表
   索引表是将数据保存在内存中，元素个数没有限制,并且下标可以是负值。

1.1.定义索引表
     

 --定义索引表类型

       type type_name is table of element_type [not null] index by key_type;

       --定义索引表对象的实例

       identifier type_name; 

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

例如：

create or replace procedure IndexTable is
  CURSOR all_emps IS
    SELECT * FROM EMP p ORDER BY p.empno;
  TYPE EMP_TABLE IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
  emps     EMP_TABLE;
  emps_max BINARY_INTEGER;
begin
  emps_max := 0;
  FOR employee IN all_emps LOOP
    emps_max := emps_max + 1;
    emps(emps_max).empno := employee.empno;
    emps(emps_max).ename := employee.ename;
    emps(emps_max).job := employee.job;
    emps(emps_max).mgr := employee.mgr;
    emps(emps_max).hiredate := employee.hiredate;
    emps(emps_max).sal := employee.sal;
    emps(emps_max).comm := employee.comm;
    emps(emps_max).deptno := employee.deptno;
  END LOOP;
 end IndexTable;

1.3.对索引表中进行操作
1）引用:

    

IF emps.EXISTS(10) THEN
        DBMS_OUTPUT.put_line('存在第10条记录！');
     END IF;

2）修改：

    

--修改emps表的第10个条目

     IF emps.EXISTS(10) THEN
          emps(10).ename := 'cdq;
          DBMS_OUTPUT.put_line(emps(10).ename);
     END IF;

3）删除:

   

 --删除emps表的第10个条目

     emps.DELETE(10);

     --删除emps 表中的从1到10的条目

     emps.DELETE(1,10);

     --删除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：删除集合中的条目，见前例。
   完整例子：

create or replace procedure IndexTable is
  CURSOR all_emps IS
    SELECT * FROM EMP p ORDER BY p.empno;
  TYPE EMP_TABLE IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
  emps     EMP_TABLE;
  emps_max BINARY_INTEGER;
begin
  emps_max := 0;
  FOR employee IN all_emps LOOP
    emps_max := emps_max + 1;
    emps(emps_max).empno := employee.empno;
    emps(emps_max).ename := employee.ename;
    emps(emps_max).job := employee.job;
    emps(emps_max).mgr := employee.mgr;
    emps(emps_max).hiredate := employee.hiredate;
    emps(emps_max).sal := employee.sal;
    emps(emps_max).comm := employee.comm;
    emps(emps_max).deptno := employee.deptno;
  END LOOP;
  
  DBMS_OUTPUT.put_line('emps表中的数量是：'||emps.COUNT);
  DBMS_OUTPUT.put_line('emps表中的LIMIT是：'||emps.LIMIT);
  DBMS_OUTPUT.put_line('emps表中的FRIST是：'||emps.FIRST);
  DBMS_OUTPUT.put_line('emps表中的LAST是：'||emps.LAST);
  DBMS_OUTPUT.put_line('emps表中的NEXT是：'||emps.NEXT(1));
  DBMS_OUTPUT.put_line('emps表中的PRIOR是：'||emps.PRIOR(6));
  IF emps.EXISTS(10) THEN
    DBMS_OUTPUT.put_line('存在第10条记录！');
  END IF;

  IF emps.EXISTS(10) THEN
    emps(10).ename := 'cdq';
    DBMS_OUTPUT.put_line(emps(10).ename);
  END IF;

  emps.DELETE(100);
  emps.DELETE(1,100);
  emps.DELETE;
end IndexTable;

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 来扩展嵌套表的数据于内存。

create or replace procedure NestTable is
CURSOR all_emps IS SELECT * FROM EMP p ORDER BY p.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) || ' ' || emps(i).ename);
  END LOOP;
end NestTable;

2.3删除嵌套表中的条目
1) DELETE 方法：
emps.DELETE(10); -- 删除嵌套表中的第10个条目。
注意：在删除嵌套表中的条目后，嵌套表中的条目并没有重新编号，还可以继续使用。
2) TRIM 方法：
TRIM方法是在表的末尾删除指定数目的条目。
TRIM方法只能用于嵌套表和变长数组。

create or replace procedure NestTable is
  CURSOR all_emps IS
    SELECT * FROM EMP p ORDER BY p.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..emps.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(emps(i).empno) || ' ' || emps(i).ename);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('================================');
  -- 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
    IF emps.EXISTS(i) THEN --不判断可能会出现没有数据的错误
       DBMS_OUTPUT.PUT_LINE(TO_CHAR(emps(i).empno) || ' ' || emps(i).ename);
    END IF;
  END LOOP;
end NestTable;

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

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

3.2扩展变长数组

create or replace procedure ArrayTable is
CURSOR all_emp IS SELECT p.ename FROM EMP p ORDER BY p.empno;
TYPE emp_name IS ARRAY(14) OF emp.ename%TYPE;
names emp_name;
i PLS_INTEGER;
begin
  names := emp_name();
  i :=0;
  FOR c1 IN  all_emp LOOP 
    i := i+1;
    names.EXTEND;
    names(i) := c1.ename;
  END LOOP;
  FOR i IN names.FIRST..names.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(names(i));
  END LOOP;
end ArrayTable;

类似于嵌套表，但不能超过最大长度。
4.批绑定
PL/SQL 批绑定是Oracle8i中的新功能。
1) 使用 BULK COLLECT
2) 使用 FORALL

create or replace procedure BULKTest is
  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 BULKTest;

5.集合的异常处理