SQL Server存储过程

目录

1. 存储过程概述
2. 存储过程分类
3. 创建存储过程
4. 创建无参存储过程
5. 修改存储过程
6. 删除存储过程
7. 重命名存储过程
8. 创建带参数的存储过程

简单来说，存储过程就是一条或者多条SQL语句的集合，可视为批处理文件，但是其作用不仅限于批处理。
本篇主要介绍变量的使用，存储过程和存储函数的创建、调用、查看、修改以及删除操作。

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

存储过程优点：

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

存储过程缺点：

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

二、存储过程分类：

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程序集是作为类的公共静态方法实现的。

3.扩展存储过程

扩展存储过程是以在SQL Server环境外执行的动态链接（DLL文件）来实现的，可以加载到SQL Server实例运行的地址空间中执行，扩展存储过程可以用SQL Server扩展存储过程API编程，扩展存储过程以前缀“xp_”来标识，对用户来说，扩展存储过程和普通存储过程一样，可以用相同的方法来执行。

三、创建存储过程：

工欲善其事，必先利其器，准备数据如下：

use sample_db;
--创建测试books表
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)
                    values
                        ('论语',25.6,'孔子'),
                        ('天龙八部',25.6,'金庸'),
                        ('雪山飞狐',32.7,'金庸'),
                        ('平凡的世界',35.8,'路遥'),
                        ('史记',54.8,'司马迁');

1. 创建无参存储过程：

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'getAllBooks'))
    drop proc proc_get_student
go
create procedure getAllBooks