存储过程

 

 

>[1]常用的存储过程

exec sp_databases
exec sp_renamedb 'monvb','monvb1'
use studb
go
exec sp_tables  -----当前数据库中可查询对象的列表
exec sp_columns stuinfo  -----查看表stuinfo中列的信息
exec sp_help stuinfo  -----查看表stuinfo的信息
exec sp_helpconstraint stuinfo  -----查看表stuinfo的约束
exec sp_index stumarks  -----查看表stumarks的索引
exec sp_helptext 'view_stuinfo_stumarks'  -----查看视图
exec sp_stored_procedures  -----返回当前数据库中的存储过程列表
exec xp_cmdshell 'mkdir d:\bank',no_output
use master
go
exec xp_cmdshell 'mkdir d:\bank',no_output
if exists (select * from sysdatabases where name = 'bankdb')
drop database bankdb
go
create database bankdb
(
name = 'studb_data',
filename = 'd:\bank\bankdb_data.mdf',
size = 1mb,
filegrowth = 15%
)
log on
(
name = 'bankdb_log',
filename = 'd;\bank\bankdb_log.ldf',
size = 1mb,
filegrowth = 15%
)
go
exec xp_cmdshell 'dir d:\bank\'

………………………………………………………………………………………………………………………………………………

>[2]用户定义的存储过程

(1) 创建不带参数的存储过程
use studb
go
if exists (select * from sysobjects where name = 'proc_stu')
drop proc proc_stu
go
create proc proc_stu
as
  declare @writtenavg float,@labavg float
  select @writtenavg = avg(writtenexam),@labavg = avg(labexam)
  from stumarks
  print '笔试平均分：' + convert(varchar(20),@writtenavg)
  print '机试平均分：' + convert(varchar(20),@labavg)
  if (@writtenavg>70 and @labavg>70)
  print '本班考试成绩：优秀'
  else
  print '本班考试成绩：较差'
  print '-------------------------------------------------'
  print '参加本次考试没有通过的学员'
  select stuname,stuinfo.stuno,writtenexam,labexam from stuinfo innner join stumarks 
  on stuinfo.stuno = stumarks.stuno
  go
exec proc_stu

(2) 创建带输入参数的存储过程
use studb
go
if exists (select * from sysobjects where name = 'proc_stu')
drop proc proc_stu
go
create proc proc_stu
@writtenpass int,
@labpass int
as
  print '-----------------------------------------------------------'
  print '参加本次考试没有通过的学员'
  select stuname,stuinfo.stuno,writtenexma,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 @writtenpass = 60,@labpass = 55

(3)创建带输出参数的存储过程
use studb
go
if exists (select * from sysobjects where name = 'proc_stu')
drop proc proc_stu
create proc proc_stu
@notpasssum int output,
@writtenpass int = 60,
@labpass int = 60
as
  print '笔试及格线：'+convert(varchar(20),@writtenpass)
  print '机试及格线：'+convert(varchar(20),@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
print '--------------------------------------------------------------'
if @sum>3
  print '未通过人数：'+convert(varchar(20),@sum)+'人,超过人数60%，及格分数线还应下调'
else
  print '未通过人数：'+convert(varchar(20),@sum)+'人，已控制在60%以下，及格分数线适中'
go

………………………………………………………………………………………………………………………………………………

>[3]处理错误信息

use studb
go
if exists (select * from sysobjects where name = 'proc_stu')
drop proc proc_stu
go
create proc proc_stu
@notpasssum int output,
@writtenpass int =60,
@labpass int = 60
as
if (not @writtenpass between 0 and 100) or (not labpass between 0 and 100)
  begin
    raiserror ('及格线错误，请指定0-100之间的分数，统计中断退出'，16，1)
    return
  end
print '笔试及格线：'+convert(varchar(20),@writtenpass)+
      '    机试及格线：'+convert(varchar(20),@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,@t int
exec proc_stu @sum output,604
set @t = @@error
print '错误号：'+convert(varchar(20),@t)
if @t<>0
  return   -----退出批处理，后续语句不在执行
print '-------------------------------------------------------------'
if @sum>3
  print '未通过的人数：'+convert(varchar(20),@sum)+'人，超过60%，及格分数线还应下调'
else
  print '未通过的人数：'+convert(varchar(20),@sum)+'人，已控制在60%以下，及格分数线适中'
go