Sail Soft Report Finereport data Connection Midstream standard Problem Solution Summary

1. Concept

In a database, cursors are a very important concept. A cursor is a mechanism that extracts one record at a time from a result set that includes multiple data records.

After retrieving data from a database in the SQL language, the result is placed in an area of memory, often a collection of multiple records. The cursor mechanism allows the user to access each record line by row in this result set and to display and process the records at the user's own will.

2. Standard notation (Oracle as an example)

Declare

Ursor mycur is select *from Books;----Define Cursors

Myrecord Books%rowtype; ----%rowtype represents the row data type, Myrecord stores one row of data in the books

Begin

Open mycur;

Fetch mycur into Myrecord; ----Deposit one row of data to Myrecord

While Mycur%found loop----%found Boolean, True if there are rows returned

Dbms_output.put_line (myrecord.books_id| | ', ' | | Myrecord.books_name); ---output information

Fetch mycur into Myrecord; ----deposit to the next line

End Loop;

Close mycur;---closing cursors

End

/

Description

Use the cursor to access the result set obtained from the Query books table line by row, outputting the BookID and bookname in each record. As you can see from this example, the cursor contains two things:

Cursor result set: The result set from which the SELECT statement is executed.

Cursor position: A pointer to a record in the result set of a cursor.

Cursors can be used to manipulate each row in the result set independently. A cursor has two states after it is defined: off and on. When a cursor is closed, its query result set does not exist, and the data in the result set can be read or modified by rows only when the cursor is open. After general use, do not write close mycur and compile errors will not occur.

3. Advantages of Cursors

Allows the program to perform the same or different operations on each row in the rowset returned by the query statement select, rather than performing the same operation on the entire rowset;

It also provides the ability to delete or update data in a table based on the cursor position;

As a collection-oriented database management system and line-oriented programming, cursors are linked together to enable two data processing methods to communicate.

In the above section, we have a detailed understanding of the contents of the cursor. However, when using cursors, there are often problems. The following is an introduction to some of the issues that occur when too many cursors are open.

4. Common error

For example: In an Oracle database, the following information is reported:

Caused by:ora-01000: Maximum number of open cursors exceeded

In an Oracle system, the number of open cursors is limited by the parameter open_cursors, which is reported when the cursor is opened above this number, indicating that the maximum number of open cursors is exceeded. There is no upper limit on the number of cursors, nor is the bigger the better.

There is a test that shows that when the open_cursor value is a certain amount, the memory shrinks, and the shrinkage is the decrease in the number of connections.

As the conclusion can be specific test, the test results are as follows:

The connection to the test database, which is set to the following values, differs greatly from the number of users that can be connected (exclusive mode) under the same memory configuration.

open_cursors=6000000000-------------number of connections 45

The number of open_cursors=300---------------------connections exceeds 500.

From the above test, when the open_cursors expanded 20 million times times the number of connections into the original one-tenth, so the size of open_cursors to the number of connections is not small, as long as not on the tens increase, the general thousands of tens of thousands of and dozens of, the impact of the number of connections is very small.

4.1 Solutions

The cursor must be closed after the cursor is used in the normal notation.

You should avoid the cursor opening too large, which can be made as large as possible by modifying the open_cursors size.

4.2 Checking open_cursors value and modification method

Sql>show parameter open_cursors; ---display parameter values

Sql>alter system Set open_cursors = 1000; ---Modify the parameter value to 1000

sql>commit;----Submit

The modification was successful.

Sail Soft Report Finereport data Connection Midstream standard Problem Solution Summary