1.什么是存储过程?
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数2113据处理的效率是有好处的。
优点:
1、重复使用:存储过程可以重复使用,从而可以减少数据库开发人员的工作量。
2、减少网络流量:存储过程位于服务器上,调用的时候只需要传递存储过程的名称以及参数就可以了,因此降低了网络传输的数5261据量。
3、安全性:参数化的存储过程可以防止SQL注入式攻击,而且可以将Grant、Deny以及Revoke权限应用于存储过程。
2.存储过程的创建与使用
2.1准备
use sample_db; --创建测试books表 create table books ( book_id int identity(1,1) primary key, book_name varchar(20), book_price float, book_auth varchar(10) ); --插入测试数据 insert into books (book_name,book_price,book_auth) values (‘论语‘,25.6,‘孔子‘), (‘天龙八部‘,25.6,‘金庸‘), (‘雪山飞狐‘,32.7,‘金庸‘), (‘平凡的世界‘,35.8,‘路遥‘), (‘史记‘,54.8,‘司马迁‘);
2.2创建存储过程
2.2.1创建无参的存储过程
--1.创建无参存储过程 if (exists (select * from sys.objects where 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.objects where name = ‘searchBooks‘)) drop proc searchBooks go create proc searchBooks(@bookID int) as --要求book_id列与输入参数相等 select * from books where book_id=@bookID; --执行searchBooks exec searchBooks 1; --带两个参数的存储过程 if (exists (select * from sys.objects where name = ‘searchBooks1‘)) drop proc searchBooks1 go create proc searchBooks1( @bookID int, @bookAuth varchar(20) ) as --要求book_id和book_Auth列与输入参数相等 select * from books where book_id=@bookID and book_auth=@bookAuth; exec searchBooks1 1,‘金庸‘; -- 带返回值的存储过程 if (exists (select * from sys.objects where name = ‘getBookId‘)) drop proc getBookId go create proc getBookId( @bookAuth varchar(20),--输入参数,无默认值 @bookId int output --输入/输出参数 无默认值 ) as select @bookId=book_id from books where book_auth=@bookAuth -- 执行getBookId这个带返回值的存储过程 declare @id int --声明一个变量用来接收执行存储过程后的返回值 exec getBookId ‘孔子‘,@id output select @id as bookId;--as是给返回的列值起一个名字