SQL Server : Sử dụng biến kiểu dữ liệu CursorPhần lớn các cơ sở dữ liệu quan hệ thường làm việc trên dữ liệu của nhiều dòng mẫu tin, còn gọi là một bộ các mẫu tin. Ví dụ lệnh SELECT kết quả luôn trả về nhiều dòng dữ liệu hơn là một dòng dữ liệu.

Ngược lại đối với một số ngôn ngữ lập trình hoặc bên trong các ứng dụng thì người lập trình vẫn còn các thói quen xử lý và tính toán dữ liệu trên từng dòng riêng lẻ. Để đáp ứng được yêu cầu này của các người lập trình, muốn làm việc chỉ trên từng dòng dữ liệu tại thời điểm hiện hành, Microsoft SQL Server tạo ra một kiểu dữ liệu đó chính là kiểu cursor.1/- Khái niệm về cursor :

Bạn có thể hình dung kiểu dữ liệu cursor (kiểu con trỏ) giống như một cuốn sổ danh bạ chứa thông tin liên lạc của các khách hàng giao dịch trong một công ty. Bằng cách dò tìm thủ công, bạn sẽ phải sử dụng đến mắt và tay để tham chiếu đến tên của các khách hàng bất bỳ trong sổ danh bạ đó. Bạn có thể di chuyển lên, xuống hoặc qua trang để tìm ra các khách hàng mong muốn, nhưng tại thời điểm hiện hành tay và mắt của bạn chỉ đứng tại một khách hàng mà thôi.Hoạt động của kiểu dữ liệu cursor trong Transaction-SQL hoàn toàn giống như ví dụ minh họa ở trên. Tuy nhiên, cursor có nhiều kiểu khác nhau cho phép bạn có thể chọn lựa để định nghĩa theo đúng yêu cầu mà mình mong muốn. Tùy thuộc vào kiểu cursor đã định nghĩa mà việc đọc và cập nhật dữ liệu sẽ có hiệu lực như thế nào.

So sánh cơ chế cursor và bộ các mẫu tin 2/- Các bước sử dụng kiểu dữ liệu cursor :

Đối với các kiểu dữ liệu thông thường sau khi khai báo biến cùng với kiểu dữ liệu thích hợp, bạn sẽ được phép gán giá trị cần lưu trữ vào bên trong biến. Hoạt động của biến kiểu dữ liệu cursor hoàn toàn không đơn giản như thế, để sử dụng được biến kiểu dữ liệu cursor, bạn phải thực hiện một cách thứ tự qua nhiều bước khác nhau.Chi tiết các bước thực hiện khi sử dụng biến kiểu dữ liệu cursor trong Transaction-SQL. Để làm việc với biến có kiểu cursor, bạn phải thực hiện từng bước như sau :• Định nghĩa biến kiểu cursor bằng lệnh DECLARE.• Sử dụng lệnh OPEN để mở ra cursor đã định nghĩa trước đó.• Đọc và xử lý trên từng dòng dữ liệu bên trong cursor.• Đóng cursor lại bằng lệnh CLOSE và DEALLOCATE.2.1/- Định nghĩa biến có kiểu cursor :

Việc định nghĩa biến có kiểu cursor trong Transaction-SQL là việc chỉ định đến những dòng dữ liệu có bên trong các bảng dữ liệu nào mà biến sẽ tham chiếu đến. Thông thường trong lệnh định nghĩa biến có kiểu cursor bên dưới sẽ chỉ định ra loại của cursor cho các mục đích sử dụng về sau này thuận tiện hơn.Cú pháp :

Trong đó :• Tên cursor : tên của biến kiểu cursor.• Từ khóa LOCAL\GLOBAL : dùng chỉ định phạm vi hoạt động của biến cursor hoặc là cục bộ (local) bên trong một thủ tục, lô (batch) các lệnh, một trigger hoặc là toàn cục (global) bên trong một kết nối. Một biến cursor có tính toàn cục sẽ được phép tham chiếu trong bất kỳ thủ tục nào của kết nối tạo ra biến cursor đó.• Từ khóa FORWARD_ONLY : dùng chỉ định việc đọc dữ liệu trong cursor chỉ theo chiều đi tới mà thôi (duyệt từ mẫu tin đầu tiên đến mẫu tin cuối cùng).• Từ khóa SCROLL : dùng chỉ định việc đọc dữ liệu trong cursor được phép di chuyển tới lui, qua lại các dòng mẫu tin bên trong cursor tùy thích.• Từ khóa STATIC : dùng chỉ định dữ liệu đọc bên trong cursor là tĩnh. Khi đó nếu những người dùng khác có các thay đổi ở bên dưới dữ liệu gốc (base table) thì các thay đổi đó sẽ không được cập nhật tự động trong dữ liệu của cursor. Bởi vì khi đó dữ liệu trong cursor chính là dữ liệu của một bảng tạm đã được hệ thống sao chép và lưu trữ trong cơ sở dữ liệu tempdb của hệ thống khi địng nghĩa cursor.

