第章存储过程演示课件.pptxVIP

第10章　存储过程10.1 概 述10.1.1 什么是存储过程 存储过程是在数据库服务器端执行的一组T-SQL语句的集合，经编译后存放在数据库服务器端。存储过程作为一个单元进行处理并以一个名称来标识。 它能够向用户返回数据、向数据库表中写入或修改数据，还可以执行系统函数和管理操作，用户在编程中只需要给出存储过程的名称和必需的参数，就可以方便地调用它们。10.1.2 存储过程的类型系统存储过程：由SQL Server提供，通常使用“sp_”为前缀，主要用于管理SQL Server和显示有关数据库及用户的信息。用户自定义存储过程：用户编定的可以重复用的T-SQL语句功能模块，并且在数据库中有唯一的名称，可以附带参数，完全由用户自己定义、创建和维护。临时存储过程：临时过程是用户自定义过程的一种形式。?临时存储过程与永久过程相似，只是临时存储过程存储于?tempdb?中。?临时存储过程有两种类型：本地过程和全局过程。?扩展存储过程：允许用户使用编程语言（例如C）创建自己的外部例程。10.2 创建存储过程10.2.1. 使用SQL Server管理控制器创建存储过程　【例10.1】 使用SQL Server管理控制器创建存储过程maxscore，用于输出所有学生的最高分。　操作步骤启动SQL Server管理控制器。在“对象资源管理器”中展开“LCB-PC”服务器节点。展开“数据库|school|可编程性|存储过程”节点，单击鼠标右键，在出现的快捷菜单中选择“新建存储过程”命令，如图10.1所示。出现存储过程编辑窗口，其中含有一个存储过程模板，用户可以参照模板在其中输入存储过程的T-SQL语句，这里输入的语句如下（其中红色部分为主要输入的T-SQL语句）：set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOCREATE PROCEDURE maxscoreASBEGIN SET NOCOUNT ON SELECT MAX(分数) AS 最高分 FROM score --从score表中查询最高分ENDGO单击工具栏中的“！”按钮，将其保存在数据库中。此时选中“存储过程”节点，单击鼠标右键，在出现的快捷菜单中选择“刷新”命令，会看到“存储过程”的下方出现了maxscore存储过程。10.2.2 使用CREATE PROCEDURE语句创建存储过程使用CREATE PROCEDURE语句的基本语法格式如下：CREATE PROC[EDURE ] 存储过程名 [; number] [ {@parameter 数据类型} = 默认值] [OUT | OUTPUT] [READONLY] ][,…n] [WITH [RECOMPILE] | ENCRYPTION ] [FOR REPLICATION] AS SQL语句 […n ]创建存储过程时应该注意下面几点：存储过程的最大大小为128MB。只能在当前数据库中创建用户定义的存储过程。在单个批处理中，CREATE PROCEDURE语句不能与其他T-SQL语句组合使用。存储过程可以嵌套使用，在一个存储过程中可以调用其他的存储过程。嵌套的最大深度不能超过32层。如果存储过程创建了临时表，则该临时表只能用于该存储过程，而且当存储过程执行完毕后，临时表自动被删除。创建存储过程时，在“SQL语句”中不能包含SET SHOWPLAN_TEXT、SET SHOWMAN_ALL、CREATE VIEW、CREATE DEFAULT、CREATE RULE、CREATE PROCEDURE和CREATE TRIGGER（用于创建触发器，在下一章介绍）语句。SQL Server允许创建的存储过程中引用尚不存在的对象。10.4 存储过程的参数10.4.1 在存储过程中使用参数 在设计存储过程时可以带有参数，这样增加存储过程的灵活性。带参数的存储过程的一般格式如下：CREATE PROCEDURE 存储过程名( 参数列表 ) AS SQL语句 在调用存储过程时，有两种传递参数的方式。 （1）在传递参数时，使传递的参数和定义时的参数顺序一致。其一般格式如下：EXEC 存储过程名 实参列表 （2）采用“参数=值”的形式，此时，各个参数的顺序可以任意排列。其一般格式如下：EXEC 存储过程名 参数1=值1,参数2=值2,… 【例10.5】设计一个存储过程maxno，以学号为参数，输出指定学号学生的所有课程中最高分和对应的课程名。USE schoolGOIF OBJECT_ID(maxno,P) IS NOT NULL DROP PROCEDURE maxno --如果存储过程maxno存在,删除之GOCREATE PROCEDURE maxno(@no int) --声明no