Searching a collection for a value [message #527543] |
Tue, 18 October 2011 13:40  |
mjm22
Messages: 54 Registered: January 2010 Location: Singapore
|
Member |
|
|
Hi,
I need to check if a small collection contains a specific value. I know I can use the .exist method to let me know if the nth element exists. But is there a similar syntax for checking if an actual value exists?
So the below collection has 6 values (300,301,310,320,400,410) after the cursor values are fetched. I want to use something similar to the below exists syntax in order to search the collection for a value (rather than position) within an IF statement.
The below code shows the kind of thing I want to do. Currently, unless my test value (310) is in the range 1 to 6 the test will always return FALSE. But is there a similar syntax for testing against the value?
The example I have given is a simplification of what I need. In reality there will be more than one test value... returned by another cursor.
DECLARE
CURSOR c_type_id_usg
IS
SELECT param_value
FROM parameters
WHERE proc_id = 112301
AND parameter_name LIKE 'ID_LIST%';
type type_id_tt is table of NUMBER;
l_type_ids type_id_tt;
BEGIN
-- get the list of sms type id usg values
OPEN c_type_id_usg;
FETCH c_type_id_usg bulk collect into l_type_ids;
CLOSE c_type_id_usg;
-- the above returns value 300,301,310,320,400,410
IF l_type_ids.exists(310)
then
dbms_output.put_line('I have found an entry ');
else
dbms_output.put_line('I have NOT found an entry ' );
end if;
END;
|
|
|
Re: Searching a collection for a value [message #527546 is a reply to message #527543] |
Tue, 18 October 2011 13:58   |
 |
Michel Cadot
Messages: 68767 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I have not your table so I used ALL_USERS:
SQL> SELECT user_id, username
2 FROM all_users
3 WHERE username like 'M%';
USER_ID USERNAME
---------- ------------------------------
50 MICHEL
46 MDSYS
49 MGMT_VIEW
235 MESDVD$PROPRIO
236 MESDVD$LECTEUR
237 MESDVD$TOUT
6 rows selected.
SQL> -- Not correct code
SQL> DECLARE
2 CURSOR c_type_id_usg
3 IS
4 SELECT user_id
5 FROM all_users
6 WHERE username like 'M%';
7
8 type type_id_tt is table of NUMBER;
9 l_type_ids type_id_tt;
10
11 BEGIN
12
13 -- get the list of sms type id usg values
14 OPEN c_type_id_usg;
15 FETCH c_type_id_usg bulk collect into l_type_ids;
16 CLOSE c_type_id_usg;
17
18 IF l_type_ids.exists(50)
19 then
20 dbms_output.put_line('I have found an entry ');
21 else
22 dbms_output.put_line('I have NOT found an entry ' );
23 end if;
24
25 END;
26 /
I have NOT found an entry
PL/SQL procedure successfully completed.
SQL> -- Correct code
SQL> DECLARE
2 CURSOR c_type_id_usg
3 IS
4 SELECT user_id
5 FROM all_users
6 WHERE username like 'M%';
7
8 type type_id_tt is table of NUMBER;
9 l_type_ids type_id_tt;
10
11 BEGIN
12
13 -- get the list of sms type id usg values
14 OPEN c_type_id_usg;
15 FETCH c_type_id_usg bulk collect into l_type_ids;
16 CLOSE c_type_id_usg;
17
18 IF 50 member of l_type_ids
19 then
20 dbms_output.put_line('I have found an entry ');
21 else
22 dbms_output.put_line('I have NOT found an entry ' );
23 end if;
24
25 END;
26 /
I have found an entry
PL/SQL procedure successfully completed.
Regards
Michel
[Updated on: Tue, 18 October 2011 13:59] Report message to a moderator
|
|
|
|