第11章存储管理导论.ppt

2）存储过程创建成功后，用户可以执行带有输入参数的存储过程来检查返回结果。在查询分析器中运行如下命令。 使用参数名传递参数值的方法执行存储过程： EXEC pro_studentno @StuNo=0463501102 使用按位置传递参数值的方法执行存储过程: EXEC pro_studentno 0463501103 运行结果如图所示，表示存储过程创建成功的同时返回相应的执行结果。 3．带输出参数的存储过程 【例11-3】使用Transact-SQL语句在jw数据库中创建一个存储过程pro_classnum，要求能够根据给定的班级编号，统计该班的人数，并将人数以输出变量返回给用户。 1）在查询分析器中运行如下命令： USE jw GO CREATE PROCEDURE pro_classnum @ClassNo char(8), @ClassNum smallint OUTPUT AS SET @ClassNum= ( SELECT COUNT(*) FROM Student WHERE ClassNo=@ClassNo ) PRINT @ClassNum 2）存储过程创建成功后，用户可以执行含有输入参数、输出参数的存储过程来检查返回结果。在查询分析器中运行如下命令： DECLARE @ClassNum smallint EXEC pro_classnum, @ClassNum OUTPUT 运行结果如下图所示，表示存储过程创建成功的同时返回相应的执行结果。 11.3 修改和删除存储过程 11.3.1 修改存储过程 若要修改现有存储过程并保留权限分配，应使用ALTER PROCEDURE语句。使用ALTER PROCEDURE修改存储过程时，SQL Server将替换该存储过程以前的定义。 使用ALTER PROCEDURE语句时应注意以下问题： 1）如果要修改使用选项（如WITH ENCRYPTION选项）创建的存储过程，则必须在ALTER PROCEDURE语句中包含该选项，以保留该选项所提供的功能。 2）ALTER PROCEDURE只更改单个过程。如果该存储过程还要调用其他存储过程，则嵌套的存储过程不受影响。 【例11-4】修改jw数据库中的存储过程pro_class，实现从Class数据表中获取学校全部班级的班级编号和名称。 USE jw GO ALTER PROCEDURE pro_class AS SELECT ClassNo,ClassName FROM Class 当然，也可以使用SQL Server Management Studio进行修改。在“对象资源管理器”窗格中展开“jw”数据库→“可编程性”节点，打开存储过程，右击需要修改的存储过程，从弹出的快捷菜单中选择“修改”命令，即可对该存储过程进行修改，如下图所示。 11.3.2 删除存储过程 若要从当前数据库中删除用户自定义的存储过程，应使用DROP PROCEDURE语句。语句格式如下： DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,…n ] 【例11-5】删除jw数据库中的存储过程pro_class。 USE jw GO DROP PROCEDURE pro_class 当然，也可以使用SQL Server Management Studio进行删除。在“对象资源管理器”窗格中右击该存储过程，从弹出的快捷菜单中选择“删除”命令，打开“删除对象”窗口，单击“确定”按钮，即可完成删除操作，如下图所示。 11.4 系统存储过程和扩展存储过程 除了前面涉及的自定义存储过程外，SQL Server还提供了系统存储过程和扩展存储过程。 系统存储过程保存在master和msdb数据库中，都是以“sp_”为前缀的；可以在任何数据库中使用。SQL Server中的许多管理工作都是通过执行系统存储过程来完成的。前面章节中已经介绍了一些系统存储过程。 扩展存储过程提供从SQL Server到外部程序的接口，以便进行各种维护活动。扩展存储过程都是以“xp_”为前缀的。 用户创建的存储过程最好不要以“sp_”“xp_”为前缀。 SQL Server 2012 数据库管理教程 第11章 第11章 存储过程的创建与管理 存储过程的概述 存储过程的创建及执行 修改和删除存储过程 系统存储过程和扩展存储过程 11.1 存储过程的概述 存储过程是一组Transact-SQL语句集合，它提供了一种封装任务的方法。SQL Server中的存储过程与其他编程语言中的过程类似，因此可以： 1）包含可在