SQL cursor principle and usage _ MySQL

SQL cursor principle and usage

During database development, when the data you retrieve is only one record, the transaction statement code you write usually uses the select insert statement. However, we often encounter this situation, that is, reading a record from a result set one by one. How can this problem be solved? Cursors provide us with an excellent solution.
1.1 Advantages of cursors and cursors
In databases, cursor is a very important concept. A cursor provides a flexible means to operate the data retrieved from a table. in essence, a cursor is actually a mechanism that can extract a record from a result set that contains multiple data records. A cursor is always associated with a T_ SQL selection statement because it is a result set (it can be zero or one or multiple records retrieved by the relevant selection statement) and the cursor position in the result set pointing to a specific record. When processing a result set, a cursor pointing to the result set must be declared. If you have written a program to process a file in C language, the cursor is like the file handle you get when you open the file. if the file is successfully opened, the file handle can represent the file. For a cursor, the principle is the same. The visible cursor can process the result set from the basic table in a similar way as the traditional program reads the flat file, so as to present the data in the table to the program in the form of a flat file.
We know that the relational database management system is actually set-oriented. in ms SQL SERVER, there is no way to describe a single record in a table, unless you use the where clause to limit that only one record is selected. Therefore, we must use the cursor to perform the operation? Why? Why? BR> The cursor allows the application to perform the same or different operations on each row in the row result set returned by the select statement, rather than performing the same operation on the entire result set at a time; it also provides the ability to delete or update table data based on the cursor position. In addition, it is the combination of the cursor as a collection-oriented database management system and row-oriented programming, enable communication between the two data processing methods.
1.2 cursor type
Ms SQL server supports three types of cursors: Transact_ SQL, API SERVER, and customer cursors.
(1) Transact_ SQL cursor
Transact_ SQL CURSOR is defined by the declare cursor syntax and is mainly used in Transact_ SQL scripts, stored procedures, and triggers. Transact_ SQL cursors are mainly used on servers. Transact_ SQL statements sent from clients to servers or Transact_ SQL statements in batch processing, stored procedures, and triggers are managed. Transact_ SQL cursor does not support extracting data blocks or multiple rows.
(2) API cursor
API cursors support using cursors in ole db, ODBC, and DB_library, and are mainly used on servers. Each client application calls the API cursor function, ms SQL SEVER's OLE DB provider, ODBC drive, or DB_library dynamic link library (DLL) all these client requests are sent to the server to process the API cursor.
(3) customer cursor
Client cursors are used only when result sets are cached on the client. In a client cursor, a default result set is used to cache the entire result set on the client. Client cursors only support static cursors rather than dynamic cursors. Because server cursors do not support all Transact-SQL statements or batch processing, client cursors are often used only as an aid for server cursors. Generally, server cursors support the vast majority of cursors.
Because API cursor and Transact-SQL cursor are used on the server side, they are also called Server cursor and background cursor, while client cursor is called foreground cursor. In this chapter, we will focus on server (background) cursors.
Select count (id) from info
Select * from info
-- Clear all records
Truncate table info
Declare @ I int
Set @ I = 1
While @ I <1000000
Begin
Insert into info values ('Justin '+ str (@ I), 'Shenzhen' + str (@ I ))
Set @ I = @ I + 1
End
1.3 cursor operation
There are four basic steps to use a cursor: declare the cursor, open the cursor, extract data, and close the cursor.

Declared cursor
Like other types of variables, declare a cursor before using it. The declaration of a cursor includes the name of the cursor and the SQL statement used by the cursor. To declare a cursor called cus-tomercursor to query the name, account, and balance of a customer whose address is located in Beijing, you can write the following code:
DECLARE mermercursor CURSOR
SELECT acct_no, name, balance
FROM customer
WHERE province = "Beijing ";
In the declaration of the cursor, it is worth noting that, like the Declaration of other variables, the code line that declares the cursor is not executed, you cannot set the breakpoint for debugging on this line of code or use IF... the end if statement is used to declare two cursors with the same name. The following code is incorrect.
IF Is_prov = "Beijing" THEN
DECLARE mermercursor CURSOR
SELECT acct_no, name, balance
FROM customer
WHERE province = "Beijing ";
ELSE
DECLARE mermercursor CURSOR
SELECT acct_no, name, balance
FROM customer
WHERE province <> "Beijing ";
END IF
Open cursor
The cursor must be opened before other operations are performed. To open a cursor is to execute an SQL statement related to it. for example, to open a cursor declared in the previous example, you only need to type:
OPEN CustomerCursor;
Opening a cursor is a SQL SELECT operation on the database. it takes a while, depending on the system performance and the complexity of the statement. If the execution takes a long time, you can change the mouse displayed on the screen to hourglass.
Extract data
When a cursor is opened with an OPEN statement and a query is executed in the database, you cannot use the data in the query result set immediately. You must use the FETCH statement to obtain data. A fetch statement can put a record into a variable specified by the programmer at a time. In fact, the FETCH statement is the core of the cursor. In DataWindow and DataStore, after the Retrieve () function is executed, all the query results can be obtained. with the cursor, we can only obtain the query results one by one.
After a cursor has been declared and opened, we can put the data into any variable. In the FETCH statement, you can specify the cursor name and the target variable name. For example:
FETCH CustmerCur-sor
INTO: ls_acct_no,
: Ls_name,
: Ll_balance;
In terms of syntax, the above is a legal statement for getting data, but generally we should use a cursor to include other parts. As we mentioned earlier, a cursor can only retrieve one record from the background database at a time. In most cases, what we want to do is to extract the first record from the database, until the end. Therefore, we usually need to put the statements for data extraction from the cursor in a loop until all the data in the result set is extracted and jumps out of the loop. By checking the value of the SQLCA.SQL-CODE, you can know whether the last FETCH statement was successful. Generally, when the SQLCODE value is 0, it indicates that everything is normal. 100 indicates that it has reached the end of the result set, and other values indicate that the operation is faulty, so that we can write the following code:

BitsCN.com