如何用存储过程向一个表中插入年历！

Question

--现有一表如下：
CREATE TABLE DMS_WORK_CALE
(
  CALE_DATE   DATE                              NOT NULL,
  CALE_SORT   VARCHAR2(2 BYTE),
  CALE_WTIME  NUMBER(3,1)
)
--欲生成数据如下：
CALE_DATE  CALE_SORT   CALE_WTIME  
2008-04-24  01          8
2008-04-25  01          8
2008-04-26  02          0
2008-04-27  02          0
2008-04-28  01          8
2008-04-29  01          8
2008-04-30  01          8
要求：过程传入参数（ 年：如 2008），将该年下每一天按一条记录插入表。
数据规则是：
第一个字段：每天的日期
第二个字段：工作日（01）非工作日（02）
第三个字段：工作日（8）非工作日（0）

Answer 1

SELECT lv,decode(to_char(lv,'d'),1,'02',7,'02','01') ,decode(to_char(lv,'d'),1,0,7,0,8) 
FROM (
select to_date('2008'||'-01-01','yyyy-mm-dd')+level-1 as lv from dual
connect by level<=366)
WHERE to_char(lv,'yyyy')='2008'
;

Answer 2

呵呵，给个能计算闰年的存储过程吧。
CREATE OR REPLACE PROCEDURE crtcal(year in varchar2)
IS
i number;
td date;
tm number;
daycnt number;
tp varchar2(2);
BEGIN
if mod(to_number(year),400) = 0 or (mod(to_number(year),4) = 0 and mod(to_number(year),100) <> 0) then
  daycnt := 366;
else 
  daycnt :=365;
end if;
td := to_date(year||'-01-01','YYYY-MM-DD');
i := 1;
tm := 0;
loop 
  exit when i > daycnt;
  if to_char(td,'D') = 7 or to_char(td,'D') = 1 then
    tm := 0;
    tp := 02;
  else
    tp := 01;
    tm := 8;
  end if;
  insert into doercal(DMS_WORK_CALE,CALE_SORT,CALE_WTIME) values(td,tp,tm);
  td := td + 1;
  i := i + 1;
end loop;
commit;
END;

Answer 3

把表名和字段名弄混了，重贴
CREATE OR REPLACE PROCEDURE crtcal(year in varchar2) 
IS 
i number; 
td date; 
tm number; 
daycnt number; 
tp varchar2(2); 
BEGIN 
if mod(to_number(year),400) = 0 or (mod(to_number(year),4) = 0 and mod(to_number(year),100)  <> 0) then 
  daycnt := 366; 
else  
  daycnt :=365; 
end if; 
td := to_date(year ¦ ¦'-01-01','YYYY-MM-DD'); 
i := 1; 
tm := 0; 
loop  
  exit when i > daycnt; 
  if to_char(td,'D') = 7 or to_char(td,'D') = 1 then 
    tm := 0; 
    tp := 02; 
  else 
    tp := 01; 
    tm := 8; 
  end if; 
  insert into DMS_WORK_CALE(CALE_DATE,CALE_SORT,CALE_WTIME) values(to_char(td,'YYYY-MM-DD'),tp,tm); 
  td := td + 1; 
  i := i + 1; 
end loop; 
commit; 
END;

Answer 4

select to_char(to_date('2008-01-01', 'yyyy-mm-dd') + (rownum - 1),
               'yyyy-mm-dd') 日期,decode(to_char(to_date('2008-01-01', 'yyyy-mm-dd') + (rownum - 1),
               'd'),7,'02',1,'02','01'),decode(to_char(to_date('2008-01-01', 'yyyy-mm-dd') + (rownum - 1),
               'd'),7,'0',1,'0','8')
  from user_objects
 where rownum < 367
   and to_date('2008-01-01', 'yyyy-mm-dd') + (rownum - 1) <
       to_date('2009-01-01', 'yyyy-mm-dd');

Answer 5

//首先感谢大家的参与，在本人闭关一下午后终于搞定，贴出代码，供大家参考，如有更优的解决方案，还请不吝赐教，周五结贴。
create or replace procedure p_date( ad_year Varchar2 )
is   
vn_days Number;
vn_date Number;
vd_date Date;
begin   
      For i In 1..12 Loop
            Select to_number(to_char( last_day( to_date( ad_year||to_char(i,'00')||'01','yyyymmdd') ),'dd' ) ) Into vn_days From dual;
            For j In 1..vn_days Loop
                --2000/01/02--周日
                Select to_date( ad_year||to_char(i,'00')||to_char(j,'00'),'yyyymmdd' ) Into vd_date From dual;
                Select mod(Mod( ( to_date( ad_year||to_char(i,'00')||to_char(j,'00'),'yyyymmdd' ) - to_date( '20000102','yyyymmdd' ) ),7 ) + 7,7) Into vn_date From dual;
                Insert Into DMS_WORK_CALE Values( vd_date,
                                                  decode( vn_date,6,'01',decode( vn_date,0,'01','02' ) ),
                                                  decode( vn_date,6,0,decode( vn_date,0,0,8 ) )
                                                  ) ;
            end   Loop;
      end   Loop;
      commit;
