9.3存储过程（课件）-《数据库应用技术-SQL Server》同步教学（人民邮电版）

SQL Server 2019高级应用 第9章 目录 01 T-SQL编程基础 02 游标 03 存储过程 04 触发器 本章主要内容 本章主要阐述T-SQL的标识符、运算符、表达式、函数、变量与常量、流程控制语句等基础知识，并结合实例探讨T-SQL最常见的应用，分析游标的基本原理和使用方法，重点介绍使用T-SQL创建、管理存储过程和触发器。 存储过程 第9章 03 9.3.1 存储过程的概念及特点 1. 存储过程的概念 存储过程（Stored Procedure），是一组为了完成特定功能的SQL 语句集，由流程控制语句和SQL语句编写，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行，并将返回值反馈给调用存储过程的用户。 9.3.1 存储过程的概念及特点 2. 存储过程的优点 当需要在不同的应用程序或平台上执行相同的函数或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看作是对编程中面向对象方法的模拟，它允许控制数据的访问方式。存储过程具有以下优点。 （1）模块化的程序设计，独立修改，增强了代码的重用性和共享性。 （2）执行效率高，一次编译。 （3）网络流量大幅减少，减轻网络负载。 （4）提高数据库安全性。 9.3.1 存储过程的概念及特点 2. 存储过程的分类 SQL Server 2019主要支持以下3种不同类型的存储过程。 （1）系统存储过程 用来执行许多管理和信息活动。用户可在应用程序中直接调用系统存储过程，以sp_为前缀。 （2）用户自定义存储过程 用户自定义存储过程是指用户根据应用程序的需要，有效地利用存储过程的优点，将业务流程中相对稳定、有独立功能、高频操作部分自定义成存储过程。 （3）扩展存储过程 扩展存储过程是用户可以使用外部程序语言编写的存储过程，用来扩展SQL Server服务器功能，以xp_为前缀。 9.3.2 创建存储过程 SQL Server 2019提供给用户两种方法创建存储过程：其一是使用CREATE PROCEDURE语句创建；其二是使用对象资源管理器创建。数据库所有者享有存储过程的管理权，可通过授权机制把许可权授权给其他用户。 无论采取哪种方式创建存储过程，均需确定存储过程的3个组成部分：所有的输入参数及传给调用者的输出参数；被执行的针对数据库的操作语句，包括调用其他存储过程的语句；返回给调用者的状态值，以指明调用是成功还是失败。 9.3.2 创建存储过程 1．使用CREATE PROCEDURE语句创建存储过程 其语法格式如下所示： CREATE PROCEDURE procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ]] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ] 9.3.2 创建存储过程 用CREATE PROCEDURE命令创建存储过程，既可以创建不带参数的存储过程，也可以创建带参数的存储过程。 【例9.14】在TeachSystem数据库中创建一个名称为TeacherProc的不带参数的存储过程，该存储过程的功能是从数据表teacher中查询所有教授的教师信息。 USE TeachSystem GO CREATE PROCEDURE TeacherProc AS SELECT * FROM teacher WHERE Prof ='教授' 9.3.2 创建存储过程 【例9.15】对于学生选课管理而言，新增一条选课记录是高频操作。创建一个存储过程，完成向SC表中插入一条新选课记录。 USE TeachSystem GO CREATE PROCEDURE InsertRecord ( @sno nchar(6), @cno nchar(6), @score numeric(4) ) AS INSERT INTO SC VALUES(@sno,@cno,@score) GO 9.3.2 创建存储过程 【例9.16】创建和执行带输出参数的存储过程，统计选修某一门课程所有学生的平均成绩，该门课程的课程编号由用户输入。 USE TeachSystem GO CREATE PROCEDURE AvgC ( @cno nchar(6) OUTPUT, @avg numeric(4) OUTPUT ) A