八、游标和数据库备份恢复

 

一、游标
1.示例
--定义scroll游标，并使用各种FETCH选项
use pubs
go
--select
select au_lname,au_fname from authors
      where city='oakland'
      order by au_lname,au_fname

--declare
declare authors_cursor scroll cursor for
select au_lname,au_fname from authors
      where city='oakland'
      order by au_lname,au_fname

--open
open authors_cursor

--fetch
fetch last from authors_cursor  --最后一条
fetch prior from authors_cursor  --前一条
fetch absolute 2 from authors_cursor --第2 条记录
fetch relative 3 from authors_cursor --当前行的后3条记录
fetch relative -2 from authors_cursor --当前行的前2条记录

--close
close authors_cursor
deallocate authors_cursor

2. 使用简单游标和语法
打开该游标时所生成的结果集包括 pubs 数据库的 authors 表中的所有行和列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项，FETCH NEXT 是唯一可用的提取选项。

DECLARE authors_cursor CURSOR
   FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor

3. 使用嵌套游标生成报表输出
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。

SET NOCOUNT ON

DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
   @message varchar(80), @title varchar(80)

PRINT "-------- Utah Authors report --------"

DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT " "
   SELECT @message = "----- Books by Author: " +
      @au_fname + " " + @au_lname

   PRINT @message

   -- Declare an inner cursor based  
   -- on au_id from the outer cursor.

   DECLARE titles_cursor CURSOR FOR
   SELECT t.title
   FROM titleauthor ta, titles t
   WHERE ta.title_id = t.title_id AND
   ta.au_id = @au_id   -- Variable value from the outer cursor

   OPEN titles_cursor
   FETCH NEXT FROM titles_cursor INTO @title

   IF @@FETCH_STATUS <> 0
      PRINT "         <<No Books>>"    

   WHILE @@FETCH_STATUS = 0
   BEGIN
     
      SELECT @message = "         " + @title
      PRINT @message
      FETCH NEXT FROM titles_cursor INTO @title
  
   END

   CLOSE titles_cursor
   DEALLOCATE titles_cursor
  
   -- Get the next author.
   FETCH NEXT FROM authors_cursor
   INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

-------- Utah Authors report --------
 
----- Books by Author: Anne Ringer
         The Gourmet Microwave
         Is Anger the Enemy?
 
----- Books by Author: Albert Ringer
         Is Anger the Enemy?
         Life Without Fear

4.示例
下面的示例用 @@FETCH_STATUS 控制在一个 WHILE 循环中的游标活动。

DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

5
declare ind_cur cursor
for select * from index1
for update of score

open ind_cur
FETCH NEXT FROM ind_cur
while @@FETCH_STATUS=0
begin

begin
update index1 set score=100
end
end

6
declare ind_cur cursor
for select * from index1
for update of score

open ind_cur
FETCH NEXT FROM ind_cur
update index1 set score=100
WHERE CURRENT OF ind_cur

7
declare ind_cur cursor
for select score from index1
for update of score

declare @score int
open ind_cur
FETCH next FROM ind_cur into @score
while @@fetch_status=0
begin
if @score=100
print '你的分数为100分'
else
print '你的分数为'+ convert(varchar(10),@score)
fetch next  from ind_cur into @score
end

close ind_cur
deallocate ind_cur
create database mydb on
(name=mydb_data,
filename='e:/cbc/mydb_data.mdf',
size=5,
maxsize=50,
filegrowth=2)
log on
(name=mydb_log,
filename='e:/cbc/mydb_log.ldf',
size=2,
maxsize=20,
filegrowth=1)

例
--建立数据库

use mydb

create table stu
(name varchar(10),
age int,
score int)
--建表

declare @a int
set @a=1
while @a<=5
begin
insert into stu values('cbc'+cast(@a as varchar(1)),1+rand()*50,rand()*100)
set @a=@a+1
end
--插入5条记录
select * from stu

delete stu

declare stu_cur1 cursor for select * from stu  --声明游标
open stu_cur1  --打开游标
declare @a varchar(10),@b int,@c int
fetch next from stu_cur1 into @a,@b,@c
--提取第一行的name,age,score值，分别赋给变量@a,@b,@c
while @@fetch_status=0  --当fetch语句提取成功时
begin
print '你的名字是'+@a
print '你的年龄是'+cast(@b as varchar(3))  --将@b转换成varchar类型
print '你的分数是'+cast(@c as varchar(3))
fetch next from stu_cur1 into @a,@b,@c
--提取下一行的值
end
close stu_cur1  --关闭游标
deallocate stu_cur1  --释放游标

declare stu_cur cursor for select name,score from stu
open stu_cur
declare @a int,@c varchar(10)
fetch next from stu_cur into @c,@a
while @@fetch_status=0
begin
if @a>=80
print @c+'的成绩是优'
--80分以上的成绩是优，下同
else
if @a>=70
print @c+'的成绩是良'
else
if @a>=60
print @c+'的成绩是及格'
else
print @c+'的成绩是不及格'
fetch next from stu_cur into @c,@a
end
close stu_cur
deallocate stu_cur

 

 

二，备份和还原数据库

--1,增加备份设备磁盘
sp_addumpdevice 'disk','mynwind1_1',
'e:/sql/lwzz_2.bat'

---2，增加备份设备磁带
EXEC sp_addumpdevice 'tape', 'mynwind1_11',
   'e:/sql/tape0'

--3，备份数据库到设备
backup database pubs to mynwind1_1,mynwind1_11

--4，还原数据库

restore database pubs from mynwind1_1

--5,差异还原数据库

restore database pubs from mynwind1_1 with norecovery

--6,还原并备份
restore database pubs from mynwind1_1 with file=2