Oracle 在整个应用程序中重复查询-最佳方法

大家好，

我需要根据预定义的时间表向用户发送通知。我的计划表包含一行用户和一列每个工作日。例如，如果我必须在星期一和星期四为user1发送通知，则行将如下所示：

表：我的计划

用户|星期一|星期二|星期三|星期四|星期五|星期六
--------------------------------------
user1 | Y | N | N | Y | N | N | N

我需要一个查询，它可以给我两个日期之间的通知日期是什么：开始和结束（ GUI输入参数）。
这是我的查询：

WITH weekdays AS (SELECT 'Y' monday, 'N' tuesday, 'N' wednesday, 'Y' thursday,
                         'N' friday, 'N' saturday, 'N' sunday
                    FROM dual my_schedules),
     weekdays_nbr AS (SELECT 1 wkday FROM weekdays WHERE sunday = 'Y'
                      UNION ALL
                      SELECT 2 wkday FROM weekdays WHERE monday = 'Y'
                      UNION ALL
                      SELECT 3 wkday FROM weekdays WHERE tuesday = 'Y'
                      UNION ALL
                      SELECT 4 wkday FROM weekdays WHERE wednesday = 'Y'
                      UNION ALL
                      SELECT 5 wkday FROM weekdays WHERE thursday = 'Y'
                      UNION ALL
                      SELECT 6 wkday FROM weekdays WHERE friday = 'Y'
                      UNION ALL
                      SELECT 7 wkday FROM weekdays WHERE saturday = 'Y'),
     first_date AS
                (SELECT MIN(NEXT_DAY(to_date(:START_DATE,'DD-MM-YYYY')-1,wkday)) my_first_date
                   FROM weekdays_nbr),
     notification_dates (notification_date, wkday) AS
                        (SELECT NEXT_DAY(fd.my_first_date-1,wkday) notification_date,
                                wk.wkday
                           FROM first_date fd
                          CROSS JOIN weekdays_nbr wk
                          WHERE NEXT_DAY(fd.my_first_date-1,wkday) <=
                                COALESCE(to_date(:END_DATE,'DD-MM-YYYY'),fd.my_first_date)
                         UNION ALL
                         SELECT notification_date+7 notification_date,
                                wkday
                           FROM notification_dates
                          WHERE notification_date+7 <=
                                to_date(:END_DATE,'DD-MM-YYYY')
                           AND :END_DATE IS NOT NULL)
   SELECT row_number() OVER (ORDER BY notification_date) || ' - Notification' description,
          notification_date
     FROM notification_dates

此查询将始终只针对一个用户运行，即，“工作日”将始终返回一行（该用户的调度）。

我需要在我的应用程序上重复这个查询（例如：通过GUI创建计划时，用户需要查看预警日期的预览；批准计划后，此查询将用于填充包含预警日期的表）。请注意，我需要向查询传递一些参数：用户ID、start_date和end_date。
关于实现这一目标的最佳方法（参数化视图、管道功能或其他） ，您有什么建议？还请让我知道您对我为计算所需日期而提出的查询/解决方案的想法。

谢谢你抽出时间。

首先，要小心使用一周中的天数。这些映射的对象取决于nls设置：

alter session set nls_territory = 'AMERICA';
select next_day(sysdate, 1) from dual;

NEXT_DAY(SYSDATE,1)
-------------------
17-JAN-16

alter session set nls_territory = 'UNITED KINGDOM';
select next_day(sysdate, 1) from dual;

NEXT_DAY(SYSDATE,1)
-------------------
18-JAN-16

客户端可以覆盖这些内容，因此您可以很容易地以意外的结果结束！

其次，您的查询比它需要的更复杂。

create table my_schedules (
  user_id varchar2(10),
 monday  varchar2(1), 
 tuesday varchar2(1),  
 wednesday varchar2(1),  
 thursday varchar2(1),  
 friday varchar2(1),  
 saturday varchar2(1),
 sunday varchar2(1)
);

