pipelined function with if statement-CodePudding

I'd like to create a function to retrieve valid years for each record. I've got columns valid_from and valid_to in table.

For example valid_from = 2018-04-30 and valid_to = 2020-06-30; function should result three values: 2018, 2019, 2020.

I created a type and a function but it isn't working at all ...

create or replace type t_years is table of varchar2(4);

create or replace function func_year(d_from date, d_to date) return t_years pipelined
is
v_date date := '21/12/31 00:00:00';
max_date date := '99/12/30 00:00:00';
begin
 for x in 1..2 loop
 if    d_from <= add_months(v_date,-1*12) AND (add_months(d_to,-1*12) >= v_date OR add_months(d_to,-1*12) = max_date) then return '2020';
    elsif d_from <= add_months(v_date,-2*12) AND (add_months(d_to,-2*12) >= v_date OR add_months(d_to,-2*12) = max_date) then return '2019';
    end if;
    pipe row(x);
 end loop;
 return;
end;

CodePudding user response：

Here's how:

SQL> CREATE OR REPLACE TYPE t_years IS TABLE OF VARCHAR2 (4);
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION func_year (d_from IN DATE, d_to IN DATE)
  2     RETURN t_years
  3     PIPELINED
  4  IS
  5  BEGIN
  6     FOR i IN EXTRACT (YEAR FROM d_from) .. EXTRACT (YEAR FROM d_to)
  7     LOOP
  8        PIPE ROW (i);
  9     END LOOP;
 10
 11     RETURN;
 12  END;
 13  /

Function created.

SQL> SELECT func_year (DATE '2019-01-01', DATE '2021-01-01') FROM DUAL;

FUNC_YEAR(DATE'2019-01-01',DATE'2021-01-01')
----------------------------------------------------------------------------------------------------
T_YEARS('2019', '2020', '2021')

SQL>

SQL> SELECT * FROM TABLE (func_year (DATE '2019-01-01', DATE '2021-01-01'));

COLU
----
2019
2020
2021

SQL>