sqlserver存儲過程學(xué)習(xí)(通俗易懂)
[導(dǎo)讀]
--------------------------------------------------------------------------
----------------------
--------------------------------------------------------------------------
-------------------------------存儲過程Study------------------------------
--------------------------------------------------------------------------
--刪除表
drop table student
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--創(chuàng)建用戶信息數(shù)據(jù)表
create table student
(
id int identity(1,1),--遞增1,
stuNo varchar(50) primary key, --唯一鍵
stuName varchar(50),
stuAge datetime,
stuSex varchar(5)
)
--創(chuàng)建用戶分?jǐn)?shù)數(shù)據(jù)表
create table course
(
id int identity(1,1),--遞增1,
stuNo varchar(50),
courseName varchar(50),
courseScore decimal
)
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--插入數(shù)據(jù)
--插入單條數(shù)據(jù)
insert into student(stuNo,stuName,stuAge,stuSex) values('01','王男','1996-08-27 09:00:00.000','男')
--多數(shù)量插入數(shù)據(jù)
--SQL Server2008特有的插入
insert into student values('02','楊冪','1995-4-20 6:0:0','女'),
('03','程峰','1988-9-17 15:30:0','男')
insert into course values('02','思想政治','85.5'),
('02','數(shù)學(xué)','70'),
('02','語文','80'),
('02','物理','90'),
('02','化學(xué)','65'),
('02','英語','96')
insert into course values('03','思想政治','60'),
('03','數(shù)學(xué)','65'),
('03','語文','84'),
('03','物理','70'),
('03','化學(xué)','76'),
('03','英語','54')
--使用UNION ALL來進(jìn)行插入
insert into student
select '04','wangan','1895-5-27 14:30:28','女'
union all
select '05','zhangnan','1990-1-20 19:0:0','女'
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--修改數(shù)據(jù)
update student set stuSex='男',stuAge='2016-5-9 8:0:0' where stuName='王男'
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--刪除數(shù)據(jù)
delete from student where stuNo=01
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲過程查詢所有數(shù)據(jù)
--begin...end 類似編程語言中的{}
create proc stu1
as
begin
select * from student;
end
go
exec stu1
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲過程根據(jù)條件用戶名查詢用戶信息
create proc stu2
@sname varchar(50) --聲明全局變量
as
begin
select * from student s where s.stuName=@sname;
end
go
exec stu2 '王男'
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲過程內(nèi)部設(shè)定用戶名查詢用戶信息
create proc stu3
@sname varchar(50)='王男'
as
begin
select * from student s where s.stuName=@sname;
end
go
exec stu3
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲過程根據(jù)用戶名查詢是否存在這個用戶信息
create proc stu4
@sname varchar(50),
@result varchar(8) output --輸出參數(shù)
as
begin
if (select COUNT(1) from student s where s.stuName=@sname)>0
--if exists (select COUNT(1) from student s where s.stuName=@sname)
set
@result='存在!'
else
set
@result='不存在!'
end
go
declare @result varchar(8)
exec stu4 '王男1',@result output
print @result
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲過程內(nèi)部設(shè)定局部變量用戶名來查詢用戶信息
create proc stu5
as
declare @sname varchar(50) --局部變量聲明
set @sname='楊冪'
begin
select * from student s where s.stuName=@sname
end
go
exec stu5
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲過程根據(jù)條件用戶學(xué)號查詢用戶名
create proc stu6
@stuNo varchar(50)
as
declare @sname varchar(50)
set @sname=(select s.stuName from student s where s.stuNo=@stuNo)
select @sname
go
exec stu6 '01'
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲過程插入用戶信息
create proc stu7
@stuNo varchar(50),
@stuName varchar(50),
@stuAge datetime,
@stuSex varchar(5)
as
begin
insert into student
(stuNo,stuName,stuAge,stuSex)
values
(@stuNo,@stuName,@stuAge,@stuSex)
end
go
exec stu7 '07','王莽','2000-9-9 9:9:9','女'
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲過程根據(jù)用戶名來刪除對應(yīng)的用戶信息
--@@rowcount返回操作條數(shù)
--return返回信息，終止下面的操作
create proc stu8
@stuName varchar(50)
as
begin
delete from student where stuName=@stuName
return @@rowcount
end
go
declare @result varchar(50)
exec @result=stu8 '王莽'
select @result as '刪除條數(shù)'
--print @result
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲過程根據(jù)用戶學(xué)號來查詢他的平均分
create proc stu9
@stuNo varchar(50),
@avg int output
as
begin
set @avg=(select AVG(courseScore) from course where stuNo=@stuNo)
--等同
--select @avg=AVG(courseScore) from course where stuNo=@stuNo
end
go
declare @avg int
exec stu9 '02',@avg output
print @avg
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲過程根據(jù)用戶學(xué)號來聯(lián)合查詢用戶信息和課程信息
create proc stu10
@stuNo varchar(50)
as
select c.stuNo,s.stuName,s.stuAge,s.stuSex,c.courseName,c.courseScore from student s join course c on s.stuNo=c.stuNo where s.stuNo=@stuNo
go
exec stu10 '02'
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲過程判斷學(xué)號是否存在，不存在，插入用戶信息，返回消息；存在，返回信息
create proc stu11
@stuNo varchar(50),
@stuName varchar(50),
@stuAge datetime,
@stuSex varchar(5),
@result varchar(50) output
as
if exists (select * from student where stuNo=@stuNo)
begin
set @result='對不起，學(xué)號已存在!'
end
else
begin
insert into student
(stuNo,stuName,stuAge,stuSex)
values
(@stuNo,@stuName,@stuAge,@stuSex)
set @result='恭喜你，用戶信息插入成功!'
end
go
declare @result varchar(50)
exec stu11 '06','王忠磊','1980-8-8 8:9:0','男',@result output
print @result
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存儲過程查詢當(dāng)前用戶的平均成績與總的平均成績之間的關(guān)系
create proc stu12
@stuNo varchar(50)
as
declare @curAvg decimal(18,2)
declare @totalAvg decimal(18,2)
if exists(select * from course where stuNo=@stuNo)
begin
set @totalAvg=(select AVG(courseScore) from course)
select @curAvg=AVG(courseScore) from course where stuNo=@stuNo
print ('總的平均分:'+convert(varchar(18),@totalAvg))
print ('該生的平均分:'+convert(varchar(18),@curAvg))
if @curAvg>@totalAvg
print '高于平均水平!'
else
print '低于平均水平!'
end
else
print '該生對應(yīng)的分?jǐn)?shù)信息不存在,請重新查詢!'
go
exec stu12 '03'
go 
下載文檔
