存储过程

一、存储过程的概念

1、分类：
 系统存储过程： 以sp_或xp_打头
 用户自定义  ：以proc_打头
2、优点：
 执行速度快 效率高
 模块式编程
 减少网络流量
 提高安全性

二、系统存储过程

常用的系统存储过程

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

EXEC  sp_renamedb 'Northwind','Northwind1'  --修改数据库的名称(单用户访问)

USE stuDB
GO
EXEC sp_tables  --返回某个表列的信息
EXEC sp_columns   --查看指定列的信息

EXEC sp_help  '表名'   --查看某个表的所有信息

EXEC sp_helpconstraint '表名'  --查看某个表的约束

EXEC sp_helpdb '数据库名' 或 EXEC sp_helpdb  --查看指定数据库或所有数据库信息

EXEC sp_helptext '对象名称'   --显示数据库对象（存储过程、触发器、试图）的定义文本

EXEC sp_helpindex '表名'   --查看指定表的索引信息

EXEC sp_renamedb '原名称','新名称'   --更改数据库名称
 
EXEC sp_stored_procedures   --列出当前环境可用的所有存储过程
 
三、用户自定义存储过程

1、语法

create procedure 存储过程名
  @参数1名 数据类型 [=默认值] [参数类型(输入/输出)]
  ...  ...
  @参数n名 数据类型 [=默认值] [参数类型(输入/输出)]
as
  begin
 sql语句
  end;
go
 
2、创建不带参数的存储过程

 --判断存储过程是否存在
if object_id('proc_student','procedure') is not null
 drop procedure proc_student
 go
create procedure proc_student
as
begin
 select pcid as '电脑编号',
 case pcuse
  when 0 then '空闲'
  when 1 then '忙碌'
  end as '使用状态'
 from pc
 
end;
--调用存储过程
execute proc_student
select * from pc
go

3、创建带输入参数的存储过程

--创建带输入参数的存储过程
if object_id('proc_stu','procedure') is not null
 drop procedure proc_stu
 go
create procedure proc_stu
 @pcuse int
as
begin
 select pcid as '电脑编号',
  case pcuse
  when 0 then '空闲'
  when 1 then '忙碌'
  end as '使用状态'
 from pc where pcuse=@pcuse
end;
--调用存储过程
execute proc_stu @pcuse=1

4、创建带输出参数的存储过程

--创建带输出参数的存储过程
if OBJECT_ID('proc_s','procedure') is not null
 drop procedure proc_s
 go
create procedure proc_s
 @pcid int,
 @pcus int output
as
begin
 select @pcus=pcuse from pc where pcid=@pcid
end;
--调用存储过程
declare @pcus int
execute proc_s 5,@pcus output

5、return的使用

--return 的返回使用
if OBJECT_ID('proc_studen','procedure') is not null
 drop procedure proc_studen
 go
create procedure proc_studen
 @rmark varchar(20)
as 
begin
 insert into pc values(0,@rmark)
 return @@identity
end;
--调用存储过程
declare @mark varchar(20)
execute @mark=proc_studen '这台电脑不错'

--return的中断操作
if OBJECT_ID('proc_students','procedure') is not null
 drop procedure proc_proc_studentsstuden
 go
create procedure proc_students
 @pcid int
as 
begin
 if exists(select * from pc where pcid=@pcid)
  begin
   print ''

   return
  end
end;
declare @mark varchar(20)
execute @mark=proc_students '这台电脑不错'

四、处理错误信息

语法：raiserror(自定义的错误信息，错误严重级别，错误状态)

 

 

五、相关习题

--1、银行的卡号规则和电话号码一样，一般前8位代表特殊含义，如某总行某支行等。
--假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxxx开始,每4位号码后有空格
--写存储过程，产生随机的卡号，后8位卡号的产生规则是：日期+小时 分钟+秒钟
--如当前的时间是2010-4-8 15：48：20，则卡号是：1010 3576 0815 4820

if OBJECT_ID('proc_save','procedure') is not null
 drop procedure proc_save
 go
