sql 存储过程例子和学习demo

骑猪看日落 2021-11-26 11:10 199阅读 0赞

-------------------------------------------------------------------------- -------------------------------存储过程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（表结构形式）**

**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（表结构形式）**

**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 行受影响)**

**结果：EDD11EF3-CD03-4C95-8B79-B3123B34C292（表结构形式）**

**三.总结与注意：**  
**1.关于存储过程的返回值：**

**(1).如果有return，则返回return的结果；**

**(2).如果没有return，则返回INT值0(即使存储过程中有select集合，或Insert受影响行等)；**

**(3).如果带输出参数，则存储过程的返回值同(1),(2);OUTPUT的参数需要select出来；**

转载于:https://www.cnblogs.com/baili-luoyun/p/11056468.html