insert into my_schedules values ('user1', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N');

with days as (
  select * from my_schedules
  unpivot (
    notify for dy in (
      monday as 'monday', 
      tuesday as 'tuesday',
      wednesday as 'wednesday',  
      thursday as 'thursday',  
      friday as 'friday',  
      saturday as 'saturday',
      sunday as 'sunday'
   )
  )
  where notify = 'Y'
), notification_dates ( notification_date ) as (
  select next_day(to_date(:start_date, 'yyyy-mm-dd')-1, dy) notification_date from days
  union all
  select notification_date + 7
  from   notification_dates
  where  notification_date + 7 <= to_date(:end_date, 'yyyy-mm-dd')
)
  select * from notification_dates;

或者，您可以通过创建my_计划来保存取消透视操作，如下所示：

create table schedules (
  user_id      varchar2(10),
  day_of_week  varchar2(10),
  notification varchar2(1)
);

insert into my_schedules values ('user1', 'monday', 'Y');
insert into my_schedules values ('user1', 'tuesday', 'N');
...

最后，要使SQL可重用，答案很简单：使用PL/SQL!

您可以创建一个函数来打开游标并返回它。然后你的应用程序就可以读取结果了。然后，可以编写另一个接受游标的过程，并将输出写入表。

create table notifications (
  notification_date date not null,
  description       varchar2(30) not null
);

create or replace package manage_notifications as
  
  function get_dates (
    user_id varchar2,
    start_date date,
    end_date date
  ) return sys_refcursor;
  
  procedure insert_dates (
    user_id varchar2,
    start_date date,
    end_date date
  );
end;
/

create or replace package body manage_notifications as
    
  function get_dates (
    user_id varchar2,
    start_date date,
    end_date date
  ) return sys_refcursor as
    cur sys_refcursor;
  begin
    open cur for 
with days as (
  select * from my_schedules
  unpivot (
    notify for dy in (
      monday as 'monday', 
      tuesday as 'tuesday',
      wednesday as 'wednesday',  
      thursday as 'thursday',  
      friday as 'friday',  
      saturday as 'saturday',
      sunday as 'sunday'
   )
  ) s
  where notify = 'Y'
  and   s.user_id = get_dates.user_id
), notification_dates ( notification_date ) as (
  select next_day(start_date-1, dy) notification_date from days
  union all
  select notification_date + 7
  from   notification_dates
  where  notification_date + 7 <= end_date
)
  select notification_date,
         row_number() over (order by notification_date) || ' - notification' description
  from   notification_dates;
  
    return cur;
  end;
  
  procedure insert_dates (
    user_id varchar2,
    start_date date,
    end_date date
  ) as
    notify_dates_cur sys_refcursor;
    
    type notifications_tab is 
      table of notifications%rowtype index by pls_integer;
    notfiy_dates_t notifications_tab;
  begin
    notify_dates_cur := manage_notifications.get_dates(user_id, start_date, end_date);
    
    fetch notify_dates_cur
    bulk collect into notfiy_dates_t;
    
    forall dts in notfiy_dates_t.first .. notfiy_dates_t.last 
      insert into notifications
      values (notfiy_dates_t(dts).notification_date,
              notfiy_dates_t(dts).description);

    close notify_dates_cur;
  end;
end;
/

在这里，它是在行动：

SQL> var c refcursor;
SQL> exec :c := manage_notifications.get_dates('user1', date'2016-01-01', date'2016-02-01');

PL/SQL procedure successfully completed.

SQL> print :c;

NOTIFICAT DESCRIPTION
--------- -------------------------------------------------------
04-JAN-16 1 - notification
07-JAN-16 2 - notification
11-JAN-16 3 - notification
14-JAN-16 4 - notification
18-JAN-16 5 - notification
21-JAN-16 6 - notification
25-JAN-16 7 - notification
28-JAN-16 8 - notification
01-FEB-16 9 - notification

9 rows selected.

SQL> select * from notifications;

no rows selected

SQL> exec manage_notifications.insert_dates('user1', date'2016-01-01', date'2016-02-01');

PL/SQL procedure successfully completed.

SQL> select * from notifications;

NOTIFICAT DESCRIPTION
--------- ------------------------------
04-JAN-16 1 - notification
07-JAN-16 2 - notification
11-JAN-16 3 - notification
14-JAN-16 4 - notification
18-JAN-16 5 - notification
21-JAN-16 6 - notification
25-JAN-16 7 - notification
28-JAN-16 8 - notification
01-FEB-16 9 - notification

9 rows selected.