《数据库基础与应用》
第9章存储过程的创建和使用
自定义函数的创建与使用？ 自定义函数的创建与使用？
CREATE FUNCTION 函数名 ( 参数表 ) RETURNS 返回值的类型 [ AS ] BEGIN 函数体 RETURN 返回的表达式 END 调用： SET @result=dbo.fun_SumCount(@maxprice,@minprice)
5．允许模块化程序设计
存储过程可以封装企业的功能模块，这种企业的功能模块也为商业规则或 者商业策赂，可以只创建一次并将其存储在数据库中，以后即可在程序中调用该 过程任意次，而且可以统一修改。
9.1.3 掌握存储过程的分类
存储过程分为两大类：系统存储过程和用户自定义存储过程。
系统存储过程：由系统定义的存储过程，存放在master数据库中，
p_topic_by_sid存储过程可以通过以下方法执行： USE bbsdb GO EXEC p_topic_by_sid 2 -- Or EXEC p_topic_by_sid 2
练习： 练习：
（2）在student数据库中，创建名为p_tj2的存储过 程，查询选修某门指定课程的学生人数、最高成绩、最 低成绩和平均成绩 ； 执行该存储过程，例如，查询选修‘C1’课程的信息
练习： 练习：
（1）在student数据库中，创建名为p_tj1的存储过 程，查询选修每门课程的学生人数、最高成绩、最低成 绩和平均成绩 ；
2．创建带有参数的简单存储过程 【例9-2】创建存储过程，除【例9-1】的要求（不显示代码,需 要显示代码意义）外， 还要求只返回指定某版块编号的帖子信息。 【分析】： （1）创建存储过程，该存储过程有输入参数“版块编号”，无 输出参数。 （2）帖子信息存放于TOPIC表，发帖人信息存放于USERS表， 版块信息存放于SECTION表, 三张表作联接行存储过程 查看和修改存储过程 常用的系统存储过程
本章核心知识点
利用T-SQL创建存储过程 存贮过程的执行 存贮过程的重新编译
本章重点难点
在存贮过程中使用参数 重新编译存贮过程
本章教学目标
了解存储过程的概念和优点,能够在实际应用中设 计合适的存储过程 理解存储过程的类型，掌握一定数量的系统存储 过程 掌握如何创建和执行存储过程。 掌握如何创建和执行存储过程。 掌握管理存储过程的方法。
（2）弹出存储过程属性对话框，如图所示。
（3）在“文本”框中输入存储过程。包括修改存储过程名, 并在”AS”后输入存储过程内容。 （4）单击【检查语法】按钮，检查所输入的SQL语句语法 的是否正确。 （5）单击【确定】按钮，创建该存储过程。
9.2.2 使用 使用Transact-SQL语言创建存储过程 语言创建存储过程
类似C语言中的系统函数。系统存储过程的名称都以“sp_”开头开头， 可以在任何数据库中执行系统存储过程。
用户自定义存储过程： 用户自定义存储过程 由用户在自己的数据库中创建的存储过
程，类似C语言中的自定义函数，只能在特定的数据库中执行。
用户自定义存储过程可根据不同的标准予以分类： 用户自定义存储过程 （1）临时存储过程。是以存储过程名＃、＃＃开头的存储过 程。存放在tempdb数据库中，当用户断开连接时将自动删除临时 存储过程。 （2）本地存储过程 本地存储过程。用户创建并存放在用户当前连接的SQL 本地存储过程 SERVER数据库中的存储过程, 是我们通常意义上的存储过程。 （3）扩展存储过程。是利用高级语言（如DELPHI和C++等） 编写的存储过程。实际是包含在一个WIn32.DLL中的一个函数。只 有那些可以独立创建DLL和EXE文件的高级程序语言可用来创建扩 展存储过程。
代码如下： USE bbsdb GO CREATE PROCEDURE p_topic_all AS SELECT TID, TSID, Users.UName, TReplyCount, TEmotin, TTopic, TContents, TTime, TClickCount, TFlag, TlastClickT, SECTION.SNAME FROM Topic , Users ,SECTION Where Topic.TUID = Users.UID and Topic.TSID = SECTION.SID GO
第9章 存储过程的创建和使用 章
顾名思义，存储过程就是存储在SQL SERVER服 务器中的一组编译 编译成单个执行计划的T-SQL语句。 编译
9.1 概述
在创建SQL Server数据库应用程序时，Transact-SQL语言是应 用程序和SQL Server数据库之间的主要编程接口 编程接口。可用如下两种方 编程接口 法存储和执行Transact-SQL语句。 (1) SQL语句嵌入在开发语言中，执行时向SQL Server发送命令 并处理结果的应用程序。） (2) 可以将Transact-SQL程序保存在SQL Server中，即存储过程 存储过程， 存储过程 开发语言中直接调用该存储过程。
9.2 创建存储过程
在当前数据库创建的用户自定义本地存储过程，不包括扩 展存储过程。 创建存储过程的方法共有三种： 使用企业管理器 使用T SQL语句 使用T-SQL语句 使用创建存储过程向导
9.2.1 利用企业管理器创建存储过程
可使用企业管理器创建存储过程。
（1）启动SQL SERVER企业管理器，依次单击控制台树上的【服务器组】 →【服务器】→【数据库】→【要创建存储过程的数据库】→右击【存储过 程】，在弹出的快捷菜单中单击【新建存储过程】，如图所示。
存储过程与自定义函数的区别？ 存储过程与自定义函数的区别？
存储过程是使用EXEC命令独立调用 调用的，而用户自定义函数是在另一个 调用 SQL语句中调用的。 存储过程是允许用户和程序去使用存储的程序，而不是允许其存取表， 这样能够增强程序安全性 安全性。与标准的SQL Server相比，存储程序限制用户行动 安全性 权限方面更为细化。例如，如果你有一个货存表，每次卖出一个货物收银员都 要对表进行更新一次(从货存中把该货品减去一件)。你可以给收银员设置权限， 允许其使用decrement_item存储过程，而不是允许他们有任意修改表的权限。 函数必须始终返回 返回一个值(一个标量值或一个表格)。而存储过程可以返 返回 回一个标量值、一个表值或无需返回值。 总而言之，存储程序对SQL Server开发员来说是最有价值的宝物之一， 用于数据库中，能够大大的提高工作效率，增强安全性，绝对超值。
执行上述T-SQL便可创建存储过程，创建成功的存储过程可由 SQL的EXECUTE调用，也可由前端开发语言（DELPHI, C#, JAVA等） 通过数据库接口(如OLD DB)的方法调用。 p_topic_all存储过程可以通过以下方法执行： USE bbsdb GO EXECUTE p_topic_all -- 或 EXEC p_topic_all GO
DECLARE @sql varchar(8000) SET @sql = 'select TOP ' + convert(varchar(5), @RowsOfPage) + ' TID, TSID, Users.UName, TReplyCount, TEmotin, TTopic,' +' TContents, TTime, ' + ' TClickCount, TFlag, TlastClickT, SECTION.SNAME ' + ' FROM Topic INNER JOIN Users ON TUID = Users.UID ' + ' INNER JOIN SECTION ON TSID = SECTION.SID ' + ' WHERE SECTION.SID = ' + convert(varchar(10), @SID) EXEC(@sql) GO
代码如下：
USE bbsdb GO CREATE PROCEDURE p_topic_by_sid @SID INT AS SELECT TID, TSID, Users.UName, TReplyCount, TEmotion, TTopic, TContents, TTime, TClickCount, TFlag, TlastClickT, SECTION.SNAME FROM Topic INNER JOIN Users ON TUID = Users.UID INNER JOIN SECTION ON TSID = SECTION.SID WHERE SECTION.SID = @SID GO
CREATE PROCEDURE p_topic_by_sid1 @SID INT, @Pages INT output, @RowsOfPage INT AS --获取该版块的页数 SELECT @Pages = ceiling(count(*)*1.0/@RowsOfPage) FROM Topic WHERE TSID = @SID --获取该版块的该版块第1页的帖子信息
3．减少网络流量
特别对于复杂的商业业务计算，例如计算用户的每月应交电费，如采用TSQL实现，则需要大量的T-SQL语句和中间数据在客户机与SQL SERVER服务器之间 的网络通道上传送；采用存储过程实现，则只需由一条执行存储过程的单独语句 就可实现，大大减少网络流量。
4．提高系统安全性
可作为安全机制使用。通过适当的权限设置，可以使系统的安全性得到有 效保障。
用CREATE PROCEDURE语句创建存储过程。 语法格式: 语法格式 CREATE PROCEDURE <存储过程名> <存储过程名 存储过程名> [参数表 参数表] 参数表 AS <SQL语句 语句> 语句