数据库性能提高.ppt

阶段五 数据库性能提高 存储过程 SQLServer提供了一种方法 可以将一些固定的操作集中起来由数据库服务器来完成 以完成某个特定的任务 这种方法就是存储过程 一般分为五类 系统存储过程 sp 用户定义的存储过程 pr 扩展存储过程 xp 临时存储过程 或 和远程存储过程 优点 加快系统运行速度 封装复杂性 实现代码重用 安全性高 减少网络流量 创建和执行存储过程 createpro edure 存储过程名 参数1数据类型 默认值 output 参数2数据类型 默认值 output asSQL语句执行存储过程 exec procedure name value list 例 编写一个存储过程以实现向course表中插入指定的记录 要求课程记录的信息在执行存储过程的时候指定 然后通过执行存储过程完成指定记录的插入createprocpr addcourse cnovarchar 20 cnamevarchar 20 ccreditint ctypevarchar 20 asinsertintocoursevalues cno cname ccredit ctype execpr addcourse 1002gdsx 高等数学 3 基础课 使用默认参数 createprocpr addcourse2 cnovarchar 20 cnamevarchar 20 ccreditint 5 ctypevarchar 20 asinsertintocoursevalues cno cname ccredit ctype execpr addcourse2 cno yy cname 英语 ctype 基础课 使用输出参数 例 编写一个存储过程 将指定课程号的学分数通过输出参数返回 然后验证createprocpr getcredit cnovarchar 20 ccreditintoutput asselect ccredit c creditfromcoursewherec no cno验证 declare ccreditintexecpr getcredit 1002c ccreditoutputprint ccredit 触发器 触发器是一种特殊类型的存储过程 它不同于前面介绍过的一般的存储过程 一般的存储过程通过存储过程名称被直接调用 而触发器主要是通过事件进行触发而被执行触发器是一个功能强大的工具 它与表格紧密相连 在表中数据发生变化时自动强制执行 触发器可以用于SQLServer约束 默认值和规则的完整性检查 还可以完成难以用普通约束实现的复杂功能 触发器的优点触发器是自动的执行的 触发器可以通过数据库中的相关表进行层叠更改 触发器的类型AFTER触发器INSTEADOF触发器FOR触发器 使用Transact SQL语句创建触发器使用Transact SQL语言中的CREATETRIGGER命令可以创建触发器 其中需要指定定义触发器的基表 触发器执行的事件和触发器的所有指令 createtrigger触发器名on表 视图for after insteadofinsert update deleteassql语句 例 需要在学生信息表中增加学生信息时 显示 欢迎新同学 可以为information表创建一个插入型触发器 createtriggerinfo insert trioninformationforinsertasprint 欢迎新同学 每个触发器有两个特殊的表 inserted表和deleted表 这两个表都是逻辑表 是有系统管理的 存储在内存中 不是存储在数据库中 因此不允许用户直接对其修改 这两个表是动态驻留在内存中的 当触发器工作完成后 这两个表也被删除 这两个表主要保存因用户操作而被影响到的原数据值或新数据值 而且这两张表是只读的 用户不能向这两个表写入内容 但可以引用表中的数据 例 需要在数据表score中插入记录时进行如下检查 当试图往score表中插入在数据表information中不存在的s no或在course表中不存在的c no时 撤销操作 并返回一条错误信息createtriggerscore insert trionscoreforinsertasdeclare snochar 11 declare cnochar 11 select sno information s nofrominformation insertedwhereinformation s no inserted s noselect cno course c nofromcourse insertedwherecourse c no inserted c noIf snoisnullor cnoisnullBeginrollbacktransactionraiserror 不存在这样的学号或课程号 16 10 end 练习 需要在score表中修改记录时进行如下检查 当试图修改score表中的记录时 检查修改后记录中的s no是否在information表存在 同时c no是否在course表中存在 若不是同时存在 则撤销update操作 并返回一条错误消息 createtriggerscore update trionscoreforupdateasdeclare snochar 11 declare cnochar 20 select sno information s nofrominformation insertedwhereinformation s no inserted s noselect cno course c nofromcourse insertedwherecourse c no inserted c noIf snoisnullor cnoisnullBeginrollbacktransactionraiserror 不存在这样的学号或课程号 16 10 练习 需要在information表中删除记录时进行如下操作 当删除information表中的一条记录时 自动删除与之相关联的score表中的所有记录 若删除失败 则撤销所有删除操作 并返回一条错误消息 createtriggerinfo delete trioninformationfordeleteasdeclare snochar 11 select sno s nofromdeleteddeletescorewheres no snoif rowcount 0Beginrollbacktransactionraiserror 删除失败 16 10 endelseprint 级联删除成功 数据完整性 数据完整性是SQLServer用于保证数据库中数据一致性的一种机制 以防止非法信息存在于数据库中 实现数据完整性是指存储在数据库中的数据是正确的 而且在逻辑上符合实现情况的要求 设计数据完整性的目的是为了防止数据库中存在不符合语义规定的数据 防止错误信息的输入和输出 数据库完整性一般分为4类 实体完整性 域完整性 引用完整性和用户定义完整性 实体完整性 实体完整性的基本思想是标识数据库中存放的每一个实体 实体就是数据库所要标识的一个实际的物体和事件 实体完整性要求每个实体都保持唯一性 SQLServer2005实现实体完整性主要是通过唯一索引 UNIQUE约束 PRIMARYKEY约束或IDENTITY属性 强制表的标识符列或主键来实现的 域完整性 实体完整性考虑究竟应该向表输入哪些值 而域完整性则考虑如何限制向表中输入的值的范围 他要求表中指定列的数据具有正确的数据类型 格式和有效的数据范围 域完整性可以通过强制域完整性限制类型 通过使用数据类型 限制格式 通过使用CHECK约束和规则 或限制可能值的范围 通过使用FOREIGNKEY CHECK约束 DEFAULT定义 NOTNULL定义和规则 来实现 引用完整性 引用完整性又称为参照完整性 在输入或删除记录时 引用完整性用于维持参照表和被参照表之间的数据一致性 在SQLServer2005中 引用完整性通过主键和外键约束来实现 引用完整性用于确保键值在所有表中一致 这类一致性要求不能引用不存在的值 如果一个键值发生更改 则整个数据库中 对该键值的所有引用要进行一致的更改 在被参照表中 当其主键值被其他表所参照时 该行不能被删除页不允许改变 在参照表中 不允许参照不存在的主键值 强制引用完整性时 SQLServer2005将防止用户执行下列操作 1 在主表中没有关联的记录时 将记录添加或更改到相关表中 2 更改主表中的值 这会导致相关表中生成孤立记录 3 从主表中删除记录 但仍存在与该记录匹配的相关记录 用户定义的完整性 不同的关系数据库系统根据应用环境的不同 往往还需要一些特殊的约束条件 用户定义的完整性即是针对某个特定关系数据库的约束条件 他反应某一具体应用所涉及的数据必须满足的语义要求 SQLServer2005提供了定义和检查这类完整性的机制 以便用同意的系统方法来处