Oracle a sequence of fields from a field to get a continuous interval worth the stored procedure notation

CREATE OR REPLACE PROCEDURECampaignprize_range (Campaignidd Number, Prizetypeidd Number, ServerindexVARCHAR2, RETVAL outVARCHAR2) ishashcodedVARCHAR2(8); Prizetypenum Number(6); Prizetypenumold Number(6); PRIZETYPENUMCVARCHAR2(6); Datacount Number(6); TotalCount Number(6); Flag Number(1);  Single  Number(6);BEGINflag:=0; RETVAL:="';  Single:=0; SELECT Count(1) intoTotalCount fromMkt_campaignprize AWHEREA.campaignid=Campaignidd andA.issend=0  andA.prizetypeid=Prizetypeidd andA.hashcode like "'||Serverindex||'%'  Order  byA.campaignprizeidASC; DECLARE CURSORC1 is      SELECTA.hashcode,a.campaignprizeid fromMkt_campaignprize AWHEREA.campaignid=Campaignidd andA.issend=0  andA.prizetypeid=Prizetypeidd andA.hashcode like "'||Serverindex||'%'  Order  byA.campaignprizeidASC; BEGIN       forR1inchC1 LOOP Prizetypenumold:=To_number (substr (R1.hashcode,3,6));  Single:= Single+1; ifFlag=0  ThenRETVAL:=RETVAL||Prizetypenumold; ifTotalCount= Single  ThenRETVAL:=RETVAL||','||Prizetypenumold||'$'; End if; Flag:=1; ifTotalCount=1  ThenRETVAL:=RETVAL||','||Prizetypenumold||'$'; End if; ElsePrizetypenum:=Prizetypenumold+1; PRIZETYPENUMC:=Lpad (Prizetypenum,6,'0'); hashcoded:=Serverindex||PRIZETYPENUMC; Select  Count(1) intoDatacount fromMkt_campaignprize AWHEREA.campaignid=Campaignidd andA.issend=0  andA.prizetypeid=Prizetypeidd andA.hashcode=hashcoded; ifDatacount<1 or  Single=TotalCount ThenRETVAL:=RETVAL||','||Prizetypenumold||'$'; Flag:=0; End if; End if; ENDLOOP; END; Exception whenOthers ThenRETVAL:='E'||SUBSTR (SQLERRM,1, -);ENDCampaignprize_range;

A brief introduction to the following scenario: the incoming parameter Campaignidd for the active Id,prizetypeidd as the prize Id,serverindex for the server number, where the table mkt_campaignprize is a prize table for the active configuration, The purpose of this stored procedure here is to query the Hashcode field, which is automatically processed when it is inserted: it is stored according to the value of the Campaignid,prizetypeid,serverindex query increment order, but there are individual cases, such as deletion, etc. There is a vacancy in the Hashcode value here, so that the value is changed to a plurality of intervals, and the above stored procedure is the use of the form of the cursor to take the beginning of the end of each interval, and the value of this interval is to the system to cache this interval value, In this way, when the large data volume gift can only save the beginning of each interval of the value can be, the system in the actual awards do not need to be in the database to query the table of available prizes, because such a high concurrency when the back of the resource conflict, transaction submission of the peer problem, The problem is that the user in the query to the available prizes after taking away and to change the status of taking the prize record, if at the same time many people query out the same prize to update a record when there is a conflict, and now the way, the prize value of the interval into the system global cache, the use of thread synchronization lock, Allow users to queue in the cache when high concurrent access to the corresponding prizes in the record of the hashcode, while removing to get the prize, the latter will not be taken, because the cache is memory, and only in which the implementation of a simple interval is worth the algorithm so access speed, everyone queue time is very short, Everyone get their own set of data prizes hashcode after the database to check corresponding records update the corresponding records will not have a resource conflict.

Oracle a sequence of fields from a field to get a continuous interval worth the stored procedure notation