想要从过程返回一组日期

Question

我写了一个像下面这样的过程

CREATE OR REPLACE PROCEDURE LeaveDates (STDATE    IN OUT DATE,
ENDDATE   IN OUT DATE)
AS
start_date   DATE := STDATE;       --to_date('01-JAN-2016','DD-MON-YYYY');
end_date     DATE := ENDDATE;      --to_date('05-JAN-2016','DD-MON-YYYY');
TYPE dates IS VARRAY (30) OF VARCHAR2 (50);
alldates     dates;
BEGIN
LOOP
IF start_date <= end_date
THEN
DBMS_OUTPUT.put_line (start_date);
start_date := start_date + 1;
ELSE
EXIT;
END IF;
END LOOP;
END LeaveDates;

它打印我需要的东西。如何返回从上述过程中打印的日期。

我可以做这样的事情吗？但它只打印一次，第二次打印语句不打印任何东西，

create or replace PROCEDURE LeaveDates2 
(
STDATE IN OUT DATE 
, ENDDATE IN OUT DATE 
, alldate OUT SYS_REFCURSOR 
) AS 
start_date date := STDATE ;--to_date('01-JAN-2016','DD-MON-YYYY');
end_date   date := ENDDATE;--to_date('05-JAN-2016','DD-MON-YYYY');
i number:=1;
TYPE dates IS VARRAY(30) OF varchar2(50);
alldates dates; 
Begin
alldates := dates();
alldates.extend(30);
loop
if (start_date <= end_date) then
dbms_output.put_line(start_date);
dbms_output.put_line(alldates(i));
alldates(i):=start_date;
i:=i+1;
start_date := start_date +1;
dbms_output.put_line(alldates(i));
else
exit;
end if;
end loop;
END LeaveDates2;

Answer 1

您需要为过程提供一个OUT参数，并且需要在过程外部定义类型并在过程内初始化它：

CREATE TYPE DateArray IS VARRAY(30) OF DATE;
CREATE PROCEDURE LeaveDates 
(
I_STDATE  IN  DATE,
I_ENDDATE IN  DATE,
O_DATES   OUT DateArray
)
AS 
n INTEGER := LEAST( I_ENDDATE - I_STDATE, 29 );
BEGIN
O_DATES := DateArray();
O_DATES.EXTEND( n + 1 );
FOR i IN 0 .. n LOOP
O_DATES(i+1) := I_STDATE + i;
END LOOP;
END LeaveDates;

Answer 2

只需添加两个作业

STDATE  := start_date;
ENDDATE := end_date;

到最后：

create or replace PROCEDURE LeaveDates 
(
STDATE IN OUT DATE 
, ENDDATE IN OUT DATE 
) AS 
start_date date := STDATE;
end_date   date := ENDDATE;
TYPE dates IS VARRAY(30) OF varchar2(50);
alldates dates;
Begin
loop
if start_date <= end_date then
dbms_output.put_line(start_date);
start_date := start_date +1;
else
exit; 
end if;
end loop;
STDATE  := start_date;
ENDDATE := end_date;
END LeaveDates;

并按以下方式调用：

declare
v_stdate  date := to_date('01-JAN-2016','DD-MON-YYYY');
v_enddate date := to_date('05-JAN-2016','DD-MON-YYYY');
begin
leavedates(stdate => v_stdate, enddate => v_enddate);
end;