存储过程生成编号（游标、GOTO等）

存储过程生成编号（游标、GOTO等）

create or replace procedure PRO_UPDATE_SE_BNUM
       (in_T1_ID IN T1.T1_ID%TYPE,in_T2_PNUM IN T2.PRJNUM%TYPE) is
--更新编号
       BUILDNUM_INDEX NUMBER:=0;
       BUILDNUM_LEN NUMBER;
       BUILDNUM_ZERO VARCHAR2(10);
       BUILDNUM_ROWNUM NUMBER:=0;
       SID T3.ID%TYPE;        
       TEMPNUM T3.T3_PNUM%TYPE;
       TEMP_FLAG NUMBER:=0;--标志位 0 1

CURSOR SINGLE_CUR IS 
       SELECT S.ID,S.T3_PNUM FROM T3 S WHERE S.T1_ID = in_T1_ID ORDER BY TO_NUMBER(S.ID);    
begin 

      OPEN SINGLE_CUR;
           FETCH SINGLE_CUR INTO SID,TEMPNUM;                 
           LOOP
              EXIT WHEN NOT SINGLE_CUR%FOUND;

              SELECT (3-LENGTH(TO_CHAR(BUILDNUM_INDEX+1))) INTO BUILDNUM_LEN FROM DUAL; 
              WHILE BUILDNUM_LEN>0 LOOP
                BUILDNUM_ZERO := BUILDNUM_ZERO || '0';
                BUILDNUM_LEN := BUILDNUM_LEN - 1;
              END LOOP;             

              <<doBUILDNUM>> --循环点
              IF TEMP_FLAG = 0 THEN
                BUILDNUM_INDEX := BUILDNUM_INDEX + 1;                
                SELECT  count(1) INTO BUILDNUM_ROWNUM FROM T3 S WHERE S.T3_PNUM = (in_T2_PNUM||'-'||BUILDNUM_ZERO||BUILDNUM_INDEX);
                IF BUILDNUM_ROWNUM > 0 THEN                  
                  GOTO doBUILDNUM;
                ELSE
                  TEMP_FLAG := 1;  
                END IF;
              END IF;                

              IF LENGTH(TEMPNUM) < 10 OR LENGTH(TEMPNUM) IS NULL THEN  
                TEMP_FLAG := 0;
                UPDATE T3 SET T3_PNUM=(in_T2_PNUM||'-'||BUILDNUM_ZERO||BUILDNUM_INDEX) WHERE ID = SID;
                COMMIT;
              END IF; 

              BUILDNUM_ZERO := '';
              BUILDNUM_ROWNUM :=0;
              FETCH SINGLE_CUR INTO SID,TEMPNUM;   
           END LOOP;
      CLOSE SINGLE_CUR;      

      Exception
        When others then
          Rollback;
          Dbms_Output.put_line(Sqlerrm);        

end PRO_UPDATE_SE_BNUM;