Removing from ms sql cursor. Cursors in MySQL stored procedures. A. Using a simple cursor and syntax
Cursor concept
A query against a relational database typically returns multiple rows (records) of data, but the application only processes one record at a time. Even if it deals with several rows at the same time (for example, displaying data in the form of spreadsheets), their number is still limited. In addition, when modifying, deleting, or adding data, the work unit is the series. In this situation, the concept of a cursor comes to the fore, and in this context, the cursor is a pointer to a row.
A cursor in SQL is an area in database memory that is designed to hold the last SQL statement. If the current statement is a database query, a row of query data called the current value, or current cursor line, is also stored in memory. The specified area in memory is named and accessible to application programs.
Typically, cursors are used to select from a database a subset of the information stored in it. At any given time, one cursor line can be checked by the application program. Cursors are often used in SQL statements embedded in application programs written in procedural languages. Some of them are implicitly created by the database server, while others are defined by programmers.
In accordance with the SQL standard, when working with cursors, the following main actions can be distinguished:
- creation or cursor declaration;
- opening cursor, i.e. filling it with data that is stored in multi-level memory;
- selection from cursor and changing data rows with it;
- closing the cursor, after which it becomes inaccessible to user programs;
- freeing the cursor, i.e. deleting the cursor as an object because closing it does not necessarily free the memory associated with it.
The definition of a cursor may vary slightly across implementations. For example, sometimes a developer must explicitly free the memory allocated for a cursor. After release the cursor its associated memory is also freed. This makes it possible to reuse his name. In other implementations when closing the cursor freeing memory occurs implicitly. Immediately after recovery, it becomes available for other operations: opening another cursor etc.
In some cases, using a cursor is unavoidable. However, if possible, this should be avoided and work with standard data processing commands: SELECT, UPDATE, INSERT, DELETE. In addition to the fact that cursors do not allow modification operations on the entire volume of data, the speed of performing data processing operations using a cursor is noticeably lower than that of standard means SQL.
Implementation of cursors in MS SQL Server environment
SQL Server supports three types of cursors:
- SQL cursors are used primarily within triggers, stored procedures, and scripts;
- server cursors operate on the server and implement the application programming interface for ODBC, OLE DB, DB_Library;
- Client cursors are implemented on the client itself. They fetch the entire result set of rows from the server and store it locally, which speeds up data processing by reducing wasted time spent on network operations.
Different types of multi-user applications require different types of parallel access to data. Some applications require immediate access to information about changes to the database. This is typical for ticket reservation systems. In other cases, such as statistical reporting systems, data stability is important because if it is constantly being modified, programs will not be able to display information effectively. Different applications need different implementations of cursors.
In SQL Server, cursor types vary in the capabilities they provide. The cursor type is determined at the stage of its creation and cannot be changed. Some types of cursors can detect changes made by other users to rows included in the result set. However, SQL Server only tracks changes to such rows while the row is being accessed and does not allow changes to be modified once the row has already been read.
Cursors are divided into two categories: sequential and scrollable. Consecutive allow you to select data in only one direction - from beginning to end. Scrollable cursors provide greater freedom of action - it is possible to move in both directions and jump to an arbitrary row of the cursor's result set. If the program is able to modify the data that the cursor points to, it is called scrollable and modifiable. Speaking of cursors, we should not forget about transaction isolation. When one user modifies a record, another reads it using their own cursor, and moreover, he can modify the same record, which makes it necessary to maintain data integrity.
SQL Server supports static, dynamic, sequential and controlled by a set of keys.
In the scheme with static cursor information is read from the database once and stored as a snapshot (as of some point in time), so changes made to the database by another user are not visible. For a while opening the cursor the server sets a lock on all rows included in its full result set. Static cursor does not change after creation and always displays the data set that existed at the time of its opening.
If other users change the data included in the cursor in the source table, this will not affect the static cursor.
IN static cursor It is not possible to make changes, so it always opens in read-only mode.
Dynamic cursor maintains data in a “live” state, but this requires network and software resources. Using dynamic cursors a complete copy of the source data is not created, but a dynamic selection is performed from the source tables only when the user accesses certain data. During the fetch, the server locks the rows, and any changes the user makes to the full result set of the cursor will be visible in the cursor. However, if another user has made changes after the cursor has fetched the data, they will not be reflected in the cursor.
Cursor controlled by a set of keys, is in the middle between these extremes. Records are identified at the time of sampling, and thus changes are tracked. This type of cursor is useful when implementing scrolling back - then additions and deletions of rows are not visible until the information is updated and the driver selects new version records, if changes have been made to them.
Sequential cursors are not allowed to fetch data in the reverse direction. The user can only select rows from the beginning to the end of the cursor. Serial cursor does not store a set of all rows. They are read from the database as soon as they are selected in the cursor, which allows all changes made by users to the database to be dynamically reflected using INSERT, UPDATE, DELETE commands. The cursor shows the most recent state of the data.
Static cursors provide a stable view of the data. They are good for information "warehousing" systems: applications for reporting systems or for statistical and analytical purposes. Besides, static cursor copes better than others with sampling large amounts of data. In contrast, electronic purchasing or ticket reservation systems require dynamic perception of updated information as changes are made. In such cases it is used dynamic cursor. In these applications, the amount of data transferred is typically small and accessed at the row (individual record) level. Group access is very rare.
Cursor management in MS SQL Server environment
Cursor control implemented by executing the following commands:
- DECLARE - creation or cursor declaration;
- OPEN – opening cursor, i.e. filling it with data;
- FETCH selection from cursor and changing data rows using the cursor;
- CLOSE – closing the cursor;
- DEALLOCATE – freeing the cursor, i.e. deleting the cursor as an object.
Cursor Declaration
The SQL standard provides the following command to create a cursor:
Using the INSENSITIVE keyword will create static cursor. Data changes are not allowed, and changes made by other users are not displayed. If the INSENSITIVE keyword is missing, a dynamic cursor.
When you specify the SCROLL keyword, the created cursor can be scrolled in any direction, allowing you to use any selection commands. If this argument is omitted, the cursor will be consistent, i.e. its viewing will be possible only in one direction - from beginning to end.
The SELECT statement specifies the body of the SELECT request, which determines the resulting set of rows for the cursor.
Specifying FOR READ_ONLY creates a read-only cursor and does not allow any modifications to the data. It differs from static, although the latter also does not allow data to be changed. Can be declared as a read-only cursor dynamic cursor, which will allow changes made by another user to be displayed.
Creating a cursor with a FOR UPDATE argument allows you to execute in the cursor data change either in the specified columns or, in the absence of the OF column_name argument, in all columns.
In the MS SQL Server environment, the following syntax for the cursor creation command is accepted:
<создание_курсора>::= DECLARE cursor_name CURSOR FOR SELECT_statement ]]
Using the LOCAL keyword will create a local cursor that is visible only within the scope of the package, trigger, stored procedure, or custom function. When a package, trigger, procedure, or function terminates, the cursor is implicitly destroyed. To pass the contents of the cursor outside the construct that created it, you must assign an OUTPUT argument to its parameter.
If the GLOBAL keyword is specified, a global cursor is created; it exists until the current connection is closed.
Specifying FORWARD_ONLY creates serial cursor; Data can only be sampled in the direction from the first row to the last.
Specifying SCROLL creates scrollable cursor; Data can be accessed in any order and in any direction.
Specifying STATIC creates static cursor.
Specifying KEYSET creates a key cursor.
Specifying DYNAMIC creates dynamic cursor.
If you specify the FAST_FORWARD argument for a READ_ONLY cursor, the created cursor will be optimized for quick access to the data. This argument cannot be used in conjunction with the FORWARD_ONLY or OPTIMISTIC arguments.
A cursor created with the OPTIMISTIC argument prevents modification or deletion of rows that were modified after opening the cursor.
By specifying the TYPE_WARNING argument, the server will inform the user of an implicit change to the cursor type if it is incompatible with the SELECT query.
Opening the cursor
For opening the cursor and filling it with data from the SELECT query specified when creating the cursor, use the following command:
After opening the cursor The associated SELECT statement is executed, the output of which is stored in multi-level memory.
Retrieving data from a cursor
Right after opening the cursor you can select its contents (the result of executing the corresponding query) using the following command:
Specifying FIRST will return the very first row of the cursor's complete result set, which becomes the current row.
Specifying LAST returns the most recent row of the cursor. It also becomes the current line.
Specifying NEXT returns the row immediately after the current one in the full result set. Now it becomes current. By default, the FETCH command uses this method for fetching rows.
The PRIOR keyword returns the row before the current one. It becomes current.
Argument ABSOLUTE (line_number | @line_number_variable) returns a row by its absolute ordinal number in the cursor's complete result set. The line number can be specified using a constant or as the name of a variable in which the line number is stored. The variable must be an integer data type. Both positive and negative values are indicated. When specifying a positive value, the string is counted from the beginning of the set, while a negative value is counted from the end. The selected line becomes the current line. If a null value is specified, no row is returned.
Argument RELATIVE (number of rows | @variable number of rows) returns the line that is the specified number of lines after the current one. If you specify a negative number of rows, the row that is the specified number of rows before the current one will be returned. Specifying a null value will return the current row. The returned row becomes the current row.
To open global cursor, you must specify the GLOBAL keyword before its name. The cursor name can also be specified using a variable.
In design INTO @variable_name [,...n] a list of variables is specified in which the corresponding column values of the returned row will be stored. The order of specifying variables must match the order of the columns in the cursor, and the data type of the variable must match the data type in the cursor column. If the INTO construct is not specified, then the behavior of the FETCH command will resemble the behavior of the SELECT command - the data is displayed on the screen.
Changing and deleting data
To make changes using a cursor, you must issue an UPDATE command in the following format:
Several columns of the current cursor row can be changed in one operation, but they all must belong to the same table.
To delete data using a cursor, use the DELETE command in the following format:
As a result, the line set current in the cursor will be deleted.
Closing the cursor
After closing, the cursor becomes inaccessible to program users. When closed, all locks installed during its operation are removed. Closure can only be applied to open cursors. Closed but not freed cursor may be reopened. It is not allowed to close an unopened cursor.
Release the cursor
Closing the cursor does not necessarily free the memory associated with it. Some implementations must explicitly deallocate it using the DEALLOCATE statement. After release the cursor Memory is also freed, making it possible to reuse the cursor name.
To control whether the end of the cursor has been reached, it is recommended to use the function: @@FETCH_STATUS
The @@FETCH_STATUS function returns:
0 if the fetch was successful;
1 if the fetch failed due to an attempt to fetch a line outside the cursor;
2 if the fetch failed due to an attempt to access a deleted or modified row.
DECLARE @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @message VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 PRINT "Shopping list" DECLARE klient_cursor CURSOR LOCAL FOR SELECT Client Code, Company, Last Name FROM Client WHERE City="Moscow" ORDER BY Company, Last Name OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Client "+@fam+ "Company "+ @firm PRINT @message SELECT @message="Product name Purchase date Cost" PRINT @message DECLARE tovar_cursor CURSOR FOR SELECT Product.Name, Transaction.Date, Product.Price* Transaction.Quantity AS Cost FROM Product INNER JOIN Transaction ON Product. Product Code=Transaction.Product Code WHERE Transaction.Customer Code=@id_kl OPEN tovar_cursor FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 PRINT "No purchases" WHILE @@FETCH_STATUS=0 BEGIN SELECT @message=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @s=@s+@p FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p END CLOSE tovar_cursor DEALLOCATE tovar_cursor SELECT @message="Total cost "+ CAST(@s AS CHAR(6)) PRINT @message -- move to next client-- FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam END CLOSE klient_cursor DEALLOCATE klient_cursor Example 13.6. Cursor for displaying a list of goods purchased by clients from Moscow and their total cost.
Example 13.7. Develop a scrollable cursor for clients from Moscow. If the phone number starts with 1, delete the client with that number and in the first cursor entry replace the first digit in the phone number with 4.
DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) PRINT "List of clients" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEYSET FOR SELECT Firm, Last name, Phone FROM Client WHERE City ="Moscow" ORDER BY Company, Last name FOR UPDATE OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Client "+@fam+ " Company "+@firm " Phone "+ @tel PRINT @message -- if the phone number starts with 1, -- delete the client with that number IF @tel LIKE '1%' DELETE Client WHERE CURRENT OF klient_cursor ELSE -- move to the next client FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel END FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- in the first entry, replace the first digit in the phone number with 4 UPDATE Client SET Phone='4' + RIGHT(@ tel,LEN(@tel)-1)) WHERE CURRENT OF klient_cursor SELECT @message="Client "+@fam+" Firm "+ @firm "Phone "+ @tel PRINT @message CLOSE klient_cursor DEALLOCATE klient_cursor Example 13.7. Scrollable cursor for clients from Moscow.
Example 13.8. Usage cursor as an output parameter of the procedure. The procedure returns a data set - a list of products.
Calling the procedure and printing data from the output cursor is carried out as follows:
DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @cur=@my_cur OUTPUT FETCH NEXT FROM @my_cur INTO @n SELECT @n WHILE (@@FETCH_STATUS=0) BEGIN FETCH NEXT FROM @my_cur INTO @n SELECT @n END CLOSE @my_cur DEALLOCATE @my_cur
Cursor is a link to the contextual memory area. In some implementations of the SQL programming language (Oracle, Microsoft SQL Server) - the result set obtained when executing a query and the associated current record pointer. I would say that the cursor is virtual table which is an alternative data store. In this case, the cursor allows you to access its data as if it were the data of a regular array.
Cursors are used in stored procedures. Enough theory, let's look at an example:
We have a database (the database is a little not good, this is one of my laboratory work, but our database teacher insisted on such a structure)
/*bank information*/
CREATE TABLE `bank` (
`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
PRIMARY KEY (`BankId`))ENGINE=InnoDB
CHARACTER SET "utf8" COLLATE "utf8_bin" ;
/*data about deposits */
CREATE TABLE `bankdistribution` (
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
PRIMARY KEY(`BankId`, `ClientId`),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
CONSTRAINT `bankdistribution_fk` FOREIGN KEY (`BankId`) REFERENCES `bank` (`BankId`),
CONSTRAINT `bankdistribution_fk1` FOREIGN KEY (`ClientId`) REFERENCES `client` (`ClientId`)
)ENGINE=InnoDB
/*data about investors*/
CREATE TABLE `client` (
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`Surname` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Name` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Address` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTEGER (5) NOT NULL ,
PRIMARY KEY(`ClientId`, `CreditCardId`),
KEY `ClientId` (`ClientId`))ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET "utf8" COLLATE "utf8_bin"
Let's say we need to receive each bank in turn and perform some actions with it, the following query could help us with this
Select `bank`.* FROM `bank` LIMIT NUMBER OF THE_RECORD_WE NEED,1. Thus, using LIMIT WE NEED_RECORD NUMBER, 1, we extract each record in a loop from the bank table and perform the actions we need with it, while increasing the value of WE NEED_RECORD NUMBER by 1. Now we will do the same but using a cursor
Begin
/* variables where we extract data */
Declare vBankId integer ;
Declare vBankName VARCHAR(50);
Declare vAddress VARCHAR(50);
Declare vPhone VARCHAR (50);
/* hadler variable - a*/
Declare done integer default 0;
/*Cursor declaration*/
Declare BankCursor Cursor for Select `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` where 1;
/*HANDLER purpose, which will be explained below*/
DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET done=1;
/* open cursor */
Open BankCursor;
/*retrieve data*/
WHILE done = 0 DO
we take the actions we need
END WHILE ;
/*closing the cursor*/
Close BankCursor;
END ;* This source code was highlighted with Source Code Highlighter.
Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Message: No data - zero rows fetched, selected, or processed
SQLSTATE: 02000 fires when the end of the cursor is reached, or when select or update returns an empty string.
The next line we declared the cursor DECLARE cursor_name CURSOR FOR select_statement;
Open the cursor Open cursor_name;
Then, until we reach the end of the cursor (WHILE done = 0 DO), we extract the data and process it.
You must close the cursor before exiting the stored procedure. Close cursor_name;
It doesn't seem complicated. But there are many pitfalls associated with SQLSTATE "02000".
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
we do some actions
END WHILE ;* This source code was highlighted with Source Code Highlighter.
Everything is fine and correct from a syntax point of view. But from a logical point of view, no. It may happen that depositors have not opened accounts in some bank, then for Select (ContributeAmount) INTO vContributeAmountSUM FROM bank distribution where BankId = vBankId limit 1; SQLSTATE: 02000 will fire, the done variable will be set to 1, and while loop end earlier than we expected. This can be avoided by doing the following
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* extract for the bank the amount of any of its deposits */
if (vContributeAmountSUM > 0) then
/* extract for the bank the amount of any of its deposits */
end if ;
we do some actions
END WHILE ;* This source code was highlighted with Source Code Highlighter.
With the first request, we checked whether there are contributions (if there are none, then vContributeAmountSUM == 0) and only if there are any, we retrieve the data.
Now let's say we need to remove the total amount in accounts in different banks for each client
Declare ClientSummCursor Cursor for Select sum
Declare ClientSummCursor Cursor for Select sum (`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`) where 1 group by `bankdistribution`. `ClientId`;Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* extract for the bank the amount of any of its deposits */
Select Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* check if there really are deposits in this bank */
if (vContributeAmountSUM > 0) then
/* extract for the bank the amount of any of its deposits */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
we do some actions.
END WHILE ;* This source code was highlighted with Source Code Highlighter.
The same situation may arise when the data in the ClientSummCursor cursor ends earlier than the data in the BankCursor, SQLSTATE: 02000 is triggered, the done variable is set to 1, and the while loop ends earlier than we expected. This can be avoided by doing the following
Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* extract for the bank the amount of any of its deposits */
Select Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* check if there really are deposits in this bank */
if (vContributeAmountSUM > 0) then
/* extract for the bank the amount of any of its deposits */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
/* before extracting data from the second cursor, remember the sqlstate state */
SET old_status = done;
/* extract the data we need */
FETCH ClientSummCursor INTO vSum,vClientId;
/* check whether the data was retrieved and whether sqlstate 0200 failed */
if (done = 0) then
we do some actions.
end if ;
/* before the end of the while, restore the value of the done variable */
set done = old_status;
END WHILE ;* This source code was highlighted with Source Code Highlighter.
Thank you to everyone who has read this far, I hope this will be useful to someone.
IT APPLIES TO: SQL Server (since 2008)Base SQL data Azure SQL Data WarehouseParallel Data Warehouse
Defines attributes of a Transact-SQL server cursor, such as view properties and the query used to build the result set on which the cursor operates. The DECLARE CURSOR statement supports both ISO standard syntax and syntax that uses the Transact-SQL language extension set.
ISO Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR ( READ ONLY | UPDATE [ OF column_name [ ,...n ] ] ) ] [;] Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]
cursor_name
cursor_name
INSENSITIVE
tempdb; thus, changes to the underlying tables are not reflected in the data returned by this cursor's selections, and this cursor is not changeable. When using ISO syntax, unless the INSENSITIVE option is specified, committed updates and deletes made to the base tables appear in subsequent selections.
SCROLL
Indicates that all sampling options are available (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). If the ISO DECLARE CURSOR statement does not specify a SCROLL option, only the NEXT fetch option is supported. The SCROLL option cannot be specified with the FAST_FORWARD option.
select_statement
Standard SELECT statement, which defines the result set of the cursor. Keywords FOR BROWSE and INTO are not allowed in select_statement cursor declaration.
select_statement conflict with a cursor of the requested type.
READ ONLY
Update ]
column_name [,
.. .n] is specified, only the listed columns allow changes. If the UPDATE statement is used without a list of columns, then the update is possible for all columns.
cursor_name
The Transact-SQL name of the specific server cursor. cursor_name must follow the rules for identifiers.
LOCAL
Indicates that the cursor is local to the package, stored procedure, or trigger in which it was created. The cursor name is only valid within this area. A cursor can be referenced by package local variables, stored procedures, triggers, or the output parameter of a stored procedure. The OUTPUT parameter is used to pass a local cursor to the calling package, stored procedure, or trigger, which can then assign the parameter to a cursor variable for subsequent access to the cursor after the stored procedure completes. The cursor is implicitly released when the batch, stored procedure, or trigger completes execution, unless the cursor was passed to the OUTPUT parameter. If the cursor was passed to the OUTPUT parameter, the cursor is released when all variables referencing it are freed or when the scope is exited.
GLOBAL
Indicates that the cursor is global to the connection. The cursor name can be used by any stored procedure or package that runs on the connection. The cursor is implicitly released only if the connection is broken.
FORWARD_ONLY
Specifies that the cursor can only be viewed from the first line to the last. Only the FETCH NEXT fetch option is supported. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor behaves as a DYNAMIC cursor. If neither the FORWARD_ONLY argument nor the SCROLL argument is specified, the default is the FORWARD_ONLY argument unless the STATIC, KEYSET, or DYNAMIC keywords are present. STATIC, KEYSET, and DYNAMIC cursors have a default value of SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY mode is supported by the following Transact-SQL cursors: STATIC, KEYSET, and DYNAMIC.
STATIC
Defines a cursor that creates a temporary copy of data for use by the cursor. All queries to the cursor access the specified temporary table in tempdb; thus, changes to the underlying tables are not reflected in the data returned by this cursor's selections, and this cursor is not changeable.
KEYSET
Indicates that the membership or order of the rows in the cursor is unchanged when it is opened. A set of keys that uniquely identify rows is built into the table in tempdb called keys.
Changes to non-key values in base tables made by the cursor owner or committed by other users are displayed when the cursor owner views it. Changes made by other users are not reflected (changes cannot be made using the Transact-SQL server cursor). If a row is deleted, attempting to fetch rows returns @@FETCH_STATUS -2. Updates to key values due to cursor boundaries are similar to deleting an old row and then inserting it new line. The row with the new values is not visible and attempts to fetch the row with the old values return @@FETCH_STATUS -2. Updates are visible immediately if they are made through the cursor using the WHERE CURRENT OF clause.
DYNAMIC
Defines a cursor that displays all data changes made to rows in the result set while viewing this cursor. Data values, order, and row membership in each selection may vary. The ABSOLUTE selection option is not supported by dynamic cursors.
FAST_FORWARD
Indicates a FORWARD_ONLY, READ_ONLY cursor that has performance optimization enabled. The FAST_FORWARD option cannot be specified with the SCROLL or FOR_UPDATE options.
READ_ONLY
Prevents changes made through this cursor. The WHERE CURRENT OF clause cannot reference a cursor in an UPDATE or DELETE statement. This option takes precedence over the default cursor refresh feature.
SCROLL_LOCKS
Indicates that positioned updates or deletes made using the cursor are guaranteed to succeed. SQL Server locks rows as they are read into the cursor to ensure that those rows are available for subsequent changes. The SCROLL_LOCKS option cannot be specified with the FAST_FORWARD or STATIC option.
OPTIMISTIC
Specifies that positioned updates or deletes made using the cursor will fail if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. Comparisons are used instead timestamp column values or checksums if the table does not have timestamp column to determine whether the row has changed since it was read into the cursor. If the row has been modified, then attempts at a positioned update or delete will fail. The OPTIMISTIC option cannot be specified with the FAST_FORWARD option.
TYPE_WARNING
Specifies that a warning will be sent to the client if the cursor is implicitly converted from one requested type to another.
select_statement
A standard SELECT statement that specifies the result set of a cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE and INTO are not allowed in select_statement cursor declaration.
SQL Server implicitly converts the cursor to another type if the clauses in select_statement conflict with a cursor of the requested type. For more information, see Implicit Cursor Conversions.
FOR UPDATE ]
Defines the columns in the cursor to be updated. If OF column_name [,
... n] is provided, only the listed columns allow changes. If the UPDATE statement is used without a column list, then the update is possible for all columns unless the READ_ONLY concurrency option was specified.
The DECLARE CURSOR statement defines attributes of a Transact-SQL server cursor, such as view properties and the query used to build the result set on which the cursor operates. The OPEN statement populates the result set, and the FETCH statement returns a row from it. The CLOSE statement clears the current result set associated with the cursor. The DEALLOCATE statement releases resources used by the cursor.
The first form of the DECLARE CURSOR statement uses ISO syntax to specify cursor parameters. The second form of the DECLARE CURSOR statement uses extensions to the Transact-SQL language that allow you to define cursors using the same types as those used in the cursor functions of database APIs such as ODBC and ADO.
These two forms cannot be mixed. If you specify SCROLL or omitting keywords before the CURSOR keyword, you cannot use keywords between the CURSOR and also for select_statement keywords. When specifying keywords between the CURSOR, as well as for select_statement keywords, you cannot specify SCROLL or INSENSITIVE before the CURSOR keyword.
If you use Transact-SQL syntax for the DECLARE CURSOR statement and do not specify the READ_ONLY, OPTIMISTIC, or SCROLL_LOCKS options, the following default value is assumed.
If the SELECT statement does not support updates (or has insufficient permissions, or is accessing remote tables that do not support updates, etc.), the cursor is set to READ_ONLY.
STATIC and FAST_FORWARD cursors default to READ_ONLY.
DYNAMIC and KEYSET cursors default to OPTIMISTIC.
Cursors can only be referenced by other Transact-SQL statements. Database API functions cannot reference cursors. For example, once a cursor is declared, OLE DB, ODBC, or ADO functions and methods cannot refer to its name. Cursor rows cannot be selected using the corresponding API functions and methods; For this purpose, you must use Transact-SQL FETCH statements.
The following stored procedures can be used to define the properties of a cursor after it has been declared.
Variables can be used as part select_statement, in which the cursor is declared. The values of cursor variables do not change after it is declared.
By default, DECLARE CURSOR permissions are granted to all users who have SELECT permission on the views, tables, and columns used by the cursor.
You cannot use cursors or triggers on a table with a clustered columnstore index. This limitation does not apply to nonclustered indexes; You can use cursors and triggers on a table with a nonclustered columnstore index.
A. Using a simple cursor and syntax
The result set created when you open this cursor includes all the rows and columns of the table. This cursor can be updated, all updates and deletions are represented in the selection for this cursor. FETCH``NEXT is fetch only because the SCROLL parameter was not specified.
DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;
B. Using nested cursors to display a report
The following example uses nested cursors to display a complex report. An internal cursor is declared for each provider.
SET NOCOUNT ON ;
DECLARE @vendor_id int , @vendor_name nvarchar ( 50 ), @message varchar ( 80 ), @product nvarchar ( 50 ); PRINT" -------- Vendor Products Report --------";
DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; OPEN vendor_cursor FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = "----- Products From Vendor: "+ @vendor_name PRINT @message -- Declare an inner cursor based
-- on vendor_id from the outer cursor. DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- Variable value from the outer cursor OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS<>0 PRINT "<
It may well happen that the response to a simple client request will be a sample of hundreds of thousands of rows, which is indigestible for most clients. In this case, the solution to the problem of interaction with clients is to use cursors as a universal mechanism for exchanging data between the server and the client. Cursors work with the result set of data (the result of a query), giving users additional data processing capabilities:
Cursors allow you to work with table rows by specifying their serial number in the data set;
Cursors allow you to implement complex data modification operations, for example, when changing the value of a column requires repeatedly accessing the values of other columns.
Cursor life cycle:
Creating a cursor: DECLARE<имя курсора>[ INSENSITIVE ] [ SCROLL ] CURSOR FOR< SELECT -оператор>FOR (READ ONLY | UPDATE)
Here, the INSENSITIVE keyword means that the cursor will be static (a snapshot of the data), while by default the cursor is created dynamic (fetching is carried out every time the row is accessed). The SCROLL keyword means that the cursor can be scrolled in any direction, otherwise the cursor is created as a "sequential" cursor.
Opening cursor: OPEN [GLOBAL]<имя курсора>. A cursor specified as GLOBAL is not automatically deleted when the procedure or package from which it was called ends.
Readingdata : FETCH [[ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ] FROM ] [ GLOBAL ]<имя курсора>[INTO@variable_name,...]. SQL Server 2000 allows you to read just one row from a cursor. The FIRST keyword is to return the first row of the cursor; LAST – last line of the cursor; NEXT – the next line after the current one, the returned line becomes the current one; PRIOR – previous before current; ABSOLUTE n – returns a line by its absolute sequence number in the cursor; RELATIVE – n lines after the current one. The column data will be stored in each of the specified variables in the order they are listed.
Change data: executes an UPDATE command with syntax designed for working with cursors.
Deleting data: executes a DELETE command with syntax designed to work with cursors.
Closing the cursor: CLOSE [GLOBAL]<имя курсора>
Release the cursor: DEALLOCATE [GLOBAL]<имя курсора>
An example of using a cursor:
DECLARE fo_curs CURSOR STATIC FOR
SELECT name_rus from fo ORDER BY name_rus
DECLARE @name varchar(50)
FETCH FIRST FROM fo_curs INTO @name
WHILE @@FETCH_STATUS=0
FETCH NEXT FROM fo_curs INTO @name
DEALLOCATE fo_curs
2.7. Ensuring data security and integrity in Microsoft SQL Server. Database management. Roles. Assigning rights to users (GRANT, DENY, REVOKE). Methods and technologies for data protection in SQL Server.
SQL Server Security and Administration. .
The main task of a DBMS is to ensure the integrity and consistency of data within the selected subject area. One of the factors preventing the system from solving this problem is the actions of users who accidentally or intentionally try to destroy the data structure or change the data itself. Consequently, the DBMS must be protected not only from physical failures, but also from users who are inadequate for the tasks being implemented. To do this, it is necessary to design and connect a security system to the database that will prevent users from performing actions beyond their authority.
Database Management
To create a database using TSQL, use the CREATE DATABASE command, but usually the capabilities of SQL Server Management Studio are used for this purpose. IN SQL server quite a lot of database operations are defined: increasing (decreasing) file sizes, changing configuration (ALTER command), attaching and detaching, transferring ownership, changing name, viewing properties and, finally, deleting (DROP DATABASE).
Like most database servers, SQL Server has a user with full administrative rights - this is System Administrator or 'sa". After the initial server installation, the sa password is empty. The user who creates a new database automatically becomes its owner ('dbo' - Data Base Owner). At the time the database is created, the user "guest" is also defined. If the user account is not explicitly mapped to a user of a specific database, the user is granted implicit access with using the guest name guest.Guest is usually prohibited.
The user who creates an object in the database automatically becomes its owner, and no one, including dbo and sa, can use that object until the owner assigns them rights to it. But in order for a user to create an object, the database owner must first grant him the appropriate rights.
Role allows you to combine users performing the same functions to simplify administration. Roles can be built-in or custom. Built-in roles are implemented at the server level and at the database level. Below is a table of built-in database roles:
db_owner. Has all rights in the database
Db_accessadmin. Can add or remove users
Db_securityadmin. Manages all permissions, objects, roles and users
Db_ddladmin. Can execute all DDL commands except GRANT, DENY, REVOKE
Db_backupoperator. Archiver can execute commands. data
db_datareader. Maybe viewing. any data in any table
db_datawriter. Maybe a modification. any data in any table
Db_denydatareader. Prohibited view love data in any tables
Db_denydatawriter. Prohibit modifying any data in any tables
Assigning rights to users. The foundation of SQL Server security is (1) Accounts(accounts); (2) users; (3) roles; (4) groups.
When a user connects to SQL Server, the actions he can perform are determined by the rights granted to him as a user and a member of a role. Rights are granted by the DBMS administrator, the database owner, or the owner of a specific database object. Rights in the database can be divided into three categories: (1) rights to access database objects; (2) rights to execute TSQL commands; (3) implicit rights. The server allows you to transfer ownership from one user to another.
The following commands are used to manage user permissions to access database objects:
GRANT(ALL |< вид действия >,…}
( ON (<имя таблицы или представления>} [(<имя столбца>,…)]
| ON(< имя хранимой процедуры >}
| ON(< имя пользовательской функции >}
TO ( PUBLIC |<имя объекта системы безопасности>,…}
[ AS<имя группы> | <имя роли>]
– assigning rights to users, Where
ALL – the user is granted all possible permissions, otherwise specify
<вид действия>– rights to actions available to the user, namely:
SELECT – for view, for table column and for table (view)
INSERT – to add, for the table (view) as a whole
UPDATE – for change, for a table column and for a table (view)
DELETE – to delete, for the table (view) as a whole
EXECUTE – to execute stored procedures
REFERENCES – the ability to refer to a specified object (include it as part of a foreign key).
<имя объекта системы безопасности>– SQL Server accounts, Windows domain users; PUBLIC – for all users.
WITH GRANT OPTION - allows the user who is currently granted rights to assign access rights to the object to other users.
AS<имя группы> | <имя роли>– participation of a user in a role that is given the ability to grant rights to other users.
GRANT SELECT ON authors TO public
GRANT INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom
GRANT SELECT ON Plan_Data TO Accounting WITH GRANT OPTION
GRANT SELECT ON Plan_Data TO Jack AS Accounting
Jack is not a member of the Accounting role, but someone in that role can grant permission
DENY(ALL |< вид действия >,…}
( ON (<имя таблицы или представления>} [(<имя столбца>,…)]
| ON(<имя хранимой процедуры>}
| ON(<имя пользовательской функции>}
TO ( PUBLIC |<имя объекта системы безопасности>,…}
– access denial users to database objects. CASCADE revokes rights not only from given user, but also for everyone to whom he granted rights.
Example (on command prohibition TSQL):
DENY CREATE TABLE TO Jack CASCADE
Team REVOKE used to implicitly deny access to database objects. The syntax is the same as the DENY command. Implicit denial is similar to denying access, except that it only applies at the level at which it is defined. Example: The user Jack, who is a member of the GoodUsers role, is granted access rights to the XFiles table. If REVOKE is denied for the GoodUsers role to access this table, Jack can still access this table because the rights for him are explicitly defined. If you use REVOKE personally for him, he will lose the right to access XFiles.
Permissions granted to roles are inherited by their members. If a user is granted access to an object through membership in one role but denied in another, then the access conflict is always resolved in favor of denial.
Data protection technologies in MS SQL Server
1.Mechanism checkpoints– checkpoints that are generated after ~60 s to write updated pages to disk (a checkpoint can be forced by the CHECKPOINT command).
2. Built-in and external mechanisms for checking the integrity of the database (launched automatically or, like the DBCC utility - Database Consistency Checker - manually).
3.Physical duplication (if allowed) of database files using the operating system (including the mechanism of mirrored hard drives).
4. Backing up databases and transaction logs - by writing a database dump to a backup device (magnetic tape or hard drive).
5. Replication – the ability to duplicate information by periodically (in some cases, synchronously) transferring it from one SQL server to another.
6. Encryption of traffic between the client and server, as well as encryption of codes used to work with database objects (stored procedures, triggers, etc.)
1) The concept of a cursor
Interactive SQL does not differentiate between single-row and multi-row queries. Embedded SQL executes these queries differently. Single-line queries return one line and we have already covered them. When the result of a query is more than one row, embedded SQL must allow the application to retrieve the query results row by row. Cursors are used for this. A cursor is a variable associated with a query. Its value is each row that matches the query. Like variables, cursors must be declared before they can be used. Unlike views, cursors are designed for line-by-line processing.
2) Cursor declaration
DECLARE [{}] [[NO] SCROLL] CURSOR [{WITH|WITHOUT} HOLD] FOR [FOR {READ ONLY|UPDATE [OF ]}]
3) Keywords
. SENSITIVE|INSENSITIVE|ASENSITIVE– changes in the result set are visible | prohibited (fixed using a copy of the data set)|The DBMS itself decides whether to make a copy (operates by default).
. WITH|WITHOUT HOLD– leaves open | closes the cursor if a COMMIT statement is encountered.
. SCROLL– [prevents] extracting result rows in random order.
. FOR READ ONLY– defines a read-only cursor.
. FOR UPDATE OF– blocks only the specified columns from updating.
4) Declaring a cursor in SQL Server
DECLARE CURSOR [LOCAL|GLOBAL] [FORWARD_ONLY|SCROLL] [STATIC|KEYSET|DYNAMIC|FAST_FORWARD] [READ_ONLY|SCROLL_LOCKS|OPTIMISTIC] FOR [FOR UPDATE [OF ]]
. STATIC– Defines a cursor that creates a temporary copy of data for use by the cursor. All queries against a cursor access the specified temporary table in the tempdb database, so changes to the base tables do not affect the data returned by samples for that cursor, and the cursor itself does not allow changes to be made.
. KEYSET– Indicates that the membership or order of rows in the cursor does not change after it is opened. A set of keys that uniquely identify rows is built into a table in the tempdb database called keyset.
. DYNAMIC– Defines a cursor that displays all data changes made to the rows of the result set when viewing this cursor. The data values, order, and row membership in each selection may vary. The ABSOLUTE selection option is not supported by dynamic cursors.
. FAST_FORWARD– Indicates a FORWARD_ONLY, READ_ONLY cursor for which performance optimization is enabled. The FAST_FORWARD option cannot be specified with the SCROLL or FOR_UPDATE options.
. SCROLL_LOCKS– Indicates that positioned updates or deletes made through the cursor are guaranteed to succeed. SQL Server locks rows as they are read into the cursor to ensure they are available for subsequent changes. The SCROLL_LOCKS option cannot be specified with the FAST_FORWARD or STATIC option.
. OPTIMISTIC– Indicates that positioned updates or deletes made through the cursor will fail if the row has been updated since the time it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. Instead, a comparison is made of the values of the timestamp column (or checksums if the table does not have a timestamp column) to determine whether the row has changed since it was read into the cursor. If a row has been modified, its positioned modification or deletion is not possible. The OPTIMISTIC option cannot be specified with the FAST_FORWARD option.
5) Opening the cursor
6) Retrieving rows from the cursor
FETCH [{NEXT|PRIOR|FIRST|LAST|{ABSOLUTE|RELATIVE }}]
FROM INTO
7) Cursor positioning options
. NEXT|PRIOR|FIRST|LAST– to the next|previous|first|last line of the result set.
. RELATIVE ±N– to a line with a positive or negative offset relative to the current line.
. ABSOLUTE ±N– to a line with an explicitly specified absolute position number from the beginning or end of the cursor.
Note: SQL Server allows the integer variable @N instead of N.
8) Closing the cursor
9) Notes on cursors
. If the cursor contains more than one line, it is necessary to organize a loop for retrieving data from it, periodically checking to reach the last line.
. Unlike tables and views, cursor rows are ordered either explicitly using a section ORDER BY, or in accordance with the conventions adopted in a particular DBMS.
. Cursors are also used to select groups of rows from tables, which can be updated or deleted one at a time.
. For a cursor to be updatable, it must meet the same criteria as the view, that is, not contain sections UNION, ORDER BY, GROUP BY, DISTINCT.
10) Example for deleting data from a cursor
exec sql declare cursor Cur1 for select * from Customer
where Rating
// print (@f1+’ ‘+convert(Varchar(5),@f2))
exec sql delete from Customer
where current of Cur1; ) – Take data to delete from the cursor
not_done:
exec sql close cursor Cur1; — Close the cursor
exit();
11) Example for increasing commissions
exec sql declare cursor CurCust for select * from SalesPeople
where SNum in (select SNum from Customer where Rating=300); — Define the cursor
exec sql open cursor CurCust; - Execute the cursor
while (sqlca.sqlcode==0) ( — Create a loop to update data in the table
exec sql fetch CurCust into:Id_num, :SalesPerson, :Loc, :Comm;
exec sql update SalesPeople set Comm=Comm+.01 where current
of CurCust; ) – Take the data for updating from the cursor
exec sql close cursor CurCust; — Close the cursorSELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt FROM SalesPeople S INNER JOIN Orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2
DECLARE Cur1 SCROLL CURSOR FOR SELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt FROM SalesPeople S INNER JOIN Orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2
OPEN Cur1
FETCH NEXT FROM Cur1
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1
DEALLOCATE Cur1
