PL/SQL补疑之Collections and Records

PL/SQL补疑之Collections and Records

 

笔记

 

Wangwj/valen won

 

Scttsc/cnoug

 

2007-12-24

 

参考文档：oracle PLSQL reference 10g r2

 

Merry Christmas!

 

 

 

废话一段，我喜欢PL/SQL。在参加过的大大小小项目中也喜欢写PL/SQL，虽然有的OOP爱好者不免BS之，说PL/SQL是面向过程，OOP特性太差。我也看《JAVA设计模式》，不过我觉得模式是为了解决问题，而不是模式消除了问题。我举一个例子，某次我在一个项目中，有一个均匀分布的问题，一个List读取1000张单据，往往某个地区会聚集在一起，占有大量处理时间，而其它地区处理不到。Leader最初的解决方案，是对List做排序，改程序大概改了一上午，也觉得不理想。后来，我来改SQL，5分钟，问题解决，后来还可以配置分布比例。虽然我用PL/SQL5年多了，仍然有一些盲区，例如记录、集合、对象是用得非常少，所以我特意重新学习并总结，贴出来大家共同进步。

 

What are PL/SQL Collections and Records?

 

PL/SQL支持三种类型的Connection：

 

n     Associative arrays 关联数组，也被称为索引表，类似于其它语言中的Hash table。你可以使用任意下标（数字或者字符串型）来查找对象。

 

n     Nested tables 嵌套表，使用一串连续的数字做下标，它可以存储在表中，用SQL进行操作。

 

n     Varrays 和Nested tables类似，但灵活性不如Nested tables。可以在runtime改变大小。

 

Nested Tables

 

你可以吧nested table 看着是一种没有宣布长度的一纬数组。你也可以创建多维数组，在nested tables中存储其它的nested tables。Nested tables中的行并没有进行特别的排序。那么它和arrays有什么区别呢？

 

nested table没有宣布长度。Arrays有预定义的长度。Nested table 可以动态增加长度，但是它并不是无限的。

 

Nested table 可以没有连续的下标，arrays则是连续的。最初，nested table是连续的，但是当你删除元素以后，它就变得离散。

 

Varrays

 

Varray有一个最大长度，它的下限是1，然而你可以扩展它的长度。Associative arrays可以帮助你快速的查询某个元素，而不用知道它的具体位置，就像用主键访问一个表一样。因为Associative arrays主要是为临时存储而用的，所以它能用于SQL中，但你可以把它定义在一个package中，从而获得在一个session中的持久性。

 

Records

 

Record类似于一行里面的字段，%ROWTYPE属性可以表现一个数据库表里的一行，而不用列出所有字段。

 

定义Collection类型和宣布Collection变量

 

一个基本涵盖大部分定义方法的例子如下：

 

create or replace package pkg_connections is

TYPE Calendar IS VARRAY(10) OF DATE;

PROCEDURE pro_test_define;

end pkg_connections;

 

/

 

create or replace package body pkg_connections is

 

PROCEDURE pro_test_define IS

TYPE nested_type IS TABLE OF VARCHAR2(30); --nested table

TYPE varray_type IS VARRAY(5) OF INTEGER; --varray

TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; --associate array

TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;

TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);

v1 nested_type;

v2 varray_type;

v3 assoc_array_num_type;

v4 assoc_array_str_type;

v5 assoc_array_str_type2;

--用%TYPE定义 Collections

v6 v1%TYPE;

TYPE EmpList IS TABLE OF company.companynumber%TYPE;

-- Declare a cursor to select a subset of columns.

CURSOR c1 IS SELECT companynumber FROM company;

-- Declare an Array type that can hold information about 10 employees.

-- The element type is a record that contains all the same

-- fields as the company table.

TYPE Senior_Salespeople IS VARRAY(10) OF company%ROWTYPE;

-- Declare a cursor to select a subset of columns.

CURSOR c2 IS SELECT companynumber, companyid FROM company;

-- Array type that can hold a list of names. The element type

