十、存储过程

一、存储过程

存储过程（procedure）是一组为了完成特定功能的SQL语句集合 ，经编译后存储在数据库中， 用户通过指定存储过程的名称并给出参数来执行。 存储过程优点 通过把处理封装在简单易用的单元中，简化复杂的操作存储过程创建后可以在程序中被多次调用执行， 而不必重新编写该存储过程的SQL语句。可以针对于某一项业务逻辑处理，把处理过程就定义好。需要 处理业务，就直接调用存储过程。 提高性能。使用存储过程比使用单独的SQL语句要快如果某一操作包含大量的SQL语句代码，分别被多 次执行，那么存储过程要比批处理的执行速度快得多。 安全,调用者只需要知道如何调用指定的存储过程即可，而不用关心存储过程的内容，防止SQL注入。 存储过程减轻网络流量对于同一个针对数据库对象的操作，这一操作所涉及到的T-SQL语句被组织成一 存储过程，那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL 语句。从而减轻了网络流量,降低了网络负载。

 

二、存储过程缺点

编写复杂
如果没有相应的权限,你将无法创建存储过程
当服务器调用过多存储过程,用户访问量大了,那么压力就丢给数据库来解决,数据库压力会过大
过多的存储过程，优化过于麻烦.

三、常见系统存储过程

存储过程 含义
exec sp_databases; 查看所有数据库
exec sp_helpdb; 查询数据库信息
exec sp_helpdb 数据名; 查询指定数据库信息
exec sp_renamedb ‘旧库名’, ‘新库名’;
更改数据库名称
exec sp_tables;
查询当前数据库的所有表
exec sp_columns 表名;
查看列
exec sp_help 表名; 返回表的所有信息
exec sp_helpIndex 表名; 查看索引
exec sp_helpConstraint 表名; 约束
exec sp_stored_procedures; 当前环境的所有存储
exec sp_helptext ‘存储过程’; 查看存储过程源码
exec sp_rename ‘旧名’, ‘新名’; 修改表、索引、列的名称
exec sp_defaultdb ‘旧库名’, ‘新库名’; 更改登录名的默认数据库
注意：exec 用于调用存储过程

四、自定义存储过程创建语法

create proc | procedure 存储名(
[{@参数 数据类型} [=默认值] [out|output],
{@参数 数据类型} [=默认值] [out|output],
....]
)
as
begin
SQL_statements
end
go

五、存储过程修改语法

alter proc | procedure 存储过程名
as
beign
sql语句;
end

六、存储过程删除语法

drop proc | procedure 存储过程名;

七、调用语法

exec 存储过程名; ---不带参数调用
exec 存储过程名 参数1 out|output,参数2 out|output; ---带参数调用

八、分页存储过程

DROP PROCEDURE IF EXISTS [dbo].[SP_CustomPager];
GO
CREATE PROCEDURE [dbo].[SP_CustomPager]
@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
--第一次执行得到
EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord OUTPUT;--返回总记录数
----执行主语句
SET @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' +
ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord));
Exec(@SqlString)
END
declare @total int;
EXEC SP_CustomPager 'JDCategorys','*','id','',20,1,@total output
select @total