| MultiSet Operations on PL/SQL Tables [message #648844] |
Fri, 04 March 2016 10:33  |
mjm22
Messages: 54
Registered: January 2010
Location: Singapore
|
Member |
|
|
Hi,
I have a Nested Table defined in the PL/SQL Package that, when populated can potentially contains duplicate entries. I would like to remove them using something like the Multiset operation but the issue I have is that I seem unable to do this for a Nested Table that has RECORD Type for the Element Datatype.
For example, the below code will find some user data, some of which have the same username. I can use the Multiset Union to join the set contents to a copy of itself and end up with double the number of entries.
DECLARE
-- multi column nested table type
TYPE user_rec_tt IS TABLE OF all_users%ROWTYPE;
l_users_all user_rec_tt;
l_users_sub user_rec_tt;
BEGIN
-- collect into multi column nested table
SELECT username
,user_id
,created
BULK COLLECT INTO l_users_all
FROM (
SELECT
username
,user_id
,created
FROM all_users
WHERE user_id BETWEEN 126 AND 130
UNION ALL
SELECT
username
,user_id
,created
FROM all_users
WHERE user_id BETWEEN 129 AND 132
);
-- how many records
DBMS_OUTPUT.PUT_LINE('Nested Table count All: ' || l_users_all.COUNT);
FOR i IN l_users_all.FIRST .. l_users_all.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('User Name :' || l_users_all(i).username || ' -> User Id :' || l_users_all(i).user_id);
END LOOP;
DBMS_OUTPUT.PUT_line(CHR(10) || ' ...' || CHR(10));
-- take a copy of the collection
l_users_sub := l_users_all;
-- take distinct values out of v_temp, v_test_type into v_test_type
l_users_sub := l_users_sub MULTISET UNION l_users_all;
-- l_users_sub := l_users_sub MULTISET UNION DISTINCT l_users_all;
-- how many records
DBMS_OUTPUT.PUT_LINE('Nested Table count All: ' || l_users_sub.COUNT);
FOR i IN l_users_sub.FIRST .. l_users_sub.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('User Name :' || l_users_sub(i).username || ' -> User Id :' || l_users_sub(i).user_id);
END LOOP;
DBMS_OUTPUT.PUT_line(CHR(10) || ' ...' || CHR(10));
END;
/
Nested Table count All: 9
User Name :RZE01 -> User Id :126
User Name :CAM25 -> User Id :127
User Name :SDL08 -> User Id :128
User Name :DAL97 -> User Id :129
User Name :MIN03 -> User Id :130
User Name :DAL97 -> User Id :129
User Name :MIN03 -> User Id :130
User Name :VRA46 -> User Id :131
User Name :VVE09 -> User Id :132
...
Nested Table count All: 18
User Name :RZE01 -> User Id :126
User Name :CAM25 -> User Id :127
User Name :SDL08 -> User Id :128
User Name :DAL97 -> User Id :129
User Name :MIN03 -> User Id :130
User Name :DAL97 -> User Id :129
User Name :MIN03 -> User Id :130
User Name :VRA46 -> User Id :131
User Name :VVE09 -> User Id :132
User Name :RZE01 -> User Id :126
User Name :CAM25 -> User Id :127
User Name :SDL08 -> User Id :128
User Name :DAL97 -> User Id :129
User Name :MIN03 -> User Id :130
User Name :DAL97 -> User Id :129
User Name :MIN03 -> User Id :130
User Name :VRA46 -> User Id :131
User Name :VVE09 -> User Id :132
But what I really want to do is use the second (commented out) Multiset option in order to remove the duplicates using the below..
l_users_sub := l_users_sub MULTISET UNION DISTINCT l_users_all;
I have shown that I can take the Username into a Simple Nested Table and perform the operation (see below) but this code doesn't work for the more complex Nested Table Type. I am kind of tied into the current datatye definitions so I am wondering if there is another technique I can use to get this working other than looping through the entire collection and removing the duplicates.
DECLARE
-- It is a table of Anniversary Account Records. It contains one record per Account.
TYPE user_rec_tt IS TABLE OF all_users%ROWTYPE;
l_users_all user_rec_tt;
TYPE nested_user_typ IS TABLE OF VARCHAR2(24);
v_all_user_ids nested_user_typ := NEW nested_user_typ();
v_dist_user_ids nested_user_typ;
BEGIN
SELECT username
,user_id
,created
BULK COLLECT INTO l_users_all
FROM (
SELECT
username
,user_id
,created
FROM all_users
WHERE user_id BETWEEN 126 AND 130
UNION ALL
SELECT
username
,user_id
,created
FROM all_users
WHERE user_id BETWEEN 129 AND 132
);
-- how many records
DBMS_OUTPUT.PUT_LINE('PL/SQL Table count All: ' || l_users_all.COUNT);
FOR i IN l_users_all.FIRST .. l_users_all.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('User Name :' || l_users_all(i).username || ' -> User Id :' || l_users_all(i).user_id);
END LOOP;
DBMS_OUTPUT.PUT_line(CHR(10) || ' ...' || CHR(10));
FOR i IN l_users_all.first .. l_users_all.last
LOOP
v_all_user_ids.EXTEND;
v_all_user_ids(v_all_user_ids.LAST) := l_users_all(i).username;
END LOOP;
-- copy table data
v_dist_user_ids := v_all_user_ids;
DBMS_OUTPUT.PUT_LINE('IDs All: ' || l_users_all.COUNT);
FOR i IN v_all_user_ids.FIRST .. v_all_user_ids.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('User Name :' || v_all_user_ids(i));
END LOOP;
DBMS_OUTPUT.PUT_line(CHR(10) || ' ...' || CHR(10));
-- take distinct values out of v_temp, v_test_type into v_test_type
v_dist_user_ids := v_dist_user_ids MULTISET UNION DISTINCT v_all_user_ids;
DBMS_OUTPUT.PUT_LINE('Distinct IDs All: ' || v_dist_user_ids.COUNT);
FOR i IN v_dist_user_ids.FIRST .. v_dist_user_ids.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('User Name : ' || i || ' - ' || v_dist_user_ids(i));
END LOOP;
DBMS_OUTPUT.PUT_line(CHR(10) || ' ...' || CHR(10));
DBMS_OUTPUT.PUT_line(CHR(10) || ' ...' || CHR(10));
END;
Thanks in advance,
Mike
|
|
|
|
|
|
| Re: MultiSet Operations on PL/SQL Tables [message #648849 is a reply to message #648845] |
Fri, 04 March 2016 14:37  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Example:
SCOTT@orcl> CREATE OR REPLACE TYPE user_rec_typ AS OBJECT
2 (username VARCHAR2(128),
3 user_id NUMBER,
4 MAP MEMBER FUNCTION sort_key RETURN VARCHAR2);
5 /
Type created.
SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> CREATE OR REPLACE TYPE BODY user_rec_typ
2 AS
3 MAP MEMBER FUNCTION sort_key RETURN VARCHAR2
4 IS
5 BEGIN
6 RETURN user_id || username;
7 END;
8 END;
9 /
Type body created.
SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> CREATE OR REPLACE TYPE user_rec_tt AS TABLE OF user_rec_typ;
2 /
Type created.
SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> DECLARE
2 l_users_all user_rec_tt;
3 BEGIN
4 SELECT user_rec_typ (username, user_id)
5 BULK COLLECT
6 INTO l_users_all
7 FROM (
8 SELECT username, user_id
9 FROM all_users
10 WHERE user_id BETWEEN 70 AND 77
11 UNION ALL
12 SELECT username, user_id
13 FROM all_users
14 WHERE user_id BETWEEN 75 AND 78
15 );
16
17 -- how many records
18 DBMS_OUTPUT.PUT_LINE('Nested Table count All: ' || l_users_all.COUNT);
19 FOR i IN l_users_all.FIRST .. l_users_all.LAST
20 LOOP
21 DBMS_OUTPUT.PUT_LINE('User Name :' || l_users_all(i).username || ' -> User Id :' || l_users_all(i).user_id);
22 END LOOP;
23 DBMS_OUTPUT.PUT_line(CHR(10) || ' ...' || CHR(10));
24
25 -- remove duplicates
26 l_users_all := l_users_all MULTISET UNION DISTINCT l_users_all;
27
28 -- how many records
29 DBMS_OUTPUT.PUT_LINE('Nested Table count All: ' || l_users_all.COUNT);
30 FOR i IN l_users_all.FIRST .. l_users_all.LAST
31 LOOP
32 DBMS_OUTPUT.PUT_LINE('User Name :' || l_users_all(i).username || ' -> User Id :' || l_users_all(i).user_id);
33 END LOOP;
34 DBMS_OUTPUT.PUT_line(CHR(10) || ' ...' || CHR(10));
35 END;
36 /
Nested Table count All: 9
User Name :SI_INFORMTN_SCHEMA -> User Id :78
User Name :ORDPLUGINS -> User Id :77
User Name :ORDDATA -> User Id :76
User Name :ORDSYS -> User Id :75
User Name :ORDPLUGINS -> User Id :77
User Name :ORDDATA -> User Id :76
User Name :ORDSYS -> User Id :75
User Name :CTXSYS -> User Id :73
User Name :OJVMSYS -> User Id :70
...
Nested Table count All: 6
User Name :SI_INFORMTN_SCHEMA -> User Id :78
User Name :ORDPLUGINS -> User Id :77
User Name :ORDDATA -> User Id :76
User Name :ORDSYS -> User Id :75
User Name :CTXSYS -> User Id :73
User Name :OJVMSYS -> User Id :70
...
PL/SQL procedure successfully completed.
[Updated on: Fri, 04 March 2016 14:39] Report message to a moderator |
|
|
|
| Re: MultiSet Operations on PL/SQL Tables [message #648850 is a reply to message #648849] |
Fri, 04 March 2016 14:55 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Careful! user_id || username might cause wrong results.
SQL> DECLARE
2 l_users_all user_rec_tt;
3 BEGIN
4 SELECT user_rec_typ (username, user_id)
5 BULK COLLECT
6 INTO l_users_all
7 FROM (
8 SELECT '0123' username, 1 user_id
9 FROM dual
10 UNION ALL
11 SELECT '123', 10
12 FROM dual
13 );
14
15 -- how many records
16 DBMS_OUTPUT.PUT_LINE('Nested Table count All: ' || l_users_all.COUNT);
17 FOR i IN l_users_all.FIRST .. l_users_all.LAST
18 LOOP
19 DBMS_OUTPUT.PUT_LINE('User Name :' || l_users_all(i).username || ' -> User Id :' || l_users_all(i).user_id);
20 END LOOP;
21 DBMS_OUTPUT.PUT_line(CHR(10) || ' ...' || CHR(10));
22
23 -- remove duplicates
24 l_users_all := l_users_all MULTISET UNION DISTINCT l_users_all;
25
26 -- how many records
27 DBMS_OUTPUT.PUT_LINE('Nested Table count All: ' || l_users_all.COUNT);
28 FOR i IN l_users_all.FIRST .. l_users_all.LAST
29 LOOP
30 DBMS_OUTPUT.PUT_LINE('User Name :' || l_users_all(i).username || ' -> User Id :' || l_users_all(i).user_id);
31 END LOOP;
32 DBMS_OUTPUT.PUT_line(CHR(10) || ' ...' || CHR(10));
33 END;
34 /
Nested Table count All: 2
User Name :0123 -> User Id :1
User Name :123 -> User Id :10
...
Nested Table count All: 1
User Name :0123 -> User Id :1
...
PL/SQL procedure successfully completed.
SQL>
We should put a separator character that can't be part of username, e.g. CHR(0):
SQL> DROP TYPE user_rec_tt
2 /
Type dropped.
SQL> CREATE OR REPLACE TYPE user_rec_typ AS OBJECT
2 (username VARCHAR2(128),
3 user_id NUMBER,
4 MAP MEMBER FUNCTION sort_key RETURN VARCHAR2);
5 /
Type created.
SQL> CREATE OR REPLACE TYPE BODY user_rec_typ
2 AS
3 MAP MEMBER FUNCTION sort_key RETURN VARCHAR2
4 IS
5 BEGIN
6 RETURN user_id || chr(0) || username;
7 END;
8 END;
9 /
Type body created.
SQL> CREATE OR REPLACE TYPE user_rec_tt AS TABLE OF user_rec_typ;
2 /
Type created.
SQL> DECLARE
2 l_users_all user_rec_tt;
3 BEGIN
4 SELECT user_rec_typ (username, user_id)
5 BULK COLLECT
6 INTO l_users_all
7 FROM (
8 SELECT '0123' username, 1 user_id
9 FROM dual
10 UNION ALL
11 SELECT '123', 10
12 FROM dual
13 );
14
15 -- how many records
16 DBMS_OUTPUT.PUT_LINE('Nested Table count All: ' || l_users_all.COUNT);
17 FOR i IN l_users_all.FIRST .. l_users_all.LAST
18 LOOP
19 DBMS_OUTPUT.PUT_LINE('User Name :' || l_users_all(i).username || ' -> User Id :' || l_users_all(i).user_id);
20 END LOOP;
21 DBMS_OUTPUT.PUT_line(CHR(10) || ' ...' || CHR(10));
22
23 -- remove duplicates
24 l_users_all := l_users_all MULTISET UNION DISTINCT l_users_all;
25
26 -- how many records
27 DBMS_OUTPUT.PUT_LINE('Nested Table count All: ' || l_users_all.COUNT);
28 FOR i IN l_users_all.FIRST .. l_users_all.LAST
29 LOOP
30 DBMS_OUTPUT.PUT_LINE('User Name :' || l_users_all(i).username || ' -> User Id :' || l_users_all(i).user_id);
31 END LOOP;
32 DBMS_OUTPUT.PUT_line(CHR(10) || ' ...' || CHR(10));
33 END;
34 /
Nested Table count All: 2
User Name :0123 -> User Id :1
User Name :123 -> User Id :10
...
Nested Table count All: 2
User Name :0123 -> User Id :1
User Name :123 -> User Id :10
...
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
|
|
| Re: MultiSet Operations on PL/SQL Tables [message #648949 is a reply to message #648851] |
Tue, 08 March 2016 10:29 |
mjm22
Messages: 54
Registered: January 2010
Location: Singapore
|
Member |
|
|
Thanks both for the feedback, links and examples. I guess I have the option to use the SQL types as recommended or try to alter one of the routines that populate the collection so that duplicates are not added in the first instance (which will probably mean also using SQL Types rather than PL/SQL). It is good to know that it is not possible though using the PL/SQL Types as I thought maybe I was missing something.
Thanks again,
Mike
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
