ch5 数据库系统概念(第6版)第五章高级SQL

1数据库系统上海交通大学计算机系张忠能[e-mail]第5章：高级SQL第5章：高级SQLSQL数据类型和模式函数和过程结构触发器高级聚集功能OLAP用编程语言接入SQL*动态SQLJDBC和ODBC嵌入式SQL过程扩展和存储过程SQL提供模块语言SQL允许用if-then-else语句，for和while循环，等等，来定义过程.存储过程可以在数据库中存储过程然后通过call语句来执行允许外部应用程序对数据库进行操作，而无需了解内部细节面向对象方面将在22章介绍(基于对象的数据库)*函数和过程SQL:1999支持函数和过程函数/过程可以用SQL语言书写，也可以使用其他外部程序语言.函数对于特殊新的数据类型，例如图片和几何对象特别有用.例如:用于检查多边形是否重叠，或者比较图片相似性的函数.一些数据库系统支持表值函数,返回一个关系作为结果.SQL:1999还支持大量的命令式结构，例如循环,if-then-else,赋值许多数据库对不同于SQL:1999的SQL提供专有的过程扩展.SQL函数定义一个函数,输入部门名字,返回该部门的教师数量.createfunctiondept_count(dept_namevarchar(20))returnsintegerbegindeclared_countinteger;selectcount(*)intod_countfrominstructorwhereinstructor.dept_name=dept_namereturnd_count;end用法:列出拥有超过12名教师的部门名字以及预算.selectdept_name,budgetfromdepartmentwheredept_count(dept_name)12表函数SQL:2003增加了能够返回一个关系的函数例如:返回给定顾客所拥有的所有账户createfunctioninstructors_of(dept_namechar(20)returnstable(IDvarchar(5),namevarchar(20),dept_namevarchar(20),salarynumeric(8,2))returntable(selectID,name,dept_name,salaryfrominstructorwhereinstructor.dept_name=instructors_of.dept_name)用法select*fromtable(instructors_of(‘Music’))SQL过程dept_count函数可以被写成如下过程:createproceduredept_count_proc(indept_namevarchar(20),outd_countinteger)beginselectcount(*)intod_countfrominstructorwhereinstructor.dept_name=dept_count_proc.dept_nameend可以使用call语句从SQL过程中或者从嵌入式SQL中调用过程.declared_countinteger;calldept_count_proc(‘Physics’,d_count);过程和函数也可以从动态SQL中调用SQL:1999允许超过一个函数/过程拥有相同的名字(称作名字超载),只要参数的数量是不同的,或者至少参数的类型是不同的.过程结构*注意:大部分数据库系统对下列标准语法实现了自己的变种阅读系统手册，查看相关的系统语法复合语句:begin…end,在begin和end之间可能包含多个SQL语句.局部变量可以在一个复合语句中被声明While和repeat语句:declarenintegerdefault0;whilen10dosetn=n+1endwhilerepeatsetn=n–1untiln=0endrepeat过程结构应用存储过程创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的Transact-SQL语句的集合。可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。语法CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]存储过程存储过程的优点：使用存储过程可以减少网络流量增强代码的重用性和共享性使用存储过程可以加快系统运行速度使用存储过程保证安全性存储过程的创建写SQL语句测试SQL语句如得到所需结果，则创建结果执行过程触发器触发器触发器是一条语句，当对数据库做修改时，它自动被系统执行.要设置触发器机制，必须满足:指明什么条件下触发器被执行.指明触发器执行的动作是什么.SQL-92标准并不包括触发器，但是许多DB系统支持触发器。触发器于SQL:1999被引进到SQL标准,但是更早就通过非标准语法被大部分数据库所支持.讲义引用的语法可能与实际使用的数据库系统不一样；查阅系统手册触发器SQL2000语法CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}[WITHAPPEND][NOTFORREPLICATION]AS[{IFUPDATE(column)[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]}]sql_statement[...n]}}触发器--举例假定不允许负的帐户余额，银行处理透支为：设定帐户余额为0在透支的数目上产生一个贷款给这个贷款一个与透支的帐户号码相同的贷款号码执行触发器的条件是account关系的更新导致余额为负值。触发器--举例(续)definetriggeroverdraftonupdateofaccountT(ifnewT.balance0then(insertintoloanvalues(T.branch-name,T.account-number,–newT.balance)insertintoborrower(selectcustomer-name,account-numberfromdepositorwhereT.account-number=depositor.account-number)updateaccountSsetS.balance=0whereS.account-number=T.Account-number))(SQL2000define=create)关键字new表明T.balance的值在更新后应该被使用。如果被省略，那么值在更新前就被使用。触发器--举例(续)CREATETRIGGERreminderONtitlesFORINSERTASEXECmaster..xp_sendmail'MaryM','Newtitle,mentioninthenextreporttodistributors.'本章结束附录触发器实例×例如，time_slot_id不是timeslot的主码,所以不能从section到timeslot创建外码约束.替代方案:在section和timeslot使用触发器来执行完整性约束createtriggertimeslot_check1afterinsertonsectionreferencingnewrowasnrowforeachrowwhen(nrow.time_slot_idnotin(selecttime_slot_idfromtime_slot))/*time_slot_idnotpresentintime_slot*/beginrollbackend;触发器实例（续）×createtriggertimeslot_check2afterdeleteontimeslotreferencingoldrowasorowforeachrowwhen(orow.time_slot_idnotin(selecttime_slot_idfromtime_slot)/*lasttuplefortimeslotiddeletedfromtimeslot*/andorow.time_slot_idin(selecttime_slot_idfromsection))/*andtime_slot_idstillreferencedfromsection*/beginrollbackend;SQL触发事件和动作×触发事件可能是insert,delete或者update更新操作的触发器可以被限制在特定的属性例如,afterupdateoftakesongrade属性更新前后的值都能被引用referencingoldrowas:用于删除和更新referencingnewrowas:用于插入和更新触发器可以在事件之前被激活，作为额外限制.例如，将空白的成绩设为空值.createtriggersetnull_triggerbeforeupdateoftakesreferencingnewrowasnrowforeachrowwhen(nrow.grade=‘‘)beginatomicsetnrow.grade=null;end;用触发器维护credits_earned值×createtriggercredits_earnedafterupdateoftakeson(grade)referencingnewrowasnrowreferencingoldrowasorowforeachrowwhennrow.grade’F’andnrow.gradeisnotnulland(orow.grade=’F’ororow.gradeisnull)beginatomicupdatestudentsettot_cred=tot_cred+(selectcreditsfromcoursewherecourse.course_id=nrow.course_id)wherestudent.id=nrow.id;end;语句级触发器×可以对所有受到事务影响的行执行一个操作，而不需要对每个受到影响的行执行单独操作使用foreachstatement代替foreachrow使用referencingoldtable或referencingnewtable来引用受影响的行的临时表(称为转换表)当使用SQL语句更新大量行的时候可以更有效有时候不使用触发器×以前触发器被用于这样的任务维护数据总结数据(例如每个部门的薪水之和)通过记录特殊关系(称作change或者delta关系)的改变来复制数据库，并且用一个单独的进程来将这些改变应用到副本现在有更好的方法来处理:如今的数据库提供内置的物化视图功能来维护总结数据数据库对复制提供内置支持在某些情况下封装功能可以被用来代替触发器定义方法来更新字段作为更新方法的一部分来执行操作，而不是通过触发器有时候不使用触发器×触发器意外执行的风险,例如从备份副本中加载数据在远程站点复制更新触发器可以在执行这些操作之前被禁用.其他触发器风险:触发器的错误导致关键事务失败级联执行递归查询SQL递归SQL:1999允许定义递归视图例如:对于一个特定的课程，找出哪些课程是其先决条件，无论是直接还是间接条件withrecursiv