Oracle, accessing collections in SQL

In Oracle it is possible to access collections within SQL statements.

With the following collection:

CREATE TYPE region_tab AS TABLE OF VARCHAR2(2)
/

And the following table and data

desc regions
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------
 SHORT_CODE                                                     VARCHAR2(2)
 NAME                                                           VARCHAR2(70)


SELECT * from regions ;

SH NAME
-- -----------------------------------------
SE South East
WM West Midlands
SW South West
NE North East

We can write the code below which populates the collection with the short_codes of 'WM' and 'SW' and then use a SQL statement to read the collection and find the corresponding regions.name. This is possible through the use of the collection condition MEMBER OF

DECLARE

CURSOR c1
(pl_region_tab    region_tab)
IS
SELECT name 
  FROM regions
 WHERE short_code MEMBER OF pl_region_tab ;

l_region_tab          region_tab := region_tab ();

BEGIN

   -- add values to collection
   l_region_tab.EXTEND ;
   l_region_tab(1) := 'WM' ;
    
   l_region_tab.EXTEND ;
   l_region_tab(2) := 'SW' ;    
    
      FOR c1_rec IN c1 (l_region_tab)
      LOOP
         dbms_output.put_line ('Region name : ' || c1_rec.name) ;
      END LOOP ;      

END ;
/

Region name : West Midlands
Region name : South West

PL/SQL procedure successfully completed.

For completeness the ddl / dml for the table is:

CREATE TABLE regions
(short_code       VARCHAR2(2),
 name             VARCHAR(70)
)
/

INSERT INTO regions (short_code, name) VALUES ('SE', 'South East') ;
INSERT INTO regions (short_code, name) VALUES ('WM', 'West Midlands') ;
INSERT INTO regions (short_code, name) VALUES ('SW', 'South West') ;
INSERT INTO regions (short_code, name) VALUES ('NE', 'North East') ;
COMMIT ;