blocks|key|5115082|text|在插入行之前，您总是可以检查星期几(星期几的名称将取决于您的NLS设置，因此这不是最可靠的解决方案)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5115083|+BEGIN
++FOR+i+IN+1..365+LOOP
++++IF(+to_char(sysdate-1%2Bi,'fmDAY')+NOT+IN+('SATURDAY',+'SUNDAY')+)
++++THEN
++++++INSERT+INTO+MY_TABLE+(ID,+MY_DATE)
++++++++VALUES+(i,+(to_date(sysdate,'DD-MON-YY')-1)%2Bi);
++++END+IF;
++END+LOOP;
+END;|code-block|syntax|javascript|5115084|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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)

<pre><code> 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;
</code></pre>

blocks|key|5115105|text|如果你让星期一=0，星期天=6，你可以使用(如果mod(i,7)+<4+))，然后插入...应该行得通。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5115106|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If you let Monday = 0 and Sunday = 6 you could use (if mod(i,7) &lt; 4 )) then Insert... should work.

blocks|key|3330872|text|您可以使用以下日期格式之一来检查日期。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3330873|select+DAY(sysdate，‘to_char’)+from+dual；/*+to_char+*/|3330874|select+dual(sysdate，'D')+from+dual；/*+3+*/|3330875|从dual中选择to_char(sysdate，'DY')；/*+TUE+*/|3330876|添加如下所示的if语句以删除等于SAT或SUN的天数。|3330877|BEGIN
++FOR+i+IN+1..365+LOOP
++IF+to_char(sysdate-1%2Bi,'DY')+NOT+in+('SAT','SUN')+THEN

+++INSERT+INTO+MY_TABLE+(ID,+MY_DATE)+VALUES+(i,+(to_date(sysdate,'DD-MON-YY')-1)%2Bi);
++END+IF;
+END+LOOP;
END;|code-block|syntax|javascript|3330878|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|V|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

You can use one of the following date formats to check which day it is.

select to_char(sysdate,'DAY') from dual; /* TUESDAY */ 
select to_char(sysdate,'D') from dual; /* 3 */ 
select to_char(sysdate,'DY') from dual; /* TUE */

Add the if statement as shown below to remove days that equal SAT or SUN.

<pre><code>BEGIN
 FOR i IN 1..365 LOOP
 IF to_char(sysdate-1+i,'DY') NOT in ('SAT','SUN') THEN

 INSERT INTO MY_TABLE (ID, MY_DATE) VALUES (i, (to_date(sysdate,'DD-MON-YY')-1)+i);
 END IF;
 END LOOP;
END;
</code></pre>

blocks|key|3045021|text|我建议使用@Jeff+Moore提到的to_date(your_date,'d')。然而，我也建议去掉for..loop。作为额外的奖励，这将添加任何给定年份的所有天数，而不像您的版本，它将在闰年生成额外的一天：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3045022|INSERT+INTO+MY_TABLE+(ID,+MY_DATE)
SELECT+++lvl,+dt
++FROM+++(++++SELECT+++LEVEL+lvl,
+++++++++++++++++++++++TO_DATE('1/1/2011',+'mm/dd/yyyy')+%2B+LEVEL+-+1+dt
++++++++++++++++FROM+++DUAL
++++++++++CONNECT+BY+++TO_DATE('1/1/2011',+'mm/dd/yyyy')+%2B+LEVEL+-+1+<
++++++++++++++++++++++++++ADD_MONTHS(TO_DATE('1/1/2011',+'mm/dd/yyyy'),+12))
+WHERE+++TO_CHAR(dt,+'d')+NOT+IN+(1,+7)|code-block|syntax|javascript|3045023|如果希望"ID“列是连续的，可以在外部查询中使用rownum而不是lvl。|3045024|entityMap^0|J|M|1F|9|0|0|O|6|X|3|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|N|$]]

I would suggest using <code>to_date(your_date,'d')</code> as @Jeff Moore mentions. However, I'd also suggest getting rid of the <code>for..loop</code>. As a bonus, this will add all days of any given year, unlike your version, which will generate an extra day on leap years:

<pre><code>INSERT INTO MY_TABLE (ID, MY_DATE)
SELECT lvl, dt
 FROM ( SELECT LEVEL lvl,
 TO_DATE('1/1/2011', 'mm/dd/yyyy') + LEVEL - 1 dt
 FROM DUAL
 CONNECT BY TO_DATE('1/1/2011', 'mm/dd/yyyy') + LEVEL - 1 &lt;
 ADD_MONTHS(TO_DATE('1/1/2011', 'mm/dd/yyyy'), 12))
 WHERE TO_CHAR(dt, 'd') NOT IN (1, 7)
</code></pre>

If you want your "ID" column to be contiguous, you can use <code>rownum</code> instead of <code>lvl</code> in the outer query.

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

<pre><code> 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;
</code></pre>

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?

Oracle 11g - FOR loop that inserts only weekdays into a table?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我想将一些数据插入到与下一年的日期相关联的表中。我实际上只需要插入工作日。 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...

问Oracle 11g -只将工作日插入到表中的FOR循环？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle 11g -只将工作日插入到表中的FOR循环？EN