sql 存储过程学习

时间:2022-09-16 14:06:55

1、存储过程

存储过程可以包含数据操纵语句、变量、逻辑 控制语句等,比如:单个select语句, select语句块,select语句与逻辑控制块。

存储过程优点: 

   执行速度更快

   允许模块化程序设计

   提高系统安全性

   减少网络流通量

由系统定义,存放在master数据库中

   类似C语言中的系统函数

   系统存储过程的名称都以“sp_”开头或”xp_”开头

用户自定义存储过程

语法:
  CREATE PROC[EDURE] 存储过程名
    @参数1 数据类型 = 默认值 OUTPUT,
    …… ,
    @参数n 数据类型 = 默认值 OUTPUT
    AS
    SQL语句
  GO

例子:请创建存储过程,查看本次考试平均分以及未通过考试的学员名单

create database test

use test

create table stuInfo
(
stuNo int identity(1,1) primary key ,
stuName varchar(1000) not null ,
stuAge int check(stuAge>=0 and stuAge<=130) not null,
stuSex char(2) default('男') not null,
stuAddress text not null
)

insert into stuInfo(stuName,stuAge,stuAddress) values('张三',20,'湖南省')

select * from stuInfo

create table stuMarks
(
stuMID int identity(1,1) primary key,
stuNo int not null references stuInfo(stuNo),
stuMMakes float not null,
stuMLungunger float not null
)

insert into stuMarks values(1,99,90),
(2,88,88),
(3,77,77),
(4,60,60),
(5,55,55),
(6,69,60)

  create proc stu_procstu--创建存储过程
  AS
  DECLARE @writtenAvg float,@labAvg float
  SELECT @writtenAvg=AVG(stuMMakes),@labAvg=AVG(stuMLungunger)
  FROM stuMarks
  print '笔试平均分:'+convert(varchar(10),@writtenAvg)
  print '机试平均分:'+convert(varchar(10),@labAvg)
  IF (@writtenAvg>70 AND @labAvg>70)
  print '本班考试成绩:优秀'
  ELSE
  print '本班考试成绩:较差'
  print '--------------------------------------------------'
  print ' 参加本次考试没有通过的学员:'
  SELECT stuName,stuInfo.stuNo,stuMMakes,stuMLungunger
  FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
  WHERE stuMMakes<60 OR stuMLungunger<60

  exec stu_procstu--调用执行存储过程
  drop proc stu_procstu--删除存储过程

存储过程的参数分为两种:输入参数和输出参数

输入参数:

  

--根据输入的参数查找本次考试没有通过的学员

  CREATE PROCEDURE proc_stu_para
  @writtenPass int,
  @labPass int
  AS
  print '--------------------------------------------------'
  print ' 参加本次考试没有通过的学员:'
  SELECT stuName,stuInfo.stuNo,stuMMakes,stuMLungunger
  FROM stuInfo
  INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
  WHERE stuMMakes<@writtenPass OR stuMLungunger<@labPass
  GO

  exec proc_stu_para 60,80

输出参数

--查看本次及格分数线的一下的学员人数,检验分数线是否需要调整

  CREATE PROCEDURE proc_stu_out
  @notpassSum int OUTPUT,
  @writtenPass int=60,
  @labPass int=60
  AS

  SELECT stuName,stuInfo.stuNo,stuMMakes, stuMLungunger
  FROM stuInfo
  INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
  WHERE stuMMakes<@writtenPass OR stuMLungunger<@labPass
  SELECT @notpassSum=COUNT(stuNo)      --改参数接受未满足条件的数量用于调用输出@notpassSum
  FROM stuMarks WHERE stuMMakes<@writtenPass OR stuMLungunger<@labPass
  GO

  DECLARE @sum int     --调用存储过程,输入参数,检验输出值
  EXEC proc_stu_out @sum OUTPUT ,64
  print '--------------------------------------------------'
  IF @sum>=3
  print '未通过人数:'+convert(varchar(5),@sum)+ '人, 超过60%,及格分数线还应下调'
  ELSE
  print '未通过人数:'+convert(varchar(5),@sum)+ '人,已控制在60%以下,及格分数线适中'
  GO

注意接收存储过程返回值时必须加output关键字