oracle 数据库集合学习

集合
1.1 索引表


索引表是将数据保存在内存中！！！
1.1.1 定义索引表
-- 定义记录集
TYPE yang_rec IS RECORD( ename varchar2(30), eid NUMBER );
-- 定义索引表类型
TYPE yang_tab IS TABLE OF yang_rec INDEX BY BINARY_INTEGER;
-- 定义索引表对象的实例
test_tab yang_tab;
1.1.2 将条目插入到索引表中
索引表中的每个元素都由一个唯一的整型值(索引)标识。引用表中的值时，必须提供该值的索引。
索引的范围：1 ---- 2147483647，索引值可以不连续，同时PL/SQL并不为没有使用的条目预留空间。
例如：
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.1.3 对索引表中进行操作
1) 插入：见上例。
2) 引用：
IF emps.EXIST(10) THEN
DBMS_OUTPUT.PUT_LINE('存在第10条记录。');
END IF;
3) 修改：
修改emps 表中的第100个条目：
emps(100).emp_name := 'yang linker';
4) 删除：
-- 删除emps 表中的第100个条目：
emps.DELETE(100);
-- 删除emps 表中的从1到100的条目：
emps.DELETE(1, 100);
-- 删除emps 表中的的所有条目：
emps.DELETE;
1.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);-- 复制集合的第三个条目，并将其添加到表的末尾。
1.2 嵌套表
将数据保存在内存中！！！
嵌套表是一个无序记录集合。
检索数据库中的嵌套表时，条目的索引是连续的，不能象索引表那样随意跳过索引值。
需要使用构造函数初始化嵌套表。
嵌套表不能是以下数据类型：
BOOLEAN, NCHAR, NCLOB, NVARCHAR2, REF CURSOR, TABLE, VARRAY, NOT NULL。
嵌套表的定义和索引表类似，但不能使用INDEX BY 子句。
1.2.1 初始化嵌套表
必须使用构造函数初始化嵌套表后，才能给它添加条目！
-- 定义索引表类型
TYPE emp_tab IS TABLE OF emp%ROWTYPE;
-- 定义索引表对象的实例
emps emp_tab;
-- 初始化嵌套表
emps := emp_tab();

1.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;
/
1.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;
/
注意：调试以上代码，并注意错误表达!!!

1.3 变长数组


变长数组与嵌套表类似，但变长数组的最大长度是固定的。
变长数组与嵌套表一样需要初始化。
1.3.1 定义变长数组
-- 定义最大长度为100 的变长数组
TYPE type_name IS VARRAY(100) OF VARCHAR2(20);
1.3.2 扩展变长数组
类似于嵌套表，但不能超过最大长度。例程类似于嵌套表的例程。
1.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;
/

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