-- is a record that contains the same fields as the cursor

-- (that is, first_name and last_name).

TYPE NameList IS VARRAY(20) OF c2%ROWTYPE;

--VARRAY of Records

TYPE name_rec IS RECORD ( first_name VARCHAR2(20), last_name VARCHAR2(25) );

TYPE names IS VARRAY(250) OF name_rec;

--NOT NULL Constraint on Collection Elements

TYPE EmpList1 IS TABLE OF company.companynumber%TYPE NOT NULL;

-- if assigning NULL raises an error

v_employees EmpList := EmpList(100, 150, 160, 200);

BEGIN

v1 := nested_type('Shipping','Sales','Finance','Payroll');

v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers

v3(99) := 10; -- Just start assigning to elements

v3(7) := 100; -- Subscripts can be any integer values

v4(42) := 'Smith'; -- Just start assigning to elements

v4(54) := 'Jones'; -- Subscripts can be any integer values

v5('Canada') := 'North America'; -- Just start assigning to elements

v5('Greece') := 'Europe'; -- Subscripts can be string value

END;

-把nested Table做为存储过程的参数

PROCEDURE pro_test_para (ca IN Calendar) IS

BEGIN

NULL;

END;

end pkg_connections;

 

/

 

初始化Collections

 

初始化以前nested table 或者 varray都是空的，我们使用构造方法来初始化，如下：

 

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: <NULL>');

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;

 

/

 

使用Collection方法

 

Collection方法使得Collection更易用，包括COUNT, DELETE, EXISTS, EXTEND, FIRST, LAST,

 

LIMIT, NEXT, PRIOR,和TRIM

 

■ Collection 不能在SQL中使用。

■ EXTEND 和 TRIM 不能用于associative arrays.

■ EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, 和 NEXT 是函数; EXTEND,

TRIM, 和 DELETE 是存储过程.

■ EXISTS, PRIOR, NEXT, TRIM, EXTEND, 和 DELETE 带有参数来对应下标, 通常是integer但是associative arrays也可能是string.

 

■ 只有 EXISTS 能用于 null collections. 其它方法将报COLLECTION_IS_NULL。

 

EXISTS的例子

DECLARE

TYPE NumList IS TABLE OF INTEGER;

n NumList := NumList(1,3,5,7);

BEGIN

n.DELETE(2); -- Delete the second element

IF n.EXISTS(1) THEN

DBMS_OUTPUT.PUT_LINE('OK, element #1 exists.');

END IF;

IF n.EXISTS(2) = FALSE THEN

DBMS_OUTPUT.PUT_LINE('OK, element #2 has been deleted.');

END IF;

IF n.EXISTS(99) = FALSE THEN

DBMS_OUTPUT.PUT_LINE('OK, element #99 does not exist at all.');

END IF;

END;

 

/

 

统计collection Elements

DECLARE

TYPE NumList IS TABLE OF NUMBER;

n NumList := NumList(2,4,6,8); -- Collection starts with 4 elements.

BEGIN

DBMS_OUTPUT.PUT_LINE('There are ' || n.COUNT || ' elements in N.');

n.EXTEND(3); -- Add 3 new elements at the end.

DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');

n := NumList(86,99); -- Assign a completely new value with 2 elements.

DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');

n.TRIM(2); -- Remove the last 2 elements, leaving none.

DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');

END;

 

/

 

varrays,COUNT和LAST是一致的，但是在nested table中，最初COUNT和LAST是一致的，但当你删除了元素，他们就不一样了。

 

用LIMIT来检查Cllection的最大长度

DECLARE

TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);

dept_names dnames_var := dnames_var('Shipping','Sales','Finance','Payroll');

BEGIN

DBMS_OUTPUT.PUT_LINE('dept_names has ' || dept_names.COUNT

|| ' elements now');

DBMS_OUTPUT.PUT_LINE('dept_names''s type can hold a maximum of '

|| dept_names.LIMIT || ' elements');

