Oracle cursor usage

Cursor definition:

The role of a cursor: To obtain records returned by a query. You can retrieve a record from the cursor at a time through a query.

Use the cursor in five steps:

(1) declare a variable to save the column value of the record

(2) Declare the cursor and make a query

(3) Open the cursor

(4) obtain a record from the cursor at a time and store the column value in the variable declared in step (1 ).

(5) Close the cursor

A cursor is like a set of result sets. It allows you to obtain the results more easily.

Set serveroutput on // set the visible output of the server. This is very important. Otherwise, the output result will not be visible.

DECLARE

V_product_id products. product_id % TYPE;

V_name products. name % TYPE;

V_price products. price % TYPE;

CURSOR v_product_cursor IS

SELECT product_id, name, price

FROM products

Order by product_id;

BEGIN

OPEN v_product_cursor;

LOOP

FETCH v_product_cursor

INTO v_product_id, v_name, v_price

Exit when v_product_cursor % NOTFOUND;

DBMS_OUTPUT.PUT_LINE ('v _ product_id = '| v_product_id |', v_name = '| v_name |', v_price = '| v_price );

End loop;

CLOSE v_product_cursor;

END;

The above example shows a complete example of using a cursor.

Implicit cursor

When an operation to retrieve a record is performed, an implicit cursor is executed.

Implicit cursors use SQL % FOUND, SQL % NOTFOUND, and SQL % ROWCOUNT attributes.

SQL % FOUND, SQL % NOTFOUND is a Boolean value, and SQL % ROWCOUNT is an integer.

SQL % FOUND and SQL % NOTFOUND

Before executing any DML statement, the values of SQL % FOUND and SQL % NOTFOUND are NULL. After executing the DML statement, the attribute values of SQL % FOUND will be:

. TRUE: INSERT

. TRUE: DELETE and UPDATE. At least one row is deleted or updated.

. TRUE: select into returns at least one row.

When SQL % FOUND is TRUE, SQL % NOTFOUND is FALSE.

SQL % ROWCOUNT

Before executing any DML statement, the SQL % ROWCOUNT value is NULL. If the SELECT INTO statement is executed successfully

1. If it fails, the SQL % ROWCOUNT value is 0, and an exception NO_DATA_FOUND is generated.

SQL % ISOPEN

SQL % ISOPEN is a Boolean value. If the cursor is opened, it is TRUE. If the cursor is closed, it is FALSE. for implicit cursors, SQL % ISOPEN is always FALSE. This is because the implicit cursors are opened when DML statements are executed and are immediately closed at the end.

Display cursor

When a query returns more than one row, an explicit cursor is required. You cannot use the select into statement. PL/SQL manages implicit cursors. When the query starts, the implicit cursor is opened. When the query ends, the implicit cursor is automatically closed. The explicit cursor is declared in the declaration part of the PL/SQL block. It is opened in the execution part or Exception Handling part, data is retrieved, and disabled. The following table shows the differences between explicit and implicit cursors:

Table 1 implicit and explicit cursors

Implicit cursor	Explicit cursor
PL/SQL maintenance. It is automatically enabled and disabled when queries are executed.	In a program, the cursor is explicitly defined, opened, and closed, and has a name.
The cursor attribute prefix is SQL	The prefix of the cursor attribute is the cursor name.
Attribute % ISOPEN is always FALSE	% ISOPEN determines the value based on the cursor status
The SELECT statement has an INTO sub-string and only one row of data is processed.	It can process multiple rows of data and set a loop in the program to retrieve each row of data.

Cursor and for Loop

You can use the for loop to access records in the cursor. When you use the for loop, you can open and close the cursor without displaying it. The for loop will automatically execute these operations, which is a method to simplify the statement, see the following example.

DECLARE

CURSOR c_product_cursor IS

SELECT product_id, name, price FROM products order by product_id;

BEGIN

FOR v_product IN c_product_cursor

LOOP

DBMS_OUTPUT.PUT_LINE

('Product _ id: '| v_product.product_id | 'name:' | v_product.name | 'price: '| v_product.price );

End loop;

END;

Note that the red text above is a temporary variable and does not need to be defined, which saves some code

OPEN-FOR statement

You can use the OPEN-FOR statement FOR the cursor. Because you can allocate the cursor to Different queries, You can process the cursor more flexibly. That is to say, the volume of a cursor is defined, and the query and other statements can be changed more flexibly, so as to flexibly change the results.

DECLARE

TYPE t_product_cursor IS

Ref cursorreturn products % ROWTYPE; // defines the type of a cursor and has a return type.

V_product_cursort_product_cursor; // apply this type and define a cursor.

V_product roducts % ROWTYPE;

BEGIN

OPEN

V_product_cursor

FOR

Select * from products where product_id <5;

LOOP

FETCH v_product_cursor INTO v_product;

Exit when v_product_cursor % NOTFOUND;

DBMS_OUTPUT.PUT_LINE (

'Product _ id: '| v_product.product_id |

'Name: '| v_product.name |

'Price: '| v_product.price );

End loop;

CLOSEv_product_cursor;

END;

/

Update and delete In cursor

You can still use the UPDATE and DELETE statements to UPDATE or DELETE data rows in PL/SQL. An explicit cursor is used only when multiple rows of data are required. PL/SQL allows you to delete or update records by using only the cursor.
The where currentof substring in the UPDATE or DELETE statement is used to process the most recent data retrieved from the table for the UPDATE or DELETE operation. To use this method, you must use the for update substring when declaring the cursor. When you use the for update substring to open a cursor, all data rows in the returned result set will be locked exclusively at the row level. Other objects can only query these data rows and cannot perform UPDATE, DELETE, or SELECT... for update operation.
Syntax:

For update [OF [schema.] table. column [, [schema.] table. column]... [Nowait]

In multi-table queries, the "OF" clause is used to lock a specific table. If the "OF" clause is ignored, all selected data rows in the table are locked. If these data rows have been locked by other sessions, ORACLE will normally wait until the data row is unlocked.
The syntax for using where current of substring in UPDATE and DELETE is as follows:

WHERE {current of cursor_name | search_condition}

Example:

DELCARE CURSOR c1 is select empno, salary FROM emp WHERE comm IS NULL For update of comm; V_comm NUMBER (10, 2 ); BEGIN FOR r1 IN c1 LOOP IF r1.salary <500 THEN V_comm: = r1.salary * 0.25; ELSEIF r1.salary <1000 THEN V_comm: = r1.salary * 0.20; ELSEIF r1.salary <3000 THEN V_comm: = r1.salary * 0.15; ELSE V_comm: = r1.salary * 0.12; End if; UPDATE emp SET comm = v_comm Where current of c1; End loop; END