Iterate through a list of strings in SQL and run SELECT for each string

Question

So i have a list of like 300 strings.

I wanted to iterate through this list and run a SELECT statement with each string.

Example:

theList = ['account1', 'account2', 'account3']

for eachItem in theList:
   SELECT * from aTable where accountName = eachItem

An important note here is, I cannot INSERT or CREATE a table in this DB as this is a production database.

And the list of 300 strings, is a part of a column accountName with 50,000 entries.

This is what i was trying, couldnt figure out how to make it work:

DECLARE 
   theaccount varchar(100); 
BEGIN 
   FOR theaccount in ['account1','account2'] LOOP 

      (
      select * 
      from aTable
      where 
      accountName = theaccount

      ); 
  END LOOP; 
END;

there isnt a datatype called "list" in oracle, do you mean a concatenation of strings with 300 entries in it? — George Joseph, Mar 18 '20 at 15:51
I usually just concatenate all the strings and run a really long query like `SELECT * from aTable where accountName in ('account1','account2',...,'account300')` — kfinity, Mar 18 '20 at 15:53

score 2 · Accepted Answer · answered Mar 18 '20 at 16:00

If you got a concatenation of strings then the following approach would do the same instead of the row_by_row approach of looping through a cursor.

Much cleaner and performant.

create table t(x int, y varchar2(30));

insert into t 
select level,to_char(level)
  from dual
connect by level<=20;

--Passing a list of varchar as '12','9','5'
select *
  from t
where y in (select * 
              from TABLE(sys.odcivarchar2List('12','9','5'))
            )

score 0 · Answer 2 · answered Mar 18 '20 at 15:51

Your logic appears to be that you want all records from the aTable whose account names matches your list of 300 accounts. SQL strongly prefers using set based operations, so the easiest solution here would be to just load those 300 matching account names into a temporary table, and then run the following query:

SELECT *
FROM aTable t1
WHERE EXISTS (SELECT 1 FROM temp_accounts t2 WHERE t2.accountName t1.accountName);

See here for more information on creating temporary tables. Note that most of the time, using temp tables in place of better options (such as creating permanent tables) is bad practice. However, given that this temporary table would only be holding 300 single points of data, it should not place very much strain at all on your production system.

Iterate through a list of strings in SQL and run SELECT for each string

2 Answers2