第10章 存储过程

第10章存储过程10.1存储过程简介10.2界面中操作存储过程10.3用代码操作存储过程

10.1存储过程简介10.1.1存储过程的概念 在数据库系统中，存储过程（storedProcedure，简写为sproc）的作用很重要。 存储过程包含一组T-SQL语句，这组语句包含一些固定的操作，可以实现某个具体任务。它被SQLServer服务器集中编译处理后，存储在数据库服务器中，可以接受输入参数、输出参数、返回状态值和嵌套调用。10.1存储过程简介10.1.2存储过程分类存储过程可以分为： 系统存储过程、扩展存储过程、用户自定义存储过程3种类型。10.1.2存储过程分类（1）系统存储过程： 以sp_开头，由SQLServer内建，存储在master数据库中。系统存储过程主要从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程可以在任何一个数据库中执行。下面列出一些常见的系统存储过程：sp_helptext 用于显示存储过程的定义文本sp_rename 用于修改当前数据库中用户对象的名称sp_renamedb用于修改指定数据库的名称10.1.2存储过程分类（2）扩展存储过程 扩展存储过程是在SQLServer环境之外执行的动态链接库，可以扩展SQLServer的功能，通常以xp_开头。使用扩展存储过程时，需要先加载到SQLServer系统中，并按照存储过程的方式执行。（3）用户自定义存储过程 用户自定义存储过程是指在用户数据库中创建的存储过程，可以完成特定的数据库操作任务，其前缀不能是sp_。

本章后面讲的主要是用户自定义的存储过程。10.2界面中操作存储过程1.简单的存储过程简单存储过程直接完成一个程序段的运行，不含任何输入输出参数和返回值。【例10.1】使用SQLServerManagementStudio图形化工具创建一个简单的没有参数的存储过程proc_AllBooks，在数据库中查询BookInfo表(表4-7)中所有图书信息。10.2界面中操作存储过程2.含输入参数的存储过程【例10.2】使用SQLServerManagementStudio图形化工具创建一个含输入参数的存储过程proc_CheapBooks，要求查询BookInfo表(表4-7)中价格低于30元的所有图书信息。

10.2.2执行存储过程【例10.3】使用SQLServerManagementStudio图形化工具执行简单的存储过程proc_AllBooks。

(1)在SSMS中，展开“数据库”节点，展开“图书借阅信息管理系统”—“可编程性”—“存储过程”，找到proc_AllBooks，右键选择“执行存储过程”。(2)在弹出的“执行过程”对话框中，选择“确定”，即执行了存储过程。需要说明的是，因为proc_AllBooks存储过程，不含任何参数，所以在弹出的“执行过程”对话框中没有需要填写的参数信息（图10-9）。执行后的结果如图10.10。10.2.2执行存储过程【例10.4】使用SQLServerManagementStudio图形化工具执行含参数的存储过程proc_CheapBooks。

(1)在SSMS中，展开“数据库”节点，展开“图书借阅信息管理系统”—“可编程性”—“存储过程”，找到proc_CheapBooks，右键选择“执行存储过程”。(2)10.2.2执行存储过程【例10.4】使用SQLServerManagementStudio图形化工具执行含参数的存储过程proc_CheapBooks。

(1)在SSMS中，展开“数据库”节点，展开“图书借阅信息管理系统”—“可编程性”—“存储过程”，找到proc_CheapBooks，右键选择“执行存储过程”。(2)10.3用代码操作存储过程使用T-SQL语句创建存储过程是最常见的方法，可以使用CREATEPROC关键词来实现。其语法格式如下：createproc[edure]

