嵌套游标

CREATE OR REPLACE PROCEDURE p_temp_count_orga
IS
   v_orga_id varchar2(20)  :='';
  
   -- 查询xxx市直 + 乡 id
   CURSOR c_sms_batch is
    select t.orga_id, t.orga_name
      from sys_organize t
     where t.orga_parentid2 IS NULL
       AND t.orga_parentid = '2013020400000015'
       AND t.virtual_flag = '1';
      
      CURSOR c_sms_batch2(vid varchar2 ) is  
        select  orga_id,orga_name from (
          select o.orga_id,o.orga_name
            from sys_organize o
           where 1 = 1
          CONNECT BY O.ORGA_PARENTID = PRIOR O.ORGA_ID
           START WITH O.ORGA_PARENTID = vid
          union all
          select o.orga_id ,o.orga_name　from sys_organize o where o.orga_parentid2 = vid
          );
 

BEGIN

   FOR r_sms IN c_sms_batch
   LOOP
    BEGIN
     
      FOR r_sms2 IN c_sms_batch2(r_sms.orga_id)
        LOOP
          BEGIN
                 insert into tttemp (id) values ( r_sms2.orga_id);
          END;
      END LOOP;
    END;
   END LOOP;
   
    DBMS_OUTPUT.put_line ('ok  ... ');
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line (SQLERRM);
         ROLLBACK;
      END;
END;