严琼.sql

1. 编写程序过程

USE XSBOOK

--1. 编写程序过程，返回所有读者的平均年龄；
-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'AVG_Year' 
	   AND 	  type = 'P')
    DROP PROCEDURE AVG_Year
GO

CREATE PROCEDURE AVG_Year
AS
	SELECT AVG(YEAR(GETDATE())-YEAR(出生时间)) AS 平均年龄
        FROM xs
GO

-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE AVG_Year
GO



--2. 编写存储过程，获取读者当前借书总数；
-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'Read_all' 
	   AND 	  type = 'P')
    DROP PROCEDURE Read_all
GO

CREATE PROCEDURE  Read_all
AS
	SELECT SUM(借书量) as 借书总数
        FROM xs
GO

-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE Read_all
GO



--3. 编写带游标类型输出参数的存储过程，返回借阅了图书“Web站点安全 ”的读者信息的游标变量。
-- =============================================
-- Create procedure with CURSOR OUTPUT Parameters
-- =============================================
-- drop the store procedure if it already exists
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'reader_cursor'
	   AND 	  type = 'P')
	DROP PROCEDURE reader_cursor
GO


-- create the store procedure 
CREATE PROCEDURE reader_cursor @reader_cur CURSOR VARYING OUTPUT
AS
SET @reader_cur = CURSOR FORWARD_ONLY STATIC FOR
     SELECT DISTINCT xs.借书证号,姓名,专业名,性别,出生时间,借书量,book.书名
     FROM xs,book,JY
     WHERE 书名='Web站点安全' and xs.借书证号=JY.借书证号 and JY.ISBN=book.ISBN
    
    OPEN @reader_cur
GO


-- =============================================
-- example to execute the store procedure
-- =============================================
DECLARE @mycursor CURSOR

EXEC reader_cursor @reader_cur=@mycursor OUTPUT
FETCH NEXT FROM @mycursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
    FETCH NEXT FROM @mycursor
END

CLOSE @mycursor

DEALLOCATE @mycursor
GO