• Từ khóa DYNAMIC : dùng chỉ định dữ liệu bên trong cursor là động. Khi đó việc cập nhật dữ liệu trong bảng cơ sở (base table) bởi những người dùng khác sẽ được cập nhật tự động trong dữ liệu cursor có kiểu là DYNAMIC.• Từ khóa KEYSET : có hoạt động gần giống với kiểu DYNAMIC, các thay đổi dữ liệu trên các cột không là khóa chính trong bảng cơ sở bởi những người dùng khác sẽ được cập nhật trong dữ liệu cursor. Tuy nhiên đối với các mẫu tin vừa thêm mới hoặc các mẫu tin đã bị hủy bỏ bởi những người dùng khác sẽ không được hiển thị trong dữ liệu cursor có kiểu là KEYSET.• Từ khóa READ_ONLY : dùng chỉ định dữ liệu bên trong cursor là chỉ đọc nhằm hạn chế việc sửa đổi dữ liệu bên trong cursor. Khi khai báo cursor với kiểu dữ liệu là tĩnh (STATIC) thì dữ liệu trong cursor xem như là chỉ đọc.• Từ khóa SCROLL_LOCK : dùng chỉ định hệ thống Microsoft SQL Server tự động khóa các dòng mẫu tin cần phải thay đổi giá trị hoặc bị hủy bỏ bên trong bảng nhằm đảm bảo các hành động cập nhật luôn luôn thành công.• Câu lệnh SELECT : dùng để chỉ đến các cột có bên trong bảng mà bạn cần đọc dữ liệu. Câu lệnh SELECT trong cursor không thể chứa các mệnh đề : INTO, COMPUTE, COMPUTE BY.• Danh sách các cột cập nhật : chỉ định danh sách tên các cột sẽ được phép thay đổi giá trị trong cursor. Mặc định tất cả các cột trong mệnh đề SELECT sẽ được phép thay đổi giá trị nếu dữ liệu cursor không phải là chỉ đọc.Ví dụ :Để định nghĩa một biến cursor chứa toàn bộ các dòng dữ liệu bên trong bảng VATTU, các dòng dữ liệu trong cursor cho phép được cập nhật. Bạn sử dụng lệnh khai báo biến cursor như sau :

Ví dụ :Để định nghĩa một biến cursor chứa toàn bộ các dòng dữ liệu bên trong bảng NHACC, các dữ liệu trong cursor chỉ được phép đọc và việc đọc dữ liệu trong cursor chỉ theo một chiều tới. Bạn sử dụng lệnh khai báo biến cursor như sau :

Nhận xét : ở ví dụ trên, bạn có thể bỏ đi từ khóa READ_ONLY bởi vì bản thân từ khóa STATIC đã định nghĩa dữ liệu của cursor là chỉ đọc. Tuy nhiên bạn nên ghi nhớ ý nghĩa của từng từ khóa riêng lẻ để định nghĩa ra dữ liệu của các cursor đúng với yêu cầu mà mình cần sử dụng.2.2/- Mở cursor :

Để có thể đọc được các dòng dữ liệu bên trong cursor trước tiên bạn cần phải mở cursor ra bằng lệnh OPEN. Hoạt động bên trong của lệnh này thực ra là hệ thống sẽ thực hiện câu lệnh truy vấn SELECT đã được chỉ định trong lệnh định nghĩa biến cursor trước đó.Trong trường hợp nếu bạn định nghĩa sử dụng cursor với kiểu STATIC hoặc KEYSET thì hệ thống sẽ tạo ra bảng tạm chứa các dữ liệu kết quả của lệnh SELECT nằm trong cơ sở dữ liệu tempdb. Cú pháp lệnh OPEN như sau :

