--------------------------------------------------------------------------
-------------------------------存储过程Study------------------------------
--------------------------------------------------------------------------
--删除表
drop table student
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--创建用户信息数据表
create table student
(
id int identity(1,1),--递增1,
stuNo varchar(50) primary key, --唯一键
stuName varchar(50),
stuAge datetime,
stuSex varchar(5)
)
--创建用户分数数据表
create table course
(
id int identity(1,1),--递增1,
stuNo varchar(50),
courseName varchar(50),
courseScore decimal
)
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--插入数据
--插入单条数据
insert into student(stuNo,stuName,stuAge,stuSex) values('01','王男','1996-08-27 09:00:00.000','男')
--多数量插入数据
--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','数学','70'),
('02','语文','80'),
('02','物理','90'),
('02','化学','65'),
('02','英语','96')
insert into course values('03','思想政治','60'),
('03','数学','65'),
('03','语文','84'),
('03','物理','70'),
('03','化学','76'),
('03','英语','54')
--使用UNION ALL来进行插入
insert into student
select '04','wangan','1895-5-27 14:30:28','女'
union all
select '05','zhangnan','1990-1-20 19:0:0','女'
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--修改数据
update student set stuSex='男',stuAge='2016-5-9 8:0:0' where stuName='王男'
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--删除数据
delete from student where stuNo=01
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程查询所有数据
--begin...end 类似编程语言中的{}
create proc stu1
as
begin
select * from student;
end
go
exec stu1
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程根据条件用户名查询用户信息
create proc stu2
@sname varchar(50) --声明全局变量
as
begin
select * from student s where s.stuName=@sname;
end
go
exec stu2 '王男'
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程内部设定用户名查询用户信息
create proc stu3
@sname varchar(50)='王男'
as
begin
select * from student s where s.stuName=@sname;
end
go
exec stu3
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程根据用户名查询是否存在这个用户信息
create proc stu4
@sname varchar(50),
@result varchar(8) output --输出参数
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
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程内部设定局部变量用户名来查询用户信息
create proc stu5
as
declare @sname varchar(50) --局部变量声明
set @sname='杨幂'
begin
select * from student s where s.stuName=@sname
end
go
exec stu5
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程根据条件用户学号查询用户名
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
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程根据用户名来删除对应的用户信息
--@@rowcount返回操作条数
--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 '删除条数'
--print @result
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程根据用户学号来查询他的平均分
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
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程根据用户学号来联合查询用户信息和课程信息
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
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程判断学号是否存在，不存在，插入用户信息，返回消息；存在，返回信息
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='对不起，学号已存在!'
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
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程查询当前用户的平均成绩与总的平均成绩之间的关系
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 '该生对应的分数信息不存在,请重新查询!'
go
exec stu12 '03'
go
一.不含参数的存储过程
1.没有返回值:
创建语句：
CREATE PROCEDURE dbo.ProTest
AS
DECLARE @test int
SET @test = 1
Go
执行SQL语句：
EXEC dbo.ProTest
消息：命令已成功完成。结果：无
2.有返回值（使用select）:
创建语句：
CREATE PROCEDURE dbo.ProTest
AS
DECLARE @test INT;
SET @test = 123;
SELECT @test;
GO
执行SQL语句：
EXEC dbo.ProTest
消息：(1 行受影响)。结果：123（表结构形式）
3.有返回值（使用return）
创建语句：
CREATE PROCEDURE dbo.ProTest
AS
DECLARE @test INT;
SET @test = 123;
RETURN @test;
GO
执行SQL语句：
DECLARE @test INT;
EXEC @test = dbo.ProTest;
SELECT @test
消息：(1 行受影响)。结果：123（表结构形式）
4.查询一个或多个集合（类似执行select）
创建语句：
CREATE PROCEDURE dbo.ProTest
AS
SELECT *
FROM dbo.Material_SO_PipeOrder;
GO
执行SQL语句：
EXEC dbo.ProTest
消息：查询出来的条数
结果：查询结果
二.含参数的存储过程
1.没有返回值
创建语句：
CREATE PROCEDURE dbo.ProTest
@OrderNO NVARCHAR(50) ,
@OrderName NVARCHAR(50) ,
@RMDSC NVARCHAR(500) = NULL --表示可为空参数
AS
IF ( @OrderNO IS NOT NULL )
BEGIN
INSERT INTO dbo.Material_SO_PipeOrder
( ID, OrderNO, OrderName, RMDSC )
VALUES ( NEWID(), -- ID - uniqueidentifier
@OrderNO, -- OrderNO - nvarchar(50)
@OrderName, -- OrderName - nvarchar(50)
@RMDSC -- RMDSC - nvarchar(500)
);
END;
GO
执行SQL语句：
EXEC dbo.ProTest @OrderNO = N'单号001', @OrderName = N'名称001', @RMDSC = N'备注'
（或不写列名"EXEC dbo.ProTest N'单号001', N'名称001', N'备注';"，但不能混合使用，下同）
消息：(1 行受影响)。结果：无
2.有返回值（使用select）
创建语句：
CREATE PROCEDURE dbo.ProTest
@OrderNO NVARCHAR(50) ,
@OrderName NVARCHAR(50) ,
@RMDSC NVARCHAR(500)
AS
IF ( @OrderNO IS NOT NULL )
BEGIN
INSERT INTO dbo.Material_SO_PipeOrder
( ID, OrderNO, OrderName, RMDSC )
VALUES ( NEWID(), @OrderNO, -- OrderNO - nvarchar(50)
@OrderName, -- OrderName - nvarchar(50)
@RMDSC -- RMDSC - nvarchar(500)
);
SELECT 1;
END;
ELSE
SELECT -1;
GO
执行SQL语句：
EXEC dbo.ProTest @OrderNO = N'单号001', @OrderName = N'名称001', @RMDSC = N'备注';
消息：
(1 行受影响)
(1 行受影响)
结果：1（表结构形式）
3.有返回值（使用return）
创建语句：
CREATE PROCEDURE dbo.ProTest
@OrderNO NVARCHAR(50) ,
@OrderName NVARCHAR(50) ,
@RMDSC NVARCHAR(500)
AS
IF ( @OrderNO IS NOT NULL )
BEGIN
INSERT INTO dbo.Material_SO_PipeOrder
( ID, OrderNO, OrderName, RMDSC )
VALUES ( NEWID(), @OrderNO, -- OrderNO - nvarchar(50)
@OrderName, -- OrderName - nvarchar(50)
@RMDSC -- RMDSC - nvarchar(500)
);
RETURN 1;
END;
ELSE
RETURN -1;
GO
执行SQL语句：
DECLARE @test INT;
EXEC @test = dbo.ProTest @OrderNO = N'单号001', @OrderName = N'名称001', @RMDSC = N'备注';
SELECT @test
消息：
(1 行受影响)
(1 行受影响)
结果：1（表结构形式）
4.带输出参数的存储过程（以没有返回值的为例）
创建语句：
CREATE PROCEDURE dbo.ProTest
@OrderNO NVARCHAR(50) ,
@OrderName NVARCHAR(50) ,
@RMDSC NVARCHAR(500) ,
@ID UNIQUEIDENTIFIER OUTPUT --输出参数要用output标识
AS
IF ( @OrderNO IS NOT NULL )
BEGIN
DECLARE @newID UNIQUEIDENTIFIER;
SET @newID = NEWID();
INSERT INTO dbo.Material_SO_PipeOrder
( ID ,
OrderNO ,
OrderName ,
RMDSC
)
VALUES ( @newID ,
@OrderNO , -- OrderNO - nvarchar(50)
@OrderName , -- OrderName - nvarchar(50)
@RMDSC -- RMDSC - nvarchar(500)
);
SET @ID = @newID;--可以不赋值
END;
ELSE
SET @ID = NULL;--可以不赋值
GO
执行SQL语句：
DECLARE @IDTest UNIQUEIDENTIFIER;
EXEC dbo.ProTest @OrderNO = N'单号001', -- nvarchar(50)
@OrderName = N'名称001', -- nvarchar(50)
@RMDSC = N'备注', -- nvarchar(500)
@ID = @IDTest OUTPUT; --如果不加OUTPUT，select结果为NULL
SELECT @IDTest;
消息：
(1 行受影响)
(1 行受影响)
结果：EDD11EF3-CD03-4C95-8B79-B3123B34C292（表结构形式）
三.总结与注意：
1.关于存储过程的返回值：
(1).如果有return，则返回return的结果；
(2).如果没有return，则返回INT值0(即使存储过程中有select集合，或Insert受影响行等)；
(3).如果带输出参数，则存储过程的返回值同(1),(2);OUTPUT的参数需要select出来；
