sql>create or replace type NumberTable as table of number;
  2  / 
 
Type created.
 
sql>CREATE OR REPLACE function f_number_table(
  2    p_list       in  varchar2,
  3    p_delimiter  in  varchar2 default ',')
  4    return numbertable
  5  is
  6    v_string  long := p_list || p_delimiter;
  7    v_pos     pls_integer;
  8    v_data    numbertable := numbertable();
  9  begin
 10    loop
 11      v_pos := instr(v_string, p_delimiter);
 12      exit when (nvl(v_pos, 0) = 0);
 13      v_data.extend;
 14      v_data(v_data.count) := trim(substr(v_string, 1, v_pos - 1));
 15      v_string := substr(v_string, v_pos + 1);
 16    end loop;
 17    return (v_data);
 18  end f_number_table;
 19  / 
 
Function created.
 
sql>create or replace procedure p_update_emp
  2  (p_emp_list in varchar2, p_sal in emp.sal%type)
  3  is
  4  begin
  5  update emp set sal = p_sal where empno in (select * from table(cast(f_number_table(p_emp_list) 
as NumberTable)));
  6  end;
  7  / 
 
Procedure created.
 
sql>select empno, sal from emp;
 
    EMPNO       SAL
--------- ---------
     7369       800
     7499      1600
     7521      1250
     7566      2975
     7654      1250
     7698      2850
     7782      2450
     7788      3000
     7839      5000
     7844      1500
     7876      1100
     7900       950
     7902      3000
     7934      1300
 
14 rows selected.
 
sql>exec p_update_emp('7369,7499,7521', 10000)
 
PL/SQL procedure successfully completed.
 
sql>select empno, sal from emp;
 
    EMPNO       SAL
--------- ---------
     7369     10000
     7499     10000
     7521     10000
     7566      2975
     7654      1250
     7698      2850
     7782      2450
     7788      3000
     7839      5000
     7844      1500
     7876      1100
     7900       950
     7902      3000
     7934      1300
 
14 rows selected.