Trong đó :• Tên cursor : tên của biến kiểu cursor đã định nghĩa trước đó bằng lệnh DECLARE.Ví dụ :Để mở các cursor đã định nghĩa ở các ví dụ trên. Bạn sử dụng lệnh OPEN như sau :

Hoặc

Sau khi định nghĩa và mở biến cursor, hành động kế tiếp mà bạn thường thực hiện là việc đọc và xử lý dữ liệu trong cursor. Bạn sử dụng lệnh FETCH cùng với các từ khóa tương ứng để chỉ định việc đọc các dòng dữ liệu bên trong cursor theo một thứ tự nào.

2.3/- Đọc và xử lý dữ liệu trong cursor :

Cú pháp :

Trong đó :• Các từ khóa NEXT, PRIOR, FIRST, LAST : dùng để đọc dữ liệu của dòng dữ liệu kế tiếp (next), dòng dữ liệu phía trước (prior), dòng dữ liệu đầu tiên (first), dòng dữ liệu cuối cùng (last) so với dòng dữ liệu hiện hành bên trong cursor. Sau khi đọc dữ liệu thành công, dòng dữ liệu hiện hành sẽ bị thay đổi chính là dòng vừa mới được đọc.• Từ khóa ABSOLUTE : dùng để chỉ định dữ liệu chính xác thứ n bên trong cursor. Với n là số nguyên dương dùng chỉ định việc đọc dữ liệu tại dòng thứ n được đếm từ dòng đầu tiên, với n là số nguyên âm dùng chỉ định việc đọc dữ liệu tại dòng thứ n được đếm ngược từ dòng cuối cùng trở lên.• Từ khóa RELATIVE : dùng để chỉ định việc đọc dữ liệu tại một dòng tương đối so

với dòng dữ liệu hiện hành. Với n là một số nguyên có thể dương hoặc âm để chỉ định việc đọc theo chiều tới hoặc lui so với dòng dữ liệu hiện hành.• Tên cursor : tên của biến kiểu cursor đã được định nghĩa trước đó bằng lệnh DECLARE.• Danh sách biến : danh sách tên các biến cục bộ đã được định nghĩa trước đó. Các biến này sẽ lưu trữ các giá trị dữ liệu được đọc từ lệnh FETCH.

Minh họa việc đọc dữ liệu theo các thứ tự khác nhau Trong quá trình đọc và xử lý các dòng dữ liệu không đảm bảo luôn luôn là thành công bởi vì có sự truy cập bởi những người dùng khác hoặc một lý do khác. Do đó hệ thống Microsoft SQL Server cung cấp cho bạn một biến hệ thống có tên là @@FETCH_STATUS dùng để kiểm tra tình trạng đọc dữ liệu là thành công hay thất bại. Giá trị của biến trả về 0 khi việc đọc dữ liệu là thành công.Ví dụ :Để đọc dữ liệu cursor bảng VATTU chỉ lọc các vật tư là Tivi. Bạn sử dụng các lệnh như sau :

Kết quả trả về :

Nhận xét : trong ví dụ trên, có ghi chú các bước thực hiện khi sử dụng biến cursor nhằm giúp bạn dễ đọc, dễ hiểu. Việc đọc dữ liệu được thực hiện trong vòng lặp

WHILE nhằm tìm ra tất cả các vật tư là Tivi, sử dụng điều kiện lặp @@FETCH_STATUS = 0 để nói rằng nếu việc đọc dữ liệu của lệnh FETCH NEXt thành công thì sẽ tiếp tục lặp. Hai lệnh cuối cùng CLOSE và DEALLOCATE dùng để đóng lại cursor sau khi đã đọc và xử lý dữ liệu xong.Ví dụ :Để cập nhật giá trị dữ liệu cho cột TGNHAP (trị giá nhập) trong bảng PNHAP bằng cách duyệt qua từng phiếu nhập, tính ra trị giá nhập của từng phiếu căn cứ vào số lượng nhập và đơn giá nhập của từng vật tư trong bảng CTPNHAP, sau cùng cập nhật vào cột TGNHAP. Bạn sử dụng các lệnh như sau để thực hiện các hành động mô tả như trên :

