严琼.sql

基于XSBOOK数据库

-- =============================================
-- Create view basic template
-- =============================================
IF EXISTS (SELECT TABLE_NAME 
	   FROM   INFORMATION_SCHEMA.VIEWS 
	   WHERE  TABLE_NAME = N'借阅信息_计算机')
    DROP VIEW 借阅信息_计算机
GO

CREATE VIEW 借阅信息
AS 
	SELECT 书名,作者,出版社,COUNT(书名) AS 借阅次数

        FROM XS,BOOK,JY
        WHERE XS.专业名='计算机' AND XS.借书证号=JY.借书证号 AND JY.ISBN=BOOK.ISBN
          GROUP BY   书名,作者,出版社
UNION
SELECT 书名,作者,出版社,COUNT(书名) AS 借阅次数

        FROM XS,BOOK,JYLS
        WHERE XS.专业名='计算机' AND XS.借书证号=JYLS.借书证号 AND JYLS.ISBN=BOOK.ISBN
          GROUP BY   书名,作者,出版社

GO    

CREATE VIEW 借阅信息_计算机
AS 
   SELECT TOP 1000 书名,作者,出版社,SUM(借阅次数) AS 借阅次数    --SELECT语句中有聚合函数,所以视图是不可更新的.
   FROM 借阅信息
   GROUP BY 书名,作者,出版社
   ORDER BY 借阅次数 DESC
GO

------------------
/*游标应用实例*/
------------------
--声明变量
DECLARE @au_lname varchar(40), @au_fname varchar(20)

--声明游标
DECLARE authors_cursor CURSOR 
FOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE 'B%'
ORDER BY au_lname, au_fname

--打开游标
OPEN authors_cursor

--读取游标
FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Author:' + @au_fname + ' ' +  @au_lname
   FETCH NEXT FROM authors_cursor
   INTO @au_lname, @au_fname
END

--关闭并释放游标资源
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO