SQL is required. Note chapter 21st uses the cursor. SQL is required.
21.1 cursor
An SQL search operation returns a group of rows called result sets. The Rows returned in this group are all rows that match the SQL statement.
Result set
Some options and features of a cursor:
(1) The cursor can be marked as read-only, so that data can be read but cannot be updated or deleted.
(2) can control the targeted operations that can be performed.
(3) Some columns can be marked as editable and some Columns cannot be edited.
(4) specifies the scope for the cursor to be accessible to a specific request for creating it or to all requests.
(5) Instruct the DBMS to copy the retrieved data so that the data does not change during the cursor opening and access.
21.2 use a cursor
To use a cursor:
(1) Before using a cursor, you must declare (define) it. This process does not actually retrieve data. It only defines the SELECT statement and cursor options to be used.
(2) once declared, the cursor must be opened for use. This process is actually retrieved using the SELECT statement defined earlier.
(3) For a cursor with data filled in, retrieve (retrieve) each row as needed.
(4) when using the end cursor, you must close the cursor. If possible, release the cursor.
After the cursor is declared, the cursor can be opened and closed frequently as needed. When the cursor is opened, you can perform the fetch operation frequently as needed.
21.2.1 create a cursor
Use DECLARE to name the cursor and define the SELECT statement. The WHERE clause and other clauses are included as needed.
Create a cursor for retrieving all customers without email addresses. Oracle version.
DECLARE CURSOR CustCursorISSELECT * FROM CustomersWHERE cust_emai IS NULL
21.2.2 Use cursor
Use open cursor to OPEN a CURSOR.
Open cursor CustCursor
Open the cursor and use the FETCH statement to access the cursor data. FETCH indicates the rows to be retrieved, where they are retrieved, and where they are stored.
Use Oracle syntax to retrieve a row from the cursor:
DECLARE TYPE CustCursor IS REF CURSOR RETURN Customers%ROWTYPE;DECLARE CustRecord Customers%ROWTYPEBEGIN OPEN CustCursor; FETCH CustCursor INTO CustRecord; CLOSE CustCursor;END;
Loop the retrieved data from the first row to the last row:
DECLARE TYPE CustCursor IS REF CURSOR RETURN Customers%ROWTYPE;DECLARE CustRecord Customers%ROWTYPEBEGIN OPEN CustCursor; LOOP FETCH CustCursor INTO CustRecord; EXIT WHEN CustCursor%NOTFOUND; ... END LOOP; CLOSE CustCursor;END;
21.2.3 close the cursor
CLOSE CustCursor;
The CLOSE statement is used to CLOSE the cursor;Once the cursor is closed, it cannot be used if it is no longer enabled. However, to use it, you do not need to declare it again. You only need to OPEN it again.