Ada Lovelace: Sử dụng cursor ( con trỏ ) .

-- SQL Server cursor example - row-by-row operation - DECLARE CURSOR

DECLARE @dbName sysname

DECLARE AllDBCursor CURSOR STATIC LOCAL FOR

SELECT name FROM MASTER.dbo.sysdatabases

WHERE name NOT IN ('master','tempdb','model','msdb') ORDER BY name

OPEN AllDBCursor; FETCH AllDBCursor INTO @dbName;

WHILE (@@FETCH_STATUS = 0) -- loop through all db-s

BEGIN

/***** PROCESSING (like BACKUP) db by db goes here - record-by-record process *****/

PRINT @dbName

FETCH AllDBCursor INTO @dbName

END -- while

CLOSE AllDBCursor; DEALLOCATE AllDBCursor;

/* Messages

AdventureWorks

AdventureWorks2008

AdventureWorksDW

AdventureWorksDW2008

..... */

------------

-- T-SQL Cursor declaration and usage example - cursor loop syntax - using t-sql cursor

------------

USE AdventureWorks2008;

DECLARE curSubcategory CURSOR STATIC LOCAL -- sql declare cursor

FOR SELECT ProductSubcategoryID, Subcategory=Name

FROM Production.ProductSubcategory ORDER BY Subcategory

DECLARE @Subcategory varchar(40), @PSID int

OPEN curSubcategory

FETCH NEXT FROM curSubcategory INTO @PSID, @Subcategory -- sql fetch cursor

WHILE (@@fetch_status = 0) -- sql cursor fetch_status

BEGIN -- begin cursor loop

/***** USER DEFINED CODE HERE - POSSIBLY NESTED CURSOR *****/

DECLARE @Msg varchar(128)

SELECT @Msg = 'ProductSubcategory info: ' + @Subcategory + ' '+

CONVERT(varchar,@PSID)

PRINT @Msg

FETCH NEXT FROM curSubcategory INTO @PSID, @Subcategory -- sql fetch cursor

END -- end cursor loop

CLOSE curSubcategory

DEALLOCATE curSubcategory

