《SQL Server 2008数据库管理与开发教程 第2版 教学课件 ppt 作者 王雨竹 张玉花 张星_ 第9章 SQL Server 2008高级开发》由会员分享，可在线阅读，更多相关《SQL Server 2008数据库管理与开发教程 第2版 教学课件 ppt 作者 王雨竹 张玉花 张星_ 第9章 SQL Server 2008高级开发（65页珍藏版）》请在金锄头文库上搜索。
1、SQL Server 2008 数据库管理与开发教程,第9章 SQL Server 2008高级开发,本章主要介绍SQL Server 2008的高级应用，包括用户自定义函数、实现交叉表查询、事务处理以及锁。通过本章的学习，读者可以创建和管理用户自定义函数，可以使用PIVOT、UNPIVOT以及CASE实现交叉表查询，并了解事务处理机制和锁，应用事务和锁优化对数据的访问。,9.1 用户自定义函数,SQL Server 2008还可以根据用户需要来自定义函数，以便用在允许使用系统函数的任何地方。,用户自定义函数有两种方法，一种是利用Microsoft SQL Server Manager管理器直
2、接创建，另一种是利用代码创建。,9.1.1 创建用户自定义函数,图9.1 创建自定义函数,9.1.2 使用Transact-SQL语言创建用 户自定义函数,（1）创建自定义函数 利用Transact-SQL创建函数的语法如下： create function 函数名（parameter 变量类型 ,parameter 变量类型 ） returns参数as begin 命令行或程序块 End,函数可以有0个或若干个输入参数，但必须有返回值，returns后面就是设置函数的返回值类型。,用户自定义函数为标量值函数或表值函数。 如果returns子句指定了一种标量数据类型，则函数为标量值函数；如果r
3、eturns子句指定TABLE，则函数为表值函数。 根据函数主体的定义方式，表值函数可分为内联函数和多语句函数。,（2）调用自定义函数,Transact-SQL调用函数的语法格式如下： Print dbo.函数（实参） 或 select dbo.函数（实参） dbo是系统自带的一个公共用户名。,【例9-1】 创建与执行以表为返回值的函数。,图9.2 用find函数查询的结果,9.1.3 修改、删除用户自定义函数,1修改自定义函数 利用Transact-SQL修改函数的语法如下： alter function 函数名（parameter 变量类型 ,parameter 变量类型 ） return
4、s参数as begin 命令行或程序块 End,2删除自定义函数,删除自定义函数的Transact-SQL语法如下： Drop function 函数名,9.2 使用SQL Server 2008实现交叉表查询,9.2.1 使用PIVOT和UNPIVOT实现交叉表查询 PIVOT和UNPIVOT运算符是SQL Server 2008新增的功能。 通过PIVOT和UNPIVOT就完全可以实现交叉表的查询，用PIVOT和UNPIVOT编写更简单，更易于理解。,在查询的FROM子句中使用PIVOT和UNPIVOT，可以对一个输入表值表达式执行某种操作，以获得另一种形式的表。 PIVOT运算符将输入表
5、的行旋转为列，并能同时对行执行聚合运算。 而UNPIVOT运算符则执行与PIVOT运算符相反的操作，它将输入表的列旋转为行。,PIVOT和UNPIVOT的语法如下： FROM table_source ,.n table_source := table_or_view_name AS table_alias pivoted_table | unpivoted_table pivoted_table :=table_source PIVOT pivot_clause table_alias pivot_clause :=( aggregate_function ( value_column )
6、FOR pivot_column IN ( column_list,unpivoted_table : = table_source UNPIVOT unpivot_clause table_alias unpivot_clause : = ( value_column FOR pivot_column IN ( column_list column_list : = column_name , . table_source PIVOT pivot_clause,图9.3 商品表,图9.4 商品表结构,图9.5 sp表,图9.6 sp表经过分组汇总后的结果,图9.7 使用 for 月份 in(
7、9 , 10 , 11 , 12 )后得到的结果集,图9.8 由sp表经行转列得到的最终结果集,图9.9 使用UNPIVOT得到的结果集,1用PIVOT举例,【例9-2】 使用PIVOT运算符实现交叉表查询。 SQL语句如下： use student select * from sp pivot(sum(销售数量) for 商品名称 in(李小葱专辑,周木人专辑,国产E601,920演唱会DVD ) as 统计,图9.10 sp表按商品名称交叉查询,2用UNPIVOT举例,UNPIVOT是PIVOT的逆操作。 假设图2.71所示的结果集存储在结果表temp1中，图2.72所示的结果集存储在结果
8、表temp2中。 【例9-3】 使用UNPIVOT运算符实现交叉表查询。,SQL语句如下： use student select * from temp1 unpivot(销售数量 for 商品名称 in(李小葱专辑,周木人专辑,国产E601,920演唱会DVD ) as a,图9.11 sp表按月份交叉查询,图9.12 unpivot对temp1表实现逆操作,图9.13 unpivot对temp2实现逆操作,9.2.2 CASE实现交叉表查询,利用CASE语句可以返回多个可能结果的表达式。 CASE具有简单CASE和CASE查询两种函数格式。 下面介绍简单CASE语句的语法。,简单CASE语
9、句：将某个表达式与一组简单表达式进行比较以确定结果。,其语法形式如下： CASE input_expression WHEN when_expression THEN result_expression .n ELSE else_result_expression END,【例9-4】 使用CASE语句实现交叉表查询。 SQL语句如下： use student,SELECT 月份,SUM(CASE 商品名称 WHEN 李小葱专辑 THEN 销售数量 ELSE NULL END)AS 李小葱专辑,SUM(CASE 商品名称 WHEN 周木人专辑 THEN 销售数量 ELSE NULL END)a
10、s 周木人专辑 ,SUM(CASE 商品名称 WHEN 国产E601 THEN 销售数量 ELSE NULL END)AS E601,SUM(CASE 商品名称 WHEN 920演唱会DVD THEN 销售数量 ELSE NULL END)AS 920演唱会DVD FROM sp group by 月份,图9.14 sp表按照商品名称交叉表查询,图9.15 sp表按照月份交叉表查询,9.3 事 务 处 理,9.3.1 事务简介 1事务概念 事务（Transaction）的作用是保证在对数据操作的过程中保证数据的完整性，不出现数据操作完成一半的未完成现象。 事务作为一个逻辑单元，它必须具备以下4
11、个属性。,（1）原子性（Atomicity） （2）一致性（Consistency） （3）隔离性（Isolation） （4）持久性（Durability）,2事务类型,根据系统的设置，可以把事务分成两种类型。 一种是系统提供的事务，另一种是用户定义的事务。 系统提供的事务是指在执行某些语句时，一条语句就是一个事务。,这时要知道，一条语句的对象既可能是表中的一行数据，也可能是表中的多行数据，甚至是表中的全部数据。 因此，只有一条语句构成的事务也可能包含了对多行数据的处理。,事务运行的3种模式如下。 （1）自动提交事务 （2）显式事务 （3）隐性事务,9.3.2 事务处理,1事务的起点 事务以
12、BEGIN TRANSACTION语句开始。 BEGIN TRANSACTION语句使全局变量TRANCOUNT按1递增。,语法如下： BEGIN TRAN | TRANSACTION transaction_name | tran_name_variable WITH MARK description ; ,2事务的终点,事务以COMMIT TRANSACTION作为隐性事务或显式事务成功结束的标志。 语法如下： COMMIT TRAN | TRANSACTION transaction_name | tran_name_variable ; ,3数据回滚,使用ROLLBACK TRANSA
13、CTION语句将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。,语法如下： ROLLBACK TRAN | TRANSACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable ; ,4事务保存点,使用SAVE TRANSACTION语句在事务内设置保存点。 语法如下： SAVE TRAN | TRANSACTION savepoint_name | savepoint_variable ; ,5事务的应用,（1）常用事务 （2）隐式事务,9.4 锁,9.4.1 锁简介 1锁的概
14、念 锁是保护事务和数据的方式，这种保护方式类似于日常生活中使用的锁。 锁是防止其他事务访问指定资源的手段，是实现并发控制的主要方法，是多个用户能够同时操纵同一个数据库中的数据而不发生数据不一致现象的重要保障。,在Microsoft SQL Server中可以锁定的资源有多种，这些可以锁定的资源分别是行、页、Extent、表和数据库，他们对应的锁分别是行级锁、页级锁、Extent级锁、表级锁和数据库级锁。 数据行存放在页上，页存放在Extent上，一个表有若干个Extent组成，而若干个表组成了数据库。,在这些可以锁定的资源中，最基本的资源是行、页和表，而Extent和数据库是特殊的可以锁定的资
15、源。,2锁的类型,（1）共享锁 （2）更新锁,【例9-5】 使用更新锁。 begin tran save tran aaa select * from table_1 with (UPDLOCK) rollback tran aaa commit tran,【例9-6】 使用排它锁。 begin tran save tran aaa select * from table_1 with (tablockx xlock) rollback tran aaa commit tran,（4）意向锁 （5）架构锁 （6）大容量更新锁 （7）键范围锁,9.4.2 死锁及其排除,当多个用户同时访问数据库的同一资源时，叫做并发访问。 如果并发访问中有用户对数据进行修改，很可能就会对其他访问同一资源的用户产生不利影响。 可能产生的并发不利影响有：脏读、不可重复读和幻读。,为了避免并发访问产生的不利影响，SQL Server设计有两种并发访问的控制机制：锁和行版本控制。,1脏读（dirty read） 2. 不可重复读（nonrepeatable read） 3幻读（phantom read）,小 结,本章介绍了关于SQL Server 2008的高级应用，如：用户自定义函数、交叉表查询、事务和锁。 读者通过创建用户自定义函数可以实现将代码封装在一个函数体内方便调用；可以使用PIVOT