DBMS_OUTPUT.PUT_LINE('The maximum number you can use with '

|| 'dept_names.EXTEND() is ' || (dept_names.LIMIT - dept_names.COUNT));

END;

 

/

 

使用FIRST 和 LAST

DECLARE

TYPE NumList IS TABLE OF NUMBER;

n NumList := NumList(1,3,5,7);

counter INTEGER;

BEGIN

DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' || n.FIRST);

DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' || n.LAST);

-- 下标由 1开始,

-- 很简单就可以循环访问.

FOR i IN n.FIRST .. n.LAST

 

LOOP

 

DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i));

 

END LOOP;

 

n.DELETE(2); -- Delete second element.

-- When the subscripts have gaps or the collection might be uninitialized,

-- the loop logic is more extensive. We start at the first element, and

-- keep looking for the next element until there are no more.

IF n IS NOT NULL THEN

counter := n.FIRST;

WHILE counter IS NOT NULL

 

LOOP

 

DBMS_OUTPUT.PUT_LINE('Element #' || counter || ' = ' || n(counter));

counter := n.NEXT(counter);

 

END LOOP;

 

ELSE

 

DBMS_OUTPUT.PUT_LINE('N is null, nothing to do.');

END IF;

END;

 

/

 

使用PRIOR 和NEXT 来 访问 Collection 元素

DECLARE

TYPE NumList IS TABLE OF NUMBER;

n NumList := NumList(1966,1971,1984,1989,1999);

BEGIN

DBMS_OUTPUT.PUT_LINE('The element after #2 is #' || n.NEXT(2));

DBMS_OUTPUT.PUT_LINE('The element before #2 is #' || n.PRIOR(2));

n.DELETE(3); -- Delete an element to show how NEXT can handle gaps.

DBMS_OUTPUT.PUT_LINE('Now the element after #2 is #' || n.NEXT(2));

IF n.PRIOR(n.FIRST) IS NULL THEN

