title: PL SQL loop through list of ids
body: <p>I have a list of names.</p>
  
  <p>john, sam, peter, jack</p>
  
  <p>I want to query the same sql with each of above as the filter. Each query will give me a unique employee id, which I want to use to delete some other records.</p>
  
  <pre><code>select emp_id from employee where emp_name like '%john%';
  </code></pre>
  
  <p>Let's say for the first query, I get the id as 1001. So the delete queries would be like following.</p>
  
  <pre><code>delete from account_details where emp_id = 1001;
  delete from hr_details where emp_id = 1001;
  delete from pay_role_details where emp_id = 1001;
  </code></pre>
  
  <p>I have to repeat this for a list of employees. Pseudocode would be like following.</p>
  
  <pre><code>var emp_list = ['john', 'jack', 'kate', 'peter', 'sam',...]
  
  for each :employee_name in emp_list
      select emp_id as :var_emp_id from employee where emp_name like '%:employee_name%'; 
  
      delete from account_details where emp_id = :var_emp_id;
      delete from hr_details where emp_id = :var_emp_id;
      delete from pay_role_details where emp_id = :var_emp_id;
  end loop
  </code></pre>
  
  <p>I want a PL-SQL query to do this. Please help. Thanks. </p>
  
  <p>What I tried is something like the following.</p>
  
  <pre><code>set serveroutput on;
  begin
  loop x in ('john','jack', 'kate') loop as :name
    select emp_id as var_emp_id from employee where emp_name like '%:name%';
    // delete queries  
  end loop;
  end;
  </code></pre>
  
  <p>P.S. Although accoring to the question, like query may result in multiple records, in actual scenario, it is guaranteed to be only one record. Why I use like is that in actual scenario, it is a list of reference numbers instead of names. The reference number has some other pre texts and post texts and my comma seperated list has only the numbers. </p>