使用存储过程参数修改游标select语句

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.

我有一个存储过程,基于其他几个创建一个表。我有一个名为“filterDown”的存储过程参数,它将是一个ID。如果filterDown = 0,那么我想要包含所有ID。如果filterDown是其他任何东西,我只想选择该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.

换句话说,存储过程应该能够根据国家/地区ID使用此参数进行过滤,并使用默认值指示应包括所有国家/地区。当程序完成时,不符合标准的国家不应在派生表中。

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;

1 个解决方案

#1

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

最简单的方法是在WHERE子句中添加类似的东西

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