Now that we have learnt the concepts of first collection function, which is COUNT in the previous tutorial. It’s time to move on to the second function of the collection method series. Thus here I present you the PL/SQL Collection Method EXISTS. In this blog we will explore the collection method EXISTS ( ) in detail.
What is Collection Method EXISTS ( )?
Collection Method EXISTS ( ) checks the existence of an element at a specific index in a collection. If it finds the specified element then it returns TRUE otherwise it returns FALSE.
You can use EXISTS ( ) function to check the existence of a specific row within the collection.
Syntax of Collection Method EXISTS ( )
The syntax of EXISTS ( ) function is –
EXISTS (index number);
EXISTS function takes the subscript/index number of a cell of the collection as an Input and searches it in the collection. If it finds any element corresponding to index number then it returns TRUE otherwise it returns FALSE.
Does the collection method EXISTS returns NULL?
No EXISTS function does not return null. It either returns True or False.
What if I remove an already existed row using TRIM or DELETE function?
If you remove a row using Trim or Delete function then collection method EXISTS ( ) will return FALSE for the index of that row.
Does the collection method EXISTS raise any exception?
No, collection method EXISTS does not raise any exception. In fact, this is the only function which does not raise any exception, even if it used with an uninitialized collection.
If it does not raise an exception then what will happen if I applied this function to an uninitialized collection?
Collection method EXISTS ( ) returns false, if it is applied either to an uninitialized collection or to an initialize collection with no elements.
Example of Collection Method EXISTS ( ).
This simple example will show you how you can use this function in your application.
SET SERVEROUTPUT ON; DECLARE --Declare a local Nested Table TYPE my_nested_table IS TABLE OF VARCHAR2 (20); --Declare collection variable and initialize the collection. col_var_1 my_nested_table := my_nested_table('Super Man','Iron Man','Bat Man'); BEGIN IF col_var_1.EXISTS (1) THEN DBMS_OUTPUT.PUT_LINE ('Hey we found '||col_var_1 (1)); ELSE DBMS_OUTPUT.PUT_LINE ('Sorry, no data at this INDEX'); END IF; END; /
In the above program we are checking if there is any element at index 1 into the collection ‘my_nested_table’ or not. If there is an element at the specified index then the IF part of the IF-ELSE statement will execute otherwise the ELSE part will come into the action.
What will you do…?
Suppose you want to insert a data into a specific index but you are not sure whether that index is already holding some data or not. In case if it is holding any then you don’t want to overwrite it. What will you do in this case?
To know how you can use the collection method EXISTS to find the solution of this problem go ahead and watch the PL/SQL tutorial on my YouTube channel.
That is a very simple demonstration. I am sure you can come-up with some crazier examples. So what are you waiting for, go ahead and write your code and see in what other possible ways you can use this method.
If you want me to review your code then you can share your code with me on my Facebook page or on my Twitter as well.
Thanks and have a great day.