Hi Oracle [2]

/*************************************** *********
Cursor and I

When I was a senior, I was about to start looking for a job. In the crowded talent market, I approached a company that recruited VB programmers with enthusiasm. An uncle asked me, have you done a project? "No, but my VB score is good. I hope you can give me a chance. I want to try it ..." Have you ever performed databases? "Well, I did it ." Do you know what a cursor is? "……" We need experienced programmers ......
In this case, the world will only believe in powerful people. At that time there will be too few, and it will be normal if there is no chance. Therefore, you have to learn and make yourself powerful. Weak countries have no diplomacy.

/*************************************** *********
Use an explicit cursor

Define the cursor declare
Cursor cursor_name is select_statement;

Open cursor
Open cursor_name

Extract cursor fetch
Fetch cursor_name into variable1, variable2 ,...;

Close cursor close
Close cursor_name;

Explicit cursor attribute
% Isopen determine whether open is true
% Found: whether to extract data from the result set to true
% Notfound: whether the data is not extracted to true from the result set
% Rowcount returns the actual number of rows that have been extracted to the current row.

Parameter cursor

Different result sets can be generated when the cursor is opened multiple times with different parameter values.
Cursor cursor_name (parameter_name datatype) is select_statement;

Update/delete data with a cursor
Cursor cursor_name (parameter_name datatype) is select_statement for update [of column_reference] [Nowait];

For update is used to add a row share lock to the result set data to prevent other users from performing the DML operation again.
Of determine which tables need to be locked
Nowait specifies that the lock is not waiting for execution. If other sessions have been locked on the target row, an error message is displayed for the current session.

Update table_name set column = .. where current of cursor_name;
Delete table_name where current of cursor_name;

Cursor Loop

Oracle implicitly opens the cursor, extracts the cursor, and closes the cursor.
For record_name in cursor_name Loop
Statement1;
Statement2;
...
End loop;

If you do not need to use any cursor attribute during the loop, you can directly use the subquery
For record_name in (select_statement) loop
Statement1;
Statement2;
...
End loop;

Use cursor variable

Define ref cursor type and cursor variable
Type ref_type_name is ref cursor [return return_type];
Cursor_variable ref_type_name;

Open cursor
Open cursor_variable for select_statement;

Extract cursor data
Fetch cursor_variable into variable1, variable2 ,...;

Close cursor close
Close cursor_variable