存储过程问题，高手帮帮忙

Question

create or replace procedure pro1 is   v_1 number;
  v_2 number;
  v_3 number;
  v_4 float;
  v_5 float;
  v_6 float;
  v_7 float;
  v_8 float;
  v_9 float;
  v_10 float;
  v_11 float;
  v_12 float;
  v_13 float;
   begin
   delete from performance p where p.周期=4 and p.荐股机构种类=5;
 select 4,5,ren,sy,sy3,sb,cg,sb/zs,cg/zs,sb3,cg3,cg3/zs,sb3/zs into  v_1,v_2,v_3,v_4,v_5,v_6,v_7,v_8,v_9,v_10,v_11,v_12,v_13 from //报错的行(select 4,5,p.人员id as ren, sum(r.performanceof1day)/count(r.id) as sy, sum(r.performanceof3day)/count(r.id) as sy3,
f判断推荐成功(r.performanceof1day) as cg,f判断推荐失败(r.performanceof1day) as sb, count(r.id) as zs,
f判断推荐成功(r.performanceof3day) as cg3,f判断推荐失败(r.performanceof3day) as sb3  
from recomstock r,人员基本信息 p where r.deliveredtime>(select to_char(sysdate-30,'yyyy-mm-dd') from dual)
and r.deliveredtime<(select to_char(sysdate,'yyyy-mm-dd') from dual)
and r.supplier=p.人员id and r.flag=1 group by p.人员id) 
where sb+cg>10;
    insert into performance(周期,荐股机构种类,supplier,performance1day,performance3day,lossnumber1day,
successnumber1day,loss1day,successrate1day,lossnumber3day,successnumber3day,loss3day,successrate3day) values(4,5,v_3,v_4,v_5,v_6,v_7,v_8,v_9,v_10,v_11,v_12,v_13);   commit;
 end;这个存储过程总是报错：ORA-01422：exact fetch returns more than requeseted number of rows
请高手指点下，小弟初学这个东西。

Answer 1

insert into performance(周期,荐股机构种类,supplier,performance1day,performance3day,lossnumber1day,
successnumber1day,loss1day,successrate1day,lossnumber3day,successnumber3day,loss3day,successrate3day) 
 select 4,5,ren,sy,sy3,sb,cg,sb/zs,cg/zs,sb3,cg3,cg3/zs,sb3/zs from (select 4,5,p.人员id as ren, sum(r.performanceof1day)/count(r.id) as sy, sum(r.performanceof3day)/count(r.id) as sy3,
f判断推荐成功(r.performanceof1day) as cg,f判断推荐失败(r.performanceof1day) as sb, count(r.id) as zs,
f判断推荐成功(r.performanceof3day) as cg3,f判断推荐失败(r.performanceof3day) as sb3  
from recomstock r,人员基本信息 p where r.deliveredtime>(select to_char(sysdate-30,'yyyy-mm-dd') from dual)
and r.deliveredtime<(select to_char(sysdate,'yyyy-mm-dd') from dual)
and r.supplier=p.人员id and r.flag=1 group by p.人员id) 
where sb+cg>10;
   commit;

Answer 2

根据这个错误提示，应该是你在用 select，，，，into，，，的时候查询出了负数记录你可以加个Exception看下BEGIN
 SELECT ....
 INTO.......
 FROM ......
 WHERE.....
 ;EXCEPTION WHEN TOO_MANY_ROWS THEN  “加入异常处理”；
END;

Answer 3

是你查询得到的结果是多个，应该用游标实现。
你可以先测试下
select 4,5,ren,sy,sy3,sb,cg,sb/zs,cg/zs,sb3,cg3,cg3/zs,sb3/zs from (select 4,5,p.人员id as ren, sum(r.performanceof1day)/count(r.id) as sy, sum(r.performanceof3day)/count(r.id) as sy3,
f判断推荐成功(r.performanceof1day) as cg,f判断推荐失败(r.performanceof1day) as sb, count(r.id) as zs,
f判断推荐成功(r.performanceof3day) as cg3,f判断推荐失败(r.performanceof3day) as sb3  
from recomstock r,人员基本信息 p where r.deliveredtime>(select to_char(sysdate-30,'yyyy-mm-dd') from dual)
and r.deliveredtime<(select to_char(sysdate,'yyyy-mm-dd') from dual)
and r.supplier=p.人员id and r.flag=1 group by p.人员id) 
where sb+cg>10;
是不是返回多行，返回多行的要用游标

