L12_Oracle数据库编程_QUST.pdf

《L12_Oracle数据库编程_QUST.pdf》由会员分享，可在线阅读，更多相关《L12_Oracle数据库编程_QUST.pdf（81页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、完整性约束概念定义 数据完整性是指数据的正确性和相容性，以及是否真实地反映现实世界 完整性约束是数据库模式定义时指定的约束条件，它限制某些数据的情形在数据库中出现 加在数据库数据之上的语义约束条件称为数据完整性约束条件 DBMS中检查数据是否满足完整性条件的机制称为完整性检查 如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性，违约反应定义 ORACLE强制执行完整性约束，保证只有合法的数据存储于数据库中 当DBA定义数据库模式/执行操作时，DBMS检查数据是否违法约束 立即执行的约束(Immediateconstraints)语句执行完后立即检查是否违背完整

2、性约束 延迟执行的约束(Deferredconstrainsts)完整性检查延迟到整个事务执行结束后进行完整性 vs.安全性作用对象 列级约束 对属性的取值类型、范围、精度等的约束条件 元组约束 对元组中各个属性列间的联系的约束 关系约束 对若干元组间、关系集合上及关系之间的联系的约束 静态约束 反映数据库状态合理性的约束 动态约束 反映数据库状态变迁的约束作用对象对象状态动态列级约束动态元组约束动态关系约束动态静态列级约束静态元组约束静态关系约束静态对象粒度列元组关系对象状态动态列级约束动态元组约束动态关系约束动态静态列级约束静态元组约束静态关系约束静态对象粒度列元组关系静态列级约束 对列的

3、类型、取值范围、精度等的约束，包括：对数据类型的约束 数据类型、长度、精度等 对数据格式的约束 20100110012010011001年专业班号序列号 对取值范围的约束 学生的成绩取值范围为0100 对空值的约束 列是否允许有空值静态元组约束 元组约束是元组中各个字段之间的联系的约束 开始日期小于结束日期 订货数量小于等于库存数量 最低工资不能低于规定的最低值 元组约束只局限在元组上静态关系约束 元组之间、关系之间的联系的约束 学号的取值不能重复也不能取空值 学生修课表中学号的取值受学生表中学号取值的限制等 常见的关系约束 实体完整性约束 参照完整性约束 函数依赖约束 统计约束动态约束 动态

4、列级约束：修改列定义或列值时应满足的约束条件 修改列定义时的约束 将原来允许空值的列改为不允许空值时，若该列目前已存在空值，则拒绝这种修改 修改列值时的约束 修改列值时新旧值之间要满足的约束条件 例：职工工资调整=原来工资 例：年龄只能增长动态约束 动态元组约束：修改元组值时各个字段之间要满足的约束条件 例：职工工资调整不得低于其原来工资+工龄1.5 动态关系约束：关系变化前后状态的限制条件 例：事务一致性、原子性等约束条件小结粒度状态列级元 组 级关 系 级粒度状态列级元 组 级关 系 级静态静态列定义列定义 类型类型 格式格式 值域值域 空值元 组 值 应 满足的条件实体完整性约束参照完整

5、性约束函数依赖约束统计约束空值元 组 值 应 满足的条件实体完整性约束参照完整性约束函数依赖约束统计约束动态动态改变列定义或列值元组新旧值之间应满足的约束条件关系新旧状态间应满足的约束条件改变列定义或列值元组新旧值之间应满足的约束条件关系新旧状态间应满足的约束条件实现数据完整性的方法 可以在服务器端完成，也可以在客户端编程实现 服务器端 声明数据完整性 触发器 客户端 前端开发工具 在应用程序中编写代码保证实体完整性约束实体完整性约束 实体完整性是用PRIMARYKEY来保证 每个表只能有一个PRIMARYKEY约束 用PRIMARYKEY约束的列的取值必须是不重复的 由多列构成的主键，这些主

