sql server数据库之存储过程

将从下面几个方面介绍存储过程

1.存储过程的概念

2.存储过程的优缺点

3.存储过程的分类

4.利用t-sql语句创建并执行图书管理系统数据库的存储过程；

5.t-sql语句管理图书管理系统数据库的存储过程；

6.利用t-sql语句修改图书管理系统数据库的存储过程；

7.利用t-sql语句删除图书管理系统数据库的存储过程;

8.利用t-sql语句对图书管理系统数据库的存储过程中的错误进行处理；

一.存储过程的概念：

存储过程是指为了完成特定的功能由一条或多条sql语句组成的集合，这些语句集合 可以被多次调用，类似于批处理文件，通常指定一个名称进行存储，经系统进行编译后存储到数据库的服务器中，作为数据库的对象，形成一个处理单元。存储过程创建之后，用户通过指定存储过程名称与参数，调用该存储过程并且执行。在调用的过程中允许用户声明变量，设置条件，以便增强程序设计的能力。

二.存储过程的优缺点：

1)优点：

1.存储过程运行速度快 为了完成某以特定任务需要大量的t-sql代码进行编译，并进行多次运行，此时存储过程是首选，因为存储过程只在创建时进行一次编译，由查询优化器对其进行分析，优化，并给出执行计划，在以后的每次调用存储过程执行时不需要重新编译。

2.存储过程减少网络流量 由于编辑存储过程的代码是存储到数据库中的，当用户端向服务器端发出调用存储过程的命令时，通过网络传输的只是存储过程的调用，不会有大量的代码在网络中传输，从而极大的减少了网络流量，降低了网络负载。

3.存储过程增强数据代码安全性 因为对存储过程中引用的对象用户不可以直接操作，必须通过sql server系统为用户指定某一具体存储过程的执行权限，由于对存储过程执行权限的限制，所以对存储过程所涉及的数据访问权限同样受到一定的限制，非授权用户是不可以访问相关的数据信息的。存储过程的使用既增加了代码安全性，又保证了数据安全性。

4.存储过程允许模块化设计组件式编程 存储过程一旦被创建在程序中可多次使用，不必每次都重写相应的t-sql代码，在实际应用中有同域的数据库访问接口，程序员可以随时对存储过程进行修改但是丝毫不会影响到应用程序源代码，所以大大提高了程序的可维护性 和可移植性。

5.存储过程屏蔽数据库复杂的细节操作 设计存储过程时用户不必访问数据库的底层信息和数据库内部的对象，可以将复杂的数据库操作进行封装，以便简化操作与设计流程。

2)缺点：

1.存储过程影响到数据库的移植，因为存储过程依赖于数据库管理系统，在存储过程中所封装的t-sql代码不能直接移植到其他数据库管理系统中

2.对集群操作不支持

3.不支持面向对象的程序设计无法面对的方式封装业务逻辑

4.存储过程的代码不易阅读，维护难度大

三.存储过程的分类：

1)系统存储过程

该类存储过程通常被存放到master数据库中，存储过程名称通常以“sp_”为前缀，但是在其他数据库中均可调用系统存储过程，调用时在存储过程名称前面不必添加数据库的限定名。其功能是在表中获取信息，实现数据库服务器的管理任务。

2)用户自定义存储过程

所谓自定义存储过程，是指为了完成某一段特定的功能需求，在用户数据库中利用t-sql自行编辑的语句集合，在用户自定义的过程中可以有输入参数，返回的输出参数及返回至客户端的信息与结果 。如果在存储过程名称前加了“##”符号，表示创建的存储过程是临时的全局性的；如果前面的为“#”符号，表示所创建的存储过程是临时的局部的，该存储过程只能在创建它的会话中使用。以上两种存储过程创建后都存放在tempdb数据库中。

用户自定义存储过程还可以细分为t-sql语言存储过程和CLR存储过程。CLR存储过程是指利用.NET框架公共语言编辑的存储过程，既可以接受用户提供的参数又可以返回存储过程的运行结果，通常用作某个类的公共静态方法。

3)扩展存储过程

通常以“xp_”为前缀标识，在sql server系统外通过执行动态链接库，即DLL文件，来实现的功能，该存储过程经常使用API接口进行编辑，可以加载到sql server实例的地址空间里试试运行。

在sql server常见的扩展存储过程有：

xp_enumgroups 指定WINDOWS本地组列表在WINDOWS域中定义的全局组表

