Oracle SQL&PLSQL课件：第8章 数据操作.ppt

单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Oracle SQL&PL/SQL,第8,章 数据操作,向表中插入数据；,更新表中数据；,删除表中数据；,控制事务。,本章要点,数据操作语言,当 您有以下操作，,DML,语句便执行：,向表中添加新记录,修改表中已存在的记录,从表中删除数据,事务由若干操作上逻辑不可分割的,DML,语句构成。,追加数据,DEPT,DEPTNO DNAME LOC,-,10ACCOUNTINGNEW YORK,20RESEARCHDALLAS,30SALESCHICAGO,40OPERATIONSBOSTON,New row,50,DEVELOPMENT,DETROIT,DEPT,DEPTNO DNAME LOC,-,10ACCOUNTINGNEW YORK,20RESEARCHDALLAS,30SALESCHICAGO,40OPERATIONSBOSTON,“insert a new row,into DEPT table”,50,DEVELOPMENT,DETROIT,INSERT,语句,使用,INSERT,语句向表中追加数据。,采用这种语法一次只能追加一条记录,INSERT INTO,table,(,column,column.,),VALUES,(value,value.,),;,插入新记录,插入的记录需要包含每一列的值,按照表中列的默认顺序列出值,在,INSERT,子句中随意地列出列名,字符和日期需要用单引号扩起来,SQL INSERT INTOdept(,deptno,dname,loc),2 VALUES(50,DEVELOPMENT,DETROIT);,1 row created.,插入含空值,NULL,的记录,隐含法,:,在列名列表中忽略该列,。,SQL INSERT INTOdept(,deptno,dname,),2 VALUES(60,MIS);,1 row created.,显示法,:,指定,NULL,关键字。,SQL INSERT INTOdept,2 VALUES(70,FINANCE,NULL);,1 row created.,插入特殊值,SYSDATE,函数记录当前日期和时间,SQL INSERT INTO,emp,(,empno,ename,job,2mgr,hiredate,sal,comm,3,deptno,),4 VALUES(7196,GREEN,SALESMAN,57782,SYSDATE,2000,NULL,610);,1 row created.,插入特殊值,添加一个雇员,.,SQL INSERT INTO,emp,2 VALUES (2296,AROMANO,SALESMAN,7782,3 TO_DATE(FEB 3,97,MON DD,YY),4 1300,NULL,10);,1 row created.,验证一下添加是否成功,.,EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO,-,2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10,使用替代变量插入值,使用,SQL*Plus,的替代参数编写一段交互式脚本。,SQL INSERT INTOdept(,deptno,dname,loc),2 VALUES (&department_id,3 ,Enter value for department_id:,80,Enter value for department_name:,EDUCATION,Enter value for location:,ATLANTA,1 row created.,定制友好的系统提示,ACCEPT,将值存储在变量中,PROMPT,系统提示文本,.,ACCEPTdepartment_id PROMPT,请输入部门编号：,ACCEPT department_name PROMPT,请输入部门名称：,ACCEPTlocation PROMPT,请输入部门位置：,INSERT INTO dept(,deptno,dname,loc),VALUES (&department_id,&department_name,从另一个表复制数据,在,INSERT,语句中使用子查询,不使用,VALUES,子句,INSERT,子句中列的数量和类型必须和子查询中列的数量和类型相匹配,SQL INSERT INTO managers,2 SELECT*,3 FROM,emp,4 WHERE job=MANAGER;,3 rows created.,修改表中的数据,EMP,“update a row,in EMP table”,EMP,EMPNO ENAME JOB.DEPTNO,7839KINGPRESIDENT 10,7698BLAKEMANAGER 30,7782CLARKMANAGER 10,7566JONESMANAGER 20,.,20,EMPNO ENAME JOB.DEPTNO,7839KINGPRESIDENT 10,7698BLAKEMANAGER 30,7782CLARKMANAGER 10,7566JONESMANAGER 20,.,UPDATE,语句,用,UPDATE,语句更新表中的记录,如果需要的话可以一次性更新多个记录,UPDATE,table,SET,column,=,value,column,=,value,WHERE,condition,;,更新表中记录,使用,WHERE,子句指定要更新的记录,如果要更新所有记录,WHERE,子句可以忽略,SQL UPDATE,emp,2 SET,deptno,=20,3 WHERE,empno,=7782;,1 row updated.,SQL UPDATE employee,2 SET,deptno,=20;,14 rows updated.,使用多列子查询更新记录,SQL UPDATE,emp,2 SET (job,deptno,)=,3 (SELECT job,deptno,4 FROM,emp,5 WHERE,empno,=7499),6 WHERE,empno,=7698;,1 row updated.,用编号为,7499,雇员的工作名称及部门编号更新编号为,7698,雇员相对应的信息,.,在另一个表的基础上更新记录,在,UPDATE,语句中使用子查询，并且子查询访问的是另一个表，用另一个表的数据更新数据。,SQLUPDATEemployee,2SET,deptno,=(SELECT,deptno,3FROM,emp,4WHERE,empno,=7788),5WHEREjob =(SELECTjob,6FROM,emp,7WHERE,empno,=7788);,2 rows updated.,UPDATE,emp,*,ERROR,位于第,1 行:,ORA-02291:,违反完整约束条件,(,SCOTT.FK_DEPTNO)-,未找到父项关键字,SQL UPDATE,emp,2 SET,deptno,=55,3 WHERE,deptno,=10;,更新记录时的完整性约束错误,55,号部门不存在,“,从,DEPT,表中删除一行,”,删除表中记录,DEPT,DEPTNO DNAME LOC,-,10ACCOUNTINGNEW YORK,20RESEARCHDALLAS,30SALESCHICAGO,40OPERATIONSBOSTON,50,DEVELOPMENT,DETROIT,60MIS,.,DEPT,DEPTNO DNAME LOC,-,10ACCOUNTINGNEW YORK,20RESEARCHDALLAS,30SALESCHICAGO,40OPERATIONSBOSTON,60MIS,.,DELETE,语句,使用,DELETE,语句删除表中记录,DELETE FROM,table,WHERE,condition,;,在,WHERE,子句中指定哪些记录要被删除,如果省略,WHERE,子句，表中所有记录将被删除,删除表中记录,SQL DELETE FROMemployee,2 WHERE job=,CLERK;,已删除,4行,SQL DELETE FROMemployee;,4 rows deleted.,基于另一个表删除本表记录,在,DELETE,语句中使用子查询可以基于另一个表删除本表记录,。,SQL DELETE FROMemployee,2 WHERE,deptno,=,3 (SELECT,deptno,4 FROM dept,5 WHERE,dname,=SALES);,6 rows deleted.,删除记录时的完整性约束错误,SQL DELETE FROMdept,2 WHERE,deptno,=10;,DELETE FROM dept,*,ERROR,位于第,1 行:,ORA-02292:,违反完整约束条件,(,SCOTT.FK_DEPTNO)-,已找到子记录日志,如果数据行主键作为外键被其它表所引用，则不能删除该数据行。,数据库事务,数据库事务可由产生数据一致性变化的,DML,语句组成,一个,DDL,语句组成,一个,DCL,语句组成,Oracle,服务器基于事务确保数据一致性。,数据库事务,开始于第一条可执行,的,SQL,语句的执行,在下列情况下结束,：,COMMIT,或,ROLLBACK,DDL,或,DCL,语句被执行，或自动提交,用户退出,系统崩溃,COMMIT,和,ROLLBACK,优点,确保数据的一致性,产生永久性变化之前可以预览数据的变化,聚合一系列逻辑相关的操作,DELETE,事务控制,事务,存储点,A,ROLLBACK,到,存储点,B,DELETE,存储点,B,COMMIT,INSERT,UPDATE,ROLLBACK,到,存储点,A,INSERT,UPDATE,INSERT,ROLLBACK,INSERT,下列情况会自动提交：,执行,DDL,语句,执行,DCL,语句,正常退出,SQL*Plus，,并且没有明确地执行,COMMIT,或,ROLLBACK,在遇到,SQL*Plus,异常结束或系统故障都会自动回滚,隐含的事务处理,执行,COMMIT,或,ROLLBACK,前的数据状态,数据变化前的状态可以被恢复,当前用户可以使用,SELECT,语句来验证,DML,操作后的结果,其它用户不能查看由当前用户的,DML,操作结果,受影响记录被锁定，也就是其它用户不能改变受影响记录中的数据,COMMIT,后的状态,在数据库中数据变化成为永久性的,先前的数据状态永久性的消失,所有用户都可以查询提交后的结果,锁定的记录被释放，可以有效地被其他用户操作,所有的存储节点被清除,提交数据,SQL UPDATE,emp,2 SET,deptno,=10,3 WHERE,empno,=7782;,1 row updated.,产生数据变化,提交数据变化,.,SQL COMMIT;,Commit complete.,ROLLBACK,后的数据状态,使用,ROLLBACK,可以放弃所有悬而未决的变化。,数据变化是可以撤销的,先前的数据状态被恢复,锁定的记录被释放,SQL DELETE FROMemployee;,14 rows deleted.,SQL ROLLBACK;,Rollback complete.,回滚到某个标记状态,在当前事务中使用,SAVEPOINT,语句创建一个标记状态,使用,ROLLBACK TO SAVEPOINT,语句回滚到指定的标记状态,SQL UPDATE.,SQL SAVEPOINT update_done;,Savepoint,created.,SQL INSERT.,SQL ROLLBACK TO update_done;,Rollback complete.,语句级回滚,如果一个,DML,语句在执行过程中出错，那么仅仅是这条语句中的操作被回滚,Oracle,服务器完成一个隐含的保存节点操作,所有其它变化仍然保留,.,用户应该使用,COMMIT,或,ROLLBACK,语句明确的终止该事务,读取的一致性,读取的一致性可以保证任何时候数据查询的一致。,一个用户所产生的数据变化不会和另一个用户所产生的数据变化相冲突。,确保对于同一数据：,读取者不用等待写入者,写入者不用等待读取者,读取一致性的实现,UPDATE,emp,SET,sal,=2000,WHERE,ename,=,SCOTT,;,数据块,回滚段,发生变化,及未发生,变化的数据,变化前的老数据,用户,A,用户,B,读取一致的镜像,SELECT *FROM,emp,;,锁定,Oracle,锁定：,阻止并发事务之间产生的破坏性交互作用,不需要用户干预,自动使用最低级别限制,锁定时间和事务持续时间相同,有两种基本模式：,排它,共享,