数据库高级相关技术

数据库高级相关技术第1页，共73页，2023年，2月20日，星期六Sample库中几张表字段名类型及长度完整性约束客户编号Int主码公司名称Varchar(50)联系人Varchar(10)联系方式Varchar(50)表6.1客户数据表第2页，共73页，2023年，2月20日，星期六几张表表6.2员工数据表字段名类型及长度完整性约束员工编号Int主码姓名Varchar(8)所属部门Varchar(8)职位Varchar(10)工资Decimal(8,2)第3页，共73页，2023年，2月20日，星期六几张表表6.3项目数据表字段名类型及长度完整性约束项目编号Int主码项目名称Varchar(8)客户编号int负责人编号int开始日期datetime终止日期datetime经费(万元)Decimal(8,2)第4页，共73页，2023年，2月20日，星期六6.1SQL编程基础T-SQL是针对数据库的查询语言，它的编程结构比其他程序设计语言更为简单第5页，共73页，2023年，2月20日，星期六一、批处理批处理是一组T-SQL语句的集合，可以是一条语句，也可以由多条语句组成。批处理语句以GO为结束标志。

GO只是个标志语句，并不被执行。在T-SQL存储过程调用时，如果调用语句不是一段批语句的第一句，则调用前需要加EXEC；创建表和创建视图的语句只能存在于一个单独的批处理语句中第6页，共73页，2023年，2月20日，星期六二、变量变量是T-SQL用来在其语句间传递数据的。由系统或用户定义并赋值。

第7页，共73页，2023年，2月20日，星期六1、全局变量

全局变量往往用来记录数据库的活动状态。用“@@变量名”表示几个常用的全局变量

@@ERROR——返回上一条T-SQL语句执行后的状态值。如果为0表示语句执行正确，非0表示发生错误。@@FETCH_STATUS——返回被FETCH语句执行的最后游标状态，如果为0表示FETCH执行成功，非0表示发生错误。@@ROWCOUNT——返回上一条T-SQL语句影响到的记录个数。第8页，共73页，2023年，2月20日，星期六2、局部变量用“@变量名”表示。声明局部变量使用DECLARE语句，后面跟上变量名和变量数据类型。局部变量赋值可以使用SELECT语句或SET语句例6.1USESAMPLEGODECLARE@MycharVARCHAR(10),@Max_SalaryDecimal(7,2)SET@Mychar=‘最高工资’SELECT@Max_Salary=MAX(工资)FROM员工数据表第9页，共73页，2023年，2月20日，星期六3、函数常用的函数有系统函数、日期函数、字符串函数、数学函数以及用户自定义函数等第10页，共73页，2023年，2月20日，星期六4、程序流程控制BEGIN……END。用于将多条语句组合起来IF……ELSE。用于条件的测试WHILE。当循环逻辑表达式结果为真时，重复执行后面的语句或语句块CASE。可以使用户方便地实现多重选择RETURN。用于无条件终止查询、存储过程或批处理。第11页，共73页，2023年，2月20日，星期六例6.2检索员工数据表，如果存在工资大于5000元的员工，则输出这些员工的姓名；否则输出消息，说明没有员工工资在5000以上。USESAMPLEGODECLARE@MessageVARCHAR(100)IF(SELECTMAX(工资)FROM员工数据表)>5000BEGINSET@Message=‘下列人员的工资超过5000:’PRINT@MessageSELECT员工姓名，工资FROM员工数据表WHERE工资>5000ENDELSEBEGINSET@Message=‘没有工资超过5000的员工。’PRINT@MessageENDGO第12页，共73页，2023年，2月20日，星期六例6.3员工发放的奖金是工资数额的30%，判断是否有员工奖金少于300，如果有，则将所有员工的工资增加500，直到所有员都多于300或有员工的工资超过了10000。

