任务6.3 存储过程的创建与管理 (课件）-《网站数据库应用技术-SQL Server》同步教学（电子工业版）

项目六 数据库的编程操作 1 任务6.1 建立视图 任务6.2 游标 的创建与应用 任务6.3 存储过程的创建与管理 任务6.4 触发器的创建与管理 任务6.5 索引 与事务的应用 1 2 3 4 5 目录页 CONTENTS PAGE 2 任务6.3 存储过程的创建与管理 6.3.1 创建存储过程 6.3.2 调用存储过程 6.3.3 管理存储过程 过渡页 TRANSITION PAGE 3 存储过程的创建与管理 6.3.1 创建存储过程 存储过程（Stored Procedure）简称过程，就是为了完成一定的功能而编写的程序段，由一系列SQL语句构成，相当于C语言中的函数或Java语言中的方法，通过调用存储过程名来执行存储过程。存储过程存放在数据库对象中，属于数据库，与表和视图的级别相同。 任务6.3 4 存储过程的创建与管理 6.3.1 创建存储过程 1、用SSMS管理器窗口创建存储过程 例6-16创建存储过程P0_GetBookInfoTest，其功能是获取bookInfo表中指定作者和出版单位的图书的信息（包括图书编号、图书名称、作者姓名、出版单位和销售价格）。 第1步，在SSMS管理器窗口中依次展开“数据库”→“libsys”→“可编程性”节点，右击“存储过程”节点，在弹出的快捷菜单中选择“新建”→“存储过程”命令，打开包含创建存储过程的语句的窗口。 第2步，在“查询”菜单中选择“指定模板参数的值”命令。 任务6.3 5 存储过程的创建与管理 6.3.1 创建存储过程 第3步，在弹出的“指定模板参数的值”对话框中输入如上一页图 中所示的参数值。 第4步，单击“确定”按钮。 第5步，在“查询编辑器”窗口中，使用以下语句替换SELECT语句： SELECT BookID,BookName,Writer,Publisher,Price FROM bookInfo WHERE Writer=@Writer AND Publisher=@Publisher; 第6步，选择“查询”菜单中的“执行”命令，创建存储过程。该 存储过程作为数据库中的对象创建。 第7步，在弹出的“执行过程”窗口中，输入“胡振华”作为参数 @Writer的值，并输入“清华大学出版社”作为参数@Publisher的值 任务6.3 6 存储过程的创建与管理 6.3.1 创建存储过程 执行结果如下： 任务6.3 7 存储过程的创建与管理 6.3.1 创建存储过程 2、用Transact-SQL命令创建存储过程 （1）创建存储过程的命令格式是： CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter_name [ type_schema_name. ] data_type } [ VARYING ] [ NULL ] [ = default ] [ OUT | OUTPUT | [READONLY] ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] ] [ FOR REPLICATION ] AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] } [;] 任务6.3 8 存储过程的创建与管理 6.3.1 创建存储过程 <procedure_option> ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ] 格式说明： ①schema_name：存储过程所属架构的名称。 ②procedure_name：存储过程的名称。存储过程名称必须遵循有关标识符的规则，并且在架构中必须唯一。 ③;number：用于对同名的存储过程分组的可选整数。使用一个DROP PROCEDURE语句可将这些分组存储过程一起删除。 任务6.3 9 存储过程的创建与管理 6.3.1 创建存储过程 ④@parameter_name：存储过程中声明的参数。通过将@符号用作第一个字符来指定参数名称，参数名称必须符合标识符的有关规则。 ⑤[type_schema_name.] data_type：参数的数据类型及该数据类型所属的架构。 ⑥[ VARYING ] [ NULL ] [ = default ] [ OUT | OUTPUT | [READONLY]： VARYING：指定输出参数支持的结果集。该参数由存储过程动态构造，其内容可能发生改变，仅适用于游标参数。 default：参数的默认值。如果为参数定义了默认值，则无须指定该参数的值即可执行存储过程，默认值必须是常量或NULL，当默认值是常量时，可以使用通配符的形式，这使其可以在将该参数传递到存储过程时使用关键字LIKE。 OUT | OUTPUT：指明参数是输出参数。使用OUTPUT参数将值返回给存储过程的调用方，不能将表值数据类型指定为存储过程的OUTPUT参数。 任务6.3 10 存储过程的创建与管理 6.3.1 创建存储过程 READONLY：不能在存储过程的主体中更新或修改参数。如果参数类型为表值数据类型，则必须指定READONLY。 ⑦<procedure_option>表示存储过程选项，各个选项介绍如下。 RECOMPILE：数据库引擎不缓存该存储过程的查询计划，强制在每次执行该存储过程时都对该存储过程进行编译。 ENCRYPTION：指示SQL Server数据库将CREATE PROCEDURE语句的原始文本转换为模糊格式。 EXECUTE AS Clause：指定在其中执行存储过程的安全上下文。 ⑧FOR REPLICATION：指定为了复制操作而创建该存储过程。 ⑨{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }：构成存储过程主体的一个或多个Transact-SQL语句。可以使用可选的关键字BEGIN和END将这些语句括起来。 任务6.3 11 存储过程的创建与管理 6.3.1 创建存储过程 （2）创建无参数存储过程 例6-17 创建存储过程P1_AllBook，其功能是显示bookInfo表中由电子工业出版社出版的全部图书的记录。 第1步，在SSMS管理器窗口中连接到数据库引擎的实例“libsys”。 第2步，单击工具栏中的“新建查询”按钮，打开一个新的“查询编辑器”窗口。 第3步，在“查询编辑器”窗口中输入以下命令，然后单击“执行”按钮。 任务6.3 12 存储过程的创建与管理 6.3.1 创建存储过程 USE libsys --打开libsys数据库 GO --判断是否存在存储过程P1_All Book，如果存在，则删除该存储过程 DROP PROCEDURE IF EXISTS P1_AllBook GO CREATE PROCEDURE P1_AllBook --创建存储过程 AS BEGIN SELECT * FROM bookInfo WHERE Publisher='电子工业出版社' END 任务6.3 13 存储过程的创建与管理 6.3.1 创建存储过程 例6-18创建存储过程P2_BookBorrow，其功能是显示由清华大学出版社出版的图书的编号、名称、销售价格、出版日期、外借情况（包括图书编号、图书名称、销售价格、出版日期、借阅人、借阅人所在部门、借阅人联系电话、借书日期、应归还日期）。要求将CREATE PROCEDURE语句的原始文本转换为模糊格式（可以通过WITH ENCRYPTION实现） 第1步，在SSMS管理器窗口中连接到数据库引擎的实例“libsys”。 第2步，单击工具栏中的“新建查询”按钮，打开一个新的“查询编辑器”窗口。 第3步，在“查询编辑器”窗口中输入以下命令，然后单击“执行”按钮。 USE libsys GO CREATE PROC P2_BookBorrow WITH ENCRYPTION 任务6.3 14 存储过程的创建与管理 6.3.1 创建存储过程 AS BEGIN SELECT bookInfo.BookID,BookName,Price,PublishDate,ReaderName,Department,Mobile, BorrowDate, ReturnDate FROM bookInfo,readerInfo,borrowInfo WHERE Publisher='清华大学出版社' AND bookInfo.BookID=borrowInfo.BookID AND borrowInfo.ReaderID=readerInfo.ReaderID END 任务6.3 15 存储过程的创建与管理 6.3.1 创建存储过程 （3）创建带输入参数的存储过程 存储过程是一个子程序，可以在创建存储过程的代码中设计若干个参数，这种参数称为形式参数，而在调用存储过程时，传递给存储过程的参数称为实际参数。 在定义参数时，必须明确该参数是输入参数还是输出参数。如果参数的后面添加了关键字OUTPUT，则该参数是输出参数；如果参数的后面没有添加关键字OUTPUT，则该参数是输入参数，带输入参数的存储过程使用更广泛。 例6-19 创建存储过程P3_BookWriter，其功能是显示bookInfo表中由指定作者编写的全部图书的记录。 分析：指定作者并不知道是谁，必须在执行存储过程时才能知道，所以需要将作者（对应的列名为Writer）设置为输入参数。如果将参数名设为editor，则其数据类型及长度必须与bookInfo表中Writer列内数据的数据类型及长度匹配。 任务6.3 16 存储过程的创建与管理 6.3.1 创建存储过程 USE libsys GO DROP PROCEDURE IF EXISTS P3_BookWriter GO CREATE PROC P3_BookWriter @editor varchar(8) AS BEGIN SELECT * FROM bookInfo WHERE Writer=@editor END 任务6.3 17 存储过程的创建与管理 6.3.1 创建存储过程 如果要查询由作者“胡振华”编写的图书的记录，则执行该存储过程的命令如下： EXEC P3_BookWriter '胡振华’ 查询结果会显示由作者“胡振华”编写的所有图书的记录。 任务6.3 18 存储过程的创建与管理 6.3.1 创建存储过程 例6-20 创建存储过程P4_WriterPublisher，其功能是显示bookInfo表中由指定作者编写并由指定出版单位出版的图书的记录。 分析：指定作者和指定出版单位都不明确，但在调用存储过程时会指定，所以需要设置两个输入参数，一个参数表示作者（对应的列名Writer），另一个参数表示出版单位（对应的列名是Publisher）。这两个参数的数据类型及长度必须与bookInfo表中Writer列和Publisher列内数据的数据类型及长度匹配。 任务6.3 19 存储过程的创建与管理 6.3.1 创建存储过程 CREATE PROC P4_WriterPublisher @editor varchar(8),@press varchar(30) AS BEGIN SELECT * FROM bookInfo WHERE Writer=@editor AND Publisher=@press END 任务6.3 20 存储过程的创建与管理 6.3.1 创建存储过程 假如要查询由作者“刘小华”编写并由“电子工业出版社”出版的图书的记录，则执行该存储过程的命令如下： EXEC P4_WriterPublisher '刘小华','电子工业出版社' 在使用存储过程时，要注意以下两点： ①在定义参数时，形式参数名（如@editor、@press）最好不要与对应的列名完全相同，以免引起混淆，但相同并不会出错。 ②在执行存储过程时，实际参数要与形式参数的个数相等、类型一致、顺序相同。 任务6.3 21 存储过程的创建与管理 6.3.1 创建存储过程 （4）创建带输入参数和输出参数的存储过程 存储过程可以带输出参数，用于将运行结果返回给该存储过程的调用方。在定义输出参数时，需要在参数的后面添加关键字OUTPUT。 在编写存储过程时，经常需要定义变量，以保存中间结果。在SQL Server 2022中，定义变量的命令是DECLARE，格式如下： DECLARE 变量名 类型(长度) 在调用带输出参数的存储过程时，也需要设计一段代码才能将结果保存到指定的变量中，供其他程序使用。 例6-21 创建一个存储过程P5_ReaderType，其功能是根据输入的读者姓名判断该读者的类型。如果其是教师，则返回“教师读者”；如果其是学生，则返回“学生读者”；否则返回“其他读者”。 任务6.3 22 存储过程的创建与管理 6.3.1 创建存储过程 USE libsys GO CREATE PROCEDURE P5_ReaderType @name char(10),@information char(20) output AS BEGIN DECLARE @type char(10) SET @type=(SELECT ReaderType FROM readerInfo WHERE ReaderName=@name) 任务6.3 23 存储过程的创建与管理 6.3.1 创建存储过程 IF exists(SELECT ReaderType FROM readerInfo WHERE ReaderName=@name) IF @type='教师' SET @information='教师读者’ ELSE IF @type='学生' SET @information='学生读者’ ELSE SET @information='其他读者' SELECT @name AS '读者姓名', @information AS '读者类型' END 任务6.3 24 存储过程的创建与管理 6.3.1 创建存储过程 如果要查询读者“张红军”的类型，则调用存储过程P5_ReaderType，并为存储过程指定两个参数值，第一个参数值为“张红军”，第二个参数值任意（符合命名规范即可）。命令如下： EXEC P5_ReaderType '张红军',xxx 查询结果如图所示。 任务6.3 25 存储过程的创建与管理 6.3.1 创建存储过程 如果要将输出结果保存在变量中，便于后续访问，则需要编写一段代码。示例如下： DECLARE @result CHAR(20) EXEC P5_ReaderType '张红军', @result OUTPUT SELECT @result as '读者类型' 上述代码表示将存储过程的查询结果保存在变量@result中。 任务6.3 26 存储过程的创建与管理 6.3.2 调用存储过程 1、执行系统存储过程 系统存储过程以前缀sp_开头。因为从逻辑意义上讲，这些系统存储过程出现在所有用户定义的数据库和系统定义的数据库中，所以可以在任意数据库中执行这些系统存储过程，而不必完全限定系统存储过程的名称。但是，建议使用sys架构名称对所有系统存储过程的名称进行架构限定，以防止名称冲突，如： EXEC sys.sp_who; 任务6.3 27 存储过程的创建与管理 6.3.2 调用存储过程 2、执行用户定义存储过程 当执行用户定义的存储过程时，建议使用架构名称来限定存储过程的名称。这种做法可以使性能得到小幅度提升，因为数据库引擎不必搜索多个架构。如果某个数据库在多个架构中存在同名的存储过程，则还可以防止执行错误的存储过程。例如，执行例6-21中的存储过程P5_ReaderType，命令如下： USE libsys GO EXEC dbo.P5_ReaderType '张红军', xxx GO dbo为系统预先定义的架构，新创建的存储过程的默认架构为dbo。 任务6.3 28 存储过程的创建与管理 6.3.2 调用存储过程 3、使用SSMS管理器窗口执行存储过程 （1）在SSMS管理器窗口中依次展开“服务器”→“数据库”→“libsys”→“可编程性”→“存储过程”节点。 （2）右击需要执行的存储过程的名称，在弹出的快捷菜单中选择“执行存储过程”命令。 （3）在弹出的“执行过程”窗口中输入参数的值（见6.3.1节中的“1．使用SSMS管理器窗口创建存储过程”部分）。 （4）单击“确定”按钮，执行存储过程。 任务6.3 29 存储过程的创建与管理 6.3.2 调用存储过程 4、使用Transact-SQL命令执行存储过程 [ { EXEC | EXECUTE } ] procedure_name [ { value | @variable [ OUT | OUTPUT ] } ] [ ,...n ] [ WITH <execute_option> [ ,...n ] ] } [;] <execute_option>::= { RECOMPILE | { RESULT SETS UNDEFINED } | { RESULT SETS NONE } | { RESULT SETS ( <result_sets_definition> [,...n ] ) } } 任务6.3 30 存储过程的创建与管理 6.3.2 调用存储过程 （1）value：要传递给模块或传递给命令的参数值。 （2）@variable：用来存储参数或返回参数的变量。 （3）OUTPUT：指定模块或命令字符串返回一个参数。 （4）RECOMPILE：在执行模块后，强制编译、使用和放弃新计划。 （5）RESULT SETS UNDEFINED：该选项不保证将返回任何结果（如果有），并且不提供任何定义。如果返回任何结果，则说明语句正常执行而没有发生错误，否则不会返回任何结果。 （6）RESULT SETS NONE：保证执行语句不返回任何结果。如果返回任何结果，则会中止批处理。 （7）RESULT SETS ( <result_sets_definition> [,...n ] )：保证返回result_sets_definition中指定的结果。对于返回多个结果集的语句，需要提供多个result_sets_definition部分，将每个result_sets_definition用括号括上，并以逗号隔开。 任务6.3 31 存储过程的创建与管理 6.3.2 调用存储过程 例6-22 执行存储过程P0_GetBookInfoTest，其功能是获取作者为“张安平”、出版单位为“清华大学出版社”的图书的信息。 第1步，在SSMS管理器窗口中连接到数据库引擎的实例“libsys”。 第2步，单击工具栏中的“新建查询”按钮，打开一个新的“查询编辑器”窗口。 第3步，在“查询编辑器”窗口中输入以下命令，然后单击“执行”按钮。 EXEC P0_GetBookInfoTest '张安平','清华大学出版社' 任务6.3 32 存储过程的创建与管理 6.3.3 管理存储过程 1、修改存储过程 （1）使用SSMS管理器窗口修改存储过程 例6-23 修改存储过程P0_GetBookInfoTest，其功能是获取bookInfo表中由指定作者编写和采购数量大于指定数量的图书的信息（包括图书编号、图书名称、作者姓名、出版单位、出版时间、销售价格和采购数量）。 第1步，在SSMS管理器窗口中依次展开“服务器”→“数据库”→“libsys”→“可编程性”→“存储过程”节点。 第2步，右击存储过程名P0_GetBookInfoTest，在弹出的快捷菜单中选择“修改”命令。 第3步，在弹出的窗口中，修改存储过程的文本如下： 任务6.3 33 存储过程的创建与管理 6.3.3 管理存储过程 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE P0_GetBookInfoTest -- Add the parameters for the stored procedure here @Writer varchar(8) = null, @BuyCount int AS 任务6.3 34 存储过程的创建与管理 6.3.3 管理存储过程 BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT BookID,BookName,Writer,Publisher,Price,BuyCount FROM bookinfo WHERE Writer=@Writer AND BuyCount>@BuyCount; END GO 任务6.3 35 存储过程的创建与管理 6.3.3 管理存储过程 第4步，如果要测试语法，则在“查询”菜单中选择“分析”命令；如果要保存对存储过程定义的修改，则在“查询”菜单中选择“执行”命令；如果要将修改后的存储过程另存为T-SQL脚本，则在“文件”菜单中选择“另存为”命令，在弹出的“另存为”对话框中，系统会默认指定一个文件名，可以接受该文件名，也可以将其修改为其他名称，然后单击“保存”按钮。 在“查询”菜单中选择“执行”命令。 第5步，单击工具栏中的“新建查询”按钮，打开一个新的“查询编辑器”窗口，在该窗口中输入以下命令，然后单击“执行”按钮。 USE libsys GO EXEC P0_GetBookInfoTest '胡振华',20 GO 任务6.3 36 存储过程的创建与管理 6.3.3 管理存储过程 查询结果如下： 任务6.3 37 存储过程的创建与管理 6.3.3 管理存储过程 （2）使用SQL命令修改存储过程 ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter_name [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY] ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] ] [ FOR REPLICATION ] AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] } [;] <procedure_option> ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ] 任务6.3 38 存储过程的创建与管理 6.3.3 管理存储过程 例6-24 修改存储过程P1_AllBook，其功能是显示bookInfo表中由电子工业出版社出版的计算机类的图书的信息。 第1步，在SSMS管理器窗口连接到数据库引擎的实例“libsys”。 第2步，单击工具栏中的“新建查询”按钮，打开一个新的“查询编辑器”窗口。 第3步，“查询编辑器”窗口中输入以下命令，然后单击“执行”按钮。 USE libsys --打开libsys数据库 GO ALTER PROCEDURE P1_AllBook --修改存储过程 AS BEGIN SELECT * FROM bookInfo WHERE Publisher='电子工业出版社' AND BookType='计算机' END 任务6.3 39 存储过程的创建与管理 6.3.3 管理存储过程 2、删除存储过程 删除存储过程的方法有两种：一种是使用SSMS管理器窗口，另一种是使用T-SQL命令。 （1）使用SSMS管理器窗口删除存储过程 例6-25 删除存储过程P0_GetBookInfoTest。 第1步，在SSMS管理器窗口中依次展开“数据库”→“libsys”→“可编程性”→“存储过程”节点。 第2步，右击存储过程名P0_GetBookInfoTest，在弹出的快捷菜单中选择“删除”命令。。 第3步，在弹出的“删除对象”对话框中单击“确定”按钮。 任务6.3 40 存储过程的创建与管理 6.3.3 管理存储过程 （2）使用T-SQL命令删除存储过程。 删除存储过程的命令格式如下： DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ] 格式说明： ①IF EXISTS：有条件地删除存储过程（仅当其已存在时）。 ②schema_name：存储过程所属架构的名称。 ③procedure：要删除的存储过程的名称。 任务6.3 41 存储过程的创建与管理 6.3.3 管理存储过程 例6-26 删除存储过程P1_AllBook。 第1步，在SSMS管理器窗口中连接到数据库引擎的实例“libsys”。 第2步，单击工具栏中的“新建查询”按钮，打开一个新的“查询编辑器”窗口。 第3步，在“查询编辑器”窗口中输入以下命令，然后单击“执行”按钮。 /*判断是否存在存储过程P1_AllBook，如果存在，则删除该存储过程*/ DROP PROCEDURE IF EXISTS P1_AllBook GO 任务6.3 42 菊次郎的夏天 菊次郎的夏天 久石譲 $$