存储过程名数据类型}[=默认值][output][,...n][withencryption]as{sql语句}10.3用代码操作存储过程1.简单的存储过程【例10.7】使用T-SQL语句创建一个简单的存储过程proc_AllReaders，要求查询ReaderCard表(表4-9)中所有读者信息。use图书借阅信息管理系统gocreateprocproc_AllReadersasselect*fromReaderCard10.3用代码操作存储过程2.含输入参数的存储过程 含参数的存储过程，目的是通过使用参数向存储过程输入和输出信息，从而扩展存储过程的功能。【例10.8】使用T-SQL语句创建一个含输入参数的存储过程proc_MorFReaders，要求查询ReaderCard表(表4-9)中指定性别的读者信息。createprocproc_MorFReaders@sexnchar(1) --输入参数asselect*fromReaderCardwhere --使用输入参数10.3用代码操作存储过程3.含输出参数的存储过程【例10.9】使用T-SQL语句创建一个含输出参数的存储过程proc_ReadersNum，要求查询ReaderCard表(表4-9)中指定性别的读者数量，并返回数量的值。createprocproc_ReadersNum@sexnchar(1)='男',--输入参数，并指定了默认值@numintoutput --输出参数asselect@num=count(*)fromReaderCard --输出参数被赋值where --使用输入参数10.3用代码操作存储过程4.含有参数和返回值的存储过程【例10.10】使用T-SQL语句创建一个含返回值的存储过程proc_ReadersNumInfo，要求查询ReaderCard表(表4-9)中指定性别的读者数量，并返回读者人数信息。createprocproc_ReadersNumInfo@famalenchar(1)='女', @malenchar(1)='男', --输入参数@f_numintoutput ,@m_numintoutput --输出参数10.3用代码操作存储过程4.含有参数和返回值的存储过程【例10.10】使用T-SQL语句创建一个含返回值的存储过程proc_ReadersNumInfo，要求查询ReaderCard表(表4-9)中指定性别的读者数量，并返回读者人数信息。asdeclare@nint

--声明返回值set@n=1--为返回值赋值select@f_num=count(*)fromReaderCard --输出参数被赋值where --使用输入参数select@m_num=count(*)fromReaderCard --输出参数被赋值where --使用输入参数ifset@n=0elseset@n=1return@n10.3.2执行存储过程【例10.12】使用T-SQL语句执行简单的存储过程proc_AllReaders，显示所有读者的信息。Execproc_AllReaders10.3.2执行存储过程【例10.13】使用T-SQL语句执行含输入参数的存储过程proc_MorFReaders，显示所有男读者的信息。Execproc_AllReaders'男'

10.3.2执行存储过程【例10.14】使用T-SQL语句执行含输出参数的存储过程proc_ReadersNum，显示所有男读者的数量。（如图10.16）declare@numintExecproc_ReadersNumoutputprint'男读者的数量:aschar(10))10.3.2执行存储过程【例10.15】使用T-SQL语句执行含返回值及输入输出参数的存储过程proc_ReadersNumInfo，显示读者的数量信息。declare@FnumintExec@Str=proc_ReadersNumInfooutputprint'男读者的数量:aschar(10))print'女读者的数量:aschar(10))if@str=0print'女读者多'elseprint'女读者不多于男读者'10.3.2执行存储过程【例10.16】使用Exec语句执行字符串实例。declare@strvarchar(40)set@str='select*fromReaderCard'Exec注意，Exec执行字符串时，后面的括号不能省略。10.3.3查看存储过程1.查看存储过程的定义文本

EXECsp_helptext存储过程名2.查看存储过程的依赖关系

EXECsp_depends存储过程名3.查看存储过程的参数

EXECsp_help存储过程名4.查看数据库中全部存储过程

EXECsp_stored_procedures10.3.3查看存储过程【例10.17】使用相关的系统存储过程查看存储过程proc_ReadersNumInfo的定义、相关性和参数。EXECsp_helptextproc_ReadersNumInfo--查看存储过程的定义EXECsp_dependsproc_ReadersNumInfo--查看存储过程的相关性EXECsp_helpproc_ReadersNumInfo --查看存储过程的参数EXECsp_stored_procedures --查看全部存储过程10.3.4修改和删除存储过程修改存储过程的语法如下：alterproc[edure]存储过程名数据类型}[=默认值][output][,...n][withencryption]as{sql语句}10.3.4修改和删除存储过程【例10.18】使用T-SQL语句修改存储过程proc_ReadersNum，能够显示不同类型的读者信息，并加密。执行存储过程，显示学生读者的信息。alterprocproc_ReadersNum@类型varchar(50)=‘Student’, --输入参数@numintoutput --输出参数WITHENCRYPTION--加密asselect@num=count(*)fromReaderCard --输出参数被赋值where

--使用输入参数10.3.4修改和删除存储过程【例10.18】