在调试代码的过程中，才发现很多代码没法直接执行成功，需要做些修改方可成功执行。
不知道是作者有意为之，还是疏忽造成的呢？我宁愿相信是前者。通过不断的代码调试，可以提高我们解决实际问题的能力！
1. Oracle Database 12C的集合API总结
| Method | 方法 | Definition |
| COUNT | 元素个数 | Returns the number of elements that have been allocated space in varray and table data types, and returns the total of all elements in associative arrays. The return value of the COUNT method can be smaller than the return value of LIMIT for the varray collection. It has the following prototype: PLS_INTEGER COUNT |
| DELETE | 删除元素 | lets you remove elements from table collections and associative arrays. It doesn’t work with varray collections, and an attempt to remove an element from a varray raises a PLS-00306 exception. The DELETE method takes two formal parameters; one is mandatory and the other is optional. Both parameters are index values and must occur in ascending order. The DELETE procedure deletes everything from the parameter n to m, inclusively, when you supply two parameters. The prototypes are as follows: void DELETE(n) void DELETE(n,m) |
| EXISTS | 指定元素是否存在 | Checks to find an element with the supplied index in a collection. It returns true when the element is found and null when an initialized varray or table is empty. It has one mandatory parameter, which is a valid index value. It has the following prototype: Boolean EXISTS(n) |
| EXTEND | 分配空间 | Allocates space for one or more new elements in a varray or table collection. It has two optional parameters. It adds space for one element by default without a parameter. The first parameter designates how many physical spaces should be allocated in memory, the only constraint being the limit (or maximum value) of the varray. The second parameter is an index value. When the function receives two parameters, the first determines how many elements to add and the second is an index value. The EXTEND procedure uses the index value to copy a value into the newly added space. It has the following prototypes: void EXTEND void EXTEND(n) void EXTEND(n,i) |
| FIRST | 最小下标 | Returns the lowest subscript value in a collection. It can return a PLS_INTEGER, VARCHAR2, or LONG type. It has the following prototype: mixed FIRST |
| LAST | 最大下标 | Returns the highest subscript value in a collection. It can return a PLS_INTEGER, VARCHAR2, or LONG type. It has the following prototype: mixed LAST |
| LIMIT | 变长数组最大长度（最大元素个数） | Returns the highest possible subscript value in a collection. It can only return a PLS_INTEGER type, and can only be used by a VARRAY data type. It has the following prototype: mixed LIMIT |
| NEXT(n) | 下一下标 | Returns the next higher subscript value in a collection when successful; otherwise returns false. The return value is a PLS_INTEGER, VARCHAR2, or LONG type. It requires a valid index value as an actual parameter and raises an exception when the index is invalid. It has the following prototype: mixed NEXT(n) |
| PRIOR(n) | 上一下标 | Returns the next lower subscript value in a collection when successful; otherwise returns false. The return value is a PLS_INTEGER, VARCHAR2, or LONG type. It requires a valid index value as an actual parameter and raises an exception when the index is invalid. It has the following prototype: mixed PRIOR(n) |
| TRIM | 移除下标（从集合末尾删除元素） | Removes a subscripted value from a collection. It has one optional parameter. Without an actual parameter, it removes the highest element from the array. An actual parameter is interpreted as the number of elements removed from the end of the collection. It has the following prototype: void TRIM void TRIM(n) |
2. EXISTS方法
1）EXISTS方法的局限性
SQL> ed
已写入 file afiedt.buf
1 DECLARE
2 /* Define the table collection */
3 TYPE empty_table IS TABLE OF prominent_object;
4 /* Declare a table collection variable */
5 lv_array EMPTY_TABLE := empty_table(null);
6 BEGIN
7 /* Check whether the element is allocate in memory. */
8 IF lv_array.EXISTS(1) THEN
9 dbms_output.put_line('Valid collection');
10 ELSE
11 dbms_output.put_line('Invalid collection');
12 END IF;
13* END;
SQL> /
Valid collection
PL/SQL 过程已成功完成。2 )EXISTS最佳使用方法
SQL> ED
已写入 file afiedt.buf
1 DECLARE
2 /* Define table. */
3 TYPE x_table IS TABLE OF VARCHAR2(10);
4 /* Declare an index counter. */
5 lv_index NUMBER := 1;
6 /* Declare a local collection variable. */
7 lv_table X_TABLE := x_table();
8 BEGIN
9 IF lv_table.EXISTS(lv_index) AND NOT lv_table.COUNT = 0 AND lv_table(lv_index) IS NOT NULL THEN
10 dbms_output.put_line(lv_table(lv_index));
11 ELSIF lv_table.EXISTS(lv_index) AND NOT lv_table.COUNT = 0 AND lv_table IS NOT EMPTY THEN
12 dbms_output.put_line('lv_table('||lv_index||') is NOT EMPTY?');
13 ELSE
14 dbms_output.put_line('lv_table('||lv_index||') is EMPTY? NULL? NOT EMPTY? NOT NULL?');
15 END IF;
16 --dbms_output.put_line(lv_table IS EMPTY);
17 dbms_output.put_line('lv_table COUNT? '|| lv_table.COUNT);
18* END;
SQL> /
lv_table(1) is EMPTY? NULL? NOT EMPTY? NOT NULL?
lv_table COUNT? 0
PL/SQL 过程已成功完成。
SQL> ED
已写入 file afiedt.buf
1 DECLARE
2 /* Define table. */
3 TYPE x_table IS TABLE OF VARCHAR2(10);
4 /* Declare an index counter. */
5 lv_index NUMBER := 1;
6 /* Declare a local collection variable. */
7 lv_table X_TABLE := x_table('Something');
8 BEGIN
9 IF lv_table.EXISTS(lv_index) AND lv_table.COUNT > 0 THEN
10 dbms_output.put_line(lv_table(lv_index));
11 END IF;
12* END;
13 /
Something
PL/SQL 过程已成功完成。3. COUNT方法
SQL> ed
已写入 file afiedt.buf
1 DECLARE
2 /* Define a table collection. */
3 TYPE NUMBER_table IS TABLE OF INTEGER;
4 /* Declare an initialized table collection. */
5 lv_table NUMBER_TABLE := number_table(1,2,3,4,5);
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE('How many? ['||lv_table.COUNT||']');
8* END;
9 /
How many? [5]
PL/SQL 过程已成功完成。4. DELETE方法
SQL> ed
已写入 file afiedt.buf
1 DECLARE
2 /* Declare variable with meaningful name. */
3 current INTEGER;
4 /* Define a table collection. */
5 TYPE x_table IS TABLE OF VARCHAR2(6);
6 /* Declare an initialized table collection. */
7 lv_table X_TABLE := x_table('One','Two','Three','Four','Five');
8 BEGIN
9 /* Remove one element with an index of 2. */
10 lv_table.DELETE(2,2);
11 /* Remove elements for an inclusive range of 4 to 5. */
12 lv_table.DELETE(4,5);
13 /* Set the starting index. */
14 current := lv_table.FIRST;
15 /* Read through index values in ascending order. */
16 WHILE (current <= lv_table.LAST) LOOP
17 dbms_output.put_line(
18 'Index ['||current||'] Value ['||lv_table(current)||']');
19 /* Shift index to next higher value. */
20 current := lv_table.NEXT(current);
21 END LOOP;
22* END;
23 /
Index [1] Value [One]
Index [3] Value [Three]
PL/SQL 过程已成功完成。
5. EXTEND方法
SQL> DECLARE
2 /* Declare variable with meaningful name. */
3 current INTEGER;
4
5 /* Define a table collection. */
6 TYPE x_table IS TABLE OF VARCHAR2(6);
7
8 /* Declare an initialized table collection. */
9 lv_table X_TABLE := x_table('One');
10 BEGIN
11 /* Extend space, and assign a value. */
12 lv_table.EXTEND;
13
14 /* Assign a value to the last allocated element. */
15 lv_table(lv_table.COUNT) := 'Two';
16
17 /* Set the starting index. */
18 current := lv_table.FIRST;
19
20 /* Read through index values in ascending order. */
21 WHILE (current <= lv_table.LAST) LOOP
22 dbms_output.put_line(
23 'Index ['||current||'] Value ['||lv_table(current)||']');
24 /* Shift index to next higher value. */
25 current := lv_table.NEXT(current);
26 END LOOP;
27 END;
28 /
Index [1] Value [One]
Index [2] Value [Two]
PL/SQL 过程已成功完成。
扫一扫
739
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
