oracle 存储过程

CREATE OR REPLACE PROCEDURE data_transplant(objType   in NUMBER,
                                            startTime in varchar2,
                                            endTime   in varchar2,
                                            ids       in varchar2
                                            --,
                                            --title in varchar2,
                                            --sectionId in varchar2
                                            )
------------ 移植业务表到历史表 by iehyou -----------
--有2种执行方式:
--1.移植执行ID (参数:ids)的Ojbect表和子表数据到历史表);
--2.根据类别和时间(参数:objType,startTime,endTime)查询的数据移植;
------------ 移植业务表到历史表 by iehyou -----------
AUTHID CURRENT_USER

 is
  execSql varchar2(1000);
  type c_obj_ref is ref cursor;
  c_obj c_obj_ref;
  obj   ITV_OBJECT%rowtype;
begin
  DBMS_OUTPUT.PUT_LINE('objType:' || objType);
  DBMS_OUTPUT.PUT_LINE('startTime:' || startTime);
  DBMS_OUTPUT.PUT_LINE('endTime:' || endTime);
  --拼接执行sql;
  execSql := ' FROM itv.ITV_OBJECT obj22 where 1=1 ';

  if (length(ids) > 0) then
    begin
      execSql := execSql || ' and obj22.object_id in (' || ids || ')';
    end;
  else
    begin
        if (objType> 0) then
        begin 
              execSql := execSql || ' and obj22.type_id = ' ||objType ;
        end;
        end if;
      execSql := execSql||' AND to_char(obj22.online_time, ''yyyy-MM-dd hh24:mm:ss'') >='''||startTime||''''
                 ||' AND to_char(obj22.online_time, ''yyyy-MM-dd hh24:mm:ss'') <= '''||endTime||'''';
    end;
  end if;

  --DBMS_OUTPUT.PUT_LINE('title:'||title);
  --DBMS_OUTPUT.PUT_LINE('sectionId:'||sectionId);
  --保存 itv.ITV_HIS_OBJECT
 -- DBMS_OUTPUT.PUT_LINE('INSERT INTO itv.ITV_HIS_OBJECT (SELECT *  ' 
   --                    ||execSql || ')');
  execute immediate ('INSERT INTO itv.ITV_HIS_OBJECT (SELECT *  ' ||
                    execSql || ')');
  --打开游标
  DBMS_OUTPUT.PUT_LINE('select *  ' || execSql);
  OPEN c_obj for('select * ' || execSql);
  LOOP
    FETCH c_obj
      INTO obj;
    EXIT WHEN c_obj%NOTFOUND;
    if (obj.type_id = 1) then
      begin
        --保存 itv.ITV_His_Vote
        INSERT INTO itv.ITV_His_Vote
          SELECT *
            FROM itv.ITV_vote vote
           WHERE exists (select h_obj.object_id
                    from itv.ITV_HIS_OBJECT h_obj
                   where h_obj.object_id = vote.vote_id
                     and h_obj.object_id = obj.object_id);
      
        --保存 itv.ITV_His_Vote_Opt
        INSERT INTO itv.ITV_His_Vote_Opt
          SELECT *
            FROM itv.ITV_vote_opt v_opt
           WHERE exists (select h_vote.vote_id
                    from itv.ITV_His_Vote h_vote
                   where h_vote.vote_id = v_opt.vote_id
                     and h_vote.vote_id = obj.object_id);
      
        --保存 itv.ITV_His_Vote_rst
        INSERT INTO itv.ITV_His_Vote_rst
          SELECT *
            FROM itv.ITV_vote_rst v_rst
           WHERE exists (select h_v_opt.opt_id
                    from itv.ITV_His_Vote_opt h_v_opt
                   where h_v_opt.vote_id = obj.object_id
                     and h_v_opt.opt_id = v_rst.opt_id);
        --删除 itv.ITV_vote_rst
        delete FROM itv.ITV_vote_rst t3
         WHERE exists (select t3.opt_id
                  from itv.ITV_Vote_opt d_h_v_opt
                 where d_h_v_opt.vote_id = obj.object_id
                   and d_h_v_opt.opt_id = t3.opt_id);
      
        --删除 itv.ITV_vote_opt
        delete from itv.ITV_vote_opt t2 where t2.vote_id = obj.object_id;
        --删除 itv.ITV_vote
        delete from itv.ITV_vote t1 where t1.vote_id = obj.object_id;
      end;
      --竞猜
    elsif (obj.type_id = 2) then
      begin
        --保存 itv.ITV_His_Guss
        INSERT INTO itv.ITV_His_Guss
          SELECT *
            FROM itv.ITV_guss guss
           WHERE exists (select h_obj.object_id
                    from itv.ITV_HIS_OBJECT h_obj
                   where h_obj.object_id = guss.guss_id
                     and h_obj.object_id = obj.object_id);
      
        --保存 itv.ITV_His_Guss_Opt
        INSERT INTO itv.ITV_His_Guss_Opt
          SELECT *
            FROM itv.ITV_guss_opt g_opt
           WHERE exists (select h_guss.guss_id
                    from itv.ITV_His_guss h_guss
                   where h_guss.guss_id = g_opt.guss_id
                     and h_guss.guss_id = obj.object_id);
        --保存 itv.ITV_His_guss_rst
        INSERT INTO itv.ITV_His_guss_rst
          SELECT *
            FROM itv.ITV_guss_rst g_rst
           WHERE exists (select h_g_opt.opt_id
                    from itv.ITV_His_guss_opt h_g_opt
                   where h_g_opt.guss_id = obj.object_id
                     and h_g_opt.opt_id = g_rst.opt_id);
        --保存 itv.ITV_His_guss_asw
        INSERT INTO itv.ITV_His_guss_asw
          SELECT *
            FROM itv.ITV_guss_asw g_asw
           WHERE exists (select h_g_opt.opt_id
                    from itv.ITV_His_Guss_Opt h_g_opt
                   where h_g_opt.guss_id = obj.object_id
                     and h_g_opt.opt_id = g_asw.opt_id);
        --删除 itv.ITV_guss_asw
        delete FROM itv.ITV_guss_asw g4
         WHERE exists (select d_h_g_opt.opt_id
                  from itv.ITV_guss_opt d_h_g_opt
                 where d_h_g_opt.guss_id = obj.object_id
                   and d_h_g_opt.opt_id = g4.opt_id);
        --删除 itv.ITV_guss_rst
        delete FROM itv.ITV_guss_rst g3
         WHERE exists (select d_h_g_opt.opt_id
                  from itv.ITV_guss_opt d_h_g_opt
                 where d_h_g_opt.guss_id = obj.object_id
                   and d_h_g_opt.opt_id = g3.opt_id);
        --删除 itv.ITV_guss_opt
        delete from itv.ITV_guss_opt g2 where g2.guss_id = obj.object_id;
        --删除 itv.ITV_guss
        delete from itv.ITV_guss g1 where g1.guss_id = obj.object_id;
      end;
      -- 讨论
    elsif (obj.type_id = 3) then
      begin
        --保存 itv.ITV_His_dscs
        INSERT INTO itv.ITV_His_dscs
          SELECT *
            FROM itv.ITV_dscs dscs
           WHERE exists (select h_obj.object_id
                    from itv.ITV_HIS_OBJECT h_obj
                   where h_obj.object_id = dscs.dscs_id
                     and h_obj.object_id = obj.object_id);
      
        --保存 itv.ITV_His_dscs_rst
        INSERT INTO itv.ITV_His_dscs_rst
          SELECT *
            FROM itv.ITV_dscs_rst g_rst
           WHERE exists (select h_dscs.dscs_id
                    from itv.ITV_His_dscs h_dscs
                   where h_dscs.dscs_id = g_rst.dscs_id
                     and h_dscs.dscs_id = obj.object_id);
      
        --保存 itv.ITV_HIS_DSCS_PHRASE
        INSERT INTO itv.ITV_HIS_DSCS_PHRASE
          SELECT *
            FROM itv.ITV_DSCS_PHRASE d_ph
           WHERE exists (select h_dscs.dscs_id
                    from itv.ITV_His_dscs h_dscs
                   where h_dscs.dscs_id = d_ph.dscs_id
                     and h_dscs.dscs_id = obj.object_id);
        --删除 itv.ITV_DSCS_PHRASE
        delete from itv.ITV_DSCS_PHRASE d3 where d3.dscs_id = obj.object_id;
      
        --删除 itv.ITV_dscs_rst
        delete from itv.ITV_dscs_rst d2 where d2.dscs_id = obj.object_id;
      
        --删除 itv.ITV_guss
        delete from itv.ITV_dscs d1 where d1.dscs_id = obj.object_id;
      end;
      --收视
    elsif (obj.type_id = 4) then
      begin
        --保存 itv.ITV_HIS_AUDIENCE
        INSERT INTO itv.ITV_HIS_AUDIENCE
          SELECT *
            FROM itv.ITV_AUDIENCE au
           WHERE exists (select h_obj.object_id
                    from itv.ITV_HIS_OBJECT h_obj
                   where h_obj.object_id = au.audience_id
                     and h_obj.object_id = obj.object_id);
      
        --保存 itv.ITV_HIS_AUDIENCE_PERSON
        INSERT INTO itv.ITV_HIS_AUDIENCE_PERSON
          SELECT *
            FROM itv.ITV_AUDIENCE_PERSON a_per
           WHERE exists (select h_au.audience_id
                    from itv.ITV_His_Audience h_au
                   where h_au.audience_id = a_per.audience_id
                     and h_au.audience_id = obj.object_id);
        --保存 itv.ITV_his_audience_show_time
        INSERT INTO itv.ITV_his_audience_show_time
          SELECT *
            FROM itv.ITV_audience_show_time a_time
           WHERE exists (select h_au.audience_id
                    from itv.ITV_His_Audience h_au
                   where h_au.audience_id = a_time.audience_id
                     and h_au.audience_id = obj.object_id);
        --删除 itv.ITV_audience_show_time
        delete from itv.ITV_audience_show_time a3
         where a3.audience_id = obj.object_id;
        --删除 itv.ITV_AUDIENCE_PERSON
        delete from itv.ITV_AUDIENCE_PERSON a2
         where a2.audience_id = obj.object_id;
        --删除 itv.ITV_AUDIENCE
        delete from itv.ITV_AUDIENCE a1 where a1.audience_id = obj.object_id;
      
      end;
    elsif (obj.type_id = 5) then
      begin
        --内容
        --保存 itv.ITV_HIS_CONTENT
        INSERT INTO itv.ITV_HIS_CONTENT
          SELECT *
            FROM itv.ITV_HIS_CONTENT h_con
           WHERE exists (select h_obj.object_id
                    from itv.ITV_HIS_OBJECT h_obj
                   where h_obj.object_id = h_con.content_id
                     and h_obj.object_id = obj.object_id);
        --删除 itv.ITV_AUDIENCE
        delete from itv.ITV_CONTENT c1 where c1.content_id = obj.object_id;
      end;
    end if;
  END LOOP;
  CLOSE c_obj;

  --删除 itv.ITV_OBJECT
  DBMS_OUTPUT.PUT_LINE(' delete  ' || execSql);
  execute immediate (' delete  ' || execSql);
  COMMIT;
end;