I have a package body, "my_package_body" and inside of it there is a procedure, "my_procedure". It looks something like this:

CREATE OR REPLACE PACKAGE BODY my_package_body 
IS

PROCEDURE my_procedure (period IN VARCHAR2, country IN VARCHAR2)
IS

BEGIN
    ******************
    ******************
END;

END my_package_body;

My Solution:

EXEC my_package_body.my_procedure('201701', 'GERMANY')
EXEC my_package_body.my_procedure('201702', 'GERMANY')
EXEC my_package_body.my_procedure('201601', 'FRANCE')
EXEC my_package_body.my_procedure('201602', 'FRANCE')
...
(more than thousands to execute)

My Question:

There are hundreds of period for each country, I do not want to type the parameters 1 by 1. Therefore, I would like to execute the procedure by passing a list of country and period from a table (see below). My preferred solution should be able to exclude, for example, exclude "SPAIN" (not passing "SPAIN" into the procedure)

Table: period_and_country

CodePudding user response：

Looks like a simple loop:

begin
  for cur_r in (select country, period
                from period_and_country
                where country not in ('SPAIN')
               )
  loop
    my_package_body.my_procedure (cur_r.country, cur_r.period);
  end loop;
end;
/

Cursor's WHERE clause excludes Spain; add other countries and/or periods, if you want.