ORACLE存储过程接受日期范围作为输入参数

Question

需要更改以下存储过程以接受日期数组。目前，此存储过程只接受一个日期作为输入参数。

procedure get_expected_del_sku_sum(
p_str               in   number,
p_to_date           in   date,
p_cursor           out   sku_qty_sum_ref)  is
v_cursor            sku_qty_sum_ref;
v_from_date         date;
v_request_key        varchar2(100);
begin
v_from_date := trunc(p_to_date - 30);
v_request_key:= p_str || '_' || p_to_date || '_' || current_timestamp;
if (getTempTableRecCnt(v_request_key) = 0) then
build_sku_qty_temp(p_str,v_from_date, p_to_date, v_request_key);
end if;
open v_cursor for
select dept_id,dept_name,prdc_code,prdc_desc,brand_id,brand_name,sku_style,str_no,sku_no,color_code,color_name,null,sum(sku_qty)
from shp_trk_temp_wrk
where trunc(est_receipt_date) = trunc(p_to_date)
and request_key = v_request_key
group by dept_id,dept_name,prdc_code,prdc_desc,brand_id,brand_name,sku_style,str_no,sku_no,color_code,color_name
order by dept_id,sku_no;
p_cursor := v_cursor;
end  get_expected_del_sku_sum;

Answer 1

这里有一个选项。我想把那些在我通过程序的日期被雇佣的员工作为集合。

类型优先：

SQL> create or replace type t_rec is object (datum date);
2  /
Type created.
SQL> create or replace type t_tab is table of t_rec;
2  /
Type created.

功能：

SQL> create or replace function f_test (par_dates in t_tab)
2    return sys_refcursor
3  is
4    rc sys_refcursor;
5  begin
6    open rc for select ename, hiredate
7                from emp
8                where hiredate in (select * from table(par_dates));
9    return rc;
10  end;
11  /
Function created.

测试：

SQL> select f_test(t_tab(t_rec(date '1980-12-17'), t_rec(date '1981-02-20'))) result from dual;
RESULT
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
ENAME      HIREDATE
---------- ----------
SMITH      12/17/1980
ALLEN      02/20/1981

SQL>