oracle迴圈語句

標籤：person   to_date   time   sch   group   for   insert   迴圈語句   mon   

直接上sql代碼：

declare   begin    for c in (select s_personinfo.nextval as seq from dual) loop        Insert into TBPERSONINFO (IPERSONINFOID,VCPERSONCARD,VCPASSWORD,VCPERSONNAME,VCPERSIONSEX) values(c.seq ,‘33000021‘,‘123456‘,‘小明‘,‘男‘,);        if c.seq = c.seq+500 then           return ;      end if;  end loop; end;

自己項目中用到迴圈執行sql如下：

 declare   begin    for c in (select tsa.year,tsa.weeknumber,tsa.owner from tr_schedule_affair tsa,to_horizon_user thu where tsa.owner = thu.id and thu.login_name=‘sh1‘ group by tsa.year,tsa.weeknumber,tsa.owner) loop         update (select * from tr_schedule_affair where year = c.year and weeknumber = c.weeknumber and         owner = c.owner order by start_time)t         set         start_time = to_char(to_date(t.start_time)+7,‘yyyy-mm-dd HH24:MI‘),        end_time = to_char(to_date(t.end_time)+7,‘yyyy-mm-dd HH24:MI‘),          year = to_char((select start_time from (select * from tr_schedule_affair where year = c.year and weeknumber = c.weeknumber and         owner = c.owner order by start_time) where rownum=1),‘yyyy‘),        month = to_char((select start_time from (select * from tr_schedule_affair where year = c.year and weeknumber = c.weeknumber and         owner = c.owner order by start_time) where rownum=1),‘mm‘),        weeknumber = c.weeknumber+1;  end loop; end;

 

oracle迴圈語句