Nhận xét : trong ví dụ trên, sử dụng vòng lặp WHILE mà điều kiện lặp là 0 = 0 để chỉ định điều kiện so sánh vòng lặp là luôn luôn đúng. Do đó, bên trong vòng lặp này bạn bắt buộc phải thoát khỏi vòng lặp bằng lệnh BREAK và điều kiện thoát là khi việc đọc dữ liệu bị lỗi (@@FETCH_STATUS <> 0). Ngoài việc cập nhật dữ liệu bằng lệnh UPDATE mà trong mệnh đề WHERE có sử dụng từ khóa CRRENT OF dùng để chỉ định việc cập nhật dữ liệu trên dòng dữ liệu hiện hành của cursor.2.4/- Đóng cursor :

Đây là công việc sau cùng cần phải thực hiện khi sử dụng biến có kiểu cursor. Thông thường để đóng cursor bạn phối hợp cả hai lệnh mô tả như bên dưới :Cú pháp :

Trong đó :• Tên cursor : tên của biến kiểu cursor đã được định nghĩa và mở ra trước đó.Lệnh CLOSE chỉ là thực hiện hành động giải phóng các dòng dữ liệu tham chiếu bên trong biến cursor, bạn có thể mở lại cursor mà không cần thiết phải định nghĩa lại biến cursor bằng lệnh DECLARE. Tuy nhiên việc đọc dữ liệu sẽ không còn là hợp lệ nữa sau khi bạn đã ra lệnh CLOSE để đóng cursor.Lệnh DEALLOCATE để giải phóng thật sự biến cursor ra khỏi bộ nhớ. Sau khi thực hiện lệnh này, nếu có lệnh nào tham chiếu đến tên cursor đều sẽ gây ra lỗi.Sơ đồ minh họa các bước thực hiện khi sử dụng biến có kiểu dữ liệu cursor :

Tiện ích duy nhất khi làm việc với cursor là chúng cho phép bạn có thể duyệt tuần tự trên các dòng dữ liệu (giống như đối tượng recordset của ADO hoặc DAO trong ngôn ngữ lập trình Visual Basic), tuy nhiên Microsft SQL Server khuyến cáo bạn không nên lạm dụng quá nhiều các hành động cập nhật dữ liệu bằng cursor bởi vì nó sẽ làm cho các xử lý này chậm. Bằng chứng là bạn có thể tính toán giá trị cho cột trị giá nhập (TGNHAP) trong bảng PNHAP bằng một lệnh UPDATE duy nhất thay vì phải sử dụng quá nhiều lệnh cho các xử lý trong cursor.

Tóm lại, bạn chỉ sử dụng kiểu dữ liệu cursor trong Transaction-SQL để giải quyết các vấn đề :• Microsoft SQL Server là một hệ quản trị cơ sở dữ liệu quan hệ (Relational DataBase Managament System) do đó chúng luôn chọn các giải pháp làm việc trên bộ các mẫu tin.• Kế tiếp khi cần giải quyết các vấn đề cập nhật dữ liệu thì bạn luôn luôn ưu tiên

chọn ra các hướng giải quyết trên bộ các mẫu tin bởi vì khi đó sẽ làm cho các xử lý được nhanh hơn.• Sau cùng các hướng giài quyết theo kiểu cursor chỉ là giải pháp sau cùng nhất để chọn lựa khi không còn giải pháp nào tốt hơn.

//----------------------------------------------------------------------------------------------------------------------------------------===================================================================================//

Một ví dụ về cursor trong SQL Server Posted on 25/06/2010 by truonggiang83

Xin chào tất cả các bạn, những ai đang theo học môn CSDL thì cũng đã từng hoặc sẽ học qua Cursor. Để hiểu rõ hơn về vấn đề này chúng ta theo dõi bài toán sau:

Yêu cầu: Tôi muốn backup tất cả Database trong hệ thống (hệ quản trị CSDL SQL Server 2005 làm ví dụ)

Hướng giải quyết: Bạn hãy xem tất cả DataBase trong hệ thống là một danh sách. Chúng ta sẽ duyệt danh sách này theo từng dòng và mỗi dòng bạn thực hiện thao tác Backup tương ứng với nó.

Phân tích:-Nên đặt tên DataBase sẽ backup theo dạng:TenDataBase_Ngay_+’.bak’-Tạo một con trỏ duyệt danh sách các DataBase của hệ thống.-Thực hiện BackupData tương ứng.

Mẫu code:

DECLARE @name VARCHAR(50) — Tên Databse sẽ duyệtDECLARE @path VARCHAR(256) — đường dẫn file backupDECLARE @fileName VARCHAR(256) — tên file backupDECLARE @fileDate VARCHAR(20) — đường dẫn để đặt theo tên từng file backup

SET @path = ‘D:\ThuMucbackUp\’ — thu muc chua cac file duoc Backup

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)–ngay thang se backup–Định nghĩa CursorDECLARE db_cursor CURSOR FORSELECT nameFROM master.dbo.sysdatabases –lấy danh sách tên DataBase từ hệ thốngWHERE name NOT IN (‘master’,'model’,'msdb’,'tempdb’) –những DataBase không cần backup, nếu bạn muốn backup hết thì bỏ dòng này

OPEN db_cursor –mở con trỏ để duyệt danh sáchFETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0BEGINSET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’ — Tên Datase sẽ được lưu.BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name –duyet record kế tiếpEND

CLOSE db_cursor –đóng con trỏDEALLOCATE db_cursor — giải phóng con trỏ

//======================================================================================================================================================================//

How to Create and Use a Sample SQL Cursor and T-SQL Cursor Example Code

Here is a SQL cursor example code created for looping through a list of records as a result of a select query, which enables the sql developer to execute a stored procedure for each row in the cursor which use the values fetched by the cursor as the input arguments. The sample cursor is developed on a MS SQL Server and is a sample for sql server cursor. The sql codes may use t-sql codes so the sql cursor example may have differences than a typical pl/sql cursor or an Oracle cursor.

The sample sql cursor codes below illustrates a process of merging duplicate customer records kept in an application database. Assume that the duplicate customers list and relation among the duclicate customer records are inserted into and kept in a table named DuplicateCustomers which is simply formed of columns MasterCustomerId, DuplicateCustomerId and some other columns like MergeDate, IsMerged, MergedByUserId, InsertedDate, InsertedByUserId, etc which are used during processing some details and useful in the reporting of the duplicate record merge process results.

The list of the original customer records and the duplicate customer records can be selected by the sql select query below:SELECT MasterCustomerId, DuplicateCustomerIdFROM DuplicateCustomers WHERE IsMerged = 0

You can either create a temporary table to keep the result set in order to use your initial set of records in the next steps of your process. Or you can just use the above transact sql query to supply your records set to feed the t-sql cursor example we will create.

Here with the variable declarations we will set column values we fetch with the tsql cursor to the variables.

DECLARE @MergeDate DatetimeDECLARE @MasterId IntDECLARE @DuplicateId Int

Then the sql cursor definition or the t-sql cursor declaration code takes place.DECLARE merge_cursor CURSOR FAST_FORWARD FORSELECT MasterCustomerId, DuplicateCustomerIdFROM DuplicateCustomers WHERE IsMerged = 0

During the example sql cursor declaration you can set the sql cursor properties or the attributes of the cursor. Note that the sample cursor declaration uses the FAST_FORWARD key attribute in order to create a sql cursor with a high performance. Since FAST_FORWARD states that the cursor is FORWARD_ONLY and READ_ONLY the performance of the cursor is optimized.

The t-sql syntax of cursor declaration command DECLARE CURSOR is stated as below in MSDN :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 ] ] ]

You can find more on how to declare a t-sql cursor and cursor attributes in Books Online

With the call of the OPEN command the t-sql server cursor is opened and the cursor is populated with the data by the execution of the select query of the DECLARE CURSOR command.OPEN merge_cursor

So the OPEN command runs or executes the "SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0" select query defined in the DECLARE CURSOR definition command which is set after FOR key. With the execution of this select query the cursor is populated with the rows or the data returned as a result set of the query.

The next step in using a cursor is fetching the rows from the populated cursor one by one.FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId

The syntax of the FETCH command is as followsFETCH [ [ NEXT | PRIOR | FIRST | LAST| ABSOLUTE { n | @nvar }| RELATIVE { n | @nvar }]FROM]{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }[ INTO @variable_name [ ,...n ] ]

With the use of the NEXT, the FETCH NEXT command returns the next row following the current row. If FETCH NEXT is called for the first time for a cursor, or we can say if it is called after the OPEN CURSOR command, then the first row in the returned result set is fetched or returned. The column values in the returned row can be set into variables with the INTO key and by giving the names of the variables as a comma seperated list after the INTO key.

