PL/SQL补疑之Collections and Records <2>

使用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 代替指定每个字段.

-- 使用 %ROWTYPE 代替%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),'') || ',

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;

/

原文：http://valen.blog.ccidnet.com/blog-htm-do-showone-uid-51502-type-blog-itemid-227042.html

阅读(391) | 评论(0) | 转发(0) |

0

上一篇： Oracle并发插入的机制

下一篇： PL/SQL补疑之Collections and Records <1>

给主人留下些什么吧！~~

评论热议

请登录后评论。

登录 注册

关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6