6、键列组合起来取值不重复 不允许有空值示例职工表CREATETABLE职工表(职工编号 CHAR(7)NOTNULL,职工名CHAR(10)NOTNULL,工作编号 CHAR(8),工资SMALLINT,电话CHAR(8),身份证号 CHAR(18)工作表CREATETABLE工作表(工作编号 CHAR(8)NOTNULL,最低工资 SMALLINT,最高工资 SMALLINT)主键约束 创建表时 列级约束：PRIMARYKEY 表级约束：PRIMARYKEY(,),修改表时 ALTERTABLE表名 ADDCONSTRAINT约束名 PRIMARYKEY（,n）示例ALTERTABLE职工表A

7、DDCONSTRAINTPK_EMPPRIMARYKEY（职工编号）ALTERTABLE工作表ADDCONSTRAINTPK_JOBPRIMARYKEY（工作编号）唯一值约束 用UNIQUE约束实现 限制一个列或者是多个列的组合取值不重复 用在事实上具有唯一性的属性列上，比如身份证号码、驾驶证号码等 有UNIQUE约束的列允许有一个空值 可在一个表中可以定义多个UNIQUE约束 可在一个列或多个列上定义UNIQUE约束唯一值约束 创建表时 列级约束：列名 类型 UNIQUE（,n）表级约束：UNIQUE（,n）修改表时 ALTERTABLE表名 ADDCONSTRAINT约束名 UNIQUE（

8、,n）示例 为“身份证号”列添加唯一值约束CREATETABLE职工表（身份证号 CHAR(19)UNIQUE,）或ALTERTABLE职工表ADDCONSTRAINTUN_EMPUNIQUE（身份证号）示例 设authors表，有au_fname和au_lname两个列，现要限制这两个列组合起来不重复CREATETABLEauthors(au_fname VARCHAR(20),au_lname VARCHAR(20),UNIQUE(au_fname,au_lname),/作为表级约束定义)示例 在已创建好的表上添加唯一值约束ALTERTABLEauthorsADDCONSTRAINTUN_

9、NameUNIQUE(au_fname,au_lname)参照完整性约束参照完整性 参照完整性（引用完整性）用FOREIGNKEY约束保证 外键列引用的列必须是有PRIMARYKEY约束或UNIQUE约束的列 通常是有PRIMARYKEY约束的列 主要考虑 外码是否可以接受空值的问题 在被参照关系中删除元组时的问题 在参照关系中插入元组时的问题 修改被参照关系中主码的问题外码是否可以接受空值的问题依赖于应用环境的语义学生选课数据库，Student关系为被参照关系（主码为Sno），SC为参照关系（外码为Sno）若SC的Sno为空值，表明尚不存在的某个学生，或者某个不知学号的学生，选修了某门课程，

10、其成绩记录在Grade中，与应用环境是不符职工部门数据库，EMP关系包含有外码Deptno 某元组的这一列若为空值，表示这个职工尚未分配到任何具体的部门工作，和应用环境的语义是相符在被参照关系中删除元组时的问题删除被参照关系的某个元组（Student），而参照关系有若干元组（SC）的外码值与被删除的被参照关系的主码值相同三种可能的违约反应 级联删除（CASCADES）：将参照关系中外码值与被参照关系中要删除元组主码值相对应的元组一起删除 受限删除（RESTRICTED）：当参照关系中没有任何元组的外码值与要删除的被参照关系的元组的主码值相对应时，系统才执行删除操作，否则拒绝此删除操作 置空值删

11、除（NULLIFIES）：删除被参照关系的元组，并将参照关系中与被参照关系中被删除元组主码值相等的外码值置为空值在被参照关系中删除元组时的问题SnoSnameSsexSageSdept0811101李勇男21计算机系0811102刘晨男20计算机系0811103王敏女20计算机系0811104张小红女19计算机系0821101张立男20信息管理系0821102吴宾女19信息管理系0821103张海男20信息管理系0811101李勇男21计算机系0811102刘晨男20计算机系0811103王敏女20计算机系0811104张小红女19计算机系0821101张立男20信息管理系0821102吴宾女

12、19信息管理系0821103张海男20信息管理系SnoCnoGrade0811101C001 960811101C002 800811101C003 840811101C005 620811102C001 920811102C002 900811102C004 840821102C001 760821102C004 850821102C005 730821102C007 NULL0821103C001 500821103C004 800811102刘晨男20计算机系0811102刘晨男20计算机系0811102C001 920811102C002 900811102C004 84ON DELE

13、TE CASCADEON DELETENO ACTIONON DELETE CASCADEON DELETENO ACTION0811102刘晨男20计算机系0811102刘晨男20计算机系0811102C001 920811102C002 900811102C004 84在参照关系中插入元组时的问题 需在参照关系中插入元组，而被参照关系不存在相应元组 违约反应 受限插入：仅当被参照关系中存在相应的元组，其主码值与参照关系插入元组的外码值相同时，系统才执行插入操作，否则拒绝此操作 递归插入：首先向被参照关系中插入相应的元组，其主码值等于参照关系插入元组的外码值，然后向参照关系插入元组在参照关系

14、中插入元组时的问题 例：向SC关系插入（99001，1，90）元组，而Student关系中尚没有Sno=99001的学生 受限插入：系统将拒绝向SC关系插入（99001，1，90）元组 递归插入：系统将首先向Student关系插入Sno=99001的元组，然后向SC关系插入（99001，1，90）元组修改被参照关系中主码的问题 违约情况 要修改被参照关系中某些元组的主码值，而参照关系中有些元组的外码值正好等于被参照关系要修改的主码值 要修改参照关系中某些元组的主码值，而被参照关系中没有任何元组的外码值等于被参照关系修改后的主码值修改被参照关系中主码的问题 违约策略 级联修改：修改被参照关系中主

15、码值同时，用相同的方法修改参照关系中相应的外码值 受限修改：拒绝此修改操作，只当参照关系中没有任何元组的外码值等于被参照关系中某个元组的主码值时，这个元组的主码值才能被修改 置空值修改：修改被参照关系中主码值，同时将参照关系中相应的外码值置为空值修改被参照关系中主码的问题SnoSnameSsexSageSdept0811101李勇男21计算机系0811102刘晨男20计算机系0811103王敏女20计算机系0811104张小红女19计算机系0821101张立男20信息管理系0821102吴宾女19信息管理系0821103张海男20信息管理系0811101李勇男21计算机系0811102刘晨男2

16、0计算机系0811103王敏女20计算机系0811104张小红女19计算机系0821101张立男20信息管理系0821102吴宾女19信息管理系0821103张海男20信息管理系SnoCnoGrade0811101C001 960811101C002 800811101C003 840811101C005 620811102C001 920811102C002 900811102C004 840821102C001 760821102C004 850821102C005 730821102C007 NULL0821103C001 500821103C004 800811102刘晨男20计算机系

17、0811102刘晨男20计算机系0811102C001 920811102C002 900811102C004 84ON UPDATE CASCADEON UPDATENO ACTIONON UPDATE CASCADEON UPDATENO ACTION08111100811110刘晨男20计算机系刘晨男20计算机系0811110C001 920811110C002 900811110C004 84添加外键约束 ALTERTABLE表名 ADDCONSTRAINT约束名 FOREIGNKEY（）REFERENCES 引用表名（）ONDELETECASCADE|NOACTION ONUPDAT

18、ECASCADE|NOACTION 其中 ONDELETE：级联或限制删除 ONUPDATE：级联或限制更新示例 为职工表的“工作编号”列添加外键约束，此列引用工作表中的“工作编号”列 ALTERTABLE职工表ADDCONSTRAINTFK_job_idFOREIGNKEY(工作编号)REFERENCES 工作表(工作编号)默认值约束默认值约束 用DEFAULT约束实现，用于提供列的默认值 只在向表中插入数据时才检查DEFAULT约束 每个列只能有一个DEFAULT约束 创建表时 列名 类型 CONSTRAINT约束名DEFAULT常量表达式,修改表时 ALTERTABLE表名 ADDCON

19、STRAINT约束名 DEFAULT常量表达式 FOR列名示例 在职工表中，如果某个职工没有电话，则写入默认值：11111111CREATETABLE职工表(电话 CHAR(8)DEFAULT11111111,)或ALTERTABLE职工表ADDCONSTRAINTDF_PHONEDEFAULT11111111FOR电话列取值范围约束列取值范围约束 用CHECK约束实现，用于限制列的取值在指定范围内，即约束列的取值符合应用语义，如：性别只能是“男”或“女”工资必须大于1000 在执行INSERT语句和UPDATE语句时系统自动检查CHECK约束 CHECK约束可以限制一个列的取值范围，也可以限

20、制同表多列之间的取值约束关系定义或添加CHECK约束 CREATETABLE表名(列名 类型 CONSTRAINT约束名 CHECK(逻辑表达式),）或 ALTERTABLE表名ADD CONSTRAINT约束名 CHECK（逻辑表达式）示例为职工表定义工资必须大于等于1000的约束 CREATETABLE职工表（工资 SMALLINTCHECK(工资=1000),）或 CREATETABLE职工表（工资 SMALLINT,CHECK(工资=1000),）示例 限制“最低工资”=“最高工资”CREATETABLE工作表（最低工资 int,最高工资 int,CHECK（最低工资=最高工资）,）多

21、列之间的CHECK约束只能定义在表级约束处示例 限制电话号码列的每一位的取值必须是09之间的数字 CHECK（电话 LIKE0-90-90-90-90-90-90-90-9）完整性约束的检查检查时机 主键约束：插入和更新数据时，检查新数据的主键值是否与已存在的主键值重复，或者新主键值是否为空 唯一值约束：同主键约束 默认值约束：插入数据且没有为某个列提供值时检查 列取值范围约束：插入和修改有列取值约束的数据检查对子表 插入数据时，检查新数据的外键值是否在主表的主键值范围内 修改外键列值时，检查修改后的外键值是否在主表的主键值范围内对主表删除数据时，检查被删除数据的主键值是否在子表中有对它的引用

22、，若无，则删除之；若有，则看是否允许级联删除：若允许：则将子表中相应数据一起删掉；若不允许：则删除失败。更改主键列值时，检查被更改的主键值是否在子表中有对它的引用，若无，则更改之；若有，则看是否允许级联更改：若允许：则将子表中相应数据一起进行更改；若不允许：则更改失败。删除约束 ALTERTABLE表名DROPCONSTRAINT约束名 删除在职工表上定义的限制电话号码的CHK_PHONE约束 ALTERTABLE职工表DROPCHK_PHONE触发器概述 是用户定义在关系表上的一类有事件驱动的特殊过程 由服务器自动激活，由对数据的更改操作（UPDATE、INSERT、DELETE）引发的自动

23、执行的代码 类似于约束，但是比约束更加灵活，可以进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力 通常用于保证业务规则和数据完整性概述 安全性 可以基于数据库的值使用户具有操作数据库的某种权利 可以基于时间限制用户的操作 例：不允许下班后和节假日修改数据库数据 可以基于数据库中的数据限制用户的操作 例：不允许股票的价格的升幅一次超过10%概述 审计：可以跟踪用户对数据库的操作 审计用户操作数据库的语句，防止用户非法操作 把用户对数据库的更新写入审计表 实现非标准的数据完整性检查和约束 例：触发器可回退任何企图吃进超过自己保证金的期货 提供可变的缺省值概述 实现复杂的数据完整性规则 同

24、步实时地复制表中的数据 自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理 例：如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据事件类型种 类关 键 字含义DML事件(3种)INSERT在表或视图中插入数据时触发UPDATE修改表或视图中的数据时触发DELETE在删除表或视图中的数据时触发DDL事件(3种)CREATE在创建新对象时触发ALTER修改数据库或数据库对象时触发DROP删除对象时触发数据库事件(5种)STARTUP数据打开时触发SHUTDOWN在使用NORMAL或IMMEDIATE选项关闭数据库时触发LOGON当用户连接到数据库并建立会话时触发LOGOFF当

25、一个会话从数据库中断开时触发SERVERERROR发生服务器错误时触发触发器类型种 类简 称作用数据操纵语言触发器DML触发器创建在表上，由DML事件引发的触发器替代触发器INSTEADOF触发器创建在视图上，用来替换对视图进行的插入、删除和修改操作数据定义语言触发器DDL触发器定义在模式上，触发事件是数据库对象的创建和修改数据库事件触发器定义在整个数据库或模式上，触发事件是数据库事件创建触发器 CREATEORREPLACETRIGGER 触发器名 BEFORE|AFTER|INSTEADOF触发事件1 OR触发事件2.ON表名 WHEN触发条件 FOREACHROW DECLARE创建触发

26、器 确定触发的表 即在其上定义触发器的表 确定触发的事件 DML触发器的触发事件有INSERT、UPDATE和DELETE三种 确定触发时间 BEFORE表示触发动作发生在DML语句执行之前 AFTER表示触发动作发生在语句执行之后创建触发器 确定触发级别 有语句级触发器和行级触发器两种 语句级触发器表示SQL语句只触发一次触发器 行级触发器表示SQL语句影响的每一行都要触发一次触发时机触发事件（如触发事件（如INSERT、UPDATE、DELETE等）等）触发器脚本触发器脚本触发时机触发时机BEFORE（事件）（事件）001GREENMED98.1002REDMED1241003REDSMA

27、LL100.1004BLUELARGE54.9005REDMED124.1partnocolorsizeweight触发对象表触发时机触发器事件（如触发器事件（如INSERT、UPDATE、DELETE等）等）触发器脚本触发器脚本触发时机触发时机AFTER（事件）（事件）001GREENMED98.1002REDMED1241003REDSMALL100.1004BLUELARGE54.9005REDMED124.1partnocolorsizeweight触发对象表触发级别 在执行INSERT、UPDATE或DELETE语句时，不论该语句影响了多少行，触发器都只被触发一次，这样的触发器可以称

28、为语句级触发器 FOREACHROW可以指定触发器是一个行级触发器示例当删除EMP表中的一条记录时，显示一条提示信息CREATETRIGGER empDeleteAFTER DELETEONempFOREACHROWBEGINdbms_output.put_line(雇员|:old.ename|被删除);END;示例 当删除EMP表中的一条记录时，显示一条提示信息 当执行delete语句时将引发触发器执行 deletefromemp whereempno=7788;伪记录“:old”代表操作完成前的旧记录“:new”代表操作完成后的新记录 在执行三种DML语句时“:old”和“:new”的存在

29、情况触发语句触发语句:old:newINSERTNULL要插入的记录UPDATE 更新前的记录更新后的记录DELETE 要删除的记录NULL伪记录在emp表中调整雇员工资，要求工资只能增加不能降低createorreplacetrigger update_salbefore updateofsal/*当update语句修改sal列时触发*/onempforeachrowwhen(new.sal old.sal)/*当新的工资小于原有工资时才触发*/begin:new.sal:=:old.sal;/*新的工资赋予原来的值，即工资不变*/end;INSTEADOF 用于对视图的DML触发 多用于多

30、表联接的视图 例：在dept和emp表上建有如下视图createorreplaceview emp_deptasselecte.empno,e.ename,d.deptno,d.dnamefromemp ejoindept done.deptno=d.deptno;INSTEADOF要求：当在emp_dept视图上执行插入操作时，能够把相应的记录插入到 dept 和 emp 表中createtriggerinsert_emp_deptinsteadofinsertonemp_deptbegininsertintodept(deptno,dname)values(:new.deptno,:new

31、.dname);insertintoemp(empno,ename,deptno)values(:new.empno,:new.ename,:new.deptno);end;说明 对AFTER型触发器，可以在同一种操作上建立多个触发器 对INSTEADOF型触发器，在同一种操作上只能建立一个触发器 大部分SQL语句都可用在触发器中，但所有的创建和更改数据库以及数据库对象的语句、所有的DROP语句都不允许在触发器中使用数据库事件触发器 数据库级 定义在整个数据库上 触发事件是数据库事件，如数据库的启动、关闭，对数据库的登录或退出 模式级 定义在模式上 触发事件包括模式用户的登录或退出，或对数据库

32、对象的创建和修改(DDL事件)数据库事件触发器的触发事件种类关键字说明模式级CREATE在创建新对象时触发ALTER修改数据库或数据库对象时触发DROP删除对象时触发数据库级STARTUP数据库打开时触发SHUTDOWN在使用NORMAL或IMMEDIATE选项关闭数据库时触发SERVERERROR发生服务器错误时触发数据库级与模式级LOGON当用户连接到数据库，建立会话时触发LOGOFF当会话从数据库中断开时触发数据库事件触发器权限 创建数据库级触发器需要ADMINISTERDATABASETRIGGER系统权限，一般只有系统管理员拥有该权限。对于模式级触发器 为自己的模式创建触发器需要CR

33、EATETRIGGER权限 为其他模式创建触发器，需要CREATEANYTRIGGER权限。示例 创建触发器，对本次数据库启动以来的用户登录时间进行记录，每次数据库启动后，先清空该表 创建登录事件记录表 CREATETABLEuserlog(USERNAMEVARCHAR2(20),LOGON_TIMEDATE);示例 创建触发器，对本次数据库启动以来的用户登录时间进行记录，每次数据库启动后，先清空该表 创建数据库STARTUP事件触发器 CREATEORREPLACETRIGGER INIT_LOGON AFTERSTARTUP ONDATABASE BEGINDELETEFROMuserl

34、og;END;示例 创建触发器，对本次数据库启动以来的用户登录时间进行记录，每次数据库启动后，先清空该表 创建数据库LOGON事件触发器 CREATEORREPLACETRIGGER DATABASE_LOGON AFTERLOGON ONDATABASE BEGININSERTINTOuserlogVALUES(sys.login_user,sysdate);END;示例 创建触发器，对本次数据库启动以来的用户登录时间进行记录，每次数据库启动后，先清空该表 验证DATABASE_LOGON触发器 CONNECTSCOTT/TIGERorcl;CONNECTdemo/passorcl;验证结果

35、 SELECTusername,TO_CHAR(logon_time,YYYY/MM/DDHH24:MI:SS)FROMuserlog;示例 创建触发器，对本次数据库启动以来的用户登录时间进行记录，每次数据库启动后，先清空该表 验证INIT_LOGON触发器 重新启动数据库 登录Demo账户 验证结果 SELECTusername,TO_CHAR(logon_time,YYYY/MM/DDHH24:MI:SS)FROMuserlog;数据库事件属性属 性 适用触发器类型 说 明 Sys.sysevent 所有类型 返回触发器触发事件字符串 Sys.instance_num 所有类型 返回 Or

36、acle 实例号 Sys.database_name 所有类型 返回数据库名字 Sys.server_error(stack_position)SERVERERROR 从错误堆栈指定位置返回错误号，参数为 1 表示最近的错误 Is_servererror(error_number)SERVERERROR 判断堆栈中是否有参数指定的错误号 Sys.login_user 所有类型 返回导致触发器触发的用户名 Sys.dictionary_obj_type CREATE、ALTER、DROP 返回 DDL 触发器触发时涉及的对象类型 Sys.dictionary_obj_name CREATE、ALTER、DROP 返回 DDL 触发器触发时涉及的对象名称 Sys.des_encrypted_password CREATE、ALTER、DROP 创建或修改用户时，返回加密后的用户密码 删除触发器 DROPTIRGGER触发器名 触发器的创建者 需要DROPANYTIRGGER系统权限 可以通过命令设置触发器的可用状态，使其暂时关闭或重新打开 ALTERTRIGGER触发器名 DISABLE|ENABLE结束