SQL存储过程总结（一）

一、存储过程的概念

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

二、存储过程的优缺点

1、优点：

(1)减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。

(2)执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

(3)更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。

(4) 布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

2、缺点：

(1)如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。

(2)可移植性差。由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。

三、存储过程的基本语法

1、创建存储过程

create procedure sp_name
@[参数名] [类型],@[参数名] [类型]
as
begin
…
end
以上格式还可以简写成：
create proc sp_name
@[参数名] [类型],@[参数名] [类型]
as
begin
…
end
注意事项："sp_name”为需要创建的存储过程的名字，该名字不可以以阿拉伯数字开头

2、调用存储过程

存储过程可以在三种环境下被调用：
command命令下，基本语法为：exec sp_name [参数名]；
SQL环境下，基本语法为：call sp_name [参数名]；
PL/SQL环境下，基本语法为：begin sp_name [参数名] end；

3、删除存储过程

基本语法：
drop procedure sp_name

注意事项：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

4、其他常用命令

(1)show procedure status
显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

(2)show create procedure sp_name
显示某一个mysql存储过程的详细信息

(3)exec sp_helptext sp_name
显示你这个sp_name这个对象创建文本

四、实际操作

以下是以SQL Server为例：

--准备数据库
create database test_db

use test_db
--创建数据表
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)
alues
('孤剑段飞刀',99,'天客神宗'),
('天龙八部',25.6,'金庸'),
('雪山飞狐',32.7,'金庸'),
('平凡的世界',35.8,'路遥'),
('史记',54.8,'司马迁');

-- 创建存储过程

-- 1、无参数存储过程

if(exists(select * from sys.objects where name='getAllBooks'))
  drop proc getAllBooks
go
create proc getAllBooks
as
select * from books

exec getAllBooks

-- 修改存储过程
alter proc getAllBooks
as
select book_name from books


-- 重命名存储过程
sp_rename getAllBooks,getBookName

exec getBookName

-- 删除存储过程
drop proc getBookName


-- 创建带参数的存储过程

-- 2、带一个参数
if(exists(select * from sys.objects where name='searchBook'))
  drop proc searchBook
go
create proc searchBook(@bookID int)
as
select * from books where book_id=@bookID

exec searchBook 1

-- 3、带两个参数
if(exists(select * from sys.objects where name='twoParams'))
	drop proc twoParams
go
create proc twoParams(
	@bookID int,
	@book_auth varchar(20)
)
as
select * from books where book_id=@bookID and book_auth=@book_auth

exec twoParams 1,'天客神宗'

-- 4、创建有返回值的存储过程

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是给返回的列值起一个名字


-- 5、创建带有通配符的存储过程
if (exists (select * from sys.objects where name = 'charBooks'))
    drop proc charBooks
go
create proc charBooks(
    @bookAuth varchar(20)='金%',
    @bookName varchar(20)='%'
)
as 
    select * from books where book_auth like @bookAuth and book_name like @bookName;
-- 执行存储过程charBooks
exec  charBooks    '天%','孤剑%';
exec sp_helptext 'charBooks'

-- 6、加密存储过程
if (object_id('books_encryption', 'P') is not null)
    drop proc books_encryption
go
create proc books_encryption 
with encryption
as 
    select * from books;
    
-- 执行此过程books_encryption
exec books_encryption;
exec sp_helptext 'books_encryption';-- 控制台会显示"对象 'books_encryption' 的文本已加密。"


-- 7、不缓存存储过程
-- with  recompile不缓存
if (object_id('book_temp', 'P') is not null)
    drop proc book_temp
go
create proc book_temp
with recompile
as
    select * from books;
go

exec book_temp;
exec sp_helptext 'book_temp';


-- 8、创建带游标参数的存储过程
if (object_id('book_cursor', 'P') is not null)
    drop proc book_cursor
go
create proc book_cursor
    @bookCursor cursor varying output
as
    set @bookCursor=cursor forward_only static for
    select book_id,book_name,book_auth from books
    open @bookCursor;
go
-- 调用book_cursor存储过程
declare @cur cursor,
        @bookID int,
        @bookName varchar(20),
        @bookAuth varchar(20);
exec book_cursor @bookCursor=@cur output;
fetch next from @cur into @bookID,@bookName,@bookAuth;
while(@@FETCH_STATUS=0)
begin 
    fetch next from @cur into @bookID,@bookName,@bookAuth;
    print 'bookID:'+convert(varchar,@bookID)+' , bookName: '+ @bookName
            +' ,bookAuth: '+@bookAuth;
end
close @cur    -- 关闭游标
DEALLOCATE @cur; -- 释放游标



-- 9、创建分页存储过程
if (object_id('book_page', 'P') is not null)
    drop proc book_page
go
create proc book_page(
    @TableName varchar(50),            -- 表名
    @ReFieldsStr varchar(200) = '*',   -- 字段名(全部字段为*)
    @OrderString varchar(200),         -- 排序字段(必须!支持多字段不用加order by)
    @WhereString varchar(500) =N'',  -- 条件语句(不用加where)
    @PageSize int,                       -- 每页多少条记录
    @PageIndex int = 1 ,               -- 指定当前为第几页
    @TotalRecord int output            -- 返回总记录数
)
as
begin
     -- 处理开始点和结束点
    Declare @StartRecord int;
    Declare @EndRecord int; 
    Declare @TotalCountSql nvarchar(500); 
    Declare @SqlString nvarchar(2000);    
    set @StartRecord = (@PageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @PageSize - 1 
    SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;-- 总记录数语句
    SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;-- 查询语句
    --
    IF (@WhereString! = '' or @WhereString!=null)
        BEGIN
            SET @TotalCountSql=@TotalCountSql + '  where '+ @WhereString;
            SET @SqlString =@SqlString+ '  where '+ @WhereString;            
        END
    -- 第一次执行得到
    --IF(@TotalRecord is null)
    --   BEGIN
           EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;-- 返回总记录数
    --  END
    ---- 执行主语句
    set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
    Exec(@SqlString)    
END
-- 调用分页存储过程book_page
exec book_page 'books','*','book_id','',3,1,0;

--
declare @totalCount int
exec book_page 'books','*','book_id','',3,1,@totalCount output; 
select @totalCount as totalCount;-- 总记录数。