分类： Oracle
2008-05-01 18:04:13
|
PL/SQL补疑之Collections and Records Nested Table的的构造器
DECLARE
TYPE dnames_tab IS TABLE OF VARCHAR2(30);
dept_names dnames_tab;
BEGIN
dept_names := dnames_tab('Shipping','Sales','Finance','Payroll');
END; / nested table不需要制定大小，你可以在构造方法里面放足够多的元素。 Varray的构造器
DECLARE
-- In the varray, we put an upper limit on the number of elements
TYPE dnames_var IS VARRAY(20) OF VARCHAR2(30);
dept_names dnames_var;
BEGIN
-- Because dnames is declared as VARRAY(20), we can put up to 10
-- elements in the constructor
dept_names := dnames_var('Shipping','Sales','Finance','Payroll');
END; / Collection 构造器包含空值处理
DECLARE
TYPE dnames_tab IS TABLE OF VARCHAR2(30);
dept_names dnames_tab;
TYPE dnamesNoNulls_type IS TABLE OF VARCHAR2(30) NOT NULL;
BEGIN
dept_names := dnames_tab('Shipping', NULL,'Finance', NULL);
-- If dept_names was of type dnamesNoNulls_type, we could not include
-- null values in the constructor
END; / 结合Collection定义和构造器
DECLARE
TYPE dnames_tab IS TABLE OF VARCHAR2(30);
dept_names dnames_tab := dnames_tab('Shipping','Sales','Finance','Payroll');
BEGIN
NULL;
END; / 空Varray 构造器
DECLARE
TYPE dnames_var IS VARRAY(20) OF VARCHAR2(30);
dept_names dnames_var;
BEGIN
IF dept_names IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Before initialization, the varray is null.');
-- 当varray 为空,我不能检查它的COUNT属性
-- DBMS_OUTPUT.PUT_LINE('It has ' || dept_names.COUNT || ' elements.');
ELSE
DBMS_OUTPUT.PUT_LINE('Before initialization, the varray is not null.');
END IF;
dept_names := dnames_var(); -- initialize empty varray
IF dept_names IS NULL THEN
DBMS_OUTPUT.PUT_LINE('After initialization, the varray is null.');
ELSE
DBMS_OUTPUT.PUT_LINE('After initialization, the varray is not null.');
DBMS_OUTPUT.PUT_LINE('It has ' || dept_names.COUNT || ' elements.');
END IF;
END; / Referencing Collection Elements Collection使用下面的语法定义下标： collection_name(subscript) 大多数例子里面，下标是Integer型，associative arrays也可以定义String作为下标。 允许的下标范围是： n nested tables, 1 .. 2147483647 (PLS_INTEGER类型的上限)。 n varrays,1 .. size_limit，但是 size_limit也不要超过2147483647。 n associative arrays，用number做key的时候 -2147483648 to 2147483647。 n associative arrays，用String做Key的时候，取决于Key的长度和字符集。 Assigning Collections 可以通过INSERT, UPDATE, FETCH, 或者 SELECT，分派语句，子程序调用来分派collection，语法如下： collection_name(subscript) := expression; 也可以使用SET, MULTISET UNION, MULTISET INTERSECT, MULTISET EXCEPT。 分派collection可能会导致异常，由于： n 下标为空，或者右值不可变，将会触发VALUE_ERROR n 下标引用了一个未初始化对象，将触发SUBSCRIPT_BEYOND_COUNT
n collection 如果是atomically null, 触发COLLECTION_IS_NULL 分派不同类型，引起的异常例子 DECLARE
TYPE last_name_typ IS VARRAY(3) OF VARCHAR2(64);
TYPE surname_typ IS VARRAY(3) OF VARCHAR2(64);
-- 同样的类型
group1 last_name_typ := last_name_typ('Jones','Wong','Marceau');
group2 last_name_typ := last_name_typ('Klein','Patsos','Singh');
-- 类似的定义不同的类型
group3 surname_typ := surname_typ('Trevisi','Macleod','Marquez');
BEGIN
-- 可以
group1 := group2;
-- 不允许，会报错
-- group3 := group2; -- raises an error
END; / 给Nested table 分派空值 DECLARE
TYPE dnames_tab IS TABLE OF VARCHAR2(30);
dept_names dnames_tab := dnames_tab('Shipping','Sales','Finance','Payroll');
-- 没有初始化 ("atomically null").
empty_set dnames_tab;
BEGIN
-- 首先它不为空.
if dept_names IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('OK, at first dept_names is not null.');
END IF;
-- 分配一个空值给它.
dept_names := empty_set; -- 它为空了. if dept_names IS NULL THEN
DBMS_OUTPUT.PUT_LINE('OK, now dept_names has become null.');
END IF;
-- We must use another constructor to give it some values.
dept_names := dnames_tab('Shipping','Sales','Finance','Payroll');
END; / 分派Nested Tables 通过 Set 操作，交集，剪集，合并等
DECLARE
TYPE nested_typ IS TABLE OF NUMBER;
nt1 nested_typ := nested_typ(1,2,3);
nt2 nested_typ := nested_typ(3,2,1);
nt3 nested_typ := nested_typ(2,3,1,3);
nt4 nested_typ := nested_typ(1,2,4);
answer nested_typ;
PROCEDURE print_nested_table(the_nt nested_typ) IS
output VARCHAR2(128);
BEGIN
IF the_nt IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Results:
RETURN;
END IF;
IF the_nt.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('Results: empty set');
RETURN;
END IF;
FOR i IN the_nt.FIRST .. the_nt.LAST LOOP output := output || the_nt(i) || ' '; END LOOP; DBMS_OUTPUT.PUT_LINE('Results: ' || output);
END;
BEGIN
answer := nt1 MULTISET UNION nt4; -- (1,2,3,1,2,4)
print_nested_table(answer);
answer := nt1 MULTISET UNION nt3; -- (1,2,3,2,3,1,3)
print_nested_table(answer);
answer := nt1 MULTISET UNION DISTINCT nt3; -- (1,2,3)
print_nested_table(answer);
answer := nt2 MULTISET INTERSECT nt3; -- (3,2,1)
print_nested_table(answer);
answer := nt2 MULTISET INTERSECT DISTINCT nt3; -- (3,2,1)
print_nested_table(answer);
answer := SET(nt3); -- (2,3,1)
print_nested_table(answer);
answer := nt3 MULTISET EXCEPT nt2; -- (3)
print_nested_table(answer);
answer := nt3 MULTISET EXCEPT DISTINCT nt2; -- ()
print_nested_table(answer);
END; / 给VARRAYs 分配复杂数据类型的值
DECLARE
TYPE emp_name_rec is RECORD (
firstname employees.first_name%TYPE,
lastname employees.last_name%TYPE,
hiredate employees.hire_date%TYPE
);
-- Array type that can hold information 10 employees
TYPE EmpList_arr IS VARRAY(10) OF emp_name_rec;
SeniorSalespeople EmpList_arr;
-- Declare a cursor to select a subset of columns.
CURSOR c1 IS SELECT first_name, last_name, hire_date FROM employees;
Type NameSet IS TABLE OF c1%ROWTYPE;
SeniorTen NameSet;
EndCounter NUMBER := 10;
BEGIN
SeniorSalespeople := EmpList_arr();
SELECT first_name, last_name, hire_date BULK COLLECT INTO SeniorTen FROM
employees WHERE job_id = 'SA_REP' ORDER BY hire_date;
IF SeniorTen.LAST > 0 THEN
IF SeniorTen.LAST < 10 THEN EndCounter := SeniorTen.LAST;
END IF;
FOR i in 1..EndCounter LOOP
SeniorSalespeople.EXTEND(1);
SeniorSalespeople(i) := SeniorTen(i);
DBMS_OUTPUT.PUT_LINE(SeniorSalespeople(i).lastname || ', '
|| SeniorSalespeople(i).firstname || ', ' ||
SeniorSalespeople(i).hiredate); END LOOP; END IF;
END; / 分配值给Tables 通过复杂数据类型
DECLARE
TYPE emp_name_rec is RECORD (
firstname employees.first_name%TYPE,
lastname employees.last_name%TYPE,
hiredate employees.hire_date%TYPE
);
-- Table type that can hold information about employees
TYPE EmpList_tab IS TABLE OF emp_name_rec;
SeniorSalespeople EmpList_tab;
-- Declare a cursor to select a subset of columns.
CURSOR c1 IS SELECT first_name, last_name, hire_date FROM employees;
EndCounter NUMBER := 10;
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
BEGIN OPEN emp_cv FOR SELECT first_name, last_name, hire_date FROM employees WHERE job_id = 'SA_REP' ORDER BY hire_date;
FETCH emp_cv BULK COLLECT INTO SeniorSalespeople;
CLOSE emp_cv;
-- for this example, display a maximum of ten employees
IF SeniorSalespeople.LAST > 0 THEN
IF SeniorSalespeople.LAST < 10 THEN EndCounter := SeniorSalespeople.LAST;
END IF;
FOR i in 1..EndCounter LOOP
DBMS_OUTPUT.PUT_LINE(SeniorSalespeople(i).lastname || ', '
|| SeniorSalespeople(i).firstname || ', ' || SeniorSalespeople(i).hiredate); END LOOP; END IF;
END; / 比较 Collections 你可以检查collection是否为空，但诸如比较greater 或者 less是不行的。同样，它也不能出现在DISTINCT, GROUP BY, 或者 ORDER BY。 检查Collection Is Null
DECLARE
TYPE emp_name_rec is RECORD (
firstname employees.first_name%TYPE,
lastname employees.last_name%TYPE,
hiredate employees.hire_date%TYPE
);
TYPE staff IS TABLE OF emp_name_rec;
members staff;
BEGIN
-- 返回 TRUE因为我们没有使用constructor.
IF members IS NULL THEN
DBMS_OUTPUT.PUT_LINE('NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('Not NULL');
END IF;
END; / 比较两个Nested Tables
DECLARE
TYPE dnames_tab IS TABLE OF VARCHAR2(30);
dept_names1 dnames_tab := dnames_tab('Shipping','Sales','Finance','Payroll');
dept_names2 dnames_tab := dnames_tab('Sales','Finance','Shipping','Payroll');
dept_names3 dnames_tab := dnames_tab('Sales','Finance','Payroll');
BEGIN
-- 我们能使用 = 或者 !=, 但不能是 < 或者 >.
-- 返回true，虽然他们的顺序不一样.
IF dept_names1 = dept_names2 THEN
DBMS_OUTPUT.PUT_LINE('dept_names1 and dept_names2 have the same members.');
END IF;
IF dept_names2 != dept_names3 THEN
DBMS_OUTPUT.PUT_LINE('dept_names2 and dept_names3 have different members.');
END IF;
END; / 比较 Nested Tables 通过 Set 操作
DECLARE
TYPE nested_typ IS TABLE OF NUMBER;
nt1 nested_typ := nested_typ(1,2,3);
nt2 nested_typ := nested_typ(3,2,1);
nt3 nested_typ := nested_typ(2,3,1,3);
nt4 nested_typ := nested_typ(1,2,4);
answer BOOLEAN;
howmany NUMBER;
PROCEDURE testify(truth BOOLEAN DEFAULT NULL, quantity NUMBER DEFAULT NULL) IS
BEGIN
IF truth IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(CASE truth WHEN TRUE THEN 'True' WHEN FALSE THEN
'False' END);
END IF;
IF quantity IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(quantity);
END IF;
END;
BEGIN
answer := nt1 IN (nt2,nt3,nt4); -- true
testify(truth => answer);
answer := nt1 SUBMULTISET OF nt3; -- true, all elements match
testify(truth => answer);
answer := nt1 NOT SUBMULTISET OF nt4; -- also true
testify(truth => answer);
howmany := CARDINALITY(nt3); -- number of elements in nt3
testify(quantity => howmany);
howmany := CARDINALITY(SET(nt3)); -- number of distinct elements
testify(quantity => howmany);
answer := 4 MEMBER OF nt1; -- false, no element matches
testify(truth => answer);
answer := nt3 IS A SET; -- false, nt3 has duplicates
testify(truth => answer);
answer := nt3 IS NOT A SET; -- true, nt3 has duplicates
testify(truth => answer);
answer := nt1 IS EMPTY; -- false, nt1 has some members testify(truth => answer); END; / 使用多级Collections 多级 VARRAY
DECLARE
TYPE t1 IS VARRAY(10) OF INTEGER;
TYPE nt1 IS VARRAY(10) OF t1; -- multilevel varray type
va t1 := t1(2,3,5);
-- initialize multilevel varray
nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va);
i INTEGER;
va1 t1;
BEGIN
-- 多级访问
i := nva(2)(3); -- 获得值 73
DBMS_OUTPUT.PUT_LINE('I = ' || i);
-- 添加一个新的 varray 元素给 nva,不扩展会报ORA-06533: 下标超出数量
nva.EXTEND;
-- replace inner varray elements
nva(5) := t1(56, 32);
nva(4) := t1(45,43,67,43345);
-- replace an inner integer element
nva(4)(4) := 1; -- replaces 43345 with 1
-- add a new element to the 4th varray element
-- and store integer 89 into it.
nva(4).EXTEND;
nva(4)(5) := 89;
END; / 多级Nested Table
DECLARE
TYPE tb1 IS TABLE OF VARCHAR2(20);
TYPE Ntb1 IS TABLE OF tb1; -- table of table elements
TYPE Tv1 IS VARRAY(10) OF INTEGER;
TYPE ntb2 IS TABLE OF tv1; -- table of varray elements
vtb1 tb1 := tb1('one', 'three');
vntb1 ntb1 := ntb1(vtb1);
vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3)); -- table of varray elements
BEGIN
vntb1.EXTEND;
vntb1(2) := vntb1(1);
-- delete the first element in vntb1 vntb1.DELETE(1); -- delete the first string from the second table in the nested table
vntb1(2).DELETE(1);
END; / 多级Associative Array
DECLARE
TYPE tb1 IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
-- the following is index-by table of index-by tables
TYPE ntb1 IS TABLE OF tb1 INDEX BY PLS_INTEGER;
TYPE va1 IS VARRAY(10) OF VARCHAR2(20);
-- the following is index-by table of varray elements
TYPE ntb2 IS TABLE OF va1 INDEX BY PLS_INTEGER;
v1 va1 := va1('hello', 'world');
v2 ntb1;
v3 ntb2;
v4 tb1;
v5 tb1; -- empty table
BEGIN
v4(1) := 34;
v4(2) := 46456;
v4(456) := 343;
v2(23) := v4;
v3(34) := va1(33, 456, 656, 343);
-- assign an empty table to v2(35) and try again
v2(35) := v5;
v2(35)(2) := 78; -- it works now
END;
/ 原文：http://valen.blog.ccidnet.com/blog-htm-do-showone-uid-51502-type-blog-itemid-227040.html |
