CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Ted
Ted

Reputation: 497

use stored procedure parameter modify cursor select statement

I have a stored procedure that creates a table based on several others. I have a parameter to the stored procedure called "filterDown" that will be an ID. If filterDown is =0 then I want to include all IDs. If filterDown is anything else, I only want to select that ID.

Here is my cursor declaration in my code:

    create or replace PROCEDURE country ( 
      fromDate IN DATE,
      toDate IN DATE,
      filterDown IN INT,
      chunkSize IN INT
      ) AS 

    --cursor
    cursor cc is
      select c.id, cd.population_total_count, cd.evaluation_date, cf.gdp_total_dollars
      from countries c
      join country_demographics cd on c.id = cd.country_id
      join country_financials cf on cd.country_id = cf.country_id and cf.evaluation_date = cd.evaluation_date
      where cd.evaluation_date > fromDate and cd.evaluation_date < toDate
      order by c.id,cd.evaluation_date;

--lots more code down here.....

In other words, the stored procedure should be able to filter down with this parameter based on the country id, with a default value indicating that all countries should be included. Countries not meeting the criteria should not be in the derived table when the procedure is complete.

How would I specify in my cursor to do something like that?

EDIT: Here's how I'm calling the procedure:

create or replace procedure testing as
  UPPER1 DATE;
  LOWER1 DATE;
  FILTERON1 NUMBER;
  CHUNKSIZE1 NUMBER;
BEGIN
  UPPER1 := TO_DATE('2000/01/01','yyyy/mm/dd');
  LOWER1 := TO_DATE('2005/01/01','yyyy/mm/dd');
  FILTERON1 := 143;
  CHUNKSIZE1 := 250;

  country(UPPER1,LOWER1,FILTERON1,CHUNKSIZE1);

END;

Upvotes: 0

Views: 161

Answers (1)

Justin Cave
Justin Cave

Reputation: 231851

The simplest approach would be to add something like this to your WHERE clause

...
where cd.evaluation_date > fromDate 
  and cd.evaluation_date < toDate
  and (filterDown = 0 or c.id = filterDown)
...

Upvotes: 2

Related Questions