SQL must-know note the 21st Chapter uses cursors

21.1 Cursors

The SQL retrieval operation returns a set of rows called the result set. The rows returned by this group are rows that match the SQL statement.
Result sets (result set) the results retrieved by the SQL query
Some options and features of the cursor:
(1) The ability to mark cursors as read-only, so that data can be read, but not updated and deleted.
(2) can control the directional operation that can be performed.
(3) Can mark some of the listed as editable, some listed as non-editable.
(4) Specify the scope to make the cursor accessible to the specific request that created it or to all requests.
(5) Instructs the DBMS to replicate the retrieved data so that the data does not change during cursor opening and access.

21.2 using Cursors

Steps to use cursors:
(1) Before a cursor can be used, it must be declared (defined). This process does not actually retrieve the data, he simply defines the SELECT statement and cursor options to use.
(2) Once declared, the cursor must be opened for use. This process is actually retrieved using the previously defined SELECT statement data.
(3) For cursors filled with data, remove (retrieve) rows as needed.
(4) At the end of the cursor use, the cursor must be closed and, if possible, the cursor is freed.
After you declare a cursor, you can open and close the cursor frequently as needed. When the cursor is open, the fetch operation can be performed frequently as needed.

21.2.1 Creating Cursors

Use declare to name the cursor and define the corresponding SELECT statement, with where and other clauses as needed.
Create a cursor that retrieves all customers without an e-mail address. Oracle version.

DECLARE CURSOR CustCursorISSELECTFROM CustomersWHEREIS NULL

21.2.2 using Cursors

The cursor opens the cursor with the open cursor.
OPEN CURSOR Custcursor
The cursor data is accessed with a FETCH statement after the cursor is opened. Fetch indicates the rows to retrieve, where they are retrieved, and where they are placed.
To retrieve a row from a cursor using the Oracle syntax:

DECLAREIS REF CURSOR     RETURN Customers%ROWTYPE;DECLARE CustRecord Customers%ROWTYPEBEGIN     OPEN CustCursor;     INTO CustRecord;     CLOSE CustCursor;END;

Loop through the retrieved data from the first line to the last line:

DECLAREIS REF CURSOR     RETURN Customers%ROWTYPE;DECLARE CustRecord Customers%ROWTYPEBEGIN     OPEN CustCursor;     LOOP     INTO CustRecord;     EXIT　WHEN CustCursor%NOTFOUND;     ...     ENDLOOP;     CLOSE CustCursor;END;

21.2.3 closing Cursors

CLOSE CustCursor;

The close statement is used to close the cursor, and once the cursor is closed, it cannot be used if it is no longer open. However, for use it does not need to be declared again, just open it again.

SQL must-know note the 21st Chapter uses cursors