End p_date;

Answer 6

select level lv from dual connect by level<=366;
出来的是个序列  1，2，。366.这样可以得到366天。decode函数用法to_char(sysdate,'d')    
出来的是1，2，7    这个是西方人的习惯。周7是1，周6是7.

Answer 7

//但是为什么我的测试结果，返回的仅是1到100的序列呢？
select level lv from dual connect by level <=366;

Answer 8

外面在写层就能看到了！
实际上不止100
select * from (select level lv from dual connect by level  <=366)
366行。
为什么会出现这个问题不知道

Answer 9

declare
   theday date;
   YearDays Pls_integer;
   Type t_s is table of pls_integer;
   v_s  t_s:=t_s();
begin
   theday:=to_date('2008-01-01','yyyy-mm-dd')-1;
   YearDays:=to_date('2008-12-31','yyyy-mm-dd')-theday; 
   for i in 1.. yearDays loop
      v_s.extend;
      v_s(i):=i;
   end loop;
   forall i in 1..yearDays     
      Insert into testDay values(theday+v_s(i),decode(to_char(theday+v_s(i),'d'),6,'02',7,'02','01'),
                                        decode(to_char(theday+v_s(i),'d'),6,'0',7,'0','8')
                                ); 
   commit;
end;大概是这个意思，看你如何定义哪天是工作日，此外注意nls设置。 我的例子是定义6,7为休息日.

Answer 10

应该是这句的问题。values(to_char(td,'YYYY-MM-DD'),tp,tm); 
改成：values(td,tp,tm);
就可以了。
我本地的表用的是varchar2所以做了一个转换。
在sql文中使用connect by 循环，
plsql中的直接使用循环 日期增加的方法可以直接加1，也没有必要按照12个月来循环。
然后就是那点：判断是不是闰年。
decode的写法：decode(mod(to_number(:year),400),0,366,decode(mod(to_number(:year),100),0,365,decode(mod(to_number(:year),4),0,366,365)))

Answer 11

尝试下merg,是这样写的吗？
或者先判断

Answer 12

楼上的是想说merge吧，简单查了下，好像oracle10后增加了delete的功能，之前的没有。

Answer 13

//以下是我最后的解决方案，但是开头删除的部分由于是对字段进行了转换，所以感觉上不够高效，有没有更高效的做法？
create or replace procedure p_date( ad_year Varchar2 )
is   
vn_num Number;
vn_days Number;
vn_date Number;
vd_date Date;
begin   
Select Count(*) Into vn_num From DMS_WORK_CALE Where cale_date = to_date(ad_year||'-01-01','yyyy-mm-dd');
        If vn_num > 0 Then
           Delete From DMS_WORK_CALE Where to_char( cale_date,'yyyy' ) = ad_year;
        End If;
      For i In 1..12 Loop
            Select to_number(to_char( last_day( to_date( ad_year||to_char(i,'00')||'01','yyyymmdd') ),'dd' ) ) Into vn_days From dual;
            For j In 1..vn_days Loop
                --2000/01/02--周日
                Select to_date( ad_year||to_char(i,'00')||to_char(j,'00'),'yyyymmdd' ) Into vd_date From dual;
                Select mod(Mod( ( to_date( ad_year||to_char(i,'00')||to_char(j,'00'),'yyyymmdd' ) - to_date( '20000102','yyyymmdd' ) ),7 ) + 7,7) Into vn_date From dual;
                Insert Into DMS_WORK_CALE Values( vd_date,
                                                  decode( vn_date,6,'01',decode( vn_date,0,'01','02' ) ),
                                                  decode( vn_date,6,0,decode( vn_date,0,0,8 ) )
                                                  ) ;
            end   Loop;
      end   Loop;
      commit;
End p_date;

Answer 14

你自己的那个严格地说，是效率一般的，你只是注意了for的效率，但是忘记了 最为关键的pl/sql引擎和sql引擎交互所需要耗费的时间。

Answer 15

请楼上把话说完：pl/sql引擎和sql引擎交互如何提高效率？
还望不吝赐教。

Answer 16

查看pl/sql中关于for all的说明即可。
因为pl/sql引擎把sql内容传输给sql引擎是需要耗费资源的。
真正执行sql的是sql引擎，而pl/sql引擎仅仅是预处理Pl/sql，最终代码需要再交付给sql引擎。