Oracle Stored Procedure + cursor processing select data

Oracle Stored Procedure + cursor processing select data

Create or replacePROCEDURE UPDATE_RECORDCODE is cursor location_data is select * from location where remark in ('20160301', '20160301', '20160301'); -- declare the cursor serviceCode NUMBER: = 1; BEGIN for l in location_data loop -- traverse cursor BEGIN -- business processing UPDATE SERIAL_CODE SET CUR_NUMBER = CUR_NUMBER + 1 where AREA_CODE = l. area_code and LOC_TYPE = l. type; select CUR_NUMBER into serviceCode from SERIAL_CODE where AREA_CODE = l. area_code and LOC_TYPE = l. type; update location lu set putrecord_code = (l. area_code | '0' | l. type | lpad (serviceCode, 6, 0) where lu. id = l. id; -- dbms_output.put_line (ServiceCode); EXCEPTION -- EXCEPTION Handling WHEN NO_DATA_FOUND then insert into SERIAL_CODE (AREA_CODE, LOC_TYPE, CUR_NUMBER) VALUES (l. area_code, l. type, 1); end loop; commit; END UPDATE_RECORDCODE;