DBMS_OUTPUT.PUT_LINE('Can''t get PRIOR of the first element or NEXT of the

last.');

END IF;

END;

 

/

 

使用NEXT 来访问Nested Table

DECLARE

TYPE NumList IS TABLE OF NUMBER;

n NumList := NumList(1,3,5,7);

counter INTEGER;

BEGIN

 

n.DELETE(2); -- Delete second element.

 

-- When the subscripts have gaps, the loop logic is more extensive. We start at

-- the first element, and keep looking for the next element until there are no

more.

counter := n.FIRST;

WHILE counter IS NOT NULL

 

LOOP

 

DBMS_OUTPUT.PUT_LINE('Counting up: Element #' || counter || ' = ' ||

n(counter));

counter := n.NEXT(counter);

 

END LOOP;

 

-- Run the same loop in reverse order.

counter := n.LAST;

WHILE counter IS NOT NULL

 

LOOP

 

DBMS_OUTPUT.PUT_LINE('Counting down: Element #' || counter || ' = ' ||

n(counter));

counter := n.PRIOR(counter);

 

END LOOP;

 

END;

 

/

 

增加Collection的长度

 

使用 EXTEND 增加Collection的长度

DECLARE

TYPE NumList IS TABLE OF INTEGER;

n NumList := NumList(2,4,6,8);

x NumList := NumList(1,3);

PROCEDURE print_numlist(the_list NumList) IS

output VARCHAR2(128);

BEGIN

FOR i IN the_list.FIRST .. the_list.LAST

 

LOOP

 

output := output || NVL(TO_CHAR(the_list(i)),'NULL') || ' ';

 

END LOOP;

 

DBMS_OUTPUT.PUT_LINE(output);

END;

 

BEGIN

 

DBMS_OUTPUT.PUT_LINE('At first, N has ' || n.COUNT || ' elements.');

n.EXTEND(5); -- Add 5 elements at the end.

DBMS_OUTPUT.PUT_LINE('Now N has ' || n.COUNT || ' elements.');

-- Elements 5, 6, 7, 8, and 9 are all NULL.

print_numlist(n);

DBMS_OUTPUT.PUT_LINE('At first, X has ' || x.COUNT || ' elements.');

x.EXTEND(4,2); -- Add 4 elements at the end.

DBMS_OUTPUT.PUT_LINE('Now X has ' || x.COUNT || ' elements.');

-- Elements 3, 4, 5, and 6 are copies of element #2.

print_numlist(x);

END;

 

BEGIN

 

DBMS_OUTPUT.PUT_LINE('At first, N has ' || n.COUNT || ' elements.');

n.EXTEND(5); -- Add 5 elements at the end.

DBMS_OUTPUT.PUT_LINE('Now N has ' || n.COUNT || ' elements.');

-- Elements 5, 6, 7, 8, and 9 are all NULL.

print_numlist(n);

DBMS_OUTPUT.PUT_LINE('At first, X has ' || x.COUNT || ' elements.');

x.EXTEND(4,2); -- Add 4 elements at the end.

DBMS_OUTPUT.PUT_LINE('Now X has ' || x.COUNT || ' elements.');

-- Elements 3, 4, 5, and 6 are copies of element #2.

print_numlist(x);

END;

 

/

 

减少Collection长度

 

■ TRIM 移除collection末尾的一个元素.

 

■ TRIM(n) 移除collection尾部n 个元素.

 

■DELETE删除所有元素,设定COUNT为0

■ DELETE(n) 删除 associative array或者nested table第n个元素

 

. 如果是字符串下标

 

■DELETE(m,n) 范围删除，M。。N

 

Collection Exceptions

 

Collection Exception Raised when...

COLLECTION_IS_NULL 尝试操作一个空connection.

NO_DATA_FOUND 一个指向删除或不存在元素的下标,

SUBSCRIPT_BEYOND_COUNT 下标超界

SUBSCRIPT_OUTSIDE_LIMIT 下标超限

 

VALUE_ERROR 下标为空或者不适合，或者PLS_ INTEGER 超限

 

选择使用哪种Collection

 

■ 其它语言里面的Arrays在PL/SQL中是varrays.

■ 其它语言里面的Sets 和 bags在PL/SQL中是 nested tables.

 

■ Hash tables 和其它类型的无序表在PL/SQL中是associative arrays.

 

Nested Tables 还是 Associative Arrays？

 

Nested tables 和associative array类似（原来被称为索引-表），都使用下标。

Nested tables能存储在字段中，而且可以用SQL操作，associative arrays 不能。

Associative arrays are appropriate for relatively small lookup tables where the

Collection适合比较小的内存查询.它非常适合数据大小未知, 因为它没有固定的上限，而且它的下标比较灵活，可以为负的，字符串的。

使用FORALL statement 或者 BULK COLLECT clause，可以最有效的把数据导入到内存中。

Nested Tables 还是 Varrays？

Varrays在下列情况是一种好的选择：

n     元素个数已知

n     元素通常按顺序访问

Nested tables 在下列情况选择：

n     索引不连续

n     没有设定索引值，但是需要一个极限

n     时常需要删除和更新

n     经常进行多重查询

 

定义Records

 

定义一个简单的Record Type

DECLARE

TYPE DeptRecTyp IS RECORD (

deptid NUMBER(4) NOT NULL := 99,

dname departments.department_name%TYPE,

loc departments.location_id%TYPE,

region regions%ROWTYPE );

dept_rec DeptRecTyp;

BEGIN

dept_rec.dname := 'PURCHASING';

END;

/

初始化 Record 类型

DECLARE

-- 定义一个record 拥有3个fields.

TYPE rec1_t IS RECORD (field1 VARCHAR2(16), field2 NUMBER, field3 DATE);

-- 如果field宣布不为空, 我们必须提供一个默认值.

TYPE rec2_t IS RECORD (id INTEGER NOT NULL := -1,

name VARCHAR2(64) NOT NULL := '[anonymous]');

-- 根据Type定义记录

 

rec1 rec1_t;

 

rec2 rec2_t;

-- 定义一个记录根据表employees.包括该表所有字段

rec3 employees%ROWTYPE;

-- 或者指定某一字段

TYPE rec4_t IS RECORD (first_name employees.first_name%TYPE,

last_name employees.last_name%TYPE,

rating NUMBER);

rec4 rec4_t;

BEGIN

-- 通过 . 读取和写入值

rec1.field1 := 'Yesterday';

rec1.field2 := 65;

rec1.field3 := TRUNC(SYSDATE-1);

-- 这里将输出默认值

DBMS_OUTPUT.PUT_LINE(rec2.name);

END;

 

/

 

使用%ROWTYPE 宣布一个 Record

DECLARE

-- 最佳: 使用 %ROWTYPE 代替指定每个字段.

-- 使用 <cursor>%ROWTYPE 代替<table>%ROWTYPE 因为我们只需要一部分字段

-- 进行定义游标，但不运行它，所以没有性能开销

CURSOR c1 IS SELECT department_id, department_name, location_id

FROM departments;

rec1 c1%ROWTYPE;

TYPE DeptRec2 IS RECORD (dept_id departments.department_id%TYPE,

dept_name departments.department_name%TYPE,

dept_loc departments.location_id%TYPE);

rec2 DeptRec2;

TYPE DeptRec3 IS RECORD (dept_id NUMBER,

dept_name VARCHAR2(14),

dept_loc VARCHAR2(13));

rec3 DeptRec3;

BEGIN

NULL;

END;

 

/

 

使用Records作为函数和存储过程的返回值

 

Record from a Function

DECLARE

TYPE EmpRecTyp IS RECORD (

emp_id NUMBER(6),

salary NUMBER(8,2));

CURSOR desc_salary RETURN EmpRecTyp IS

SELECT employee_id, salary FROM employees ORDER BY salary DESC;

emp_rec EmpRecTyp;

FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS

BEGIN

OPEN desc_salary;

FOR i IN 1..n LOOP

FETCH desc_salary INTO emp_rec;

 

END LOOP;

 

CLOSE desc_salary;

RETURN emp_rec;

END nth_highest_salary;

BEGIN

NULL;

END;

 

/

 

Using a Record as Parameter to a Procedure

DECLARE

TYPE EmpRecTyp IS RECORD (

emp_id NUMBER(6),

emp_sal NUMBER(8,2) );

PROCEDURE raise_salary (emp_info EmpRecTyp) IS

BEGIN

UPDATE employees SET salary = salary + salary * .10

WHERE employee_id = emp_info.emp_id;

END raise_salary;

BEGIN

NULL;

END;

 

/

 

Records赋值

 

DECLARE

TYPE RecordTyp IS RECORD (field1 NUMBER,

field2 VARCHAR2(32) DEFAULT 'something');

rec1 RecordTyp;

rec2 RecordTyp;

-- 两个一样的定义

TYPE DeptRec1 IS RECORD ( dept_num NUMBER(2), dept_name VARCHAR2(14));

TYPE DeptRec2 IS RECORD ( dept_num NUMBER(2), dept_name VARCHAR2(14));

dept1_info DeptRec1;

dept2_info DeptRec2;

dept3_info DeptRec2;

rec2 c1%ROWTYPE;

BEGIN

-- 首先我们指定一些值.

rec1.field1 := 100; rec1.field2 := 'something else';

-- 将rec1 赋值 rec2

rec1 := rec2;

DBMS_OUTPUT.PUT_LINE('Field1 = ' || NVL(TO_CHAR(rec1.field1),'<NULL>') || ',

field2 = ' || rec1.field2);

-- 不允许; 类型不一致.

-- dept1_info := dept2_info;

-- 允许.

dept2_info := dept3_info;

END;

 

/

 

通过游标来赋值

DECLARE

TYPE RecordTyp IS RECORD (last employees.last_name%TYPE,

id employees.employee_id%TYPE);

CURSOR c1 IS SELECT last_name, employee_id FROM employees;

rec1 RecordTyp;

rec2 c1%ROWTYPE;

BEGIN

SELECT last_name, employee_id INTO rec2 FROM employees WHERE ROWNUM < 2;

rec1 := rec2;

DBMS_OUTPUT.PUT_LINE('Employee #' || rec1.id || ' = ' || rec1.last);

END;

 

/

 

使用 SELECT INTO 赋值

 

DECLARE

TYPE RecordTyp IS RECORD (last employees.last_name%TYPE,

id employees.employee_id%TYPE);

rec1 RecordTyp;

BEGIN

SELECT last_name, employee_id INTO rec1 FROM employees WHERE ROWNUM < 2;

DBMS_OUTPUT.PUT_LINE('Employee #' || rec1.id || ' = ' || rec1.last);

END;

 

/

 

Comparing Records

 

不能比较，除非你自己定义你的比较规则和方法。

 

PL/SQL Records的数据库操作

 

■ Record 变量允许出现在以下位置：

■ UPDATE中SET的右值

■ INSERT的VALUES

■ RETURNING .. INTO 字句

 

 

 

 

使用%ROWTYPE插入

DECLARE

dept_info departments%ROWTYPE;

BEGIN

-- department_id, department_name, 和 location_id 是表字段

dept_info.department_id := 300;

dept_info.department_name := 'Personnel';

dept_info.location_id := 1700;

-- 语法很简单

INSERT INTO departments VALUES dept_info;

END;

 

/

 

使用Record做UPDATE，和INSERT一样

DECLARE

dept_info departments%ROWTYPE;

BEGIN

dept_info.department_id := 300;

dept_info.department_name := 'Personnel';

dept_info.location_id := 1700;

UPDATE departments SET ROW = dept_info WHERE department_id = 300;

END;

 

/

 

带RETURNING 的UPDATE

DECLARE

TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE,

salary employees.salary%TYPE);

emp_info EmpRec;

emp_id NUMBER := 100;

BEGIN

UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id

RETURNING last_name, salary INTO emp_info;

DBMS_OUTPUT.PUT_LINE('Just gave a raise to ' || emp_info.last_name ||

', who now makes ' || emp_info.salary);

ROLLBACK;

END;

 

/

 

使用 BULK COLLECT 和SELECT INTO

DECLARE

TYPE EmployeeSet IS TABLE OF employees%ROWTYPE;

underpaid EmployeeSet; -- Holds set of rows from EMPLOYEES table.

CURSOR c1 IS SELECT first_name, last_name FROM employees;

TYPE NameSet IS TABLE OF c1%ROWTYPE;

some_names NameSet; -- Holds set of partial rows from EMPLOYEES table.

BEGIN

-- With one query, we bring all the relevant data into the collection of records.

SELECT * BULK COLLECT INTO underpaid FROM employees

WHERE salary < 5000 ORDER BY salary DESC;

-- Now we can process the data by examining the collection, or passing it to

-- a separate procedure, instead of writing a loop to FETCH each row.

DBMS_OUTPUT.PUT_LINE(underpaid.COUNT || ' people make less than 5000.');

FOR i IN underpaid.FIRST .. underpaid.LAST

 

LOOP

 

DBMS_OUTPUT.PUT_LINE(underpaid(i).last_name || ' makes ' ||

underpaid(i).salary);

 

END LOOP;

 

-- We can also bring in just some of the table columns.

-- Here we get the first and last names of 10 arbitrary employees.

SELECT first_name, last_name BULK COLLECT INTO some_names FROM employees

WHERE ROWNUM < 11;

FOR i IN some_names.FIRST .. some_names.LAST

 

LOOP

 

DBMS_OUTPUT.PUT_LINE('Employee = ' || some_names(i).first_name || ' ' ||

some_names(i).last_name);

 

END LOOP;

 

END;

 

/