SQL is required. Note chapter 21st uses the cursor. SQL is required.

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.