问题描述

oracle存储过程使用游标的问题

create or replace procedure test3(
templete_id in varchar2
) as
t_id_tmp varchar2(50);
ty_id_tmp varchar2(50);
cursor type_cur is select t1.id as type_id,t1.type_name from t_type t1,t_templete_link t2,t_templete t3
where t1.id = t2.type_id and t2.templete_id = t3.id and t3.id = templete_id;
-- n number;
begin
select sys_guid() into t_id_tmp from dual;

 --复制模板信息
 insert into t_templete
 select t_id_tmp as id, t.templete_name
   from t_templete t
  where t.id = templete_id;
 commit;      

   dbms_output.put_line(templete_id);
 --复制类别信息
-- declare 

 begin
   for type_rec in type_cur
     loop
        -- n:=n+1;
        -- dbms_output.put_line(1);
         select sys_guid() into ty_id_tmp from dual;
         insert into t_type
           select ty_id_tmp as id, type_rec.type_name from dual;
            -- from t_type t2
          --  where t2.id = type_rec.type_id;
         --  commit;   

          insert into t_templete_link
              select sys_guid() as id,t_id_tmp as TEMPLETE_ID ,ty_id_tmp as type_id from dual; -- t_templete_link t2
              --where t2.templete_id = templete_id;
          commit;
       end loop;
   end;

--dbms_output.put_line(t_id_tmp);

exception
  when others then
    Rollback;

end test3;

如果我传入的参数templete_id=1，我的t_cur循环的时候循环了3次。
但如果游标中的变变量templete_id 替换成 ‘1’就循环2次 这才是正常结果为什么

解决方案

Oracle 存储过程、游标的使用
Oracle存储过程,游标使用
oracle存储过程和游标的使用