DECLARE
@id INT,
@name NVARCHAR(10)
DECLARE cursor_name CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT
id,
name
FROM dbo.tablename WITH (NOLOCK)
ORDER BY id
OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @id, @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
/* your sql commands */
FETCH NEXT FROM cursor_name INTO @id, @name
END
CLOSE cursor_name
DEALLOCATE cursor_name
------------游标的使用-----------------------
------------游标的使用-----------------------
CREATE TABLE #Shop
(
BrandShopCode CHAR(9),
ShopName NVARCHAR(200),
InDate NVARCHAR(10)
)
DECLARE @BrandCode VARCHAR(4) = 'BC';
DECLARE @ShopCode CHAR(4) = '';
DECLARE @ShopName NVARCHAR(200) = '';
DECLARE @InDateTime DATETIME;
DECLARE cur CURSOR FOR SELECT ShopCode,ShopName,InDateTime FROM Shop WHERE BrandCode=@BrandCode
OPEN cur
FETCH NEXT FROM cur INTO @ShopCode,@ShopName,@InDateTime
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #Shop(BrandShopCode,ShopName,InDate)
SELECT SUBSTRING(CAST(@BrandCode AS CHAR(9)),1,2)+'——'+LEFT(@ShopCode,2)+'~'+SUBSTRING(@ShopCode,3,2) AS BrandShopCode
, CASE WHEN CHARINDEX(@BrandCode,LTRIM(@ShopName))=0 THEN @ShopName
ELSE LEFT(LTRIM(@ShopName),2)+'——'+SUBSTRING(@ShopName,3,LEN(@ShopName))
END AS ShopName
, CONVERT(NVARCHAR(10),@InDateTime,126) AS InDate
FETCH NEXT FROM cur INTO @ShopCode,@ShopName,@InDateTime
END
CLOSE cur
DEALLOCATE cur
SELECT * FROM #Shop
DROP TABLE #Shop
--------------------------------