How to do FOR variable_name IN (values)? (With enumarated values instead of ranges)

Question

I wrote a procedure to delete lines based on an ID. I'd like to call my proc on a number of IDs, I think I should use a FOR loop but I'm not sure how to plug my values.

I want to turn this: (1 id)

DECLARE
    pi_client_id varchar2(255);
BEGIN
    pi_client_id := 'AMB01000000159481249';

    delete from TABLE1 t1
    where t1.client_id = pi_client_id ;

    delete from ...
END;

Into something like this : (a list of given ids)

DECLARE
    pi_client_id varchar2(255);
BEGIN
    FOR pi_client_id 
    IN ('AMB01000000159481215',
         'AMB01000000159481217',
         'AMB01000000159481219',
         'AMB01000000159481221',
         'AMB01000000159481223',
         'AMB01000000159481225',
         'AMB01000000159481227',
         'AMB01000000159481229',
         'AMB01000000159481231',
         'AMB01000000159481233',
         'AMB01000000159481235',
         'AMB01000000159481237',
         'AMB01000000159481239',
         'AMB01000000159481249',
         'AMB01000000159481251')
    LOOP
        delete from TABLE1 t1
        where t1.client_id = pi_client_id ;

        delete from ...
    END LOOP;
END;

I'm familiar with IN followed by lists like that used in statements like select X from t where Y IN ('A','B','C') but it doesn't seem to work in a for loop.

Also it's for a one-time thing so I can't add the procedure properly and then call it with my values. I want to be able to paste the ids I want and execute it in my editor.

score 1 · Answer 1 · answered May 06 '19 at 16:48

This can be done by defining a local PLSQL TYPE, then iterating over a collection of the type by index. An example is below.

DECLARE
    TYPE CLIENT_ID_LIST IS TABLE OF VARCHAR2(255);
    V_CLIENT_IDS CLIENT_ID_LIST := CLIENT_ID_LIST('AMB01000000159481215',
                                                  'AMB01000000159481217',
                                                  'AMB01000000159481219',
                                                  'AMB01000000159481221',
                                                  'AMB01000000159481223',
                                                  'AMB01000000159481225',
                                                  'AMB01000000159481227',
                                                  'AMB01000000159481229',
                                                  'AMB01000000159481231',
                                                  'AMB01000000159481233',
                                                  'AMB01000000159481235',
                                                  'AMB01000000159481237',
                                                  'AMB01000000159481239',
                                                  'AMB01000000159481249',
                                                  'AMB01000000159481251');
BEGIN
    FOR PI_CLIENT_ID IN 1..V_CLIENT_IDS.COUNT
        LOOP
            DELETE
            FROM TABLE1 T1
            WHERE T1.CLIENT_ID = V_CLIENT_IDS(PI_CLIENT_ID);
        END LOOP;
END;
/

How to do FOR variable_name IN (values)? (With enumarated values instead of ranges)

1 Answers1