关于存储过程与游标的使用

Question

BOSS让俺写个存储过程,希望大虾们帮帮忙,写个给小弟学习学习,要求如下:
    
编写储存过程 sp_tb_jf：实现执行存储过程的时候把查询实现的缴费编号，代理编号，姓名，身份证号，性别，所属分中心编号，缴费日期，医保缴费金额,档案费等的结果保存到新建的表 tb_jf(需建表) ,用 游标 取相应的信息插入tb_jf.刚接触存储过程,大虾们帮帮忙呀,谢谢了!

Answer 1

示例代码：create or replace procedure sp_tb_jf is
   cursor c_tb_jf is 
       select jf_code,
              dl_code,
              name,
              certi_code,
              sex,
              center_code,
              jf_date,
              fee1,
              fee2
        from tablename where ....;
begin
   while c_tb_jf%FOUND loop
      insert into tb_jf values(
          c_tb_jf.jf_code,
          c_tb_jf.dl_code,
          c_tb_jf.name,
          c_tb_jf.certi_code,
          c_tb_jf.sex,
          c_tb_jf.center_code,
          c_tb_jf.jf_date,
          c_tb_jf.fee1,
          c_tb_jf.fee2);
       commit;
    end loop;
end;

Answer 2

1楼的正解，不过觉得commit放在循环的最后好一点，还需要些异常判断，人性化一点，都取决于楼主的需求！

Answer 3

大虾们,帮我看看这样写对吗?
create or replace procedure sp_tb_jf
is
     cursor c_tb_jf
     is 
     select b.ced053 "缴费编号",
       b.ced004 "代理编号",
       b.aac003 "姓名",
       a.aac004 "性别",
       b.aac002 "身份证号",
       b.aae036 "缴费日期",
       b.bab013 "所属分中心编号",
       d.ced057 "医保缴费金额",
       e.ced057 "档案费"
     from eaa1 a,eaa8 b,(select * from varchar2(14) eaa9 where ced051='001') d,
                        (select * from eaa9 where ced051='006') e
     where b.aae036 >= to_date('20080101', 'yyyy-mm-dd') and b.aae036 <= to_date('20080131','yyyy-mm-dd')  
       and a.ced001 = b.ced001        
       and b.ced053 = d.ced053(+)
       and b.ced053 = e.ced053(+)) 
begin     
     if c_tb_jf%isopen = false
     then
              open c_tb_jf;
              while c_tb_jf%found
              loop
                  insert into tb_jf 
                  VALUES(c_tb_jf.ced053,c_tb_jf.ced004,c_tb_jf.aac003,c_tb_jf.aac004,c_tb_jf.aac002,
                         c_tb_jf.ae036,c_tb_jf.bab013,c_tb_jf.ced057,c_tb_jf.daf);                 
              end loop;
              close c_tb_jf;
              commit;
     end if;
     exception
              when others
              then 
              rollback;
end sp_tb_jf; 

Answer 4

直接插入得了，不用游标。
insert into tb_jf  
select b.ced053 "缴费编号",
  b.ced004 "代理编号",
  b.aac003 "姓名",
  a.aac004 "性别",
  b.aac002 "身份证号",
  b.aae036 "缴费日期",
  b.bab013 "所属分中心编号",
  d.ced057 "医保缴费金额",
  e.ced057 "档案费"
  from eaa1 a,eaa8 b,(select * from varchar2(14) eaa9 where ced051='001') d,
  (select * from eaa9 where ced051='006') e
  where b.aae036 >= to_date('20080101', 'yyyy-mm-dd') and b.aae036 <= to_date('20080131','yyyy-mm-dd')   
  and a.ced001 = b.ced001   
  and b.ced053 = d.ced053(+)
  and b.ced053 = e.ced053(+))  

Answer 5

我怎么感觉少了点东西呢！
是不是得需要将结果集的每一行读取到行变量中呀~~
FETCH c_tb_jf INTO cur_tb_jf;
create or replace procedure sp_tb_jf is
   cursor c_tb_jf is 
       select jf_code,
              dl_code,
              name,
              certi_code,
              sex,
              center_code,
              jf_date,
              fee1,
              fee2
        from tablename where ....;
begin
   while c_tb_jf%FOUND loop
      FETCH c_tb_jf INTO cur_tb_jf;
      insert into tb_jf values(
          cur_tb_jf.jf_code,
          cur_tb_jf.dl_code,
          cur_tb_jf.name,
          cur_tb_jf.certi_code,
          cur_tb_jf.sex,
          cur_tb_jf.center_code,
          cur_tb_jf.jf_date,
          cur_tb_jf.fee1,
          cur_tb_jf.fee2);
    end loop;
    commit;
end;
再者楼主的Procedure结果集每个字段已经重命名了，就不存在ced053，ced004等了！
你得：c_tb_jf.缴费编号

Answer 6

create or replace procedure sp_tb_jf
is
     v_number number :=0;
     cursor c_tb_jf
     is
     select b.ced053 "缴费编号",
       b.ced004 "代理编号",
       b.aac003 "姓名",
       a.aac004 "性别",
       b.aac002 "身份证号",
       b.aae036 "缴费日期",
       b.bab013 "所属分中心编号",
       d.ced057 "医保缴费金额",
       e.ced057 "档案费"
     from eaa1 a,eaa8 b,(select * from eaa9 where ced051='001') d,
                        (select * from eaa9 where ced051='006') e
     where b.aae036 >= to_date('20080101', 'yyyy-mm-dd') and b.aae036 <= to_date('20080131','yyyy-mm-dd')
       and a.ced001 = b.ced001
       and b.ced053 = d.ced053(+)
       and b.ced053 = e.ced053(+);
begin
for r in c_tb_jf loop
        
select count(*) into v_number from eaa8 where eaa8.ced053 =r.缴费编号;
if  v_number>0 then
insert into tb_jf ---调试时这里提示不存在表或者视图,应该怎么弄?新建表?后面+(create table...)?              VALUES(r.缴费编号,r.代理编号,r.姓名,r.性别,r.身份证号,
                     r.缴费日期,r.所属分中心编号,r.医保缴费金额,r.档案费);
end if;
      end loop;
      commit;
      
      exception
      when others
      then
      rollback;
end sp_tb_jf;

Answer 7

直接在PL/SQL块中写create table语句就行了，跟普通创建表没有区别