2024年6月5日发(作者：)
sql 游标练习题
游标是一种在数据库中进行数据操作的工具，它能够对查询结果集
进行逐行操作，提供了对数据的灵活处理能力。本文将为您提供一些
SQL 游标练习题，帮助您熟悉和掌握游标的使用。
一、游标基础练习题
1. 创建一个游标，遍历一个表中的所有记录，并输出每条记录的 ID
和名称。
```
DECLARE @id INT
DECLARE @name VARCHAR(100)
DECLARE cursor_name CURSOR FOR
SELECT id, name FROM table_name
OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ID: ' + CAST(@id AS VARCHAR(10)) + ', Name: ' +
@name
FETCH NEXT FROM cursor_name INTO @id, @name
END
CLOSE cursor_name
DEALLOCATE cursor_name
```
2. 修改上述游标的功能，在遇到名称为 "John" 的记录时停止遍历。
```
DECLARE @id INT
DECLARE @name VARCHAR(100)
DECLARE cursor_name CURSOR FOR
SELECT id, name FROM table_name
OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @name = 'John'
BREAK
PRINT 'ID: ' + CAST(@id AS VARCHAR(10)) + ', Name: ' +
@name
FETCH NEXT FROM cursor_name INTO @id, @name
END
CLOSE cursor_name
DEALLOCATE cursor_name
```
二、游标进阶练习题
1. 创建一个游标，更新一个表中的记录，将名称为 "Tom" 的记录修
改为 "Jerry"。
```
DECLARE @id INT
DECLARE @name VARCHAR(100)
DECLARE cursor_name CURSOR FOR
SELECT id, name FROM table_name FOR UPDATE OF name
OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @name = 'Tom'
BEGIN
SET @name = 'Jerry'
UPDATE table_name SET name = @name WHERE CURRENT
OF cursor_name
END
FETCH NEXT FROM cursor_name INTO @id, @name
END
CLOSE cursor_name
DEALLOCATE cursor_name
```
2. 创建一个游标，删除一个表中的记录，将名称为 "Peter" 的记录
删除。
```
DECLARE @id INT
DECLARE @name VARCHAR(100)
DECLARE cursor_name CURSOR FOR
SELECT id, name FROM table_name FOR UPDATE OF name
OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
发布评论