So for our example the first row in the return result set of the cursor is set into two variables named @MasterId and @DuplicateId. Here one important point is the first column of the result set (column named MasterCustomerId) is set to first variable defined in the list which is the @MasterId variable. And the secod column named DuplicateCustomerId is set to the second variable @DuplicateId.

So the variable types must be carefully declared according to the column types of the selected rows.

After the FETCH command, you should always control the value of the @@FETCH_STATUS. This variable returns the status of the last cursor FETCH command in the current connection.

The possible return values of @@FETCH_STATUS are;

0 FETCH statement was successful

-1FETCH statement failed or the row was beyond the result set

-2 Row fetched is missing

By always checking the @@FETCH_STATUS and controlling that it is value is equal to "0" we will have a new row fetched. When the fetched status is different than the "0" we can say that we have no more records are fetched. In short, the value of @@FETCH_STATUS variable is the controlling parameter of the loop we will use during processing all records or rows in the cursor.

In the body part of the WHILE statement the codes to process each row returned by the cursor takes place. This code block changes according to your reason to create and use a cursor. I placed an EXEC call for a sql stored procedure and an UPDATE sql statement here in order to show as a sample.

The most important thing to care for the inside codes of the WHILE code block is the last code statement FETCH NEXT command is recalled to get the next row from the return cursor data set.

After all the records are processed the @@FETCH_STATUS parameter returns -1, so the cursor can be now closed with the CLOSE CURSOR command. CLOSE CURSOR releases the current result set. And the DEALLOCATE CURSOR command releases the last cursor reference.

Here you can find the full sql cursor example code used in this article for explaining the t-sql cursors in SQL Server.

DECLARE @MergeDate DatetimeDECLARE @MasterId IntDECLARE @DuplicateId Int

SELECT @MergeDate = GetDate()

DECLARE merge_cursor CURSOR FAST_FORWARD FOR SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0

OPEN merge_cursor

FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId

WHILE @@FETCH_STATUS = 0BEGINEXEC MergeDuplicateCustomers @MasterId, @DuplicateId

UPDATE DuplicateCustomersSETIsMerged = 1,MergeDate = @MergeDateWHEREMasterCustomerId = @MasterId ANDDuplicateCustomerId = @DuplicateId

FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateIdEND

CLOSE merge_cursor DEALLOCATE merge_cursor

//=====================================================================================================================================================================//

DECLARE CURSOR (Transact-SQL)SQL Server 2008 R2

Other Versions

15 out of 21 rated this helpful Rate this topic

Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

Transact-SQL Syntax Conventions

Syntax

ISO SyntaxDECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ][;]Transact-SQL Extended SyntaxDECLARE 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 ] ] ][;]

Arguments

cursor_name

Is the name of the Transact-SQL server cursor defined. cursor_name must conform to the rules for identifiers. For more information about rules for identifiers, see Using Identifiers As Object Names .

INSENSITIVE

Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications. When ISO syntax is used, if INSENSITIVE is omitted, committed deletes and updates made to the underlying tables (by any user) are reflected in subsequent fetches.

SCROLL

Specifies that all fetch options (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) are available. If SCROLL is not specified in an ISO DECLARE CURSOR, NEXT is the only fetch option supported. SCROLL cannot be specified if FAST_FORWARD is also specified.

select_statement

Is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within select_statement of a cursor declaration.

SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type. For more information, see Using Implicit Cursor Conversions.

READ ONLY

Prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.

UPDATE [OF column_name [,...n]]

Defines updatable columns within the cursor. If OF column_name [,...n] is specified, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated.

cursor_name

Is the name of the Transact-SQL server cursor defined. cursor_name must conform to the rules for identifiers. For more information about rules for identifiers, see Using Identifiers As Object Names .

LOCAL

Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

GLOBAL

Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.

Note

If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option. In SQL Server version 7.0, this option defaults to FALSE to match earlier versions of SQL Server, in which all cursors were global. The default of this option may change in future versions of SQL Server. For more information, see Setting Database Options.

FORWARD_ONLY

Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors.

STATIC

Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

KEYSET

Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset.

Note

If the query references at least one table without a unique index, the keyset cursor is converted to a static cursor.

Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the owner scrolls around the cursor. Inserts made by other users are not visible (inserts cannot be made through a Transact-SQL server cursor). If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.

DYNAMIC

Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.

FAST_FORWARD

Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

Note

In SQL Server 2000, FAST_FORWARD and FORWARD_ONLY cursor options are

