Oracle PL/SQL/PL SQL/Table of number
Содержание
- 1 A nested table of a scalar variable:
- 2 assignments to nested table elements, and the ORA-6533 error.
- 3 Associate array: varchar2 to number map
- 4 Clear the salaries table by assigning the empty version to it
- 5 Declare an index-by table variable to hold the employee records that we read in
- 6 Delete a elements from 2, 3 and 4.
- 7 Delete element 2.
- 8 EXISTS method
- 9 Extend space in number list.
- 10 FIRST method returns the lowest subscript value used in a collection
- 11 Legal and illegal table assignments.
- 12 NULL key value in an index-by table
- 13 number_list.EXTEND(2): Add two null value members at the end of the list.
- 14 number_list.EXTEND(3,4): Add three members at the end of the list and copy the contents of item 4
- 15 Number Table by BINARY_INTEGER
- 16 Table of number index by varchar2
- 17 Try to insert elements 3 through 5
- 18 TYPE NumbersTab IS TABLE OF NUMBER.
- 19 Use nested table constructors.
- 20 Use the Oracle10g Collection API COUNT method against an element.
- 21 Use the Oracle10g Collection API DELETE method against a set of elements.
- 22 Use the Oracle10g Collection API EXISTS method against an element.
- 23 Use the Oracle10g Collection API EXTEND method against an element.
- 24 Use the Oracle10g Collection API FIRST and LAST methods against a collection.
- 25 Use variable.Last to get the last element
- 26 use virtual table in PL/SQL block
A nested table of a scalar variable:
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE number_table IS TABLE OF NUMBER;
3 list NUMBER_TABLE := number_table(1,2,3,4,5,6,7,8);
4 BEGIN
5 list.DELETE(2);
6 FOR i IN 1..list.COUNT LOOP
7 IF list.EXISTS(i) THEN
8 dbms_output.put("["||list(i)||"]");
9 END IF;
10 END LOOP;
11 dbms_output.new_line;
12 END;
13 /
[1][3][4][5][6][7] PL/SQL procedure successfully completed. SQL>
</source>
assignments to nested table elements, and the ORA-6533 error.
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE NumbersTab IS TABLE OF NUMBER; 3 v_Numbers NumbersTab := NumbersTab(1, 2, 3); 4 BEGIN 5 -- v_Numbers was initialized to have 3 elements. So the 6 -- following assignments are all legal. 7 v_Numbers(1) := 7; 8 v_Numbers(2) := -1; 9 10 -- However, this assignment will raise ORA-6533. 11 v_Numbers(4) := 4; 12 END; 13 /
DECLARE
ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 11
SQL> SQL>
</source>
Associate array: varchar2 to number map
<source lang="sql">
SQL> DECLARE
2 TYPE varcharType IS TABLE OF NUMBER(17,2) INDEX BY VARCHAR2(12);
3 city varcharType;
4 sales_figure NUMBER;
5 first VARCHAR2(12);
6 last VARCHAR2(12);
7 BEGIN
8 city("West") := 2.3;
9 city("East") := 1.9;
10 city("MidWest") := 3.4;
11 sales_figure := city("East");
12 first := city.FIRST;
13 last := city.LAST;
14 sales_figure := city(city.LAST);
15 city("West") := 1.55;
16 DBMS_OUTPUT.PUT_LINE (first);
17 DBMS_OUTPUT.PUT_LINE (last);
18 DBMS_OUTPUT.PUT_LINE (sales_figure);
19 END;
20 /
East West 2.3 PL/SQL procedure successfully completed. SQL>
</source>
Clear the salaries table by assigning the empty version to it
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SQL> DECLARE
2 TYPE salary_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 3 4 salaries salary_table; 5 salaries_empty salary_table; 6 BEGIN 7 salaries(20) := 50550; 8 salaries(40) := 50550; 9 salaries(60) := 50550; 10 11 salaries := salaries_empty; 12 END; 13 /
PL/SQL procedure successfully completed. SQL> SQL> --
</source>
Declare an index-by table variable to hold the employee records that we read in
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 TYPE num_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3
4 nums num_table;
5 some_num NUMBER;
6 BEGIN
7 nums(10) := 11;
8
9 BEGIN
10 some_num := nums(11);
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN
13 DBMS_OUTPUT.PUT_LINE("Element 11 does not exist.");
14 END;
15
16 IF nums.EXISTS(11) THEN
17 some_num := nums(11);
18 ELSE
19 DBMS_OUTPUT.PUT_LINE("Element 11 still does not exist.");
20 END IF;
21 END;
22 /
Element 11 does not exist. Element 11 still does not exist. PL/SQL procedure successfully completed. SQL> SQL> --
</source>
Delete a elements from 2, 3 and 4.
<source lang="sql">
SQL> SQL> create or replace TYPE number_table IS TABLE OF INTEGER;
2 /
Type created. SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS
2 BEGIN
3 FOR i IN list_in.FIRST..list_in.LAST LOOP
4 IF list_in.EXISTS(i) THEN
5 DBMS_OUTPUT.PUT_LINE("List "||list_in(i));
6 END IF;
7 END LOOP;
8 END print_list;
9 /
Procedure created. SQL> SQL> SQL> SQL> DECLARE
2 number_list NUMBER_TABLE;
3
4 BEGIN
5 IF NOT number_list.EXISTS(1) THEN
6 number_list := number_table(1,2,3,4,5);
7 END IF;
8
9 DBMS_OUTPUT.PUT_LINE("Nested table before a deletion");
10 print_list(number_list);
11
12 number_list.DELETE(2,4);
13
14 DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion");
15 print_list(number_list);
16 END;
17 /
Nested table before a deletion List 1 List 2 List 3 List 4 List 5 Nested table after a deletion List 1 List 5 PL/SQL procedure successfully completed. SQL>
</source>
Delete element 2.
<source lang="sql">
SQL> SQL> create or replace TYPE number_table IS TABLE OF INTEGER;
2 /
Type created. SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS
2 BEGIN
3 FOR i IN list_in.FIRST..list_in.LAST LOOP
4 IF list_in.EXISTS(i) THEN
5 DBMS_OUTPUT.PUT_LINE("List "||list_in(i));
6 END IF;
7 END LOOP;
8 END print_list;
9 /
Procedure created. SQL> SQL> SQL> SQL> SQL> DECLARE
2 number_list NUMBER_TABLE;
3 BEGIN
4 IF NOT number_list.EXISTS(1) THEN
5 number_list := number_table(1,2,3,4,5);
6 END IF;
7
8 DBMS_OUTPUT.PUT_LINE("Nested table before a deletion");
9 print_list(number_list);
10
11 number_list.DELETE(2);
12
13
14 DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion");
15 print_list(number_list);
16 END;
17 /
Nested table before a deletion List 1 List 2 List 3 List 4 List 5 Nested table after a deletion List 1 List 3 List 4 List 5 PL/SQL procedure successfully completed. SQL> SQL>
</source>
EXISTS method
<source lang="sql">
SQL> create or replace TYPE number_table IS TABLE OF INTEGER;
2 /
Type created. SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS
2 BEGIN
3 FOR i IN list_in.FIRST..list_in.LAST LOOP
4 IF list_in.EXISTS(i) THEN
5 DBMS_OUTPUT.PUT_LINE("List "||list_in(i));
6 END IF;
7 END LOOP;
8 END print_list;
9 /
Procedure created. SQL> SQL> SQL> SQL> DECLARE
2 number_list NUMBER_TABLE;
3
4 BEGIN
5 IF NOT number_list.EXISTS(1) THEN
6 number_list := number_table(1,2,3,4,5);
7 END IF;
8
9 DBMS_OUTPUT.PUT_LINE("Nested table before a deletion");
10 print_list(number_list);
11
12 number_list.DELETE(2);
13
14 DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion");
15 print_list(number_list);
16 END;
17 /
Nested table before a deletion List 1 List 2 List 3 List 4 List 5 Nested table after a deletion List 1 List 3 List 4 List 5 PL/SQL procedure successfully completed. SQL>
</source>
Extend space in number list.
<source lang="sql">
SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> SQL> -- Create a table for the example. SQL> CREATE TABLE myTable
2 (id NUMBER NOT NULL 3 ,CONSTRAINT id_pk PRIMARY KEY (id));
Table created. SQL> SQL> SQL> SQL> DECLARE
2 TYPE number_table IS TABLE OF myTable.id%TYPE; 3 4 number_list NUMBER_TABLE := number_table(); 5 6 BEGIN 7 8 9 number_list.EXTEND(10000); 10 11 12 FOR i IN 1..10000 LOOP 13 14 15 number_list(i) := i; 16 17 END LOOP; 18 19 20 FORALL i IN 1..number_list.COUNT 21 INSERT INTO myTable VALUES (number_list(i)); 22 END; 23 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myTable; Table dropped.
</source>
FIRST method returns the lowest subscript value used in a collection
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE number_table IS TABLE OF INTEGER INDEX BY VARCHAR2(9 CHAR);
3 number_list NUMBER_TABLE;
4 BEGIN
5
6 number_list("One") := 1;
7 number_list("Two") := 2;
8 number_list("Nine") := 9;
9
10 DBMS_OUTPUT.PUT_LINE("FIRST Index "||number_list.FIRST);
11 END;
12 /
FIRST Index Nine PL/SQL procedure successfully completed.
</source>
Legal and illegal table assignments.
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE NumbersTab IS TABLE OF NUMBER; 3 v_Numbers NumbersTab := NumbersTab(1, 2, 3); 4 BEGIN 5 v_Numbers(1) := 7; 6 v_Numbers(2) := -1; 7 8 v_Numbers(4) := 4; 9 END; 10 /
DECLARE
ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 8
SQL> SQL>
</source>
NULL key value in an index-by table
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE t_NumTab IS TABLE OF NUMBER 3 INDEX BY BINARY_INTEGER; 4 v_TempVAR t_NumTab; 5 BEGIN 6 v_TempVAR(NULL) := 4; 7 END; 8 /
DECLARE
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at line 6
SQL>
</source>
number_list.EXTEND(2): Add two null value members at the end of the list.
<source lang="sql">
SQL> create or replace TYPE number_table IS TABLE OF INTEGER;
2 /
Type created. SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS
2 BEGIN
3 FOR i IN list_in.FIRST..list_in.LAST LOOP
4 IF list_in.EXISTS(i) THEN
5 DBMS_OUTPUT.PUT_LINE("List "||list_in(i));
6 END IF;
7 END LOOP;
8 END print_list;
9 /
Procedure created. SQL> SQL> SQL> SQL> DECLARE
2 number_list NUMBER_TABLE; 3 4 BEGIN 5 IF NOT number_list.EXISTS(1) THEN 6 number_list := number_table(1,2,3,4,5); 7 END IF; 8 9 print_list(number_list); 10 11 -- Add two null value members at the end of the list. 12 number_list.EXTEND(2); 13 14 -- Print revised contents. 15 DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion"); 16 print_list(number_list); 17 END; 18 /
List 1 List 2 List 3 List 4 List 5 Nested table after a deletion List 1 List 2 List 3 List 4 List 5 List List PL/SQL procedure successfully completed. SQL> SQL>
</source>
number_list.EXTEND(3,4): Add three members at the end of the list and copy the contents of item 4
<source lang="sql">
SQL> create or replace TYPE number_table IS TABLE OF INTEGER;
2 /
Type created. SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS
2 BEGIN
3 FOR i IN list_in.FIRST..list_in.LAST LOOP
4 IF list_in.EXISTS(i) THEN
5 DBMS_OUTPUT.PUT_LINE("List "||list_in(i));
6 END IF;
7 END LOOP;
8 END print_list;
9 /
Procedure created. SQL> SQL> SQL> SQL> DECLARE
2 number_list NUMBER_TABLE; 3 4 BEGIN 5 IF NOT number_list.EXISTS(1) THEN 6 number_list := number_table(1,2,3,4,5); 7 END IF; 8 9 print_list(number_list); 10 11 12 number_list.EXTEND(3,4); 13 14 15 DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion"); 16 print_list(number_list); 17 END; 18 /
List 1 List 2 List 3 List 4 List 5 Nested table after a deletion List 1 List 2 List 3 List 4 List 5 List 4 List 4 List 4 PL/SQL procedure successfully completed. SQL>
</source>
Number Table by BINARY_INTEGER
<source lang="sql">
SQL> SQL> set serveroutput on SQL> SQL> DECLARE
2 TYPE NumberTab IS TABLE OF NUMBER
3 INDEX BY BINARY_INTEGER;
4 v_Numbers NumberTab;
5 BEGIN
6 FOR v_Count IN 1..10 LOOP
7 v_Numbers(v_Count) := v_Count * 10;
8 END LOOP;
9
10 DBMS_OUTPUT.PUT_LINE("Table elements: ");
11 FOR v_Count IN 1..10 LOOP
12 DBMS_OUTPUT.PUT_LINE(" v_Numbers(" || v_Count || "): " ||
13 v_Numbers(v_Count));
14 END LOOP;
15
16 BEGIN
17 DBMS_OUTPUT.PUT_LINE("v_Numbers(11): " || v_Numbers(11));
18 EXCEPTION
19 WHEN NO_DATA_FOUND THEN
20 DBMS_OUTPUT.PUT_LINE(
21 "No data found reading v_Numbers(11)!");
22 END;
23 END;
24 /
Table elements: v_Numbers(1): 10 v_Numbers(2): 20 v_Numbers(3): 30 v_Numbers(4): 40 v_Numbers(5): 50 v_Numbers(6): 60 v_Numbers(7): 70 v_Numbers(8): 80 v_Numbers(9): 90 v_Numbers(10): 100 No data found reading v_Numbers(11)! PL/SQL procedure successfully completed. SQL> SQL> SQL>
</source>
Table of number index by varchar2
<source lang="sql">
SQL> DECLARE
2 TYPE charTableType IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
3 charTable charTableType;
4 stringTable charTableType;
5 howmany NUMBER;
6 which VARCHAR2(64);
7
8 BEGIN
9 charTable("A") := 1;
10 charTable("B") := 2;
11 howmany := charTable("A");
12
13 stringTable("C") := 3;
14 stringTable("D") := 1000;
15 stringTable("D") := 1001;
16 which := stringTable.FIRST;
17 dbms_output.put_line(which);
18 which := stringTable.LAST;
19 dbms_output.put_line(which);
20 howmany := stringTable(stringTable.LAST);
21 dbms_output.put_line(howmany);
22 END;
23 /
C D 1001 PL/SQL procedure successfully completed. SQL> SQL> SQL>
</source>
Try to insert elements 3 through 5
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF NUMBER; 3 v_Numbers t_Numbers := t_Numbers(1, 2, 3, 4, 5); 4 BEGIN 5 v_Numbers.DELETE(4); 6 7 FORALL v_Count IN 3..5 8 INSERT INTO MyTable (num_col) VALUES (v_Numbers(v_Count)); 9 END; 10 /
DECLARE
ERROR at line 1: ORA-22160: element at index [4] does not exist ORA-06512: at line 7
SQL> SQL> select * from MyTable; no rows selected SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
TYPE NumbersTab IS TABLE OF NUMBER.
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE NumbersTab IS TABLE OF NUMBER; 3 4 v_Tab1 NumbersTab := NumbersTab(-1); 5 6 v_Primes NumbersTab := NumbersTab(1, 2, 3, 5, 7); 7 8 v_Tab2 NumbersTab := NumbersTab(); 9 BEGIN 10 v_Tab1(1) := 12345; 11 12 FOR v_Count IN 1..5 LOOP 13 DBMS_OUTPUT.PUT(v_Primes(v_Count) || " "); 14 END LOOP; 15 DBMS_OUTPUT.NEW_LINE; 16 END; 17 /
1 2 3 5 7 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Use nested table constructors.
<source lang="sql">
SQL> SQL> set serveroutput on SQL> SQL> DECLARE
2 TYPE NumbersTab IS TABLE OF NUMBER; 3 4 5 v_Tab1 NumbersTab := NumbersTab(-1); 6 7 8 v_Primes NumbersTab := NumbersTab(1, 2, 3, 5, 7); 9 10 11 v_Tab2 NumbersTab := NumbersTab(); 12 BEGIN 13 14 15 v_Tab1(1) := 12345; 16 17 18 FOR v_Count IN 1..5 LOOP 19 DBMS_OUTPUT.PUT(v_Primes(v_Count) || " "); 20 END LOOP; 21 DBMS_OUTPUT.NEW_LINE; 22 END; 23 /
1 2 3 5 7 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Use the Oracle10g Collection API COUNT method against an element.
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE
2
3 TYPE number_table IS TABLE OF INTEGER;
4
5
6 number_list NUMBER_TABLE := number_table(1,2,3,4,5);
7
8 BEGIN
9
10
11
12 DBMS_OUTPUT.PUT_LINE("Count :"||number_list.COUNT);
13
14 END;
15 /
Count :5 PL/SQL procedure successfully completed. SQL>
</source>
Use the Oracle10g Collection API DELETE method against a set of elements.
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE
2
3
4 TYPE number_table IS TABLE OF INTEGER;
5
6
7 number_list NUMBER_TABLE;
8
9
10 PROCEDURE print_list (list_in NUMBER_TABLE) IS
11
12 BEGIN
13
14
15 FOR i IN list_in.FIRST..list_in.LAST LOOP
16
17
18 IF list_in.EXISTS(i) THEN
19 DBMS_OUTPUT.PUT_LINE("List:"||list_in(i));
20
21 END IF;
22
23 END LOOP;
24
25 END print_list;
26
27 BEGIN
28
29
30 IF NOT number_list.EXISTS(1) THEN
31
32
33 number_list := number_table(1,2,3,4,5);
34
35 END IF;
36
37 print_list(number_list);
38
39
40 number_list.DELETE(2,4);
41
42
43 print_list(number_list);
44
45 END;
46 /
List:1 List:2 List:3 List:4 List:5 List:1 List:5 PL/SQL procedure successfully completed. SQL>
</source>
Use the Oracle10g Collection API EXISTS method against an element.
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE
2
3
4 TYPE number_table IS TABLE OF INTEGER;
5
6
7 number_list NUMBER_TABLE;
8
9
10 PROCEDURE print_list
11 (list_in NUMBER_TABLE) IS
12
13 BEGIN
14
15
16 FOR i IN list_in.FIRST..list_in.LAST LOOP
17
18
19 IF list_in.EXISTS(i) THEN
20
21
22 DBMS_OUTPUT.PUT_LINE("List :"||list_in(i));
23
24 END IF;
25
26 END LOOP;
27
28 END print_list;
29
30 BEGIN
31
32
33 IF NOT number_list.EXISTS(1) THEN
34
35
36 number_list := number_table(1,2,3,4,5);
37
38 END IF;
39
40
41 DBMS_OUTPUT.PUT_LINE("Nested table before a deletion");
42
43
44 print_list(number_list);
45
46
47 number_list.DELETE(2);
48
49
50 DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion");
51
52
53 print_list(number_list);
54
55 END;
56 /
Nested table before a deletion List :1 List :2 List :3 List :4 List :5 Nested table after a deletion List :1 List :3 List :4 List :5 PL/SQL procedure successfully completed. SQL>
</source>
Use the Oracle10g Collection API EXTEND method against an element.
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE
2
3
4 TYPE number_table IS TABLE OF INTEGER;
5
6
7 number_list NUMBER_TABLE := number_table(1,2);
8
9
10 PROCEDURE print_list
11 (list_in NUMBER_TABLE) IS
12
13 BEGIN
14
15
16 FOR i IN list_in.FIRST..list_in.LAST LOOP
17
18
19 IF list_in.EXISTS(i) THEN
20
21
22 DBMS_OUTPUT.PUT_LINE("List :"||list_in(i));
23
24 END IF;
25
26 END LOOP;
27
28 END print_list;
29
30 BEGIN
31 print_list(number_list);
32
33
34 number_list.EXTEND(2);
35
36
37 number_list.EXTEND(3,2);
38
39 print_list(number_list);
40
41 END;
42 /
List :1 List :2 List :1 List :2 List : List : List :2 List :2 List :2 PL/SQL procedure successfully completed. SQL>
</source>
Use the Oracle10g Collection API FIRST and LAST methods against a collection.
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE
2
3
4 TYPE number_table IS TABLE OF INTEGER INDEX BY VARCHAR2(9 CHAR);
5
6
7 number_list NUMBER_TABLE;
8
9 BEGIN
10
11
12 number_list("One") := 1;
13 number_list("Two") := 2;
14 number_list("Nine") := 9;
15
16
17 DBMS_OUTPUT.PUT_LINE("FIRST Index :"||number_list.FIRST);
18 DBMS_OUTPUT.PUT_LINE("NEXT Index :"||number_list.NEXT(number_list.FIRST));
19
20
21 DBMS_OUTPUT.PUT_LINE(CHR(10)||"LAST Index :"||number_list.LAST);
22 DBMS_OUTPUT.PUT_LINE(" PRIOR Index :"||number_list.PRIOR(number_list.LAST));
23
24 END;
25 /
FIRST Index :Nine NEXT Index :One LAST Index :Two PRIOR Index :One PL/SQL procedure successfully completed.
</source>
Use variable.Last to get the last element
<source lang="sql">
SQL> DECLARE
2 TYPE charTableType IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
3 charTable charTableType;
4 stringTable charTableType;
5 howmany NUMBER;
6 which VARCHAR2(64);
7
8 BEGIN
9 charTable("A") := 1;
10 charTable("B") := 2;
11 howmany := charTable("A");
12
13 stringTable("C") := 3;
14 stringTable("D") := 1000;
15 stringTable("D") := 1001;
16 which := stringTable.FIRST;
17
18 dbms_output.put_line(which);
19 which := stringTable.LAST;
20 dbms_output.put_line(which);
21 howmany := stringTable(stringTable.LAST);
22
23 dbms_output.put_line(howmany);
24 END;
25 /
C D 1001 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL>
</source>
use virtual table in PL/SQL block
<source lang="sql">
SQL> SQL> SQL> create or replace type virtual_table_type as table of number
2 /
Type created. SQL> create or replace
2 function virtual_table( p_start number, 3 p_end number ) return virtual_table_type as 4 l_vt_type virtual_table_type := virtual_table_type(); 5 begin 6 for i in p_start .. p_end loop 7 l_vt_type.extend(); 8 dbms_output.put_line( "adding " || i || " to collection..." ); 9 l_vt_type(l_vt_type.count) := i; 10 end loop; 11 dbms_output.put_line( "done..." ); 12 return l_vt_type; 13 end virtual_table; 14 /
Function created. SQL> set serveroutput on SQL> begin
2 for x in ( select * 3 from table( virtual_table( -2, 2) ) ) 4 loop 5 dbms_output.put_line( "printing from anonymous block " || 6 x.column_value ); 7 end loop; 8 end; 9 /
adding -2 to collection... adding -1 to collection... adding 0 to collection... adding 1 to collection... adding 2 to collection... done... printing from anonymous block -2 printing from anonymous block -1 printing from anonymous block 0 printing from anonymous block 1 printing from anonymous block 2 PL/SQL procedure successfully completed.
</source>
