CODEWITHSUNDEEP
sqloracle-databaseloops
Mike
Mike

Reputation: 4405

Oracle SQL For Loop Options

I have a query where I have been using a user input (i.e. &&year) to enter years to be run in the query:

SELECT 
  t.type_code , 
  t.sub_type_code,
  COUNT(t.unique_number_id), 
  SUM(t.area_in_hectares)
FROM   
  mytable t
WHERE
       to_char(t.start_date, 'YYYY') <= '&&year' AND
       (
          to_char(t.event_date, 'YYYY') > '&&year' OR
          t.event_date IS NULL OR
          t.type_code = 'P'
       )
GROUP BY 
  t.type_code , 
  t.sub_type_code

I wanted to make this more efficient and given some of my other programming experience I had thought that, if I can loop over a range of years, I can make this query much more efficient to run. I am wondering if there is a way to iteratively inject the year from a list into the user variables instead of manually typing them in. I run the query annually and user run back almost 100 years and clearly it is not efficient to enter years manually.

I've been reading about for loops and while loops but I ma not clear if I can use those in Oracle SQL.

Thanks for any thoughts or suggestions.

Upvotes: 0

Views: 72

Answers (2)

Yusuf
Yusuf

Reputation: 212

you can do in that way:

BEGIN
FOR years IN (
             SELECT * FROM years
              ORDER BY year asc
             )
LOOP
      SELECT 
            t.type_code , 
            t.sub_type_code,
            COUNT(t.unique_number_id), 
            SUM(t.area_in_hectares)
        FROM mytable t
       WHERE to_char(t.start_date, 'YYYY') <= year.year AND
            (
             to_char(t.event_date, 'YYYY') > year.year OR
             t.event_date IS NULL OR
             t.type_code = 'P'
            )
    GROUP BY  t.type_code , t.sub_type_code
END LOOP;
END;

Upvotes: 1

MT0
MT0

Reputation: 167867

Enter all the years in a single query using a sub-query factoring (WITH) clause and then JOIN it to your table. That way you do not have to have the overhead of multiple requests between the client and the database and the database does not need to parse multiple queries:

WITH years (year) AS (
  SELECT 1942 FROM DUAL UNION ALL
  SELECT 1970 FROM DUAL UNION ALL
  SELECT 2000 FROM DUAL UNION ALL
  SELECT 2022 FROM DUAL
)
SELECT y.year,
       t.type_code, 
       t.sub_type_code,
       COUNT(t.unique_number_id), 
       SUM(t.area_in_hectares)
FROM   mytable t
       INNER JOIN years y
       ON (
           t.start_date < TO_DATE( (year+1) || '-01-01', 'YYYY-MM-DD' )
       AND (
              t.event_date >= TO_DATE( (year+1) || '-01-01', 'YYYY-MM-DD' )
           OR t.event_date IS NULL
           OR t.type_code = 'P'
           )
       )
GROUP BY
       y.year,
       t.type_code,
       t.sub_type_code

You can also convert the year to a date rather than trying to convert a table column to a string. That would allow you to use an index on the date columns, if one exists.

Upvotes: 2

Related Questions