Oracle 存储过程

create or replace procedure ProcedTest is

begin

  FOR fw IN (select f.id, f.name, f.address, f.pid, f.orgclass
               from td_tyorg f
              where f.id not in (select a.id
                                   from td_tyorg                      a,
                                        ts_organizations_compare_back b,
                                        ts_organizations_back         c
                                  where a.pid = b.xyw_org_id
                                    and c.pid = b.xj_org_id
                                    and a.orgclass = 3
                                    and a.name = c.name)
                and f.orgclass = 3) LOOP
  
    delete from org_temp;
    commit;
    
    
    insert into org_temp
      select max(id) + 1, pid
        from ts_organizations_back
       where pid in
             (select c.xj_org_id
                from ts_organizations_compare_back c
               where c.xyw_org_id in
                     (select id from td_tyorg a where a.orgclass = 2))
      
       group by pid;
    commit;
  
  
    insert into ts_organizations_back(
       id,
       name,
       pid,
       orgclass,
       address,
       isoff,
       createoperator,
       modifyoperator,
       createdate)
      values(
      (select a.city_id
         from org_temp a
        where a.province_id =
              to_number((select b.xj_org_id
                 from ts_organizations_compare_back b
                where b.xyw_org_id = fw.pid))),
       fw.name,
       (select b.xj_org_id
                 from ts_organizations_compare_back b
                where b.xyw_org_id = fw.pid),
       fw.orgclass,
       fw.address,
       1,
       2,
       2,
       sysdate);
  
  
    insert into ts_organizations_compare_back
      (id,
       xj_org_id,
       xyw_org_id,
       crt_platform,
       act_state,
       frist_act_time,
       last_login_time)
    values
      ((select a.city_id
         from org_temp a
        where a.province_id =
              to_number((select b.xj_org_id
                 from ts_organizations_compare_back b
                where b.xyw_org_id = fw.pid))),
       (select a.city_id
          from org_temp a
         where a.province_id = 
                   to_number((select b.xj_org_id
                                  from ts_organizations_compare_back b
                                 where b.xyw_org_id = fw.pid))),
       fw.id,
       '1',
       '',
       '',
       '');
  
    commit;

  end loop;

end ProcedTest;

 

 

以上是一个Oracle存储过程实例，主要功能是查询出符合条件的记录插入到相关表中，其中for循环中的fw字段类似于

Java中的for each循环的iterator( 即迭代器)；Loop 和endloop之间为循环体。此外存储过程的主要语句为：

create or replace procedure ProcedTest is

begin

 

 ......  // 存储过程主体代码

end ProcedTest;      (其中ProcedTest为存储过程名)