Oracle PL/SQL/PL SQL/Table of number — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 09:59, 26 мая 2010
Содержание
- 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:
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>
assignments to nested table elements, and the ORA-6533 error.
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>
Associate array: varchar2 to number map
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>
Clear the salaries table by assigning the empty version to it
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> --
Declare an index-by table variable to hold the employee records that we read in
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> --
Delete a elements from 2, 3 and 4.
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>
Delete element 2.
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>
EXISTS method
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>
Extend space in number list.
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.
FIRST method returns the lowest subscript value used in a collection
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.
Legal and illegal table assignments.
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>
NULL key value in an index-by table
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>
number_list.EXTEND(2): Add two null value members at the end of the list.
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>
number_list.EXTEND(3,4): Add three members at the end of the list and copy the contents of item 4
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>
Number Table by BINARY_INTEGER
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>
Table of number index by varchar2
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>
Try to insert elements 3 through 5
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>
TYPE NumbersTab IS TABLE OF NUMBER.
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>
Use nested table constructors.
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>
Use the Oracle10g Collection API COUNT method against an element.
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>
Use the Oracle10g Collection API DELETE method against a set of elements.
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>
Use the Oracle10g Collection API EXISTS method against an element.
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>
Use the Oracle10g Collection API EXTEND method against an element.
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>
Use the Oracle10g Collection API FIRST and LAST methods against a collection.
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.
Use variable.Last to get the last element
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>
use virtual table in PL/SQL block
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.