The first half of this chapter provides the necessary knowledge and various grammar applications related to the cursor in MS SQL Server. The readers can understand the advantages, types, effects of the cursor, learn how to define, open, access, close, release cursors, and cursors. In addition, in the second half of this chapter, we introduce views and user-defined functions to enable readers to understand the many advantages of the view, such as simplifying operations, improving data security; learning how to create, manage view, and user-defined functions; Understand how many issues such as user-defined functions in stored procedures and batch.
During the database development, when the data you retrieve is just a record, the transaction statement code you have written often uses the SELECT INSERT statement. But we often encounter such a record from one of the results to read a record one by one. So how do you solve this problem? The cursor provides us with an extremely excellent solution.
13.1.1 The advantages of the cursors and cursors are in the database, the cursor is a very important concept. The cursor provides a flexible means of operating the data detected from the table, in essence, the cursor is actually a mechanism for extracting a record from the results of the result of multiple data records. The cursor is always associated with a T_SQL selection statement because the cursor consists of a result set (can be zero, one or a plurality of records retrieved by the related selection statement) and the result set points to the cursor position of the specific record. When it is decided to process the result set, a cursor pointing to the result set must be declared. If you have written a file for a file with a C language, the cursor is like the file handle you get the file, as long as the file is opened success, the file handle can represent the file. For the cursor, the truth is the same. The visible sign can implement similar to the conventional program to read a flat file similar to the conventional program, which presented the data in the form to the program in the form of a flat file.
We know that the relationship database management system is essentially a collection, and there is no form in the MS SQL Server, there is no expression form in the table, unless the WHERE clause is used to limit only one record is selected. Therefore, we must carry out data processing for single records by means of a cursor.
It can be seen that the cursor allows the application to perform the same or different operations in each line of row results returned by the query statement Select, not one operation of the entire result set; it also provides data on the table based on the cursor position The ability to delete or update; but, the cursor is associated with a set of aggregated database management systems and a line-oriented programming, so that two data processing methods can communicate.
13.1.2 Telings
MS SQL Server supports three types of cursors: Transact_SQL cursors, API server cursors, and customer cursors.
(1) Transact_sql cursor
The Transact_SQL cursor is defined by the Declare Cursor syntax, mainly used in the Transact_SQL script, stored procedures, and triggers. The Transact_SQL cursor is mainly used on the server, which is managed by the transact_sql statement from the client to the server or a batch, stored procedure, and transact_sql in the trigger. Transact_sql cursors do not support extracting data blocks or multi-wire data.
(2) API cursor
The API cursor supports the use of cursor functions in OLE DB, ODBC, and DB_Library, mainly used on the server. Each client application call API cursor function, MS SQL SEVER's OLE DB provider, ODBC drive, or DB_Library dynamic link library (DLL) will transfer these clients to the server to process the API cursor.
(3) Customer cursor
Customer cursors are mainly used when the cache result is in the client. In the customer's cursor, there is a default result set is used to cache the entire result set on the client. Customer cursors only support static cursors rather than dynamic cursors. Because the server cursor does not support all Transact-SQL statements or batch, customer cursors are often only the aids of server cursors. Because in general, server cursors can support most of the cursor operations. Since the API cursor and the Transact-SQL cursor are used on the server, it is called the server cursor, also known as the background cursor, and the client game is called a front game. In this chapter, we mainly tell the server (background) cursor.