mutually exclusive. If both are specified, an error is raised. In SQL Server 2005 and later, both keywords can be used in the same DECLARE CURSOR statement.

READ_ONLY

Prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.

SCROLL_LOCKS

Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD or STATIC is also specified.

OPTIMISTIC

Specifies that positioned updates or deletes made through the cursor do not succeed 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. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.

TYPE_WARNING

Specifies that a warning message is sent to the client when the cursor is implicitly converted from the requested type to another.

select_statement

Is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within select_statement of a cursor declaration.

Note

You can use a query hint within a cursor declaration; however, if you also use the FOR UPDATE OF clause, specify OPTION (query_hint) after FOR UPDATE OF.

SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type. For more information, see Implicit Cursor Conversions.

FOR UPDATE [OF column_name [,...n]]

Defines updatable columns within the cursor. If OF column_name [,...n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.

Remarks

DECLARE CURSOR defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. The OPEN statement populates the result set, and FETCH returns a row from the result set. The CLOSE statement releases the current result set associated with the cursor. The DEALLOCATE statement releases the resources used by the cursor.

The first form of the DECLARE CURSOR statement uses the ISO syntax for declaring cursor behaviors. The second form of DECLARE CURSOR uses Transact-SQL extensions that allow you to define cursors using the same cursor types used in the database API cursor functions of ODBC or ADO.

You cannot mix the two forms. If you specify the SCROLL or INSENSITIVE keywords before the CURSOR keyword, you cannot use any keywords between the CURSOR and FOR select_statement keywords. If you specify any keywords between the CURSOR and FOR select_statement keywords, you cannot specify SCROLL or INSENSITIVE before the CURSOR keyword.

If a DECLARE CURSOR using Transact-SQL syntax does not specify READ_ONLY, OPTIMISTIC, or SCROLL_LOCKS, the default is as follows:

• If the SELECT statement does not support updates (insufficient permissions, accessing remote tables that do not support updates, and so on), the cursor is READ_ONLY.

• STATIC and FAST_FORWARD cursors default to READ_ONLY.

• DYNAMIC and KEYSET cursors default to OPTIMISTIC.

Cursor names can be referenced only by other Transact-SQL statements. They cannot be referenced by database API functions. For example, after declaring a cursor, the cursor name cannot be referenced from OLE DB, ODBC or ADO functions or methods. The cursor rows cannot be fetched using the fetch functions or methods of the APIs; the rows can be fetched only by Transact-SQL FETCH statements.

After a cursor has been declared, these system stored procedures can be used to determine the characteristics of the cursor.

System stored procedures Description

sp_cursor_list Returns a list of cursors currently visible on the connection and their attributes.

sp_describe_cursor Describes the attributes of a cursor, such as whether it is a forward-only or scrolling cursor.

sp_describe_cursor_columns Describes the attributes of the columns in the cursor result set.

sp_describe_cursor_tables Describes the base tables accessed by the cursor.

Variables may be used as part of the select_statement that declares a cursor. Cursor variable values do not change after a cursor is declared. In SQL Server version 6.5 and earlier, variable values are refreshed every time a cursor is reopened.

Permissions

DECLARE CURSOR permissions default to any user that has SELECT permissions on the views, tables, and columns used in the cursor.

Examples

A. Using simple cursor and syntax

The result set generated at the opening of this cursor includes all rows and all columns in the table. This cursor can be updated, and all updates and deletes are represented in fetches made against this cursor. FETCH NEXT is the only fetch available because the SCROLL option has not been specified.USE AdventureWorks2008R2;GODECLARE vend_cursor CURSOR FOR SELECT BusinessEntityID, Name, CreditRating FROM Purchasing.VendorOPEN vend_cursorFETCH NEXT FROM vend_cursor;

B. Using nested cursors to produce report output

The following example shows how cursors can be nested to produce complex reports. The inner cursor is declared for each vendor.USE AdventureWorks2008R2;GOSET 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 BusinessEntityID, NameFROM Purchasing.VendorWHERE PreferredVendorStatus = 1ORDER BY BusinessEntityID;

OPEN vendor_cursor;

FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name;

WHILE @@FETCH_STATUS = 0BEGIN 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 AS pv INNER JOIN Production.Product AS v ON pv.ProductID = v.ProductID AND pv.BusinessEntityID = @vendor_id; -- Variable value from the outer cursor

OPEN product_cursor; FETCH NEXT FROM product_cursor INTO @product;

IF @@FETCH_STATUS <> 0 PRINT ' <<None>>' ;

WHILE @@FETCH_STATUS = 0 BEGIN

SELECT @message = ' ' + @product PRINT @message FETCH NEXT FROM product_cursor INTO @product; END;

CLOSE product_cursor; DEALLOCATE product_cursor; -- Get the next vendor. FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name;ENDCLOSE vendor_cursor;DEALLOCATE vendor_cursor;

See Also

Reference@@FETCH_STATUS (Transact-SQL)

CLOSE (Transact-SQL)

Cursors (Transact-SQL)

DEALLOCATE (Transact-SQL)

FETCH (Transact-SQL)

SELECT (Transact-SQL)

sp_configure (Transact-SQL)

Top of Form

Did you find this helpful? Yes No

Bottom of Form

Community Content Add

FAQ

Cursor To Find Orphaned Records

$0--Hopefully, this displays properly on the msdn website. This is my 3rd, and final, time uploading this text.$0 $0$0 $0 $0DECLARE @_ColumnName NVARCHAR(500) $0 $0DECLARE @_PrimaryTableName NVARCHAR(500) $0 $0DECLARE @_ColumnType NVARCHAR(500) $0 $0$0 $0 $0--Change the following three values to meet your needs. $0 $0SET @_ColumnName = 'PolicyID' $0 $0SET @_PrimaryTableName = 'tbl_policies' $0 $0SET @_ColumnType = 'int' $0 $0SET nocount ON $0 $0$0 $0 $0DECLARE orph_cursor CURSOR FOR $0 $0 SELECT a.name AS tablename, $0 $0 b.name AS columnname $0 $0 FROM sys.tables a $0 $0 INNER JOIN sys.columns b $0 $0 ON a.object_id = b.object_id $0 $0 INNER JOIN sys.types c $0 $0 ON b.system_type_id = c.system_type_id $0 $0 WHERE b.name LIKE '%' + @_ColumnName + '%' $0 $0 AND c.name = @_ColumnType $0 $0$0 $0 $0OPEN orph_cursor $0 $0$0 $0 $0DECLARE @TableName VARCHAR(500) $0 $0DECLARE @ColumnName VARCHAR(500) $0 $0DECLARE @Query NVARCHAR(500) $0 $0DECLARE @RowCount NVARCHAR(500) $0 $0$0 $0 $0FETCH NEXT FROM orph_cursor INTO @TableName, @ColumnName $0 $0$0 $0 $0WHILE @@FETCH_STATUS = 0 $0 $0 BEGIN $0 $0 SET @Query = 'SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + $0 $0 ' NOT IN (SELECT ' + $0 $0 @_ColumnName + ' FROM ' + @_PrimaryTableName + $0 $0 ')' $0 $0$0 $0 $0 EXEC Sp_executesql @Query $0 $0$0 $0 $0 SELECT @RowCount = @@ROWCOUNT $0 $0$0 $0 $0 IF @RowCount <> 0 $0 $0 BEGIN $0 $0 PRINT @Query $0 $0$0 $0 $0 PRINT @TableName + ' - ' + @RowCount $0 $0$0 $0 $0 PRINT '----------' $0 $0 END $0 $0$0 $0 $0 FETCH NEXT FROM orph_cursor INTO @TableName, @ColumnName $0 $0 END $0 $0$0 $0 $0CLOSE orph_cursor $0 $0$0 $0 $0DEALLOCATE orph_cursor$0 $0$0 $0

History

• 2/6/2012

• Dataknox

• 2/6/2012

• Dataknox

Use cursor to change the permissions of all Stored Procedures We can use cursor to grant execute permission to all the stored procedures in a database. $0See the code below:

DECLARE Proc_Cursor CURSOR

FOR SELECT name FROM sysobjects WHERE type = 'P' and xtype = 'P'

OPEN Proc_Cursor

DECLARE @Name VARCHAR(500)

DECLARE @Query VARCHAR(500)

FETCH NEXT FROM Proc_Cursor INTO @Name;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Query = 'GRANT EXEC ON ' + @Name + ' To Public'

EXEC(@Query)

FETCH NEXT FROM Proc_Cursor INTO @Name;

END

CLOSE Proc_Cursor

DEALLOCATE Proc_Cursor