在PL/SQL的where子句中使用数组查询表?

Question

我将employee_id的列表作为数组。如何在PL/SQL的where子句中查询带有数组的表?

empIdArray = [1,2,3]

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID IN (empIdArray);

Answer 1

就像这样(见第8行):

SQL> declare
2    empidarray sys.odcinumberlist := sys.odcinumberlist(7369,7499,7521);
3    l_names    sys.odcivarchar2list;
4  begin
5    select ename
6    bulk collect into l_names
7    from emp
8    where empno in (select * from table(empidarray));      --> this
9
10    for i in l_names.first .. l_names.last loop
11      dbms_output.put_line(l_names(i));
12    end loop;
13  end;
14  /
SMITH
ALLEN
WARD
PL/SQL procedure successfully completed.
SQL>