[所有分类]第5章 函数和存储过程理论.ppt

课程回顾 事务具有ACID4个特性：原子性、一致性、隔离性、持久性 事务可以分为3类：显示事务、隐式事务、自动提交事务 T-SQL管理事务使用3条语句： BEGIN TRANSACTION COMMIT TRANSACTION ROLLBACK TRANSACTION 建立索引有助于快速检索数据。索引分为：唯一索引、主键索引、聚集索引、非聚集索引 聚集索引决定表中数据的物理存放顺序，所以一个表只能有一个聚集索引；非聚集索引决定表的逻辑顺序，一个表可以有多个非聚集索引 视图是查看表中数据的方法，它是一种虚拟表，作为执行查询结果而创建的；视图充当筛选器，可以对表数据按要求进行行和列的筛选 同义词建立在表、视图、过程、函数基础上，可以简化对对象的引用 本章内容 表值函数和标量值函数 系统存储过程 用户定义存储过程及执行 本章目标 掌握标量值函数和表值函数的编写及使用 了解存储过程的优点 掌握常用的系统存储过程 掌握如何创建和调用存储过程 1.函数 1.2 标量值函数 标量值函数 创建标量值函数 标量值函数 调用标量值函数 标量值函数 在标量值函数中调用标量值函数 1.3 表值函数 表值函数是指函数返回一个表(TABLE)，该函数功能强大，甚至可以替代视图，且可以作为带参数的视图。它可以分为内联表值函数和多语句表值函数 多语句表值函数 要求返回类型是TABLE类型 需要在函数定义的时候RETURNS关键字后面确定返回的表的结构 RETURN关键字后面不需要返回指定的值或表达式 内联表值函数 是多语句表值函数的一种特殊形式 内联表值函数只能有一条SELECT语句， 不需要定义返回TABLE类型的变量的结构 在RETURN关键字后面直接返回SELECT语句的结果 表值函数 多语句表值函数 表值函数 无参的多语句表值函数，其功能是返回所有员工的当前工资标准 表值函数 调用多语句表值函数getEmployeesSalary 表值函数 带参数的多语句表值函数，函数的功能通过传入部门编号，返回这个部门所有员工的当前工资标准 表值函数 调用带参数多语句表值函数getempsalarybydept 表值函数 内联表值函数 表值函数 内联表值函数 小结1 2.存储过程 存储过程（Procedure）是SQL语句和流程控制语句的预编译集合 它可以包含逻辑控制语句和数据操作语句，也可以接收参数、输出参数、返回结果集 存储过程在声明时不能定义返回类型（与C#方法不同），返回值通过传出参数实现 存储过程分为三类： 系统存储过程 用户自定义的存储过程 扩展存储过程 2.1 存储过程的优点 2.2 常用的系统存储过程 所有系统存储过程都已“sp_”开头 系统存储过程中的扩展存储过程以“xp_”开头 存储过程放在master数据库中，系统管理员拥有这些存储过程的使用权限 可以在任何数据库中运行系统存储过程，执行的结果反映在当前的数据库中 常用的系统存储过程 常用的系统存储过程 使用系统存储过程创建数据库 2.3 用户自定义的存储过程 用户自定义的存储过程 创建无参数的存储过程 用户自定义的存储过程 执行存储过程 用户自定义的存储过程 带输入参数的存储过程 用户自定义的存储过程 带输出参数的存储过程 用户自定义的存储过程 执行带输出参数的存储过程 用户自定义的存储过程 错误处理 PRINT语句显示用户自定义的、临时的错误信息，且只对用户显示。使用RAISERROR可以返回用户定义的错误信息，并且能指定错误的严重级别，设置系统变量记录所发生的错误 用户自定义的存储过程 错误处理 用户自定义的存储过程 执行存储过程，捕获错误 小结2 本章总结 函数和存储过程都是数据对象，前者函数声明有返回值类型，后者没有返回值类型，且二者均为预编译的，比匿名的T-SQL代码块执行效率更高 函数分为系统函数和用户自定义函数两大类。用户定义的函数有3种：内联表值函数、多语句表值函数、标量值函数 标量值函数返回确定类型的标量值；内联表值函数返回值为表类型，函数体内只有一个SELECT语句；多语句表值函数在begin end块中定义的函数主体包含T-SQL语句，这些语句可生成行并将行插入至表中，最后返回表 存储过程是SQL语句和控制流语句的预编译集合 存储过程可以提高查询速度、帮助实现模块化编程、保证数据的安全性和一致性 存储过程参数分为输入参数和输出参数 declare @maxsal money=0,@emp nvarchar(20) exec UP_Salary_Insert @maxsal output,@emp output,E011,13000,2007-1-1 declare @err int set @err=@@ERR