/* Partial output in Messages

ProductSubcategory info: Bib-Shorts 18

ProductSubcategory info: Bike Racks 26

ProductSubcategory info: Bike Stands 27

ProductSubcategory info: Bottles and Cages 28

ProductSubcategory info: Bottom Brackets 5

ProductSubcategory info: Brakes 6

------------

-- T SQL Search All Text & XML Columns in All Tables

------------

-- SQL nested cursors - sql server nested cursor - transact sql nested cursor

USE AdventureWorks;

-- SQL Server create stored procedure with nested cursors

CREATE PROC sprocSearchKeywordInAllTables @Keyword NVARCHAR(64)

BEGIN

SET NOCOUNT ON

DECLARE @OutputLength VARCHAR(4),

@NolockOption CHAR(8)

SET @OutputLength = '256'

SET @NolockOption = ''

-- SET @NolockOption = '(NOLOCK)'

DECLARE @DynamicSQL NVARCHAR(MAX),

@SchemaTableName NVARCHAR(256),

@SchemaTableColumn NVARCHAR(128),

@SearchWildcard NVARCHAR(128)

SET @SearchWildcard = QUOTENAME('%' + @Keyword + '%',CHAR(39)+CHAR(39))

PRINT @SearchWildcard

DECLARE @SearchResults TABLE(

SchemaTableColumn NVARCHAR(384),

TextWithKeyword NVARCHAR(MAX)

)

DECLARE curAllTables CURSOR STATIC LOCAL FOR

SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS ST

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.'

+ QUOTENAME(TABLE_NAME)), 'IsMSShipped') != 1

ORDER BY ST

OPEN curAllTables

FETCH NEXT FROM curAllTables

INTO @SchemaTableName

WHILE (@@FETCH_STATUS = 0) -- Outer cursor loop

BEGIN

PRINT @SchemaTableName

SET @SchemaTableColumn = ''

DECLARE curAllColumns CURSOR FOR -- Nested cursor

SELECT QUOTENAME(COLUMN_NAME)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = PARSENAME(@SchemaTableName,1)

AND TABLE_SCHEMA = PARSENAME(@SchemaTableName,2)

AND DATA_TYPE IN ('varchar','nvarchar','char','nchar','xml')

ORDER BY ORDINAL_POSITION

OPEN curAllColumns

FETCH NEXT FROM curAllColumns

INTO @SchemaTableColumn

WHILE (@@FETCH_STATUS = 0) -- Inner cursor loop (nested cursor while)

BEGIN

PRINT ' ' + @SchemaTableColumn

SET @DynamicSQL = 'SELECT ''' + @SchemaTableName + '.' +

@SchemaTableColumn + ''', LEFT(CONVERT(nvarchar(max),' +

@SchemaTableColumn + '),' + @OutputLength + ') FROM ' +

@SchemaTableName + ' '+@NolockOption+

' WHERE CONVERT(nvarchar(max),' + @SchemaTableColumn +

') LIKE ' + @SearchWildcard

INSERT INTO @SearchResults

EXEC sp_executeSQL @DynamicSQL

FETCH NEXT FROM curAllColumns

INTO @SchemaTableColumn

END -- Inner cursor loop

CLOSE curAllColumns

DEALLOCATE curAllColumns

FETCH NEXT FROM curAllTables

INTO @SchemaTableName

END -- Outer cursor loop

CLOSE curAllTables

DEALLOCATE curAllTables

SELECT DISTINCT SchemaTableColumn, TextWithKeyWord FROM @SearchResults

END

EXEC sprocSearchKeywordInAllTables 'Hamilton'

EXEC sprocSearchKeywordInAllTables 'Sánchez'

EXEC sprocSearchKeywordInAllTables 'O''Donnell'

EXEC sprocSearchKeywordInAllTables 'Certification'

------------

The following nested cursors consist of an outer cursor for purchase orders header info and an inner cursor for the details of each purchase order. It is an example for MS SQL nested cursor loop.

------------
-- SQL Server Nested Cursors example - transact sql nested cursor

------------

-- SQL nested cursors - transact sql fetch_status - transact sql while loop

-- SQL nesting cursors - transact sql fetch next

-- T-SQL script for execution timing setup

USE AdventureWorks;

DBCC DROPCLEANBUFFERS

DECLARE @StartTime datetime

SET @StartTime = getdate()

-- Setup local variables

DECLARE @IterationID INT,

@OrderDetail VARCHAR(max),

@ProductName VARCHAR(10)

-- Setup table variable

DECLARE @Result TABLE (PurchaseOrderID INT, OrderDetail VARCHAR(max))

-- OUTER CURSOR declaration - transact sql declare cursor

DECLARE curOrdersForReport CURSOR STATIC LOCAL FOR

SELECT PurchaseOrderID

FROM Purchasing.PurchaseOrderHeader

WHERE Year(OrderDate) = 2004

AND Month(OrderDate) = 2

ORDER BY PurchaseOrderID

OPEN curOrdersForReport

FETCH NEXT FROM curOrdersForReport INTO @IterationID

PRINT 'OUTER LOOP START'

WHILE (@@FETCH_STATUS = 0) -- sql cursor fetch_status

BEGIN

SET @OrderDetail = ''

-- INNER CURSOR declaration - transact sql declare cursor

-- SQL Nested Cursor - sql cursor nested - cursor nesting

DECLARE curDetailList CURSOR STATIC LOCAL FOR

SELECT p.productNumber

FROM Purchasing.PurchaseOrderDetail pd

INNER JOIN Production.Product p

ON pd.ProductID = p.ProductID

WHERE pd.PurchaseOrderID = @IterationID

ORDER BY PurchaseOrderDetailID

OPEN curDetailList

FETCH NEXT FROM curDetailList INTO @ProductName

PRINT 'INNER LOOP START'

WHILE (@@FETCH_STATUS = 0)

BEGIN

SET @OrderDetail = @OrderDetail + @ProductName + ', '

FETCH NEXT FROM curDetailList INTO @ProductName

PRINT 'INNER LOOP'

END -- inner while

CLOSE curDetailList

DEALLOCATE curDetailList

-- Truncate trailing comma

SET @OrderDetail = left(@OrderDetail, len(@OrderDetail)-1)

INSERT INTO @Result VALUES (@IterationID, @OrderDetail)

FETCH NEXT FROM curOrdersForReport INTO @IterationID

PRINT 'OUTER LOOP'

END -- outer while

CLOSE curOrdersForReport

DEALLOCATE curOrdersForReport

-- Publish results

SELECT * FROM @Result ORDER BY PurchaseOrderID

-- Timing result

SELECT ExecutionMsec = datediff(millisecond, @StartTime, getdate())

-- 220 msecs

------------

-- Equivalent set-based operations solution

------------

-- Execution timing setup

DBCC DROPCLEANBUFFERS

DECLARE @StartTime datetime

SET @StartTime = getdate()

-- SQL comma-limited list generation

-- SQL nested select statement

-- SQL FOR XML PATH

SELECT

poh.PurchaseOrderID,

OrderDetail = Stuff((

-- SQL correlated subquery

SELECT ', ' + ProductNumber as [text()]

FROM Purchasing.PurchaseOrderDetail pod

INNER JOIN Production.Product p

ON pod.ProductID = p.ProductID

WHERE pod.PurchaseOrderID = poh.PurchaseOrderID

ORDER BY PurchaseOrderDetailID

FOR XML PATH ('')), 1, 1, '')

FROM Purchasing.PurchaseOrderHeader poh

WHERE Year(OrderDate) = 2004

AND Month(OrderDate) = 2

ORDER BY PurchaseOrderID ;

-- Timing result

SELECT ExecutionMsec = datediff(millisecond, @StartTime, getdate())

-- 110 msecs

/* Partial results

PurchaseOrderID OrderDetail

1696 GT-0820, GT-1209

1697 HN-6320, HN-7161, HN-7162, HN-8320, HN-9161, HN-9168

1698 NI-4127

1699 RM-T801

1700 LI-1201, LI-1400, LI-3800

1701 TI-R982, TI-T723
*/

