SQL Server之存储过程

    存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。

    存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

    由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

 --  ===========系统存储过程============== 

 --  显示系统数据库 
 exec   sp_databases

  --  显示数据库详细信息 
 exec   sp_helpdb

  --  给指定的数据库更换名称 
 exec  sp_renamedb  ‘  aa  ‘ , ‘  bb  ‘ 

 --  查看指定表名的详细信息 
 exec   sp_help student

  --  查看指定索引、视图、存储过程等的创建文本信息 
 exec   sp_helptext sp_help

  --  调用存储过程必须在批处理文件第一位 
 if   exists ( select   *   from  sysobjects  where  name  =   ‘  Table1  ‘  )
      drop   table   Table1
  go      
sp_help sp_help


  --  ==========系统扩展存储过程================ 
 use   master
  go 

 --  创建文件夹bank 
 exec  xp_cmdshell  ‘  mkdir D:\bank  ‘  ,no_output
      if   exists ( select   *   from  sysdatabases  where  name  =   ‘  bankDB  ‘  )
          drop   database   bankDB
      go 
    
     create   database   bankDB
      on   primary  
    (
        name   =   ‘  bankDB  ‘  ,
        filename   =   ‘  D:\bank\bankDB.mdf  ‘  ,
        size   =   5MB,
        maxsize   =   10MB,
        filegrowth   =   15  %      
    )  log   on  (
        name   =   ‘  bankDB_log  ‘  ,
        filename   =   ‘  D:\bank\bankDB_log.ldf  ‘  ,
        size   =   5MB,
        filegrowth   =   15  %  
    )
    
  --  调用储存过程查看文件夹信息 
 exec  xp_cmdshell  ‘  dir D:\bank\  ‘ 


 --  ========创建存储过程，查询Java Logic最近一次考试平均分以及未通过考试的学员名单========= 
 use   MySchool
  go 

 if   exists ( select   *   from  sysobjects  where  name  =   ‘  sp_getavgresult  ‘  )
  drop   proc   sp_getavgresult
  go 
 --  创建存储过程实现 
 create   proc   sp_getavgresult
      @returnnum   int  output,  --  返回未及格的人数 
     @returnsum   int  output,  --  参加考试总人数 
     @subjectName   varchar ( 30 ),  --  添加科目 
     @score   int   =   60   --  添加输入参数（及格分数） 
 as 
 declare   @subjectId   int 
 declare   @maxdate   date
  declare   @avg   int 

 --  查询java logic课程的编号 
 select   @subjectId   =  SubjectId  from  Subject  where  SubjectName  =   @subjectName 

 --  查询java logic课程最近一次考试时间 
 select   @maxdate   =   MAX (ExamDate)  from  Result  where  SubjectId  =   @subjectId 

 --  查询java logic课程最近一次考试的平均分 
 select   @avg   =   AVG (StudentResult)  from  Result  where   
    SubjectId   =   @subjectId   and  ExamDate  =   @maxdate 
 print   ‘  未通过考试的人员名单：=======================  ‘ 
 --  查询java logic课程最近一次考试未通过的学生名单 
 select  studentName,studentResult  from   student s 
  inner   join  Result r  on  r.StudentNo  =   s.StudentNo
          where  SubjectId  =   @subjectId  
         and  ExamDate  =   @maxdate 
         and  StudentResult  <   @score 
        
 --  查询参加考试的总人数 
 select   @returnsum   =   COUNT ( * )  from  Result  where  ExamDate  =   @maxdate   and  SubjectId  =   @subjectId 

 --  查询未及格的人数         
 select   @returnnum   =   COUNT ( * )  from  Result  where  ExamDate  =   @maxdate   and  SubjectId  =   @subjectId 
                                   and  StudentResult  <   @score 
        
 if ( @avg   >   70  )
  begin 
     print   ‘  考试结果：优秀  ‘ 
 end 
 else 
 begin 
     print   ‘  考试结果：较差  ‘ 
 end 

 go 

 --  =======调用储存过程实现业务逻辑===========---- 

 declare   @sum   int   --  参加考试总人数 
 declare   @num   int   --  未及格人数 
 declare   @percent   float ( 2 )  --  及格百分比 

 --  调用存储过程 
 exec  sp_getavgresult  @num  output, @sum  output, @subjectName   =   ‘  java logic  ‘ , @score   =   60 

 print   ‘  =========================================  ‘ 
 print   ‘  参加考试人数为：  ‘   +   convert ( varchar ( 30 ), @sum  )
  print   ‘  未及格人数为：  ‘   +   convert ( varchar ( 30 ), @num  )

  --  计算及格率 
 set   @percent   =   convert ( float ( 2 ),( @sum   -   @num )) /  @sum   *   100 
 print   ‘  及格百分比：  ‘   +   convert ( varchar ( 30 ), @percent )  +  ‘  %  ‘ 

 --  判断是否要调及格分数线 
 if ( @percent   >   50  )
  begin 
     print   ‘  不需要调分数线。。。  ‘ 
 end 
 else 
 begin 
     print   ‘  需要降低分数线。。。  ‘ 
 end 

SQL Server之存储过程

标签：

查看更多关于SQL Server之存储过程的详细内容...