2007年12月26日 13:51:57
PL/SQL补疑之Collections and Records <2>
|
:-( 长度限制，所以分两篇发 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; / Tags：
plsql
|
一共有 0 条评论