oracle plsql進階編程 一 集合

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);
PL/SQL 進階編程
第 4 頁 共 4 頁
-- 刪除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;
-- 定義索引表對象的執行個體
PL/SQL 進階編程
第 5 頁 共 5 頁
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
PL/SQL 進階編程
第 6 頁 共 6 頁
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
PL/SQL 進階編程
第 7 頁 共 7 頁
常式：
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 使用一個不能轉換為整形的下標