【SQL开源代码栏目提醒】:网学会员,鉴于大家对SQL开源代码十分关注,论文会员在此为大家搜集整理了“CH19.sql”一文,供大家参考学习!
USE 练习02
-- P19-2
DECLARE 书籍 CURSOR
FOR SELECT 书籍名称
FROM 书籍
WHERE 单价 < 400
OPEN 书籍
DECLARE @name varchar(25),
@list varchar(500),
@cnt int
SET @list = '低于400的书有:'
SET @cnt = 0
FETCH NEXT FROM 书籍
INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @list = @list + @name + ', '
SET @cnt = @cnt + 1
FETCH NEXT FROM 书籍
INTO @name
END
SET @list = @list + '共' + CAST(@cnt AS VARCHAR) + '本'
CLOSE 书籍
DEALLOCATE 书籍
PRINT @list
-- P19-4
DECLARE @list varchar(500);
SET @list = '低于400的书有:'
SELECT @list = @list + 书籍名称 + ', '
FROM 书籍
WHERE 单价 < 400
SET @list = @list + '共' + CAST(@@ROWCOUNT AS VARCHAR) + '本'
PRINT @list
-- P19-6
DECLARE MyCursorl
SCROLL CURSOR
FOR SELECT * FROM 标标公司
-- P19-9a
DECLARE MyCursor2 CURSOR
GLOBAL
FOR SELECT * FROM 标标公司
-- P19-9b
DECLARE MyCursor3 CURSOR
SCROLL KEYSET SCROLL_LOCKS TYPE_WARNING
FOR SELECT * FROM 标标公司
-- P19-12
DECLARE MyCursor CURSOR
SCROLL STATIC
FOR SELECT *
FROM 标标公司
OPEN MyCursor
FETCH NEXT FROM MyCursor
FETCH LAST FROM MyCursor
CLOSE MyCursor
DEALLOCATE MyCursor
-- P19-13
DECLARE MyCursor CURSOR
LOCAL SCROLL STATIC
FOR SELECT *
FROM 标标公司
OPEN MyCursor
DECLARE @NAME char(20)
DECLARE @money money
FETCH ABSOLUTE 3 FROM MyCursor
INTO @name, @money
PRINT @name + '' + convert(varchar, @money)
FETCH RELATIVE -2 FROM MyCursor
INTO @name, @money
PRINT @name + '' + convert(varchar, @money)
-- P19-14
DECLARE MyCursor CURSOR
SCROLL STATIC
FOR SELECT *
FROM 标标公司
OPEN MyCursor
DECLARE @NAME char(20)
DECLARE @money money
FETCH NEXT FROM MyCursor
INTO @name, @money
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @money > 50
SET @money = @money * 0.9
ELSE SET @money = @money * 0.95
PRINT @name + convert(varchar, @money)
FETCH NEXT FROM MyCursor
INTO @name, @money
END
CLOSE MyCursor
DEALLOCATE MyCursor
-- P19-15
DECLARE CUR_客户 CURSOR
FOR SELECT 客户编号, 客户名称
FROM 客户
DECLARE @custno INT, @custname VARCHAR(20)
DECLARE @orderno INT, @orderlist VARCHAR(200)
OPEN CUR_客户
FETCH CUR_客户
INTO @custno, @custname
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE CUR_订单 CURSOR
FOR SELECT 订单序号
FROM 订单
WHERE 客户编号 = @custno
SET @orderlist = @custname
OPEN CUR_订单
FETCH CUR_订单 INTO @orderno
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @orderlist = @orderlist + ', '
+ CAST (@orderno AS VARCHAR)
FETCH CUR_订单 INTO @orderno
END
PRINT @orderlist
CLOSE CUR_订单
DEALLOCATE CUR_订单
FETCH CUR_客户 INTO @custno, @custname
END
CLOSE CUR_客户
DEALLOCATE CUR_客户
-- P19-17
DECLARE MyCursor CURSOR
LOCAL SCROLL_LOCKS
FOR SELECT 价格 FROM 旗旗公司
FOR UPDATE
OPEN MyCursor
DECLARE @money money
FETCH MyCursor INTO @money
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @money <=50
BEGIN
SET @money = @money * 1.1
UPDATE 旗旗公司
SET 价格 = @money
WHERE CURRENT OF MyCursor
END
FETCH MyCursor INTO @money
END
-- P19-18
DECLARE cur_declare CURSOR
FOR SELECT *
FROM 标标公司
DECLARE @cur_var CURSOR
SET @cur_var = cur_declare
OPEN @cur_var
FETCH NEXT FROM @cur_var
CLOSE @cur_var
DEALLOCATE @cur_var
-- P19-19a
DECLARE @cur_var CURSOR
SET @cur_var = CURSOR
FORWARD_ONLY KEYSET
FOR SELECT *
FROM 标标公司
OPEN @cur_var
FETCH NEXT FROM @cur_var
-- P19-19b
CREATE PROC testproc
@cur_parm CURSOR VARYING OUTPUT
AS
SET @cur_parm = CURSOR
FORWARD_ONLY STATIC
FOR SELECT * FROM 标标公司
OPEN @cur_parm
GO
DECLARE @cur_var CURSOR
EXEC testproc @cur_var OUTPUT
FETCH NEXT FROM @cur_var
-- P19-21
DECLARE cur CURSOR
FOR SELECT * FROM 标标公司
DECLARE cur CURSOR LOCAL
FOR SELECT * FROM 旗旗公司
OPEN cur
FETCH cur
OPEN GLOBAL cur
FETCH GLOBAL cur
CLOSE GLOBAL cur
DEALLOCATE GLOBAL cur
-- P19-23
CREATE PROCEDURE open_cursor
@资料表名称 varchar(30),
@查询栏位 varchar(30),
@cur_parm CURSOR VARYING OUTPUT
AS
/* 检查指定资料表是否存在 */
IF OBJECTPROPERTY ( object_id(@资料表名称),'ISTABLE') = 1
BEGIN
/* 建立一个内含宣告 Cursor 的字串 */
DECLARE @sql_str varchar(200)
SET @
sql_str = 'DECLARE global_cursor CURSOR'
+ ' FOR'
+ ' SELECT ' + @
查询栏位
+ ' FROM ' + @资料表名称
EXEC(@sql_str)
SET @cur_parm = global_cursor
OPEN @cur_parm
DEALLOCATE global_cursor
END
GO
DECLARE @cur_var CURSOR
EXECUTE open_cursor '旗旗公司', '*', @cur_var OUTPUT
/* 检查 Cursor 是否正确传回 */
IF CURSOR_STATUS ('variable', '@cur_var') = 1
FETCH @cur_var
ELSE
SELECT 'Cursor open fail!'
-- P19-25
DECLARE test_cur CURSOR
FOR SELECT * FROM 标标公司
DECLARE @test_cur CURSOR
SET @test_cur = test_cur
OPEN test_cur
DECLARE @result_cur CURSOR
-- 查询 @test_cur 的信息, 该信息由 @result_cur 传回
EXEC sp_describe_cursor @result_cur OUTPUT, 'variable', '@test_cur'
FETCH @result_cur
CLOSE test_cur
DEALLOCATE test_cur