游标的学习-CSDN博客

print '================================================================================
初始化数据库：
================================================================================'

USE master
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name='aDB')
DROP DATABASE aDB
GO
EXEC XP_cmdshell 'mkdir D:\project',no_output
CREATE DATABASE aDB
ON
(
  NAME='aDB_data',
  FILENAME='D:\project\aDB_data.mdf',
  SIZE =10 MB,
  FILEGROWTH=20%

)
LOG ON
(
  NAME='aDB_log',
  FILENAME='d:\project\aDB_log.ldf',
  SIZE =20 MB,
  FILEGROWTH=10%
)
GO

print '================================================================================
开始：
================================================================================'

USE aDB
GO
SET NOCOUNT ON

IF EXISTS(SELECT * FROM sysobjects WHERE name='Books')
DROP table Books
GO
create table Books
(
    ID int,  --书籍编号
Author int, --作者编号
Title varchar(100) --书名
)
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='Authors')
DROP table Authors
GO
Create table Authors
(
ID int,  --作者编号
Name varchar(20) --姓名
)
declare @n int,@m int
select @n=1,@m=1
while (@n<=5)
begin
insert into Authors values(@n,substring(replace(newid(),'-',''),1,5))
set @n=@n+1
end
while (@m<=10)
begin
insert into Books values(@m,cast(rand()*5 as int)+1,substring(replace(newid(),'-',''),1,10)) --一个作者可以有多本书
set @m=@m+1
end

if exists (select * from sysobjects where name = 'newtable')
drop table newtable
GO
create table newtable
(
    ID int,  --作者编号
Name varchar(20), --姓名
    Title varchar(1000) --拥有的书名

)
declare @name varchar(20)
declare @id int
        DECLARE author_Cursor CURSOR FOR
  SELECT ID, Name
  FROM Authors
  OPEN author_Cursor
  FETCH NEXT FROM author_Cursor into @id,@name
  WHILE @@FETCH_STATUS = 0
   BEGIN
                    declare @bid int
                    declare @title varchar(50)
                    declare @str varchar(1000)
                    set @str=''
     DECLARE book_Cursor CURSOR FOR
     SELECT ID, Title
     FROM Books
     WHERE  Author = @id
     OPEN book_Cursor
     FETCH NEXT FROM book_Cursor into @bid,@title
     WHILE @@FETCH_STATUS = 0
      BEGIN
      set @str = @str + ' 《' + @title+ '》'
      FETCH NEXT FROM book_Cursor into @bid,@title
      END
     CLOSE book_Cursor
     DEALLOCATE book_Cursor
         insert into newtable values (@id,@name,@str)
         FETCH NEXT FROM author_Cursor into @id,@name
         END
      CLOSE author_Cursor
      DEALLOCATE author_Cursor
print '================================================================================
全部作者信息：
================================================================================'
select 作者编号=ID,作者姓名=Name,写过的书籍=Title from newtable
GO

print '================================================================================
写过2本书以上的作者信息：
================================================================================'

select 作者编号=ID,作者姓名=Name,写过的书籍=Title from newtable N
where
(select count(*) from Books B where B.Author=N.ID)>=2

print '================================================================================
作者表：
================================================================================'
select * from Authors
print '================================================================================
书表：
================================================================================'
select * from Books

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16436858/viewspace-612308/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16436858/viewspace-612308/