Oracle cursor, exception, stored procedure usage

A small memory process is written in the project. The function is to record the corresponding file serial number for certain personnel types. Summary:

Prefix: create or replace procedure insert_serial

As is followed by the applied variable:

PerNum_str person_info.person_no % type; indicates that the declared variable perNum_str has the same type as person_info.person_no.

Cursor cur_perInfo is to define a cursor
Select person_no, person_type, hj_area_id indicates that the cursor corresponds to the query result set.
From person_info;

    insert_serial  cur_perInfo      person_info;

Start to traverse the result set:

First, declare and open the cursor. Read data must be completed using the fetch statement. In turn, the result set of each row is placed in the declared variable.

If you open the cursor explicitly, you must add the % found attribute to judge. Otherwise, when the cursor reaches the last row, the result set of the last row will be retrieved in an infinite loop and the loop will not be automatically introduced.

End loop, commit transaction commit, close cursor

You can also use for... in ..:

If you use the for... in... statement to traverse the result set, you do not need to declare that the cursor is opened. (The result can be obtained directly after the variable v is connected to the field name)

Process the result set retrieved from the cursor:

If the cursor has no valid data, jump to exit to launch a loop.

Select... into... from implicit cursor, which means to put the query result into the corresponding variable. In this way, only one row of data can be found at a time. If there are multiple rows (too_many_rows) or no data is found (no_data_found), the program will throw an exception and interrupt the loop.

Therefore, begin... exception (when then end;) is added ;):

When a goto jump is used, if an exception occurs, it jumps to <ponit1> and continues the loop.

In oracle, ": =" indicates a value, and "=" indicates an equivalent value. "|" Indicates String concatenation.

When to_char () is used to format a number, the highest character bit after the conversion format is automatically used to save the number symbol. Therefore, when formatting a positive number, the highest digit will have an extra space. You can use "fm" to remove spaces.

  cur_perInfofound                nvl(cur_num,  area_id  cur_area_id  NO_DATA_FOUND            TOO_MANY_ROWS            licNum_num    licNum_num   cur_num  area_id  cur_area_id  DOCUMENT_NO (licType_strto_char(licNum_num, person_no  point1             ;

Complete code:

    insert_serial  cur_perInfo        cur_perInfofound                nvl(cur_num,  area_id  cur_area_id  NO_DATA_FOUND            TOO_MANY_ROWS            licNum_num    licNum_num   cur_num  area_id  cur_area_id  DOCUMENT_NO (licType_strto_char(licNum_num, person_no  point1              insert_serial;