我想創建一個函式來檢索每條記錄的有效年份。我有列valid_from和valid_to表。
例如valid_from = 2018-04-30和valid_to = 2020-06-30; 函式應產生三個值：2018, 2019, 2020。
我創建了一個型別和一個函式，但它根本不起作用......
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;
uj5u.com熱心網友回復：
就是這樣：
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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/318469.html
上一篇：史密斯-沃特曼用python實作
下一篇：在PHP中通過參考傳遞變數的問題