Cộng đồng chia sẻ tri thức Lib24.vn

SQLServerCh16

d41d8cd98f00b204e9800998ecf8427e
Gửi bởi: Khoa CNTT - HCEM 24 tháng 2 2021 lúc 9:37:58 | Được cập nhật: 24 tháng 3 lúc 12:03:03 Kiểu file: PPTX | Lượt xem: 194 | Lượt Download: 1 | File size: 0.900693 Mb

Nội dung tài liệu

Tải xuống
Link tài liệu:
Tải xuống

Các tài liệu liên quan


Có thể bạn quan tâm


Thông tin tài liệu

Chapter 16

How to work
with cursors

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 1

Objectives
Applied
 Given the specifications for a database problem that can be solved
by using Transact-SQL cursors, write a script that solves it.
Knowledge
 Describe the use of cursors.
 Describe the two ways in which cursors can be implemented for a
SQL Server database.
 Describe these types of cursors in terms of whether they are
scrollable and sensitive: dynamic, keyset-driven, static, and
forward-only.
 Describe local scope and global scope as they apply to cursors.
 Describe these concurrency options for Transact-SQL cursors:
optimistic, scroll-locks, and read-only.

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 2

Objectives (cont.)
 Describe the way rows are updated or deleted when you use
cursors.
 Given a script, stored procedure, user-defined function, or trigger
that uses a cursor, explain what each statement does.

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 3

How a cursor works
A result set with a cursor pointing to the first row
Cursor

The first row retrieved through the cursor

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 4

The two implementations of SQL Server cursors
 Cursors implemented through standard database APIs
 Cursors implemented through Transact-SQL

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 5

Two common uses for Transact-SQL cursors
 For your own use in scripts and procedures
 For use in procedures that provide database access to application
programs that can’t use standard database APIs

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 6

When to use Transact-SQL cursors
 For administrative scripts and procedures that manipulate
database objects using dynamic SQL or system stored procedures
 For scripts or procedures that need to do something different to
each row in a result set based on criteria that are more complex
than can be stated in a WHERE clause

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 7

The seven types of SQL Server cursors
Standard API
cursor type
Dynamic
Keyset-driven

Transact-SQL
cursor keyword
DYNAMIC
KEYSET

Static
Forward-only
(none)

STATIC
FORWARD_ONLY
FORWARD_ONLY
KEYSET

Yes
No
No

(none)

FORWARD_ONLY

No

Sensitive to
database
changes
Yes
Yes, for updates and
deletes, but not for
insertions
No
Yes
Yes, for updates and
deletes, but not for
insertions
No

(none)

FAST_FORWARD

No

No

Murach's SQL Server 2012, C16

Scrollable
Yes
Yes

© 2012, Mike Murach & Associates, Inc.

Slide 8

The SQL statements for cursor processing
 DECLARE CURSOR
 OPEN
 FETCH
 CLOSE
 DEALLOCATE

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 9

A SQL script that declares and uses a cursor
DECLARE Vendors_Cursor CURSOR
STATIC
FOR
SELECT VendorID, VendorName
FROM Vendors
ORDER BY VendorName;
OPEN Vendors_Cursor;
FETCH NEXT FROM Vendors_Cursor;
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM Vendors_Cursor;
CLOSE Vendors_Cursor;
DEALLOCATE Vendors_Cursor;

The response from the system

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 10

The syntax of the DECLARE CURSOR statement
DECLARE cursor_name CURSOR
[LOCAL|GLOBAL]
[FORWARD_ONLY|SCROLL]
[FAST_FORWARD|STATIC|KEYSET|DYNAMIC]
[READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]
FOR select_statement
[FOR UPDATE [OF column_name [, ...]]]

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 11

A cursor that uses the default options
DECLARE Invoices_Cursor CURSOR
FOR
SELECT * FROM Invoices;

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 12

A dynamic local cursor
DECLARE VendorInvoice_Cursor CURSOR
LOCAL DYNAMIC
FOR
SELECT VendorName, InvoiceDate, InvoiceTotal
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID;

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 13

A cursor for updating specific columns
DECLARE VendorUpdate_Cursor CURSOR
GLOBAL SCROLL DYNAMIC SCROLL_LOCKS
FOR
SELECT * FROM Vendors
FOR UPDATE OF VendorName, VendorAddress1,
VendorAddress2, VendorCity,
VendorState, VendorZipCode,
VendorPhone;

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 14

Terms
 Cursor scrollability
 Cursor sensitivity
 Scope of a cursor

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 15

The syntax of the FETCH statement
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name
[INTO @variable_name [, ...]]

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 16

A DECLARE CURSOR statement
DECLARE Vendor_Cursor CURSOR
STATIC
FOR SELECT VendorID, VendorName FROM Vendors;

FETCH statements for retrieving rows
from the cursor
-- Retrieves the next row
FETCH FROM Vendor_Cursor;
-- Retrieves the next row
FETCH NEXT FROM Vendor_Cursor;
-- Retrieves the previous row
FETCH PRIOR FROM Vendor_Cursor;
-- Retrieves the first row
FETCH FIRST FROM Vendor_Cursor;
-- Retrieves the last row
FETCH LAST FROM Vendor_Cursor;
-- Retrieves the third row
FETCH ABSOLUTE 3 FROM Vendor_Cursor;

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 17

FETCH statements for retrieving rows
from the cursor (continued)
-- Retrieves the tenth row after the current row
FETCH RELATIVE 10 FROM Vendor_Cursor;
-- Retrieves the row two rows before the current row
FETCH RELATIVE –2 FROM Vendor_Cursor;
-- Retrieves the current row again
FETCH RELATIVE 0 FROM Vendor_Cursor;
-- Retrieves the next row and assigns the values
-- to two local variables
FETCH FROM Vendor_Cursor
INTO @VendorIDVar,@VendorNameVar;

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 18

@@FETCH_STATUS system function values
Value
0
-1
-2

Description
The FETCH was successful.
The FETCH was unsuccessful because
it reached the end of the result set.
The FETCH was unsuccessful because
the row was deleted.

Warning
 @@FETCH_STATUS is global to all of the cursors open on the
current connection. So if you’re using multiple cursors, don’t code
other statements between Fetch and @@FETCH_STATUS.

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 19

A WHILE loop that steps forward
through a result set
FETCH FIRST FROM Vendor_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
...
FETCH NEXT FROM Vendor_Cursor;
END;

Murach's SQL Server 2012, C16

© 2012, Mike Murach & Associates, Inc.

Slide 20