任务5.3存储过程的创建与应用（课件）-高二《 SQL Server 2017数据库应用技术项目化教程》 同步教学（工信版）

数据库的高级管理   第五章 1 目 录 2 5.1 视图的创建与应用 5.2 T-SQL编程与应用 5.3 存储过程的创建与应用 5.4 事务管理 5.5 触发器的创建和应用 5.5 存储过程的创建与管理 理解存储过程的概念与作用。 掌握存储过程的创建和调用的命令格式。 掌握存储过程参数的传递方式。 知识目标 会创建各种类型的存储过程。 会执行各种类型的存储过程。 会管理存储过程。 能力目标 5 1. 教师任课课程成绩查询 学期期末考试结束后，马丽丽老师在网上录入学生的总评成绩后，还希望经常查看自己任课课程的学生成绩。 、 任务分析 这项工作通过多表查询即可完成，但此项操作需经常执行，可将该任务编写成存储过程，以方便用户随时调用，从而提高系统效率。 、 任务描述 6 2. 学生成绩等级自动折算 教务处要求考试科目成绩以百分制登记，考查科目的成绩则以等级登记。请设计一个成绩等级自动划分程序，实现将分数转换为等级，转换规则如下：高于90分（包括90分）为“优秀”，80～89分为“良好”，70～79分为“中等”，60～69分为“及格”，低于60分为“不及格”。 、 任务分析 该任务也可以利用存储过程实现。设计一个输入参数用于传递考试的课程，由于5个分数段的等级各不相同，因此可以采用CASE语句分情况转换。 、 任务描述 7 3.教师任课课程成绩统计 每学期期末考试结束后，任课老师除了需将学生的总评成绩录入数据库，还需对所任班级的课程成绩进行统计，统计最高分、最低分、平均分和考试通过率并打印统计结果。 、 任务分析 该任务同样是一项经常性的操作，因此也可利用存储过程实现。为了在运行该存储过程时能将计算结果输出，需要通过OUTPUT关键字定义输出参数，同时定义一个输入参数，接收要统计成绩的班级编号。 、 任务描述 8 5.3.1存储过程的概念 存储过程（Stored Procedure）是在数据库管理系统中预先编译并保存的能实现某种功能的SQL程序。 存储过程作为数据库对象预先保存在数据库中，经过一次创建后，可以被多次调用。 它可以包含程序流、逻辑及对数据库的相关操作，也可以接收参数、输出参数、返回记录集及返回需要的值。 知识导读 5.3.2存储过程的分类 1．系统存储过程 系统存储过程是由SQL Server系统提供的存储过程，可以作为命令执行各种操作，系统管理员通过它可以方便地查看数据库和数据库对象的相关信息，以帮助管理SQL Server数据库管理系统。 2．用户自定义存储过程 用户自定义存储过程是由用户创建的能完成某一特定功能的存储过程，包括T-SQL和CLR两种类型。T-SQL存储过程是指保存的T-SQL语句集合，可接收用户提供的参数和返回用户需要的参数，也可以从数据库向客户端返回数据。 知识导读 5.3.3存储过程的优点 存储过程一般用于处理需要与数据库进行频繁交互的业务，使用存储过程具有以下几个优点。 1．执行速度快，效率高 存储过程只在创建时在服务器端进行编译，以后每次执行都不需要重新编译，并且在执行一次后就驻留在高速缓冲存储器中，如需再次调用，只需从高速缓冲存储器中调用即可，从而提高了系统性能。 2．模块化程序设计 存储过程封装业务逻辑，使数据库管理员与应用系统开发人员的分工更明确，支持模块化设计。 3．具有良好的安全性 存储过程可以作为安全机制来运用。存储过程保存在数据库中，用户只需提交存储过程名就可以直接执行，避免了攻击者非法截取T-SQL代码以获取用户数据的风险。另外还可以通过授予用户对存储过程的操作权限来实现安全机制。 知识导读 5.3.4常见的系统存储过程 系统存储过程主要存储于master数据库中并以“SP_”为前缀。尽管系统存储过程被存储于master数据库中，但是仍然可以在其他数据库中对其进行调用，在调用时也不必在存储过程前加上数据库名。 系统存储过程 说 明 SP_DATABASES 列出服务器上的所有数据库 SP_HELPDB 报告有关指定数据库或所有数据库的信息 SP_RENAMEDB 更改数据库的名称 SP_TABLES 返回当前环境下可查询的对象列表 SP_COLUMNS 返回某个表或视图的列信息 SP_HELP 查看某个表或视图的所有信息 SP_HELPCONSTRAINT 查看某个表或视图的约束信息 SP_HELPINDEX 查看某个表或