Cursor overview
I believe that many Delphi programmers have written this code:
...
Begin
MyDataSet.open;
MyDataSet.frist;
While Not (MyDataSet.Bof or MyDataSet.eof) Do
Begin
...
END;
MyDataSet.Close;
END;
...
For a long time, we are accustomed to performing progressive operations with such code to return the database returned by the database. Before using the code with the client program, we use it as a disorderly collection. However, when needed, we can directly handle the data set in the server side, which is the cursor.
How to use the cursor is similar to the Delphi code in front, usually as follows:
First, declared the cursor: Declare Cursor, this process is not reflected in the previous code. However, we all know that before using a data set, we always define all of its properties, such as data sources, SQL statements, open mode, and more. In the cursor, the data source is generally not specified (because in the current database, the MS SQL Server can also read the heterogeneous data source through the SQL language). However, we have to specify a data set for it, but also specify a variety of open mode settings, such as whether to write operations, whether you can read it randomly. In general, the default cursor of the database system is read only, one-way, and is read.
Second, open the cursor: Open Cursor, a Delphi (or other development platform) data set component, after specifying the various necessary properties, you must also use an open instruction (such as a myDataset.open) to open it, Get the data set we need, for the cursor, we also need an Open instruction to open it to use it.
Third, operational data: This step typically includes mobile current cursors, reads current data, and operates three parts. The front settings of the cursor properties are a large part of what can be made about this. For example, for the two-way cursor, we can go forward or later, and random cursors, we can even specify the operational position of the cursor in random way, but the most common cursor is one-way, only one of read-only. For current line data, we can often read them by defining some variables, or move to the appropriate location, this is the same as the general development tool. As for the operation code, this is not SQL's strength, but the general database system also provides basic pro process coding capabilities, allowing us to do.
Fourth, close the result set: Close Cursor has the final event, in Delphi, if you forget to turn off your own data set, you will bring a lot of trouble, and in the database system, if you open a cursor, you have not closed (think about it, the cursor The operation is to lock the data. If there are many users who have problems with problems ... So, in the standard grammar of the cursor, there is a clear statement to turn off the data set and release the occupied. Various resources. This is more like a destructor in the OO language, more than something to do more than the CLOSE method of Delphi's data set components.
The above steps, SQL standards have agreed the corresponding code. But the implementation of each DBMS platform is similar to the difference. The problem is that this difference is enough to die. So, I don't specifically write implementation methods here. The reader can fully check the help files you use by yourself, see how you use the database to achieve the cursor, which functions have realized.
Unreasonable existence
We can see that there is a considerable difference between the cursor and the SQL language. Its implementation and operation is very complicated, and because of the equivalence operation, it is usually slower than the collection operation. How big will the gap? Extremely an extreme example: I used the cursor to fill the line number, and the results were not completed twelve hours, and the same operation, the unsuccessful connection mentioned in front of the article, as long as the three seconds bell. I guarantee that the gap will be so big in all cases, but this situation does exist. Especially when there is a large number of concurrent tasks, this long time lock is dangerous. Complex and inefficient, is the biggest shortcoming of the cursor, just these two points, it is enough to make us have a cautious attitude toward it. Moreover, in general, it is necessary to use a cursor to complete (able to live in a so-called host language, Host Language) at the client.
Perhaps, in the extreme relationship model support, the cursor is an ugly existence. In a system of managing information in a perfect, elegant, unordered collection, why should we hold a cursor in an orderly manner? However, as the "Dragon Bun History", the sacred Palatin Temple of Ista City, but wandering the most dark black Master Festan, but Tirus, in the huge rigorous relationship database, there is a cursor like this Alternative. This is the will of creating the gods of the world, their own truth.
Existence reasonable
Although there is such a shortcoming, it also has the value. First, when the data set in an orderly manner is large, especially the final calculation result is relatively small, if it is also sent to the front desk, it is too great to waste network resources. Moreover, a large data is passed, and the host language does not necessarily support such a huge data structure (such as Delphi's VCL container, which is accused in this area), which also limits our use of host language to expand the system. Functions (such as MS SQL Server and Interbase can write extended functions and extended stored procedures. In addition, if you want a lot of progressive write operations, it is often less efficient with the foreground interaction. The cursor is indeed a good way, but there is no better way, it is the best way. Then, with my experience, the cursor written by script is stronger than the maintenance and adjustability of the two code compiled after the host language.
In the public world, there is no absolute dark and bright. The key is whether we use it correctly.
Use a cursor correctly
The cursor itself does not have a so-called right mistake, but when we use it, we should think twice before.
Many times, the cursor is not necessarily the only way you imagine. I have seen too many cursor scripts, which can be done with a more concise and efficient structured operation. As long as the simple statement can achieve the same effect, do not use the cursor. "Programmer" reads a sentence: Simple is smart. This is the truth of software development.
In the cursor, it is obviously read-only, the unidirectional cursor is the fastest, and it is not easy to cause dead locks, use it as much as possible.
In the table used by the cursor, establish an appropriate index, which makes it more efficient than the general SQL statement, especially the cursor of the write operation.
The result set of the cursor operation should be as small as possible.
If there is a lot of calculations in the cursor code, consider whether it is dispersed, put it in other servers or clients.
A sufficient testing and verification of the cursor code, then put into use, especially optimizing procedures and stability. This area cannot believe the system. For example, you write a cursor, each reading a variable is accumulating a variable, and the system will never take the initiative to optimize it into count (*).
Some systems can keep the cursors that current transactions open to future transactions until it is displayed. But it is best not to use it casually. This feature certainly looks cool, but it will bring us endless trouble. Do you really need this function? When appropriate, write it into an extended stored procedure or extend storage function, link into the database system in the form of binary code. The disadvantage of this is that the flexibility is lost, and the efficiency is increased.
Attachment:
I used to enter multiple batch-executed statements in Interbase's ISQL, so what stored procedures, triggers, even cursors, are not built. Until one day, pay attention, found this in IsQL:
Set term ^;
...
^
...
^
...
Set term; ^
From the first line set term ^; start, ISQL will group the statement separated by ^ to the background until SET TERM; ^. It is a bit like the "Go" of the Query Analyzer of MS SQL Server. In this way, we can write scripts with ISQL.
