looping through an array for the where condition p

问题:

Is it possible in pl/sql to loop through a number of id's that need to go in the WHERE clause of the pl/sql statement. The sql statement itself is pretty simple, but I need to iterate over a number of id's:

SELECT x_name
FROM table_x
WHERE x_id = {array of 90 id's};

How can I insert the 90 id's here so that sql iterates over them? I tried using the cursor For Loop, but I'm stuck. The code below is erroneous, but it might give an indication what Im trying to achieve here

DECLARE
  TYPE x_id_array IS VARRAY(3) OF NUMBER;
  CURSOR cur_x_id (x_ondz_id NUMBER) IS
  SELECT x_name
  FROM table_x
  WHERE x_id = var_ondz_id;
  loop_total integer;
  x_id x_id_array;
  name VARCHAR;
BEGIN
  x_id_new := x_id_array(8779254, 8819930, 8819931); --3 for testing
  loop_total := x_id_new.count;
  FOR i in 1 .. loop_total LOOP
    dbms_output.put_line('x_id: ' || x_id_new(i) || '= Name: ' || x_name );
  END LOOP;
END;
/

The expected out put would be

x_id: 8779254= Name: Name_1 
x_id: 8819930= Name: Name_2
x_id: 8819931= Name: Name_3
...
... etc for all 90 id's in the array 

Any help is appreciated

回答1:

We can use TABLE function on a collection to get a list of numbers / character.

SELECT *
FROM TABLE ( sys.odcinumberlist(8779254,8819930,8819931) );

8779254
8819930
8819931

Here I'm using Oracle's internal VARRAY with a limit of 32767. You may use your own NESTED TABLE type.

create OR REPLACE TYPE yourtype AS TABLE OF NUMBER;

and then select it.

SELECT *
FROM TABLE ( yourtype(8779254,8819930,8819931) );

So, your query can simply be written as

SELECT x_name
FROM table_x
WHERE x_id IN ( SELECT * FROM 
       TABLE ( yourtype(8779254,8819930,8819931) ) );

12.2 and above, you won't even need to specify TABLE.

SELECT * FROM yourtype(8779254,8819930,8819931) works.