MS SQL Basics Tutorial: Advantages and types of cursors, cursors

The first half of this chapter provides the necessary knowledge and various syntax for cursors that should be applied to the application of cursors in MS SQL SERVER. The reader can learn about the advantages, types, roles of cursors, learn how to define, open, access, close, release cursors, and apply cursors. In addition, we introduced views and user-defined functions in the second half of this chapter. Make the reader aware of many of the benefits of the view, such as simplifying operations, improving data security, understanding how to create, manage views, and user-defined functions, and understanding how to invoke user-defined functions in stored procedures and batches.

During database development, when you retrieve data that is just a record, the transaction statement code you write often uses a SELECT INSERT statement. But we often encounter situations where one record is read from one result set to another. So how do you solve this problem? Cursors provide us with a very good solution.

Advantages of 13.1.1 Cursors and cursors

In the database, cursors are a very important concept. Cursors provide a flexible means of manipulating data retrieved from a table, essentially, a cursor is actually a mechanism for extracting one record at a time from a result set that includes multiple data records. Cursors are always associated with a T_SQL selection statement because the cursor consists of a result set (multiple records that can be retrieved by 0, one, or a related selection statement) and a cursor position in the result set that points to a specific record. When you decide to process a result set, you must declare a cursor that points to the result set. If you've ever written a program that handles files in C, the cursor is just like the file handle you get when you open the file, and the file handle can represent that file as long as the file is opened successfully. For cursors, the rationale is the same. A visible cursor enables the processing of a result set from the underlying table in a similar way to reading a flat file from a traditional program, thus rendering the data in the table as a flat file to the program.

We know that relational database management systems are essentially collection-oriented, and there is no representation in MS SQL SERVER that describes a single record in a table unless a WHERE clause is used to restrict that only one record is selected. Therefore, we must use the cursor to carry on the data processing which faces the single record.

This shows that the cursor allows the application to perform the same or different operations on each row of the row result set returned by the query statement select, rather than at one time for the entire result sets, and it provides the ability to delete or update data in the table based on the cursor position; It is the cursor that links the database management system which is a set-oriented and the line-oriented program design, so that two data processing methods can communicate.

13.1.2 cursor Type

MS SQL Server supports three types of cursors: Transact_sql cursors, API server cursors, and client cursors.

(1) Transact_sql cursors

TRANSACT_SQL cursors are defined by the declare CURSOR syntax and are primarily used in transact_sql scripts, stored procedures, and triggers. Transact_sql cursors are primarily used on servers and are managed by TRANSACT_SQL statements sent from clients to the server, or by batches, stored procedures, and transact_sql in triggers. Transact_sql cursors do not support extracting blocks of data or multiple rows of data.

(2) API cursors

API cursors support the use of cursor functions in OLE DB, ODBC, and Db_library, primarily on servers. Each time a client application invokes an API cursor function, the MS SQL SEVER OLE DB provider, ODBC Drive, or db_library dynamic-link library (DLL) sends these client requests to the server for processing API cursors.

(3) Customer cursors

Client cursors are used primarily when a result set is cached on a client computer. In a client cursor, a default result set is used to cache the entire result set on the client. Client cursors support only static cursors, not dynamic cursors. Because server cursors do not support all Transact-SQL statements or batches, client cursors are often used only as a helper for server cursors. Because in general, server cursors can support most cursor operations.

Because API cursors and Transact-SQL cursors are used on the server side, they are called server cursors, also known as background cursors, and client cursors are called foreground cursors. In this chapter we focus on server (background) cursors.

See the full set of "MS SQL Basics Tutorials"