How to define in PL/SQL the following structure:
A list of strings which contains multiple rows.
Example:
'User A'
-->
(1)
-->
1
(2)
-->
2
'User B'
-->
(1)
-->
0
(2)
-->
9
The integers are defined as:
TYPE number_arry IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
How to define the whole structure ?
I want to fill this structure from this table:
RowId | User_A | User_B
------+--------+--------
1 | 1 | 0
2 | 2 | 0
3 | 3 | 9
With these statements:
CURSOR c1
IS
SELECT User_A, User_B FROM my_table;
OPEN c1;
LOOP
FETCH c1
BULK COLLECT INTO
my_dict('User A'),
my_dict('User B')
LIMIT 1000;
EXIT WHEN c1%NOTFOUND;
END LOOP;
You could use the following structure:
SQL> CREATE TABLE my_table AS
2 SELECT 1 user_a, 0 user_b FROM dual
3 UNION ALL SELECT 2, 0 FROM dual
4 UNION ALL SELECT 3, 9 FROM dual;
Table created
SQL> DECLARE
2 CURSOR c1 IS
3 SELECT User_A, User_B FROM my_table;
4 TYPE number_arry IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5 TYPE dictionary_type IS TABLE OF number_arry INDEX BY VARCHAR2(30);
6 l_dico dictionary_type;
7 BEGIN
8 OPEN c1;
9 LOOP
10 FETCH c1 BULK COLLECT
11 INTO l_dico('User A'), l_dico('User B') LIMIT 1000;
12 EXIT WHEN c1%NOTFOUND;
13 END LOOP;
14 CLOSE c1;
15 END;
16 /
PL/SQL procedure successfully completed