Answer 4

用游标的要先定义，然后fetch  into..自己看看学习下。
其实你这可以不用定义那些变量的，直接一条语句就可以了。你是在要写存储过程的话用下面的：
create or replace procedure pro1 is
begin
insert into performance(周期,荐股机构种类,supplier,performance1day,performance3day,lossnumber1day,
successnumber1day,loss1day,successrate1day,lossnumber3day,successnumber3day,loss3day,successrate3day)
 select ren,sy,sy3,sb,cg,sb/zs,cg/zs,sb3,cg3,cg3/zs,sb3/zs    from (select p.人员id as ren, sum(r.performanceof1day)/count(r.id) as sy, sum(r.performanceof3day)/count(r.id) as sy3,
f判断推荐成功(r.performanceof1day) as cg,f判断推荐失败(r.performanceof1day) as sb, count(r.id) as zs,
f判断推荐成功(r.performanceof3day) as cg3,f判断推荐失败(r.performanceof3day) as sb3  
from recomstock r,人员基本信息 p where r.deliveredtime>(select to_char(sysdate-30,'yyyy-mm-dd') from dual)
and r.deliveredtime<(select to_char(sysdate,'yyyy-mm-dd') from dual)
and r.supplier=p.人员id and r.flag=1 group by p.人员id) 
where sb+cg>10;
 

Answer 5

create or replace procedure pro1 is
begin
insert into performance(周期,荐股机构种类,supplier,performance1day,performance3day,lossnumber1day,
successnumber1day,loss1day,successrate1day,lossnumber3day,successnumber3day,loss3day,successrate3day)
 select 4,5,ren,sy,sy3,sb,cg,sb/zs,cg/zs,sb3,cg3,cg3/zs,sb3/zs    from (select p.人员id as ren, sum(r.performanceof1day)/count(r.id) as sy, sum(r.performanceof3day)/count(r.id) as sy3,
f判断推荐成功(r.performanceof1day) as cg,f判断推荐失败(r.performanceof1day) as sb, count(r.id) as zs,
f判断推荐成功(r.performanceof3day) as cg3,f判断推荐失败(r.performanceof3day) as sb3  
from recomstock r,人员基本信息 p where r.deliveredtime>(select to_char(sysdate-30,'yyyy-mm-dd') from dual)
and r.deliveredtime<(select to_char(sysdate,'yyyy-mm-dd') from dual)
and r.supplier=p.人员id and r.flag=1 group by p.人员id) 
where sb+cg>10;
end;

Answer 6

帮人帮到底，虽然没什么难度，但也很麻烦的。看在我有始有终的份上得多加点分哦。呵呵，玩笑。
sql如下：自己排版create or replace procedure pro1 is 
 v_1 number; v_2 number; v_3 number; v_4 float; v_5 float; v_6 float; v_7 float; v_8 float; v_9 float; v_10 float; v_11 float; v_12 float; v_13 float; 
cursor cu is select 4,5, p.人员id as ren, sum(r.performanceof1day)/count(r.id) as sy, sum(r.performanceof3day)/count(r.id) as sy3, f判断推荐成功(r.performanceof1day) as cg,f判断推荐失败(r.performanceof1day) as sb, count(r.id) as zs, f判断推荐成功(r.performanceof3day) as cg3,f判断推荐失败(r.performanceof3day) as sb3 from recomstock r,人员基本信息 p where r.deliveredtime>(select to_char(sysdate-30,'yyyy-mm-dd') from dual) and r.deliveredtime<(select to_char(sysdate,'yyyy-mm-dd') from dual) and r.supplier=p.人员id and r.flag=1 group by p.人员id) where sb+cg>10;
begin  
delete from performance p where p.周期=4 and p.荐股机构种类=5; 
open cu;
loopfetch cu  into v_1,v_2, v_3,v_4,v_5,v_6,v_7,v_8,v_9,v_10,v_11,v_12,v_13 ;
exit when cu%notfound;
 insert into performance(周期,荐股机构种类,supplier,performance1day,performance3day,lossnumber1day, successnumber1day,loss1day,successrate1day,lossnumber3day,successnumber3day,loss3day,successrate3day) values(v_1,v_2,v_3,v_4,v_5,v_6,v_7,v_8,v_9,v_10,v_11,v_12,v_13);
 commit; 
end loop;
end;