面试准备之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

 

 

 

转载于:https://www.cnblogs.com/Simcoder/archive/2010/04/16/1713424.html