Check if a value is in a PL/SQL collection using MEMBER OF

You may find yourself working with a collection in PL/SQL and think to yourself, "Wow, it sure would be great if I could do a quick search of this collection to see if it contains a specific value." Well, never fear, cause I'm going to show you how to do just that using MEMBER OF.

ID	NAME
1	Black
2	Brown
3	Blue
4	Green
5	Orange
6	Red
7	Purple
8	Yellow

Let's say that you have the names of the eight basic colors (according to Crayola) in a lookup table called LKBASICCOLORS that has only two columns, ID and NAME.

In our situation, we want to verify that a given color name is one of our eight basic colors. There are numerous ways to do this in PL/SQL, including hard coding these color names into our code. While in this situation that wouldn't be such a bad idea, but in the real world, values tend to change often and nobody wants to be the poor schmuck that gets to go back through all of the PL/SQL looking for hard coded values. By using member of, you can actually look up these values on the fly, load them into a collection, and verify that our given color is a basic color.

DECLARE

 v_color_white varchar2(10) := 'White;
 v_color_purple varchar2(10) := 'Purple';

 TYPE lkbasiccolor_names IS TABLE OF VARCHAR2(20);
 v_color_names lkbasiccolor_names;

BEGIN

 SELECT NAME BULK COLLECT INTO v_color_names FROM LKBASICCOLORS;

 IF v_color_white MEMBER OF v_color_names THEN
 dbms_output.put_line('White is a basic color');
 ELSE
 dbms_output.put_line('White is not a basic color');
 END;

 IF v_color_purple MEMBER OF v_color_names THEN
 dbms_output.put_line('Purple is a basic color');
 ELSE
 dbms_output.put_line('Purple is not a basic color');
 END;

END;

Now let's take a look at what's going on in the sample above. In the DECLARE, we declare two color name variables. Then we define our collection type as a table of varchar2 values. We move on and declare our actual collection as v_color_names as the collection type of lkbasiccolor_names.

In the first line of the BEGIN section, we load all of our color names into our v_color_names collection. The first test we try is to see if the color white is a member of our v_color_names collection. Since it isn't, it will fall into the else and print out that White is not a basic color. In the next block, since Purple is in our basic color list, it should print Purple is a basic color.