The following example uses @@FETCH_STATUS to control the WHILE loop in a typical cursor application:

-- T-SQL cursor declaration

DECLARE curManager CURSOR FOR

SELECT EmployeeID,

Title

FROM AdventureWorks.HumanResources.Employee

WHERE Title LIKE '%manager%'

OR Title LIKE '%super%';

OPEN curManager;

FETCH NEXT FROM curManager;

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Cursor loop'

FETCH NEXT FROM curManager;

END; -- while

CLOSE curManager;

DEALLOCATE curManager;

/* Partial results

EmployeeID Title

3 Engineering Manager

EmployeeID Title

6 Marketing Manager

However, the @@FETCH_STATUS is global to all cursors on a connection, therefore using @@FETCH_STATUS to control nested cursors may not be advisable. To play it safe for the case of following triple nested cursors demonstration, we avoid using @@FETCH_STATUS. Instead we order the SELECTs for the cursor and find the max value on one unique column. We use a comparison between the running values and maximum value to control the loop. The OUTER cursor loop is based on OrderDate. The MIDDLE cursor loop is based PurchaseOrderID-s received on a particular date. The INNER cursor loop is based on the products belonging to a particular PurchaseOrderID.
This is the entire triple nested cursors T-SQL script:

-- MSSQL nested cursors

USE AdventureWorks

DECLARE @DateIteration DATETIME,

@IterationID INT,

@OrderDetail VARCHAR(1024),

@ProductNo VARCHAR(10)

DECLARE @MaxOrderDate DATETIME,

@MaxPOID INT,

@MaxProdNo VARCHAR(10)

DECLARE @Result TABLE(

OrderDate DATETIME,

PurchaseOrderID INT,

OrderDetail VARCHAR(1024)

)

DECLARE curOrderDate CURSOR FOR

SELECT DISTINCT OrderDate

FROM Purchasing.PurchaseOrderHeader

WHERE year(OrderDate) = 2002

AND month(OrderDate) = 7

ORDER BY OrderDate

SELECT @MaxOrderDate = OrderDate

FROM Purchasing.PurchaseOrderHeader

WHERE year(OrderDate) = 2002

AND month(OrderDate) = 7

OPEN curOrderDate

FETCH NEXT FROM curOrderDate

INTO @DateIteration

PRINT 'OUTER LOOP'

WHILE (1 < 2)

BEGIN

DECLARE curOrdersForReport CURSOR FOR

SELECT PurchaseOrderID

FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate = @DateIteration

ORDER BY PurchaseOrderID

SELECT @MaxPOID = PurchaseOrderID

FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate = @DateIteration

OPEN curOrdersForReport

FETCH NEXT FROM curOrdersForReport

INTO @IterationID

PRINT 'MIDDLE LOOP'

WHILE (1 < 2)

BEGIN

SET @OrderDetail = ''

DECLARE curDetailList CURSOR FOR

SELECT p.ProductNumber

FROM Purchasing.PurchaseOrderDetail pd

INNER JOIN Production.Product p

ON pd.ProductID = p.ProductID

WHERE pd.PurchaseOrderID = @IterationID

ORDER BY p.ProductNumber

SELECT @MaxProdNo = p.ProductNumber

FROM Purchasing.PurchaseOrderDetail pd

INNER JOIN Production.Product p

ON pd.ProductID = p.ProductID

WHERE pd.PurchaseOrderID = @IterationID

OPEN curDetailList

FETCH NEXT FROM curDetailList

INTO @ProductNo

PRINT 'INNER LOOP'

WHILE (1 < 2)

BEGIN

SET @OrderDetail = @OrderDetail + @ProductNo + ', '

IF (@ProductNo = @MaxProdNo)

BREAK

FETCH NEXT FROM curDetailList

INTO @ProductNo

PRINT 'INNER LOOP'

END

CLOSE curDetailList

DEALLOCATE curDetailList

INSERT INTO @Result

VALUES (@DateIteration,@IterationID,@OrderDetail)

IF (@IterationID = @MaxPOID)

BREAK

FETCH NEXT FROM curOrdersForReport

INTO @IterationID

PRINT 'MIDDLE LOOP'

END

CLOSE curOrdersForReport

DEALLOCATE curOrdersForReport

IF (@DateIteration = @MaxOrderDate)

BREAK

FETCH NEXT FROM curOrderDate

INTO @DateIteration

PRINT 'OUTER LOOP'

END

CLOSE curOrderDate

DEALLOCATE curOrderDate

SELECT * FROM @Result

/* Messages (partial)

OUTER LOOP

MIDDLE LOOP

INNER LOOP

...

Here is the result set:

OrderDate	PurchaseOrderID	OrderDetail
July 1, 2002	157	HJ-3416, HJ-3816, HJ-3824, HJ-5161, HJ-5162, HJ-5811,
July 1, 2002	158	BA-8327,
July 1, 2002	159	AR-5381,
July 1, 2002	160	HJ-3816, HJ-3824, HJ-5161,
July 1, 2002	161	SP-2981,
July 1, 2002	162	BE-2908,
July 1, 2002	163	RM-R800,
July 1, 2002	164	RM-T801,
July 1, 2002	165	CA-5965, CA-6738, CA-7457,
July 1, 2002	166	LI-1201, LI-1400, LI-3800, LI-5160,
July 1, 2002	167	LJ-5811, LJ-5818, LJ-7161, LJ-7162, LJ-9080, LJ-9161,
July 1, 2002	168	CB-2903, CN-6137, CR-7833,
July 1, 2002	169	LN-3410, LN-3416, LN-3816, LN-3824, LN-4400,
July 1, 2002	170	PD-T852,
July 1, 2002	171	CR-7833,
July 1, 2002	172	RA-2345,
July 13, 2002	173	PB-6109,
July 13, 2002	174	CR-9981,
July 13, 2002	175	SD-2342, SD-9872,
July 13, 2002	176	PA-187B, PA-361R, PA-529S, PA-632U,
July 24, 2002	177	SE-M236, SE-M798, SE-M940, SE-R581, SE-R908, SE-R995,
July 24, 2002	178	RF-9198,
July 24, 2002	179	FC-3982, FL-2301, RC-0291,
July 24, 2002	180	RM-M464, RM-M692,
July 24, 2002	181	TP-0923,
July 24, 2002	182	FC-3982, FL-2301,
July 24, 2002	183	RM-M464, RM-M692,
July 24, 2002	184	NI-9522,
July 24, 2002	185	FW-1000, FW-1200, FW-1400, FW-3400, FW-3800, FW-5160, FW-5800, FW-7160, FW-9160,
July 24, 2002	186	PD-M282, PD-M340,
July 24, 2002	187	HN-3824, HN-4402, HN-5161, HN-5162, HN-5400, HN-5811,
July 24, 2002	188	MS-1981, MS-2259, MS-2341, MS-2348, MS-6061,
July 24, 2002	189	KW-4091,
July 24, 2002	190	RM-R436, RM-R600, RM-R800,
July 24, 2002	191	LE-5160, LE-6000, SE-T312, SE-T762,
July 24, 2002	192	SH-4562,
July 27, 2002	193	SH-9312,
July 27, 2002	194	SE-M236, SE-M798,
July 27, 2002	195	GT-0820, GT-1209,
July 27, 2002	196	PD-M282, PD-M340,
July 27, 2002	197	SD-9872,
July 27, 2002	198	SE-R581, SE-R908,
July 27, 2002	199	SE-M940,
July 27, 2002	200	PD-M562,

Source : sqlusa.contro

Ada Lovelace

Tuesday, January 27, 2015

Sử dụng cursor ( con trỏ ) .

No comments:

Post a Comment