create procedure proc_save
as
 declare @cardNo varchar(100)='1010 3756'
begin
 set @cardNo+=' '+substring(convert(varchar,sysdatetime()),9,2)+substring(convert(varchar,sysdatetime()),12,2)
  +' '+substring(convert(varchar,sysdatetime()),15,2)+substring(convert(varchar,sysdatetime()),18,2)
 print @cardNo
end;
go
execute proc_save

--2、在sql中执行以下代码：
create database NetBarDB
go

use NetBarDB
go

create table userInfo
(
 userId int primary key identity(1,1),
 userName varchar(20) not null unique,
 userPwd varchar(20) not null check(len(userPwd)>=6)
)
go

insert into userInfo values('admin1','123456')
insert into userInfo values('admin2','123456')

create table cardInfo
(
 cardId int primary key identity(1,1),
 cardNumber varchar(20) not null unique,
 cardPassword varchar(20) not null check(len(cardPassword)>=6),
 cardBalance int not null default(50),
 transactTime datetime default(getdate()) not null
)
go

insert into cardInfo values('023-001','123456',50,'2009-5-11 10:00:00')
insert into cardInfo values('023-002','123456',50,'2009-5-12 10:00:00')
insert into cardInfo values('023-003','123456',50,'2009-5-13 10:00:00')
insert into cardInfo values('023-004','123456',50,'2009-5-14 10:00:00')
insert into cardInfo values('023-005','123456',50,'2009-5-15 10:00:00')
insert into cardInfo values('023-006','123456',50,'2009-5-16 10:00:00')
insert into cardInfo values('023-007','123456',50,'2009-5-17 10:00:00')
insert into cardInfo values('023-008','123456',50,'2009-5-18 10:00:00')
insert into cardInfo values('023-009','123456',50,'2009-5-19 10:00:00')

create table PCInfo
(
 PCId int primary key identity(1,1),
 PCUse int not null default(0) check(PCUse=0 or PCUse=1),
 PCNote varchar(30) default('这台机器不错')
)
go

insert into PCInfo values(0,'左边第1台')
insert into PCInfo values(0,'左边第2台')
insert into PCInfo values(0,'左边第3台')
insert into PCInfo values(0,'左边第4台')
insert into PCInfo values(0,'左边第5台')
insert into PCInfo values(0,'左边第6台')
insert into PCInfo values(0,'左边第7台')
insert into PCInfo values(0,'左边第8台')

create table recordInfo
(
 recordId int primary key identity(1,1),
 cardId int not null references cardInfo(cardId),
 PCId int not null references PCInfo(PCId),
 beginTime datetime default(getdate()),
 endTime datetime,
  check(beginTime<endTime),
 free int check(free>=0)
)
go

insert into recordInfo values(1,1,'2009-5-11 12:00:00','2009-5-11 14:00:00',4)

select * from userInfo
select * from cardInfo
select * from PCInfo
select * from recordInfo

--（1）写存储过程，实现上机
--上机:
  --查询出会员号是否存在
  --查询出该电脑的使用状态是否为空闲状态 
  --查询出会员卡内的余额 看它是否大于2
  --向营业记录表中添加上机记录
  --修改电脑表中电脑状态
if OBJECT_ID('proc_begin','procedure') is not null
 drop procedure proc_begin
 go
create procedure proc_begin
 @cardId int,
 @pcId int
