定时生成数据

create or replace procedure pro_nor_maktask
is
taskid number;   
sepr varchar2(1);  
sbuf varchar2(200);  
sres varchar2(200);  
pos number := 1;  
istart number;  
r_task varchar2(100);

rs_row tb_nor_tactic%rowtype;
--查询是策略性质为==自动
cursor my_cursor is
       select ta.* from  tb_nor_tactic ta where ta.nature=1;
begin
     --打开游标
     open my_cursor;
     loop
     fetch my_cursor into rs_row;--通过游标提取行记录
     --exit when my_cursor%notfound;--当找不到记录时退出循环
       if my_cursor%notfound then
       exit;
       end if;
      ---表示下次运行的时间是当前时间
      if(to_char(rs_row.nextdate,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd')) then
           ---获取任务的当前id
           select seq_nor_task.nextval into r_task from dual;
           ---新增到任务中
            insert into tb_nor_task
              (id, sortid, title, content, allsign, backsign, evalsign, startdate, enddate, estaber, estabtime, pubsign, pubuser, pubtime, finsign)
            values
              (r_task, rs_row.sortid,  rs_row.title,  rs_row.content, rs_row.allsign,  rs_row.backsign,  rs_row.evalsign,  rs_row.startdate,  rs_row.enddate,  rs_row.estaber,  rs_row.estabtime,  rs_row.pubsign, rs_row.pubuser, rs_row.pubtime, 0);

            if((rs_row.allsign = 1) and length(rs_row.userids)>0) then
                  dbms_output.put_line('start.....................................');
                  dbms_output.put_line('all userids is .....................................');
                  ---在前面加一个','
                  sbuf := ','||rs_row.userids;  
                  dbms_output.put_line(sbuf);
                  sepr := ',';  
                  --istart := 1;  
                  --tokenizer(istart, sepr, sbuf, sres, pos);  
                  --if (pos <> 0) then  
                  --dbms_output.put_line (sres);  
                  --end if;   
                  while (pos <> 0)  
                  loop  
                      istart := pos;  
                      pro_splitstr(istart, sepr, sbuf, sres, pos);  
                      ----新增操作
                      insert into tb_nor_process
                      (id, userid, taskid, tasksign,prosign,fatherid)
                      values
                      (seq_nor_process.nextval,sres, r_task, 0,0,0);
                      dbms_output.put_line(sres||'::');  
                  end loop;   
            end if;
      end if;
      -----修改策略下次执行时间
      update tb_nor_tactic set nextdate = func_nor_cirnextdate(rs_row.cirnum,rs_row.cirunit) where id = rs_row.id;
     end loop;
     -- 关闭游标
     close my_cursor;
end;