xp_findnextmsg 接受输入的邮件ID号，返回输出的邮件ID号

xp_grantlogin 给用户分配对sql server系统的权限

xp_logevent 把用户自定义消息输入到sql server日志文件或WINDOWS系统事件查看器中

xp_loginconfig 显示sql server 实例运行时登陆的安全配置

四.利用t-sql语句创建并执行图书管理系统数据库的存储过程：

建立并执行存储过程实例(假设你已有一个数据库LibraryManagement)：

--建立一个名为sp_bookquantity的存储过程CREATE PROCEDURE sp_bookquantityASBEGINSELECT Book_ID,Book_name,Book_author,Book_press,Book_price,Book_quantityFROM BookinfoWHERE Book_quantity<5END

创建存储过程语法：

CREATE PROCEDURE [EDURE] <存储过程名>--创建过程名[,n] --可选整数，主要作用时对同同名的过程分组，可以使用一条DROP PROCEDURE语句--将分配到同一组中的过程一同删除，若名称中包含分隔标识符，则数字不应该包含于标识符中，只能在存储过程名称前后使用适当的分隔符[{@<参数名><参数数据类型>} --用于指定存储过程中的参数[VARYING] [=<默认值>]--用于指定输出参数的结果集，当使用游标数据类型作为输出参数时必须要使用该关键字[OUTPUT][READONLY][,..n] --被output指定的参数是输出参数，可以将值返回给调用方[WITH{RECOMPILE|ENCRYOTION|RECOMPILE,ENCRYPTION}][FOR REPLICATION]AS<SQL 语句>[...n]

执行过程语法：

[EXEC|EXECUTE]{[@<返回状态>=]{<存储过程名称>[,<number>] --number主要作用是对同名的存储过程分组，该选项在扩展存储过程中是无效的|@<局部变量名>}--模块名称[[@<参数名>=]{<参数值>|@<variable>[OUTPUT]|[DEFAULT]}[,..n][WITH RECOMPILE]} --该选项表示执行存储过程后，需要进行重新编译，重新拟定计划，如果现在有查询计划，该计划将存储到缓存中，不适宜使用该选项。

创建并执行具有输入参数的存储过程proc_readerdepartment

USE LibraryManagementIF EXISTS(SELECT NAME FROM SYSOBJECTSWHERE NAME='pro_readerdepartment'AND TYPE='P')DROP PROCEDURE pro_readerdepartmentGOCREATE PROCEDURE pro_readerdepartment@ReaderID NVARCHAR(8),@Readername NVARCHAR(30) OUTPUT,@Readerdepartment NVARCHAR(30) OUTPUTASSELECT @Readername=Reader_name,@Readerdepartment=Reader_departmentFROM Readerinfo WHERE Reader_ID=@ReaderIDGO

执行：

DECLARE @ReaderID NVARCHAR(8),@Readername NVARCHAR(30),@Readerdepartment NVARCHAR(50)EXEC pro_readerdepartment '1614',@Readername OUTPUT,@Readerdepartment OUTPUTSELECT @Readername,@Readerdepartment

五.利用t-sql语句查看存储过程详细信息：

sp_helptext pro_readerdepartment

六.利用t-sql语句修改图书管理系统数据库的存储过程

将图书名称以“计算机”字样开头的全部检索出来：

USE LibraryManagementGOALTER PROCEDURE pro_bookName@bookname NVARCHAR(50)='计算机%'ASSELECT Book_name,Book_pressFROM BookinfoWHERE Book_name LIKE @booknameGO

执行：

EXECUTE pro_bookname

七.利用t-sql语句删除图书管理系统数据库的存储过程

DROP PROC proc_bookname

八.利用t-sql语句对图书管理系统数据库的存储过程中的错误进行处理

创建存储过程的t-sql语句如下：

USE LibraryManagementIF EXISTS(SELECT name FROM SYSOBJECTS WHERE name='proc_readerdelerror' AND xtype='p')DROP PROCEDURE proc_readerdelerrorGOCREATE PROCEDURE proc_readerdelerror@ReaderID NVARCHAR(8)ASDELETE FROM Readerinfo WHERE Reader_ID=@ReaderIDPRINT N'error='+CAST(@@ERROR AS NVARCHAR(8))+'rows deleted='+CAST(@@ROWCOUNT AS NVARCHAR(8))

执行存储过程的t-sql语句如下：

EXECUTE proc_readerdelerror @ReaderID='12110203'