获取顺序排列

CREATE OR REPLACE PROCEDURE Campaignprize_range( CampaignIdd NUMBER,PrizeTypeIdd NUMBER,ServerIndex VARCHAR2, RETVAL OUT VARCHAR2) IS

  hashcoded VARCHAR2(8);
   prizetypenum number(6);
   prizetypenumold number(6);
   prizetypenumc VARCHAR2(6);
   totalcount number(6);
   flag number(1);
   single number(6);
BEGIN
  flag:=0;
  RETVAL :='';
  single:=0;
  SELECT count(1) into totalcount FROM mkt_campaignprize a WHERE a.campaignid=CampaignIdd AND a.issend=0 and a.prizetypeid=PrizeTypeIdd  and a.hashcode like ''||ServerIndex||'%'  order by a.campaignprizeid asc; 
 
DECLARE CURSOR c1 IS
      SELECT a.hashcode,a.campaignprizeid  FROM mkt_campaignprize a WHERE a.campaignid=CampaignIdd AND a.issend=0 and a.prizetypeid=PrizeTypeIdd  and a.hashcode like ''||ServerIndex||'%'  order by a.campaignprizeid asc;
  BEGIN
      FOR r1 IN c1 LOOP
         single:=single+1;
         if flag=0 then
          prizetypenumold:= to_number( substr(r1.hashcode,3,6));
          RETVAL :=prizetypenumold;
           if totalcount=single then
             RETVAL :=RETVAL||','||prizetypenumold||'$';
             end if;
          flag:=1;
          else
             prizetypenum:=prizetypenumold+1;
             prizetypenumc:=lpad(prizetypenum,6,'0');
             hashcoded:=ServerIndex||prizetypenumc;
             if hashcoded=r1.hashcode then
               prizetypenumold:=prizetypenum;
             else
               RETVAL :=RETVAL||','||prizetypenumold||'$';
               prizetypenumold:= to_number( substr(r1.hashcode,3,6));
               RETVAL :=RETVAL||prizetypenumold;
             end if;
             if totalcount=single then
             RETVAL :=RETVAL||','||prizetypenumold||'$';
             end if;
          end if; 
      END LOOP;  
  END;
  Exception
     When others then
       RETVAL :='E'||SUBSTR(SQLERRM, 1, 600);
END Campaignprize_range;