(1)所有刚开始入手sql server的小白们都会很奇怪,为什么要用存储过程,存储过程好在哪?我们使用正常的sql语句实现这一切功能就好,为什么要用存储过程?以下首先建立一个图书的存储过程
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <小白-不倒翁>
-- Create date: <2018/4/16>
-- Description: <学习存储过程>
-- =============================================
--创建存储过程,存储过程名为insert_book --
create PROCEDURE insert_book
-- Add the parameters for the stored procedure here
--声明三个变量--
@bid int,@bname varchar(30),@bprice money--
AS
BEGIN
create table #book(--创建临时表--
number int not null primary key,
name varchar(200) ,
price money,
)
insert into #book (number,name,price)values('1','鲁滨孙漂流记',12.12)
insert into #book (number,name,price)values('2','爱',13.13)
insert into #book (number,name,price)values('3','人生',14.14)
insert into #book (number,name,price)values('4','平凡的世界',15.15)
insert into #book (number,name,price)values('5','飘',16.16)
insert into #book (number,name,price)values('6','简爱',17.17)
insert into #book (number,name,price)values('7','一千零一夜',18.18)
insert into #book (number,name,price)values('8','谁动了我的奶鐃',19.19)
insert into #book (number,name,price)values('9','钢铁是怎样炼成的',22.22)
insert into #book (number,name,price)values('10','三万个为什么',23.23)
--带参插入
insert into #book--
(number,name,price) values (@bid,@bname,@bprice)--
--声明参数并取值
--declare @bookname varchar
--select @bookname=name from #book where price=18.18
--print @bookname
--select * from #book where price>12.12 order by price desc--
declare @rows int
select @rows=(select COUNT (*) from #book)
print @rows
--select * from #book
--设置游标
--1.声明游标
declare my_youbiao cursor for select * from #book
--2打开游标
open my_youbiao
--声明2个变量
declare @o_ID int ,
@o_name varchar(50) ,
@o_price money
--3,读取数据将取出的值传入刚才声明的2个变量
Fetch next from my_youbiao into @o_ID,@o_name, @o_price
print '游标成功取出一条数据'
print @o_ID
print @o_name
print @o_price
--4关闭游标
CLOSE my_youbiao
--5删除游标
DEALLOCATE my_youbiao
END
GO
存储过程的执行:
execute insert_book
'11','看见',24.24
执行结果:
![](https://i-blog.csdnimg.cn/blog_migrate/fb160a45f73c2563135e8206ee95908a.png)
所有的表:
--select * from #book
执行结果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/2e2381a5a513dfbcc9da6ed6a7b8c354.png)