USESAMPLEGOWHILE(SELECT*FROM员工数据表WHERE工资*0.3<300)BEGINUPDATE员工数据表SET工资=工资+500IF（SELECTMAX（工资）FROM员工数据表）>10000BREAKENDGO第13页，共73页，2023年，2月20日，星期六例6.4分别输出员工姓名和所在的部门，并在部门后面添加说明

。USESAMPLEGOSELECT姓名,“部门说明”=/*分别为个部门添加说明*/CASE所属部门WHEN‘办公室’THEN‘在办公室工作，该部门主要负责各部门的办公事务。’WHEN‘项目部’THEN‘在项目部工作，该部门主要负责工程项目事务。’WHEN‘生产部’THEN‘在生产部工作，该部门主要负责生产事务。’WHEN‘检验处’THEN‘在检验处工作，该部门主要负责产品检验事务。’ENDFROM员工数据表GO第14页，共73页，2023年，2月20日，星期六6.2事务一、什么是事务二、如何定义事务三、事务的特性第15页，共73页，2023年，2月20日，星期六一、什么是事务事务(Transaction)是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位事务和程序是两个概念在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序一个应用程序通常包含多个事务事务是恢复和并发控制的基本单位第16页，共73页，2023年，2月20日，星期六二、如何定义事务显式定义方式

BEGINTRANSACTIONBEGINTRANSACTIONSQL语句1SQL语句1SQL语句2SQL语句2。。。。。。。。。。

COMMITROLLBACK隐式方式当用户没有显式地定义事务时，DBMS按缺省规定自动划分事务。第17页，共73页，2023年，2月20日，星期六事务结束COMMIT事务正常结束提交事务的所有操作（读+更新）事务中所有对数据库的更新永久生效ROLLBACK事务异常终止事务运行的过程中发生了故障，不能继续执行回滚事务的所有更新操作事务滚回到开始时的状态第18页，共73页，2023年，2月20日，星期六保存点目前在很多DBMS中对事务处理除了上述三条语句外，还增加了一条保存点设置的语句：

SAVETRANSACTION——后跟存储点名代表设置保存点，也可以书写成SAVETRAN。

第19页，共73页，2023年，2月20日，星期六例6.5在员工数据表中插入两行数据，如果出错，则回滚到事务开始处。

BEGINTRANtran_exam1/*事务开始*/USESAMPLE

INSERTINTO员工数据表(员工编号,姓名,工资)VALUES(1000,’李晓丽’,2400)/*在员工数据表中插入一行数据*/

INSERTINTO员工数据表(姓名,工资)VALUES(’张正枫’,2000)/*在员工数据表中插入一行，但未插入员工编号*/IF@@error<>0ROLLBACK/*如果插入语句出现错误则回滚到事务开始*/COMMITTRANtran_exam1GO第20页，共73页，2023年，2月20日，星期六例6.6在员工数据表中插入两行数据，如果出错，则回滚到保存点。

BEGINTRANtran_exam2USESAMPLE

INSERTINTO员工数据表(员工编号,姓名,工资)VALUES(1000,’李晓丽’,2400)

SAVETRANinsertsav/*在此设置保存点，保存前面的结果*/

INSERTINTO员工数据表(姓名,工资)VALUES(’张正枫’,2000)IF@@error<>0/*如果插入语句出现错误则回滚到保存点*/ROLLBACKTRANinsertsav

PRINT“继续执行程序”COMMITTRANtran_exam2GO第21页，共73页，2023年，2月20日，星期六三、事务的特性(ACID特性)事务的ACID特性：原子性（Atomicity）一致性（Consistency）隔离性（Isolation）持续性（Durability）第22页，共73页，2023年，2月20日，星期六6.3游标（cursor）为什么要使用游标

SQL语言与主语言具有不同数据处理方式SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录第23页，共73页，2023年，2月20日，星期六游标（续）主语言是面向记录的，一组主变量一次只能存放一条记录仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式第24页，共73页，2023年，2月20日，星期六游标（续）

什么是游标游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果每个游标区都有一个名字用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理第25页，共73页，2023年，2月20日，星期六游标的使用过程打开(open)声明(declare)处理关闭(close)解除(deallocate)第26页，共73页，2023年，2月20日，星期六1.申明游标语句格式DECLARE游标名称[SCROLL]CURSORFORSELECT语句[FORREADONLY|UPDATE[OF列1,列2,列3,…]]是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。当cursor申请成功后，游标名就成为该cursor的识别，后续对该cursor的处理必须指定该游标名。游标名在同一程序（存储过程）内不可重复。第27页，共73页，2023年，2月20日，星期六申明游标（续）SCROLL：如果选用了此关键字，表示可以使用相应的关键字指定游标的移动位置。如果不用SCROLL，则表示游标的移动只能是NEXT。关键字游标移动位置FIRST结果集中第一条记录LAST结果集中最后一条记录PRIOR前一条记录NEXT后一条记录RELATIVE按相对位置取记录ABSOLUTE按绝对位置取记录READONLY|UPDATE[OF列1,列2,列3,……]：设置游标的只读属性和更新属性。方括号中定义游标内可更新的列，如果没有指定具体的列名，则表示能修改所有的列。第28页，共73页，2023年，2月20日，星期六2.打开游标语句格式OPEN<游标名>;功能打开游标实际上是执行相应的SELECT语句，把所有满足查询条件的记录从指定表取到缓冲区中这时游标处于活动状态，指针指向查询结果集中第一条记录之前第29页，共73页，2023年，2月20日，星期六3.移动游标指针，然后取当前记录语句格式FETCH[[NEXT|PRIOR|FIRST|LAST]FROM]<游标名>INTO<主变量>[<指示变量>][,<主变量>[<指示变量>]]...;第30页，共73页，2023年，2月20日，星期六4.关闭游标语句格式CLOSE<游标名>;功能关闭游标，释放结果集占用的缓冲区及其他资源说明游标被关闭后，就不再和原来的查询结果集相联系被关闭的游标可以再次被打开，与新的查询结果相联系第31页，共73页，2023年，2月20日，星期六例6.7声明一个游标的基础上，利用该游标变量读取结果集中的所有记录。

USESAMPLEDECLAREcursor_exam1CURSOR/*声明游标*/FORSELECT*FROM员工数据表WHERE所属部门=‘项目部’ORDERBY姓名FORREADONLYOPENcursor_exam1/*打开游标*/FETCHFROMcursor_exam1/*读取结果集中的数据*/WHILE@@FETCH_STATUS=0/*根据游标状态来确定是否继续读取数据*/FETCHFROMcursor_exam1第32页，共73页，2023年，2月20日，星期六例6.8使用游标，将客户数据表中的公司名称和联系人显示出来。

USESAMPLEDECLARE@comnameVERCHAR(50),@connameVARCHAR(10)/*定义两变量，类型和客户数据表中的“公司名称”、“联系人”字段相同*/DECLAREcur_exam2CURSOR/*声明游标*/FORSELECT公司名称，联系人FROM客户数据表

ORDERBY客户编号FORREADONLY/*定义只读游标在查询“客户数据表”的公司名称和联系人姓名上*/OPENcur_exam2/*打开游标*/FETCHFROMcur_exam2INTO@comyname,@connamePRINT“公司名称：”+RTRIM(@comname)+”联系人：”+RTRIM(@conname)/*使用游标取查询结果集中的第一行数据赋给变量并打印出来，同时给游标状态赋初值*/第33页，共73页，2023年，2月20日，星期六例6.8（续）WHILE@@FETCH_STATUS=0/*根据游标状态来确定是否继续读取数据*/BEGINFETCHFROMcur_exam2INTO@comname,@connamePRINT“公司名称：”+RTRIM(@comname)+”联系人：”+RTRIM(@conname)END/*循环取数直到游标状态值@@FETCH_STATUS非0，即没有数据了*/CLOSEcur_exam2/*关闭游标*/DEALLOCATEcur_exam2/*释放游标*/第34页，共73页，2023年，2月20日，星期六6.4存储过程存储过程是一段预先编译好地T-SQL程序，有自己的名字，可以作为一个独立的数据库对象，也可以作为一个单元供用户在应用程序中调用。存储过程有以下优点：执行速度快提高工作效率规范程序设计提高系统的安全性第35页，共73页，2023年，2月20日，星期六一、创建存储过程