as
begin
 --查询出会员号是否存在
 if exists (select * from recordInfo where cardId=@cardId and endTime is null)
  begin
   raiserror('上机失败  该会员正在上机',15,1)
   return
  end
 
 --查询出该电脑的使用状态是否为空闲状态
 if((select PCUse from PCInfo where PCId=@pcId)=1)
  begin
   raiserror('上机失败  该电脑正在使用',15,1)
   return
  end 
 --查询出会员卡内的余额 看它是否大于2
 if((select cardBalance from cardInfo where cardId=@cardId)<2)
  begin
   raiserror('上机失败  会员卡内余额不足',15,1)
   return
  end
 --开始上机
  --向营业记录表中添加上机记录
 insert into recordInfo values (@cardId,@pcId,GETDATE(),null,null)
  if(@@ERROR>0 or @@IDENTITY<1)
   begin
    raiserror('上机失败  添加上机记录失败',15,1)
    return 
   end
 ----测试数据
 --insert into recordInfo values(1,1,GETDATE(),null,2)
  --修改电脑的使用状态
  update PCInfo set PCUse=1 where PCId=@pcId
   if(@@ERROR>0 or @@IDENTITY<1)
   begin
    raiserror('上机失败  修改电脑使用状态失败',15,1)
    return 
   end
  select * from recordInfo where cardId=@cardId
end;
go 
--调用存储过程
execute proc_begin 5,5
select * from recordInfo
select * from cardInfo
select * from PCInfo
--（2）写存储过程，实现下机
  --扣去会员卡内金额
  --修改电脑状态
  --修改会员卡记录
  --修改营业记录表
if OBJECT_ID('proc_end','procedure') is not null
 drop procedure proc_end
 go
create procedure proc_end
 @cardId int,
 @pcId int
as
 declare @free money,@begintime datetime,@endtime datetime
begin
 if exists (select * from recordInfo where cardId=@cardId and PCId=@pcId and endTime is null)
  begin
   update PCInfo set PCUse=0 where PCId=@pcId
   select @begintime=begintime from recordInfo where cardId=@cardId and PCId=@pcId and endTime is null
   select @endtime=GETDATE()
   set @free=(DATEDIFF(HH,@begintime,@endtime))*3
   update recordInfo set endTime=@endtime,free=@free where cardId=@cardId and PCId=@pcId
   update cardInfo set cardBalance=cardBalance-@free where cardId=@cardId
   select * from recordInfo where cardId=@cardId
   select * from PCInfo where PCId=@pcId
   select * from cardInfo where cardId=@cardId
  end
end;
go
execute proc_end 5,5

--（3）写存储过程，实现换机
if OBJECT_ID('proc_change','procedure') is not null
 drop procedure proc_change
 go
create procedure proc_change
 @cardId int,
 @NewpcId int
as
declare @oldpcId int
begin
 --根据会员号查询出该会员现在正在上机的电脑
 set @OldpcId=(select PCId from recordInfo where cardId=@cardId)
 --查询出要换到的机器的使用状态
 if((select PCUse from PCInfo where PCId=@NewpcId)=1)
  begin
   print '换机失败 该电脑正在使用'
   return
  end
 --实现换机功能
 update recordInfo set PCId=@NewpcId where cardId=@cardId
  if(@@ERROR>0 or @@IDENTITY!=1)
   begin
    print '换机失败'
    return
   end
 --新换到的机器的使用状态改为正在使用
 update PCInfo set PCUse=1 where PCId=@NewpcId
  if(@@ERROR>0 or @@IDENTITY!=1)
   begin
    print '换机失败'
    return
   end
 --将原来使用的机器使用状态改为空闲状态
 update PCInfo set PCUse=0 where PCId=@OldpcId
 if(@@ERROR>0 or @@IDENTITY!=1)
   begin
    print '换机失败'
    return
   end
 select * from recordInfo where cardId=@cardId
 select * from PCInfo
end;
go
execute proc_change 2,2
select * from recordInfo
select * from PCInfo
update PCInfo set PCUse=1 where PCId=5
 

--3、在sql中执行以下代码：
create table gz
(
 EName varchar(8),
 ESalary money
)
insert into gz values('张三',3000)
insert into gz values('李四',5000)
insert into gz values('王五',4000)
insert into gz values('刘六',3500)
--定义存储过程，根据用户姓名，查找对应的工资
select * from gz
if OBJECT_ID('proc_gz','procedure') is not null
 drop procedure proc_gz
 go
create procedure proc_gz
 @Ename varchar(8)
as
begin
 select ESalary from gz where EName=@Ename
 print '该员工工资为：'
end;
go
execute proc_gz '张三'
go