Arrays in Oracle SQL
Here is a simplified version of the pseudocode of what I would like to do in PL-SQL (Oracle):
DECLARE
mylist as ARRAY
BEGIN
mylist (1) := '1'
mylist (2) := '3'
...
SELECT *
FROM aTable
WHERE aKey IN mylist;
END;
SELECT should return matching records for mylist (1), mylist (2), etc. It should be like ORing for all values, but of course we don't know in advance how many values we get.
How can I achieve this? I know that PL / SQL has some collection data types, but I cannot get them to work as expected in SQL statements.
Thanks for any ideas.
source to share
It's easy to do with a function TABLE()
. One catch is that the array variable must use the type declared in SQL. This is because it SELECT
uses the SQL engine, so PL / SQL declarations are not available.
SQL> create or replace type numbers_nt as table of number
2 /
Type created.
SQL>
SQL> declare
2 l_array numbers_nt;
3 begin
4 l_array := numbers_nt (7521,7566,7654);
5 for r in ( select ename
6 from emp
7 where empno in ( select *
8 from table (l_array)
9 )
10 )
11 loop
12 dbms_output.put_line ( 'employee name = '||r.ename);
13 end loop;
14 end;
15 /
employee name = PADFIELD
employee name = ROBERTSON
employee name = BILLINGTON
PL/SQL procedure successfully completed.
SQL>
source to share
A few suggestions:
1.) There's a CAST SQL keyword you can do that could do the job ... that makes your collection treated as if it were a table.
2.) Conveyor functions. Basically, the function returns data that looks like a table.
This link lists the parameters and has a list of code lists that explain them.
source to share