命令的语法为：

CREATEPROC[EDURE]存储过程名称[{@parameterdata_type}[OUTPUT]][,……n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSQL语句｜语句组

第36页，共73页，2023年，2月20日，星期六例6.9在SAMPLE数据库中创建一存储过程，返回指定的时间段中立项的项目的经费总额。

USESAMPLEIFEXISTS(SELECTnameFROMsysobjectsWHEREname=’pro_exam1’ANDtype=’P’)DROPPROCpro_exam1/*如果数据库SAMPLE对象系统表中已经有名为pro_exam1的存储过程，则删除*/CREATEPROCpro_exam1@beginning_dataDATETIME,@ending_dateDATETIME,@all_outlaydecimal(9,2)OUTPUT/*给存储过程定义三个参数：开始时间和结束时间以及经费总额*/ASSELECT@all_outlay=SUM(经费)FROM项目数据表WHERE开始时间BETWEEN@beginning_dataAND@ending_date/*查询出要求的时间内所立项目的经费总额*/GO第37页，共73页，2023年，2月20日，星期六二、执行和删除存储过程只有对存储过程有访问权限的用户才可以调用存储过程，调用时如果不是一个批的首行用EXECUTE命令，可简写为EXEC。例：DECLARE@all_outlayDECIMAL(9,2)EXECpro_exam1’2004-1-1’,’2004-12-31’,@all_outlayOUTPUTSELECT‘2004年立项的项目总经费是：’＋RTRIM(CAST(◎all_outlayASVARCHAR(10)))GO删除存储过程。DROPPROC存储过程名称第38页，共73页，2023年，2月20日，星期六6.5触发器触发器是一种数据库基本表被修改时自动执行的内嵌程序，当使用UPDATE、INSERT、或DELETE命令在指定表中对数据进行修改时，用来保证数据正确性。触发器可以查询其他表，而且可以包含复杂的SQL语句，它们主要用于强制较为复杂的业务规则或要求。使用触发器能够帮助保证数据的一致性和完整性。你可把存储过程想象成手枪，它必须有人扣扳机才会射出子弹，触发器则较像地雷，当用户踩到它（条件符合）时它便会“引爆”（被执行）第39页，共73页，2023年，2月20日，星期六触发器优点级联修改数据库中相关联的表执行比CHECK约束更为复杂的数据完整性约束使用自定义的错误信息比较数据表修改前后得数据维护非规范化数据第40页，共73页，2023年，2月20日，星期六触发器的类型传统的触发器（INSERT、UPDATE、DELETE）：是由一个动作或事件的发生而触发的，现在的Microsoft把它们划分为AFTER触发器。这种类型的触发器将在数据变动完成后才被触发。对变动的数据进行检查，如果发现错误，则拒绝或回滚。INSTEADOF触发器：这种类型的触发器并不是有动作或事件触发，一般在数据变动之间被触发，并取代变动数据的操作（INSERT、UPDATE、DELETE），转而去执行触发器定义的操作。一般用在视图操作中。第41页，共73页，2023年，2月20日，星期六一、创建触发器创建触发器语法CREATETRIGGER触发器名称ON表名|视图名[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASSQL语句组第42页，共73页，2023年，2月20日，星期六例6.10在SAMPLE数据库中，在“员工数据表”中创建一触发器，实现在删除某一员工时，将该员工担任负责人的项目也删除。USESAMPLEGOCREATETRIGGERtri_exam1ON员工数据表 FORDELETE

/*在“员工数据表”中创建DELETE触发器*/ASIF@@rowcount=0RETURN

/*如果删除0行，则不启动触发器*/DELETE项目数据表WHERE负责人编号IN(SELECT员工编号FROMDELETED)

/*启动触发器，删除“项目数据表”中所有以删除的员工作为负责人的项目记录*//*系统提供INSERTED和DELETED两个虚拟表。插入一行时，INSERT表保存一份插入行的拷贝。删除一行时，DELETE表保存了删除行的拷贝。更新一行时，DELETED和INSERTED表分别保存了更新前后的数据。

*/第43页，共73页，2023年，2月20日，星期六例6.10（续）

IF@@error!=0BEGINROLLBACKTRANRETURNEND

/*如果触发器执行过程中出错，则整个程序回滚到原始状态*/RETURNGO在“员工数据表”中创建该触发器，如果应用程序执行以下语句：USESAMPLEDELETE员工数据表WHERE员工编号=2011GO在删除“员工数据表”中2011号员工的同时，也删除了“项目数据表中2011号员工负责的项目第44页，共73页，2023年，2月20日，星期六二、删除触发器语句格式为：DROPTRIGGER触发器名称第45页，共73页，2023年，2月20日，星期六6.6嵌入式SQLSQL语言提供了两种不同的使用方式：交互式嵌入式为什么要引入嵌入式SQLSQL语言是非过程性语言事务处理应用需要高级语言这两种方式细节上有差别，在程序设计的环境下，SQL语句要做某些必要的扩充第46页，共73页，2023年，2月20日，星期六一、嵌入式SQL的一般形式为了区分SQL语句与主语言语句，需要:结束标志：随主语言的不同而不同前缀：EXECSQL以C为主语言的嵌入式SQL语句的一般形式EXECSQL<SQL语句>;例：EXECSQLDROPTABLEStudent;以COBOL作为主语言的嵌入式SQL语句的一般形式EXECSQL<SQL语句>END-EXEC例：EXECSQLDROPTABLEStudentEND-EXEC第47页，共73页，2023年，2月20日，星期六DBMS处理宿主型数据库语言SQL的方法预编译修改和扩充主语言使之能处理SQL语句第48页，共73页，2023年，2月20日，星期六预编译1．由DBMS的预处理程序对源程序进行扫描，识别出SQL语句2．把它们转换成主语言调用语句，以使主语言编译程序能识别它3．最后由主语言的编译程序将整个源程序编译成目标码。第49页，共73页，2023年，2月20日，星期六嵌入SQL语句

说明性语句嵌入SQL语句 数据定义 可执行语句数据控制 数据操纵允许出现可执行的高级语言语句的地方，都可以写可执行SQL语句允许出现说明语句的地方，都可以写说明性SQL语句第50页，共73页，2023年，2月20日，星期六二、嵌入式SQL语句与主语言之间的通信将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句SQL语句

描述性的面向集合的语句负责操纵数据库高级语言语句

过程性的面向记录的语句负责控制程序流程第51页，共73页，2023年，2月20日，星期六工作单元之间的通信方式1.SQL通信区向主语言传递SQL语句的执行状态信息主语言能够据此控制程序流程2.主变量1）主语言向SQL语句提供参数2）将SQL语句查询数据库的结果交主语言进一步处理3.游标解决集合性操作语言与过程性操作语言的不匹配第52页，共73页，2023年，2月20日，星期六1.SQL通信区SQLCA：SQLCommunicationAreaSQLCA是一个数据结构SQLCA的用途SQL语句执行后，DBMS反馈给应用程序信息描述系统当前工作状态描述运行环境这些信息将送到SQL通信区SQLCA中应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句第53页，共73页，2023年，2月20日，星期六SQLCA的内容

与所执行的SQL语句有关与该SQL语句的执行情况有关例：在执行删除语句DELETE后，不同的执行情况，SQLCA中有不同的信息：违反数据保护规则，操作拒绝没有满足条件的行，一行也没有删除成功删除，并有删除的行数无条件删除警告信息由于各种原因，执行出错第54页，共73页，2023年，2月20日，星期六SQLCA的使用方法定义SQLCA用EXECSQLINCLUDESQLCA加以定义使用SQLCASQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错应用程序每执行完一条SQL语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理第55页，共73页，2023年，2月20日，星期六2.主变量什么是主变量嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据在SQL语句中使用的主语言程序变量简称为主变量（HostVariable）第56页，共73页，2023年，2月20日，星期六主变量（续）主变量的类型输入主变量由应用程序对其赋值，SQL语句引用输出主变量由SQL语句赋值或设置状态信息，返回给应用程序一个主变量有可能既是输入主变量又是输出主变量第57页，共73页，2023年，2月20日，星期六主变量（续）主变量的用途输入主变量

指定向数据库中插入的数据将数据库中的数据修改为指定值指定执行的操作指定WHERE子句或HAVING子句中的条件输出主变量

获取SQL语句的结果数据

获取SQL语句的执行状态第58页，共73页，2023年，2月20日，星期六主变量（续）

指示变量一个主变量可以附带一个指示变量（IndicatorVariable）什么是指示变量整型变量用来“指示”所指主变量的值或条件指示变量的用途输入主变量可以利用指示变量赋空值输出主变量可以利用指示变量检测出是否空值，值是否被截断第59页，共73页，2023年，2月20日，星期六主变量（续）在SQL语句中使用主变量和指示变量的方法1)说明主变量和指示变量BEGINDECLARESECTION ......... .........(说明主变量和指示变量) .........ENDDECLARESECTION第60页，共73页，2023年，2月20日，星期六主变量（续）2)使用主变量说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:）作为标志第61页，共73页，2023年，2月20日，星期六主变量（续）3)使用指示变量

