Oracle 11g - 仅在工作日中插入表的FOR循环？ [英] Oracle 11g - FOR loop that inserts only weekdays into a table?

问题描述

我想将一些数据插入到与下一年的日期相关联的表中。实际上我只需要插入工作日。

I want to insert some data into a table associated with dates for the next year. I actually only need workdays inserted.

 BEGIN
  FOR i IN 1..365 LOOP
  INSERT INTO MY_TABLE (ID, MY_DATE)
  VALUES (i, (to_date(sysdate,'DD-MON-YY')-1)+i);
  END LOOP;
 END;

我可以通过回去删除那些周末的行来解决我的问题，但这似乎不太客气 - 任何人都可以想方法来修改我的循环，以便它跳过周末？

I can solve my problem by going back and deleting rows that are weekend days, but that seems rather inelegant - can anyone think of a way to modify my loop so that it skips weekends?

推荐答案

你可以随时检查在插入行之前的一周（每周的日期名称将取决于您的NLS设置，因此这不是最强大的解决方案）

You could always check the day of the week before inserting the row (the names of the days of the week will depend on your NLS settings so this isn't the most robust solution possible)

 BEGIN
  FOR i IN 1..365 LOOP
    IF( to_char(sysdate-1+i,'fmDAY') NOT IN ('SATURDAY', 'SUNDAY') )
    THEN
      INSERT INTO MY_TABLE (ID, MY_DATE)
        VALUES (i, (to_date(sysdate,'DD-MON-YY')-1)+i);
    END IF;
  END LOOP;
 END;

这篇关于Oracle 11g - 仅在工作日中插入表的FOR循环？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！