第12章存储过程和用户存储过程设计PPT.ppt

存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。Transact SQL存储过程可以接受输入参数，以参数形式返回输出值，或者返回成功、失败的状态信息。;12.1 存储过程概述 12.2 系统存储过程 12.3 创建和执行用户存储过程 12.4 带状态参数的存储过程及实例分析 12.5 修改和删除存储过程 12.6 存储过程设计实例分析 ;12.1 存储过程概述;12.1 存储过程概述;12.1 存储过程概述;12.2 系统存储过程;12.2.2 一些常用的系统存储过程;系 统 过 程 ; 如果没有指定数据库名，则sp_helpdb报告master.dbo.sysdatabases中的所有数据库。 【例12-1】返回pubs数据库的信息 exec sp_helpdb pubs 【例12-2】返回有关所有数据库的信息 exec sp_helpdb ;12.3.1 创建用户存储过程 用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在SQL Server中，可以使用以下方法创建存储过程： ① 利用SQL Server管理器创建存储过程。 ② 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。 创建存储过程时，需要确定存储过程的三个组成部分： ① 所有的输入参数以及传给调用者的输出参数。 ② 被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 ③ 返回给调用者的状态值，以指明调用是成功还是失败。; 1．使用SQL Server管理器创建存储过程 ① 在SQL Server管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程…”选项，从弹出的快捷菜单中选择“新建存储过程…”选项，出现创建存储过程对话框。;2．使用CREATE PROCEDURE命令创建存储过程 创建存储过程前，应该考虑下列几个事项： ① 在一个批处理中，Create Procedure 语句不能与其它SQL语句合并在一起； ② 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 ③ 存储过程是数据库对象，其名称必须遵守标识符规则。 ④ 只能在当前数据库中创建当前数据库的存储过程。 ⑤ 一个存储过程的最大尺寸为128M。 ;语法格式： CREATE PROC [ EDURE ] 存储过程名 [；版本号] [ ({ @参数名 数据类型 } [VARYING] [=default] [OUTPUT] [，…])] [WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION] [FOR REPLICATION] AS SQL语句 ; 【例12-3】创建一个简单的存储过程，返回所有学生的基本信息，包括学生的学号、姓名、所学专业、所在二级学院、来自的城市。 USE 教学管理 GO CREATE PROCEDURE P_学生部分信息 AS SELECT 学号,姓名,专业,所在院系,籍贯 FROM 学生表 ;? USE 教学管理 IF EXISTS(SELECT* FROM sysobjects WHERE name=SC_infor AND type=p) BEGIN DROP PROCEDURE SC_infor END GO CREATE PROCEDURE SC_infor(@smajor CHAR(20), @cname CHAR(20)) AS SELECTS.sno,sname,smajor,sdepa,O.cno,cname,grade FROM student S, enrollment E, offering O, course C where smajor=@smajor AND cname=@cname AND S.sno=E.sno AND E.ono=O.ono AND O.cno=C.cno GO ; 12.3.2 执行用户存储过程 执行已创建的存储过程使用EXECUTE 命令。 语法格式： [ [ EXEC [ UTE ] ] { [ @return_status = ]{ 存储过程名 [ ;版本号] | @procedure_name_var} [ [ @参数= ] { value |