指示变量前也必须加冒号标志必须紧跟在所指主变量之后

在SQL语句之外(主语言语句中)使用主变量和指示变量的方法可以直接引用，不必加冒号第62页，共73页，2023年，2月20日，星期六例6.11从屏幕输入某一职工的工号、姓名和工资，插入“员工数据表”中。

………EXECSQLINCLUDESQLCA;/*定义SQL通信区*/EXECSQLBEGINDECLARESECTION;/*主变量说明开始*/Inteno; charename(8); chardno(8);EXECSQLENDDECLARESECTION;/*主变量说明结束*/ printf(“请输入职工工号、姓名、工资：”); scanf(“%d,%s,%s”，&eno,ename,dno);/*输入数据*/ EXECSQLINSERTINTO“员工数据表”(员工编号,姓名,所属部门)VALUES（:eno,:ename,:dno）;/*将表中该项目号的记录个*/if(sql.sqlcode<>0)/*利用SQLCA中状态信息判*/ break;printf(“Insertedsuccessful!!”);……第63页，共73页，2023年，2月20日，星期六例6.12查询全部客户的信息，然后根据客户的变更情况修改其中某些客户的联系方式。…………EXECSQLINCLUDESQLCA;EXECSQLBEGINDECLARESECTION; Intcus_no; Charlink_name(10); char link_addr(50); char new_link_addr(50);EXECSQLENDDECLARESECTION;EXECSQLDECLAREcur_updateCURSORFOR/*定义游标cur_update*/ SELECE客户编号，联系人，联系方式/*使其与查询结果集相联系*/ FROM客户数据表;EXECSQLOPENcur_update;

第64页，共73页，2023年，2月20日，星期六例6.12（续）while(1)/*用循环结构逐条处理结果集中的记录*/{ EXECSQLFETCHcur_update/*从结果集中取当前行，送相应*/INTO:cus_no，:link_name，:ling_addr;/*主变量，并推进游标*/ if(sql.sqlcode<>0)/*如果查询结果已处理完或出现错误，则退出循环