兄弟们,看看这个存储过程都有些什么语法错误,谢谢.

Question

v_count number(10);
v_area varchar(6);
v_channel varchar(6);
v_total number(10);
v_datecount number(10);
CURSOR cur0 IS  select CommissaryId,count(*) as MemberCount from t_s_memberInfo 
where TO_DATE(CreateDate,'YYYY-MM-DD')=TO_DATE(sysdate,'YYYY-MM-DD')
group by CommissaryId order by MemberCount desc
BEGIN   
       v_count=0; 
       FOR s IN cur0 LOOP
         BEGIN  
         v_count=v_count+1; 
         v_total=0;
         select area into v_area from t_o_paragraphDecompound 
         where t_o_paragraphDecompound.commissary=s.CommissaryId ;  
         select channel into v_channel from t_o_paragraphDecompound 
         where t_o_paragraphDecompound.commissary=s.CommissaryId ;         
         select total into v_total from t_o_dateTop order by ri desc;
         v_total=v_total+s.MemberCount;
         insert t_o_dateTop(ri,area,channel,commissary,amount,total,classTop) 
         values(sysdate,v_area,v_channel,s.CommissaryId , s.MemberCount,v_total,v_count)
         END;
      END LOOP;
 END;

Answer 1

加上
create or replace procedure test 
as 
编译没有语法错误
如果执行错误，那可能是表和字段写的有问题，检查下

Answer 2

好的.谢谢楼上.oracle不熟,
         select area into v_area from t_o_paragraphDecompound 
         where t_o_paragraphDecompound.commissary=s.CommissaryId ;  
         select channel into v_channel from t_o_paragraphDecompound 
         where t_o_paragraphDecompound.commissary=s.CommissaryId ;  
这两句可不可以合起来写.因为他们就是寻找表的字段不一样.

Answer 3

可以阿！
         select area， channel into v_area，v_channel  
         from t_o_paragraphDecompound 
         where t_o_paragraphDecompound.commissary=s.CommissaryId ;

Answer 4

v_count number(10);
v_area varchar(6);
v_channel varchar(6);
v_total number(10);
v_datecount number(10);
CURSOR cur0 IS select CommissaryId,count(*) MemberCount from t_s_memberInfo
where TO_DATE(CreateDate,'YYYY-MM-DD') = sysdate group by CommissaryId ;BEGIN
v_count := 0;
FOR s IN cur0 LOOP
v_count := v_count+1;
v_total := 0;
select area, channel into v_area, v_channel from t_o_paragraphDecompound
where t_o_paragraphDecompound.commissary=s.CommissaryId ;
//t_o_dateTop表中下列查询返回记录数可能大于1么，若大于1，则下列语句出错
select total into v_total from t_o_dateTop order by ri desc;
v_total := v_total+s.MemberCount;
insert into t_o_dateTop(ri,area,channel,commissary,amount,total,classTop)
values(sysdate,v_area,v_channel,s.CommissaryId, s.MemberCount, v_total, v_count);
commit;
END LOOP;
END;

Answer 5

谢谢楼上的.
你说的对.
t_o_dateTop表中记录可能会大于1.
oracle中有没有 top 这样的关键字,我就要取第一条记录.谢谢.
而且我想把sysdate刚开始的时候就付给一个日期变量,v_date,看下面的代码.这样可以吗?
v_count number(10);
v_area varchar(6);
v_channel varchar(6);
v_total number(10);
v_datecount number(10);
v_date date;
v_date=sysdate;
CURSOR cur0 IS select CommissaryId,count(*) MemberCount from t_s_memberInfo
where TO_DATE(CreateDate,'YYYY-MM-DD') = v_date ate group by CommissaryId ;BEGIN
v_count := 0;
FOR s IN cur0 LOOP
v_count := v_count+1;
v_total := 0;
select area, channel into v_area, v_channel from t_o_paragraphDecompound
where t_o_paragraphDecompound.commissary=s.CommissaryId ;
//t_o_dateTop表中下列查询返回记录数可能大于1么，若大于1，则下列语句出错
select total into v_total from t_o_dateTop order by ri desc;
v_total := v_total+s.MemberCount;
insert into t_o_dateTop(ri,area,channel,commissary,amount,total,classTop)
values(v_date,v_area,v_channel,s.CommissaryId, s.MemberCount, v_total, v_count);
commit;
END LOOP;
END;

Answer 6

to: xiaoxiao1984(笨猫儿)
CURSOR cur0 IS select CommissaryId,count(*) MemberCount from t_s_memberInfo
where TO_DATE(CreateDate,'YYYY-MM-DD') = v_date ate group by CommissaryId ;
我想按CommissaryId分组,
例如下面表数据
CommissaryId  Name
    1          a
    1          b
    2          c
    3          e
    3          f
    3          g
上面的查询语句想实现如下查询结果
CommissaryId MemberCount
      3         3
      1         2
      2         1
不知道 select CommissaryId,count(*) MemberCount from t_s_memberInfo
where TO_DATE(CreateDate,'YYYY-MM-DD') = sysdate group by CommissaryId order by  MemberCount desc 这样写可不可以.

Answer 7

v_count number(10);
v_area varchar(6);
v_channel varchar(6);
v_total number(10);
v_datecount number(10);
v_date date default sysdate;
CURSOR cur0 IS select CommissaryId,count(*) MemberCount from t_s_memberInfo
where TO_DATE(CreateDate,'YYYY-MM-DD') = v_date ate group by CommissaryId ;BEGIN
v_count := 0;
FOR s IN cur0 LOOP
v_count := v_count+1;
v_total := 0;
select area, channel into v_area, v_channel from t_o_paragraphDecompound
where t_o_paragraphDecompound.commissary=s.CommissaryId ;
select total into v_total from (select total, rownum rn from t_o_dateTop order by ri desc) where rn = 1;
v_total := v_total+s.MemberCount;
insert into t_o_dateTop(ri,area,channel,commissary,amount,total,classTop)
values(v_date,v_area,v_channel,s.CommissaryId, s.MemberCount, v_total, v_count);
commit;
END LOOP;
END;  

Answer 8

select CommissaryId, MemberCount from (
select CommissaryId,count(*) MemberCount from t_s_memberInfo
where TO_DATE(CreateDate,'YYYY-MM-DD') = sysdate group by CommissaryId 
)
order by MemberCount desc