SqlServer存储过程

1.什么是存储过程？

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
优点：

1. 重复使用：存储过程可以重复使用，从而可以减少数据库开发人员的工作量。
2. 减少网络流量：存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数5261据量。
3. 安全性：参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。

2.存储过程的创建与使用

2.1准备

use sample_db;
--创建测试books表
create table books (
    book_idint identity(1,1)primary key,
    book_namevarchar(20),
    book_pricefloat,
    book_authvarchar(10)
);
--插入测试数据
insert into books (book_name,book_price,book_auth)
                    values
                        ('论语',25.6,'孔子'),
                        ('天龙八部',25.6,'金庸'),
                        ('雪山飞狐',32.7,'金庸'),
                        ('平凡的世界',35.8,'路遥'),
                        ('史记',54.8,'司马迁');

2.2创建存储过程

基本语法:

create proc | procedure pro_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as
    SQL_statements

2.2.1创建无参的存储过程

--1.创建无参存储过程
if (exists (select *from sys.objectswhere name ='getAllBooks'))
    drop proc proc_get_student
go
create procedure getAllBooks
as
select *from books;
--调用,执行存储过程
exec getAllBooks;

2.2.2创建有参的存储过程

-- 带一个参数的存储过程
if (exists (select *from sys.objectswhere name ='searchBooks'))
    drop proc searchBooks
go
create proc searchBooks(@bookIDint)
as
    -- 要求book_id列与输入参数相等
    select * from bookswhere book_id=@bookID;
--执行searchBooks
exec searchBooks 1;
 
-- 带两个参数的存储过程
if (exists (select * from sys.objectswhere name ='searchBooks1'))
    drop proc searchBooks1
go
create proc searchBooks1(
    @bookIDint,
    @bookAuthvarchar(20)
)
as
    --要求book_id和book_Auth列与输入参数相等
    select * from bookswhere book_id=@bookIDand book_auth=@bookAuth;
exec searchBooks1 1,'金庸';
 
-- 带返回值的存储过程
if (exists (select * from sys.objectswhere name ='getBookId'))
    drop proc getBookId
go
create proc getBookId(
    @bookAuthvarchar(20),--输入参数,无默认值
    @bookIdint output --输入/输出参数 无默认值
)
as
    select @bookId=book_id from bookswhere book_auth=@bookAuth
-- 执行getBookId这个带返回值的存储过程
   declare @idint --声明一个变量用来接收执行存储过程后的返回值
   exec getBookId '孔子', @idoutput
   select @idas bookId;--as是给返回的列值起一个名字