求指导一下这个oracle题目，谢谢！！

Question

create or replace procedure your_pro(in_date in date,
                                     RetMemo out varchar2 --若失败，返回失败信息
                                     )
is
 v_cust_type    varchar2(20);
 v_count number;
 cursor c_find is
 select cust_id,100*round(e_money/sum(e_money)over(),4) bfb from
 (select cust_id,sum(money) e_money from cust_dep_info where mounth=in_date group by cust_id );
 cc c_find%rowtype;
begin
  RetMemo :='';
  v_count :=0;
  open c_find;
      loop
          fetch c_find into cc;
          exit when c_find%notfound;
          if cc.bfb>=0 and cc.bfb<=20 then
            v_cust_type :='核心客户';
            elsif  cc.bfb>20 and cc.bfb<=80 then
               v_cust_type :='战略客户';
            elsif cc.bfb>80 and cc.bfb<=100 then
                   v_cust_type :='普通客户';
                   end if;
          update cust_info set cust_type=v_cust_type where cust_id=cc.cust_id;
          commit;
          v_count :=v_count+1;
          end loop;
          close c_find;
          RetMemo:=RetMemo||'成功更新了'||v_count||'条数据';
exception
   when others then
     begin
      rollback;
      RetMemo := sqlerrm;
      return;
     end;
end;不过你这个分客户类型是不是分错了 ？还是我理解错了、、、

Answer 1

#1 很详细，楼主很细致的运用了开区间闭区间。
但是我也奇怪，百分比小的反而是核心客户。
还是说，在全部用户中 每个客户的money占总money的比例 逆序排列以后，取人数总数的百分比？若是我说的总人数百分比的话可以用下面的sql:[修改自1#]create or replace procedure your_pro(in_date in date,
                                     RetMemo out varchar2 --若失败，返回失败信息
                                     )
is
 v_cust_type    varchar2(20);
 cust_num number;
 v_count number;
 cursor c_find is
 select cust_id,rank() over(order by cust_money) as rnk from 
 (select cust_id,sum(money) cust_money from cust_dep_info where mounth=in_date group by cust_id );
 cc c_find%rowtype;
begin
  RetMemo :='';
  v_count :=0;
  select count(distinct cust_id) into cust_num from cust_dep_info;
  open c_find;
      loop
          fetch c_find into cc;
          exit when c_find%notfound;
          if cc.rnk>=0 and cc.rnk<=round(cust_num*0.2) then
            v_cust_type :='核心客户';
            elsif  cc.rnk>round(cust_num*0.2) and cc.bfb<=round(cust_num*0.8) then
               v_cust_type :='战略客户';
            elsif cc.bfb>round(cust_num*0.8) and cc.bfb<=cust_num then
                   v_cust_type :='普通客户';
                   end if;
          update cust_info set cust_type=v_cust_type where cust_id=cc.cust_id;
          commit;
          v_count :=v_count+1;
          end loop;
          close c_find;
          RetMemo:=RetMemo||'成功更新了'||v_count||'条数据';
exception
   when others then
     begin
      rollback;
      RetMemo := sqlerrm;
      return;
     end;
end;

Answer 2

哦 上面有的细节没改好create or replace procedure your_pro(in_date in date,
                                     RetMemo out varchar2 --若失败，返回失败信息
                                     )
is
 v_cust_type    varchar2(20);
 cust_num number;
 v_count number;
 cursor c_find is
 select cust_id,rank() over(order by cust_money) as rnk from 
 (select cust_id,sum(money) cust_money from cust_dep_info 
  where mounth=in_date group by cust_id );
 cc c_find%rowtype;
begin
  RetMemo :='';
  v_count :=0;
  select count(distinct cust_id) into cust_num from cust_dep_info 
  where mounth=in_date;
  open c_find;
      loop
          fetch c_find into cc;
          exit when c_find%notfound;
          if cc.rnk>=0 and cc.rnk<=round(cust_num*0.2) then
            v_cust_type :='核心客户';
            elsif  cc.rnk>round(cust_num*0.2) and cc.rnk<=round(cust_num*0.8) then
               v_cust_type :='战略客户';
            elsif cc.rnk>round(cust_num*0.8) and cc.rnk<=cust_num then
                   v_cust_type :='普通客户';
                   end if;
          update cust_info set cust_type=v_cust_type where cust_id=cc.cust_id;
          commit;
          v_count :=v_count+1;
          end loop;
          close c_find;
          RetMemo:=RetMemo||'成功更新了'||v_count||'条数据';
exception
   when others then
     begin
      rollback;
      RetMemo := sqlerrm;
      return;
     end;
end;