实验9-T-SQL、游标、存储过程、并发控制.doc

实验9-T-SQL、游标、存储过程、并发控制XX实验报告学号 : 系别专业班级姓 名课程名称课程类型学时数实验名称T-SQL、游标、存储过程、并发控制实验目的：1、 了解并能简单应用T-SQL语言。2、 理解并简单的使用游标。实验内容：一、了解并应用T-SQL编程语言（1） 用下面的脚本创建一个表并利用循环向表中添加26条记录：USE AdventureWorksCREATE TABLE MYTB(ID INT,VAL CHAR(1)GODECLARE COUNTER INT;SET COUNTER=0WHILE(COUNTER 26)BEGIN INSERT INTO MYTB VALUES(COUNTER,CHAR(COUNTER + ASCII(A) SET COUNTER= COUNTER + 1END在Microsoft SQL Server Management Studio中新建一个查询，输入并执行上面的脚本，然后在Microsoft SQL Server Management Studio的“对象资源管理器”中查看MYTB表以及其中的数据。（2）用下面的脚本查询Employee表中的雇员信息，包括EmployeeID和Gender,Gender的属性根据其值相应地显示为男或女。USE AdventureWorksSELECT EmployeeID,Gender= CASE Gender WHEN M THEN Male WHEN F THEN Female ENDFROM HumanResources.Employee在Microsoft SQL Server Management Studio中新建一个查询，输入并执行上面的脚本，观察执行结果。（3） 下面的脚本显示了T-SQL中的错误处理。BEGIN TRY SELECT 5/0END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS错误号，ERROR_MESSAGE() AS错误信息。END CATCH在Microsoft SQL Server Management Studio中新建一个查询，输入并执行上面的脚本，观察执行结果。2、 使用游标1、游标的功能游标通过以下方式来扩展对结果集的处理能力：（1）允许在结果集中定位特定行。（2）从结果集的当前位置检索一行或一部分行。（3）支持对结果集中当前位置的行进行数据修改。（4）为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持。2、T-SQL游标的类型基于数据库服务器的DECLAER CURSOR创建的T-SQL游标主要用于T-SQL脚本，存储过程和触发器。SQL Server支持4种API服务器游标类型：静态游标，动态游标，只进游标和由键集驱动的游标。T-SQL游标类型主要由DECLAER CURSOR命令定义时指定不同的选项决定，下面是该命令的语法：DECLAER cursor_name CURSORLOCAL|GLOBALFPRWARD_ONLY|SCROLLSTATIC|KEYSET|DYNAMIC|FAST_FPRWARDREAD_ONLY|SCROLL_LOCKS|OPTIMISTICTYPE_WARNINGFOR select_statementFOR UPDATE OF column_name,. ;这些游标检测结果集变化的能力和消耗资源（如在tempdb中所占的内存和空间）的情况各不相同。游标检测这些变化的能力也受事务隔离级别的影响。静态游标在滚动期间很少或根本检测不到变化，消耗的资源相对较少；动态游标在滚动期间能检测到所有变化，但消耗的资源也较多；由键集驱动的游标介于两者之间。3、 在脚本，存储过程或触发器中使用游标在脚本、存储过程或触发器中使用T-SQL游标的典型过程为：（1） 声明T-SQL变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从列数据类型隐式转换得到的数据类型。（2） 使用DECLAER CURSOR语句将T-SQL游标与SELECT语句相关联。另外，DECLAER CURSOR语句还定义了游标的特性。（3） 使用OPEN语句执行SELECT语句并填充游标。（4） 使用FETCH INTO语句提取单个行，并将每列中的数据移至制定的变量中。然后，其他T-SQL语句可以引用那些变量来访问提取的数据值。T-SQL游标不支持提取行块。（5） 使用CLOSE语句结束游标的使用。关闭游标可以释放某些资源，例如游标结果集及其对其当前行的锁定，但如果重新发出一个OPEN语句，则该游标结构仍可用于处理。由于游标仍然存在，此时还不能重新使用该游标的名称。DEALLOCATE语句则完全释放分配给游标的资源，包括游标名称。释放游标后，必须使用DECLARE语句来重新生成游标。3、 了解存储过程1、 存储过程的功能：（1）变量说明（2）ANSI（美国国家标准化组织）兼容的SQL命令(如Select,Update.)（3）一般流程控制命令(ifelse、while.)（4）内部函数2、 存储过程的分类：（1）系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。（2）本地存储过程：用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。（3）临时存储过程：分为两种存储过程： 一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它; 二是全局临时存储过程，以两个井字号(#)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。（4）远程存储过程：在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。（5）扩展存储过程：扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。3、存储格式：sql中的存储过程及相关介绍：CREATE PROCEDURE 拥有者.存储过程名;程序编号(参数#1,参数#1024)WITHRECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONFOR REPLICATIONAS 程序行其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数(SQL Server 7.0以上版本),参数的使用方法如下：参数名数据类型VARYING =内定值 OUTPUT每个参数名前要有一个“”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外，其他SQL Server所支持的数据类型都可使用。内定值相当于我们在建立数据库时设定一个字段的默认值，这里是为这个参数设定默认值。OUTPUT是用来指定该参数是既有输入又有输出值的，也就是在调用了这个存储过程时，如果所指定的参数值是我们需要输入的参数，同时也需要在结果中输出的，则该项必须为OUTPUT，而如果只是做输出参数用，可以用CURSOR，同时在使用该参数时，必须指定VARYING和OUTPUT这两个语句。4、 基本语法：（1）创建存储过程create procedure sp_name参数名 类型,参数名 类型asbegin.end以上格式还可以简写成：create proc sp_name参数名 类型,参数名 类型asbegin.end/*注：“sp_name”为需要创建的存储过程的名字，该名字不可以以阿拉伯数字开头*/（2）调用存储过程1.基本语法：exec sp_name 参数名（3）删除存储过程基本语法：drop procedure sp_name注意事项：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程（4）其他常用命令1.show procedure status显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等2.show create procedure sp_name显示某一个mysql存储过程的详细信息3、exec sp_helptext sp_name显示你这个sp_name这个对象创建文本5、 优缺点：优点重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。提高性能。存储过程在创建的时候在进行了编译，将来使用的时候不再重新翻译。一般的SQL语句每执行一次就需要编译一次，所以使用存储过程提高了效率。减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。简单讲：1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。3.存储过程可以重复使用,可减少数据库开发人员的工作量4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权有一点需要注意的是，一些网上盛传的所谓的存储过程要比sql语句执行更快的说法，实际上是个误解，并没有根据，包括微软内部的人也不认可这一点，所以不能作为正式的优点，希望大家能够认识到这一点。缺点1：调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。2：移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。3：重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。4： 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。4、 数据库管理系统中的并发控制 数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。下面举例说明并发操作带来的数据不一致性问题：现有两处火车票售票点，同时读取某一趟列车车票数据库中车票余额为X。两处售票点同时卖出一张车票，同时修改余额为X -1写回数据库，这样就造成了实际卖出两张火车票而数据库中的却记录只少了一张。产生这种情况的原因是因为两个事务读入同一数据并同时修改，其中一个事务提交的结果破坏了另一个事务提交的结果，导致其数据的修改被丢失，破坏了事务的隔离性。并发控制要解决的就是