sql server存储过程学习

一、什么是存储过程

存储过程就是一条或者多条sql语句的集合,可视为批处理文件，但是其作用不仅限于批处理。

存储过程就是为了实现特定任务，而将一些需要多次调用的固定操作语句编写成程序段，这些程序段存储在服务器上，有数据库服务器通过程序来调用。

二、存储过程的优缺点

存储过程的优点:

 

1. 存储过程加快系统运行速度，存储过程只在创建时编译，以后每次执行时不需要重新编译。
2. 存储过程可以封装复杂的数据库操作，简化操作流程，例如对多个表的更新，删除等。
3. 可实现模块化的程序设计，存储过程可以多次调用，提供统一的数据库访问接口，改进应用程序的可维护性。
4. 存储过程可以增加代码的安全性，对于用户不能直接操作存储过程中引用的对象，SQL  Server可以设定用户对指定存储过程的执行权限。
5. 存储过程可以降低网络流量，存储过程代码直接存储于数据库中，在客户端与服务器的通信过程中，不会产生大量的T_SQL代码流量。

存储过程的缺点：

1. 数据库移植不方便，存储过程依赖与数据库管理系统， SQL Server 存储过程中封装的操作代码不能直接移植到其他的数据库管理系统中。
2. 不支持面向对象的设计，无法采用面向对象的方式将逻辑业务进行封装，甚至形成通用的可支持服务的业务逻辑框架.
3. 代码可读性差，不易维护。不支持集群。

三、常用语法

1.@parameter 
      过程中的参数。在 Create PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。

2.AS 

指定过程要执行的操作

3.变量的声明:
声明变量时必须在变量前加@符号 
DECLARE @I INT

4.声明多个变量:
DECLARE @s varchar(10),@a INT

5.变量的赋值:
变量赋值时变量前必须加set
SET @I = 30

使用如下：

例A；

DECLARE @d INT  

set @d = 1  

IF @d = 1 BEGIN  

 PRINT '正确'  

END  

ELSE BEGIN  

PRINT '错误'  

END  

例B：

declare @today int  

declare @week nvarchar(3)  

set @today=3  

set @week= case  

when @today=1 then '星期一'  

when @today=2 then '星期二'  

when @today=3 then '星期三'  

 when @today=4 then '星期四'  

 when @today=5 then '星期五'  

 when @today=6 then '星期六'  

 when @today=7 then '星期日'  

 else '值错误'  

end  

print @week  

5.游标：

//定义游标

DECLARE @cur1 CURSOR FOR SELECT .........  

//打开游标  

OPEN @cur1

--从游标变量中读取值

FETCH NEXT FROM @cur1 INTO 变量  

--判断FETCH语句是否执行成功

WHILE(@@FETCH_STATUS=0)  

BEGIN  

--读取游标变量中的数据

FETCH NEXT FROM @cur1 INTO 变量  

END  

CLOSE @cur1  

//摧毁游标

DEALLOCATE @cur1   

详细的游标说明可参考该博客，查看我的这一篇博客：

https://blog.youkuaiyun.com/bird_tp/article/details/89524964

四、存储过程创建及相关操作

1.创建存储过程的基本语法模板：

if (exists (select * from sys.objects where name = 'pro_name'))
    drop proc pro_name
go
create proc pro_name
    @param_name param_type [=default_value]
as
begin    
    sql语句
end

2.执行方法
exec dbo.USP_GetAllUser 2;

3.修改存储过程

alter proc proc_name
as
　　sql语句

4.删除存储过程

drop procedure getAllBooks;

5.存储过程中的输出参数的使用

 if (exists(select * from  sys.objects where name='GetUser'))
      drop proc GetUser
 go 
 create proc GetUser
      @id int output,
     @name varchar(20) out
 as 
  begin 
     select @id=Id,@name=Name from UserInfo where Id=@id
 end
 
 go 
 declare 
 @name varchar(20),
 @id int;
set @id=3;
 exec dbo.GetUser @id,@name out;
 select @id,@name;
 print Cast(@id as varchar(10))+'-'+@name;

6.创建带通配符的存储过程

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    '孔%','论%';