SQL server 存储过程建立以及实现

(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
执行结果:
所有的表:
--select * from #book 
执行结果如下:
阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页