本文共 3864 字，大约阅读时间需要 12 分钟。

create procedure s1@Rname nvarchar(50)='王小明',@Btitle nvarchar(50)output,@BorrowDate date output,@Bauthor nvarchar(50) outputas select @Rname=Rname,@Btitle=Btitle,@BorrowDate=BorrowDate,@Bauthor=Bauthorfrom Book,Borrow,Reader where @Rname=Rname and Book.Bno=Borrow.Bno and Reader.Rno=Borrow.Rnogocreate procedure s2@maxprice float output,@minprice float outputasselect @maxprice=max(Bprice),@minprice=min(Bprice) from Bookgocreate procedure s3@Rname nvarchar(50)='王小明',@Btitle nvarchar(50),@Bprice float output,@BorrowDate date output,@ReturnDate date output,@Bauthor nvarchar(50) outputas select @Bprice=Bprice,@BorrowDate=BorrowDate,@ReturnDate=ReturnDate,@Bauthor=Bauthorfrom Book,Borrow,Reader where @Rname=Rname and @Btitle=Btitle and Book.Bno=Borrow.Bno and Reader.Rno=Borrow.Rnogocreate procedure s4@Btitle nvarchar(50),@Borrowtotal int outputas select  @Borrowtotal=count(Borrnum) from Book,Borrow,Reader where @Btitle=Btitle and Book.Bno=Borrow.Bno and Reader.Rno=Borrow.Rnogodeclare @Btitle1 nvarchar(50),@Borrowtotal1 intselect @Btitle1='数据结构'exec s4  @Btitle1,@Borrowtotal1 outputprint @Borrowtotal1gocreate procedure s5@Btitle nvarchar(50),@Rname nvarchar(50)output,@Rage smallint output,@Reducation nvarchar(10)output,@BorrowDate date output,@ReturnDate date outputas select @Rname=Rname,@Rage=Rage,@BorrowDate=BorrowDate,@ReturnDate=ReturnDate,@Reducation=Reducationfrom Book,Borrow,Reader where  @Btitle=Btitle and Book.Bno=Borrow.Bno and Reader.Rno=Borrow.Rnogodeclare @Btitle1 nvarchar(50),@Rname1 nvarchar(50),@Rage1 smallint ,@Reducation1 nvarchar(10),@BorrowDate1 date ,@ReturnDate1 date select @Btitle1='操作系统概论'exec s5 @Btitle1,@Rname1 output,@Rage1 output,@Reducation1 output,@BorrowDate1 output,@ReturnDate1 outputprint @Rname1print @Rage1print @Reducation1print @BorrowDate1print @ReturnDate1

-- ================================================-- Template generated from Template Explorer using:-- Create Scalar Function (New Menu).SQL---- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below.---- This block of comments will not be included in-- the definition of the function.-- ================================================SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:		
   
    -- Create date: 
    
     -- Description:	
     
      -- =============================================CREATE FUNCTION f1(@Rname nvarchar(100),@Btitle  nvarchar(100))RETURNS dateASBEGIN	-- Declare the return variable here	DECLARE @BorrowDate date	-- Add the T-SQL statements to compute the return value here	SELECT @BorrowDate=BorrowDate from Book,Borrow,Reader	where  @Btitle=Btitle and Book.Bno=Borrow.Bno and Reader.Rno=Borrow.Rno	and Book.Btitle=@Btitle and Reader.Rname=@Rname	-- Return the result of the function	RETURN (@BorrowDate)ENDGOCREATE FUNCTION f2(@Btitle  nvarchar(100))RETURNS @f2 table(Rname nvarchar(100),Rage smallint,Reducation nvarchar(10),BorrowDate date ,ReturnDate date )ASBEGIN	-- Declare the return variable here	insert @f2	-- Add the T-SQL statements to compute the return value here	SELECT Rname,Rage,Reducation,BorrowDate,ReturnDate from Book,Borrow,Reader	where  @Btitle=Btitle and Book.Bno=Borrow.Bno and Reader.Rno=Borrow.Rno	and Book.Btitle=@Btitle order by BorrowDate desc	-- Return the result of the function	RETURN ENDGOCREATE FUNCTION f3(@Btitle  nvarchar(100))RETURNS @f3 table(Btitle nvarchar(100),Bookcount smallint,avgRage smallint)ASBEGIN	-- Declare the return variable here	insert @f3	-- Add the T-SQL statements to compute the return value here	SELECT Btitle,avg(Rage) as avgRage,count(Borrnum) as Bookcount from Book,Borrow,Reader 	where  @Btitle=Btitle and Book.Bno=Borrow.Bno and Reader.Rno=Borrow.Rno	and Book.Btitle=@Btitle group by Btitle	-- Return the result of the function	RETURN ENDGO
     
    
   

转载地址：http://yvcki.baihongyu.com/