sql语句大全之MSSQL sql存储过程创建简介及应用举

当前位置:

首页 > 数据库 > T-SQL >

sql语句大全之MSSQL sql存储过程创建简介及应用举

MSSQL存储过程简介

存储过程的概念:我们将一组常用、经常重复使用的sql,采用一个包名进行定义，并将其放入数据库中，作为DB服务器中的一个对象，对外服务，我们将此包称之为”存储过程”。

存储过程的优点:
1 存储过程作为服务器对象，所以运行存储过程中的sql块,通常比sql块速度更快，效率更高。
2 存储过程将sql块存储在数据库对象中，减少了客户端同服务器端通信的数据块大小，减轻网络浏览。
3 存储过程将sql块封装为一个包，增加了sql代码的可读性和sql代码调试的便利性。
4 存储过程作为db服务器中的一个对象，我们可以通过相应的权限设置，来限制客户端操作数据库的权限，增加db服务器的安全性。

存储过程的缺点:
1 存储过程作为db服务器对象，在后台进行大量的运算，加大了DB服务器的压力。
2 存储过程作为db服务器对象，里面包含了不同类型的sql语法，所以存储过程不方便不同类型数据库的移植操作。
3 对应用系统而言，由于业务逻辑存在程序代码和存储过程中，所以加大了应用程序的维护难度。

存储过程的应用场景:
1 数据库仓库抽取数据
2 超大型报表获取获取
3 超复杂的sql业务逻辑代码编写

MSSQL存储过程举例应用

mssql 存储过程语法:

create proc 存储过程名称 
参数名1 参数类型 [参数默认值] [是否为输出参数],
参数名2 参数类型 [参数默认值] [是否为输出参数]
...
 [with {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
as 
/*sql 代码块*/

存储过程名称最大长度不能超过256个字符
with recompile:代表每次调用存储过程，db服务器都为对存储过程进行重编译,清除buffer pool中的缓存信息
with ENCRYPTION:加密存储过程
with recompile, ENCRYPTION:重编译存储过程和加密存储过程

  create table A (keyId int)
  insert into A(keyId)values(1),(10),(100),(1000),(10000),(100000),(100000),(2)
    
  /*创建存储过程*/
   /*创建存储过程查询数据表数据*/
   create proc pr_showTableAInfo
   as
   begin
   select keyId from  A 
   end 
   go 
   /*执行存储过程*/
   exec  pr_showTableAInfo 
   go
   
   /*创建存储过程返回多张数据表数据*/
   create proc pr_showTableAInfo
   as
   begin
   select keyId from  A 
   select '这是第二张数据表' as table 
   end 
   go 
   /*执行存储过程*/
   exec  pr_showTableAInfo 
   go

/*创建带输出参数的存储过程*/
create proc pr_t
@a int,
@b varchar(36) output
as
begin
set @b=NEWID() ----对output参数赋值
select 'test',@b as t ---返回数据表
end
go
/*调用带output参数的存储过程方法*/
declare @t2 varchar(36)
exec pr_t 1,@t2 output ----设置调用参数属性为output,存储过程中的数值，则会传送至外部变量中
select @t2 as [backValue]
go

栏目列表