面试准备之SQL 6 —— 存储过程

1.什么是存储过程?

存储过程是一次编译可多次运行（存储过程存放在服务器中），预编译好的集合，运行速度快。

 2.常用系统存储过程

代码
-- Purpose: 常用系统存储过程使用

EXEC sp_databases  --列出当前系统中的数据库

EXEC  sp_renamedb 'test','test1'--改变数据库名称(单用户访问)

USE stuDB
GO

EXEC sp_tables  --当前数据库中查询的对象的列表

EXEC sp_columns stuInfo  --返回某个表列的信息

EXEC sp_help stuInfo  --查看表stuInfo的信息

EXEC sp_helpconstraint stuInfo --查看表stuInfo的约束

EXEC sp_helpindex stuMarks  --查看表stuMarks的索引

EXEC sp_helptext 'view_stuInfo_stuMarks' --查看视图的语句文本

EXEC sp_stored_PRocedures  --返回当前数据库中的存储过程列表
 

use master
go
exec xp_cmdshell 'mkdir D:/bank',no_output--创建文件夹
 

 3.自定义存储过程

（1.）不带参数的存储过程

代码
use studb
go
if exists(select * from sysobjects where name='proc_stu')
drop proc proc_stu
go

create procedure proc_stu
 AS
    DECLARE @writtenAvg float,@labAvg float --笔试和机试平均分变量
    SELECT @writtenAvg=AVG(writtenExam), @labAvg=AVG(labExam) FROM stuMarks
    print '笔试平均分：'+convert(varchar(5),@writtenAvg) 
    print '机试平均分：'+convert(varchar(5),@labAvg)
    IF (@writtenAvg>70 AND @labAvg>70)
       print '本班考试成绩：优秀'
    ELSE
       print '本班考试成绩：较差'
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<60 OR labExam<60
GO

exec proc_stu --执行存储过程
（2.）带输入参数的存储过程

代码
USE stuDB
GO
/*---检测是否存在：存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu
  @writtenPass int,
  @labPass int-- 可以添加默认值 这样 执行可以是这样的 exec proc_stu 不用指定参数了
  AS
    print '笔试及格线：'+convert(varchar(5),@writtenPass)
    print '机试及格线：'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<@writtenPass OR labExam<@labPass
GO

exec proc_stu 60,55
exec proc_stu 60,@labPass=55
exec proc_stu @writtenPass=60,@labPass=55
exec proc_stu @writtenPass=60,55--这一行会报错 ：必须传递参数 2，并以 '@name = value' 的形式传递后续的参数。
--  一旦使用了 '@name = value' 形式之后，所有后续的参数就必须以 '@name = value' 的形式传递

（3.）带输出参数的存储过程

代码
USE stuDB
GO
/*---检测是否存在：存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu
  @notpassSum int OUTPUT, --OUTPUT关键字，否则视为输入参数
  @writtenPass int=60,  --默认参数放后
  @labPass int=60       --默认参数放后
  AS
    print '笔试及格线：'+convert(varchar(5),@writtenPass)
       + '   机试及格线：'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<@writtenPass OR labExam<@labPass
    /*--统计并返回没有通过考试的学员人数--*/
    SELECT @notpassSum=COUNT(stuNo)FROM stuMarks
      WHERE writtenExam<@writtenPass OR labExam<@labPass
   
GO

/*---调用存储过程----*/
DECLARE @sum int
EXEC proc_stu @sum OUTPUT ,64   --机试及格线采用默认值：笔试及格线64分，机试及格线60分。
print '--------------------------------------------------'
IF @sum>=3
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'
ELSE
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'
GO