SQLServer存储过程学习记录

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

一、存储过程的概述

SQL Server中的存储过程是使用T_SQL编写的代码段。它的目的在于能够方便的从系统表中查询信息，或者完成与更新数据库表相关的管理任务和其他的系统管理任务.T_SQL语句是SQL Server数据库与应用程序之间的编程接口。在很多情况下，一些代码会被开发者重复编写多次，如果每次都编写相同功能的代码，不但繁琐，而且容易出错，而且由于SQL Server逐条的执行语句会降低系统的运行效率。

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

存储过程的优点:

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

存储过程的缺点：

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

　　说一下自己对存储过程的理解：对于SQLServer中的存储过程，和C#中的函数和相似，可以理解为一个函数，可以给存储过程携带参数，可以有返回值，可以带有output参数，也可以让参数带默认值。这样子理解可以让自己快速理解存储过程

二、存储过程的分类

　　1.系统存储过程

系统存储过程是 SQL Server系统自身提供的存储过程，可以作为命令执行各种操作。

系统存储过程主要用来从系统表中获取信息，使用系统存储过程完成数据库服务器的管理工作，为系统管理员提供帮助，为用户查看数据库对象提供方便，系统存储过程位于数据库服务器中，并且以sp_开头，系统存储过程定义在系统定义和用户定义的数据库中，在调用时不必在存储过程前加数据库限定名。例如：sp_rename系统存储过程可以修改当前数据库中用户创建对象的名称，sp_helptext存储过程可以显示规则，默认值或视图的文本信息，SQL SERVER服务器中许多的管理工作都是通过执行系统存储过程来完成的，许多系统信息也可以通过执行系统存储过程来获得。

系统存储过程创建并存放在与系统数据库master中，一些系统存储过程只能由系统管理员使用，而有些系统存储过程通过授权可以被其它用户所使用。

　　2.用户存储过程(自定义存储过程)

自定义存储过程即用户使用T_SQL语句编写的、为了实现某一特定业务需求，在用户数据库中编写的T_SQL语句集合，自定义存储过程可以接受输入参数、向客户端返回结果和信息，返回输出参数等。创建自定义存储过程时，存储过程名前加上"##"表示创建了一个全局的临时存储过程;存储过程前面加上"#"时，表示创建的局部临时存储过程。局部临时存储过程只能在创建它的回话中使用，会话结束时，将被删除。这两种存储过程都存储在tempdb数据库中。

　　用户定义的存储过程分为两类：T_SQL 和CLR

　　　　T_SQL:存储过程是值保存的T_SQL语句集合，可以接受和返回用户提供的参数，存储过程也可能从数据库向客户端应用程序返回数据。

　　　　CLR存储过程是指引用Microsoft.NET Framework公共语言的方法存储过程，可以接受和返回用户提供的参数，它们在.NET Framework程序集是作为类的公共静态方法实现的。

三、存储过程的创建

　　准备数据如下：

use Test;
--创建测试books表
create table Money (
    id int identity(1,1) primary key,
    money int
);

　　里面插入若干条测试数据

　　1.创建一个无参存储过程

if exists(select * from sysobjects where name='usp_getAllMoneyInfo')
drop proc usp_getAllMoneyInfo
go
--存储过程相当于一个函数 可以有参数，输出参数 as后为方法体 使用return进行返回--
create proc usp_getAllMoneyInfo
--as前面为定义参数 相当于函数中的参数， 不用进行声明--
as
select * from Money
go

　　执行存储过程

exec usp_getAllMoneyInfo

　　删除存储过程

drop proc usp_getAllMoneyInfo

　　修改存储过程

alter procedure usp_getAllMoneyInfo
as
**********
go

2.创建一个带参数的存储过程并执行

if exists(select * from sysobjects where name='usp_getMoneyInfo')
drop proc usp_getMoneyInfo
go
--存储过程相当于一个函数 可以有参数，输出参数 as后为方法体 使用return进行返回--
create proc usp_getAllMoneyInfo
--as前面为定义参数 相当于函数中的参数， 不用进行声明--
@id int
as
select * from Money where id=@id
go
exec usp_getMoneyInfo 2 --在调用后面添加参数--

3.创建一个带output参数的存储过程并执行

if exists(select * from sysobjects where name='usp_getAllMoneyCount')
drop proc usp_getAllMoneyInfo
go
create proc usp_getAllMoneyCount
@count int output --外部参数在后面加output 类似于C#的out--
as
set @count = (select count(*) from Money)
go

declare @cnt int;
exec usp_getAllMoneyCount @count=@cnt output --在调用的时候 要加output的关键字-
print @cnt

4.创建一个带有返回值的存储过程并执行

-带有返回值的存储过程--
--return只能返回整型的数据，如果需要返回其他类型的数据，需要使用output输出参数--
if exists(select * from sysobjects where name='usp_getCount')
drop proc usp_getAllMoneyInfo
go
create proc usp_getCount

as
declare @count int
set @count = (select count(*) from Money)
return @count
go
declare @totalCount int
exec @totalCount = usp_getCount  --获取返回值的格式：在exec后面添加返回值变量=--
print @totalCount