《Oracle数据库-处理数据ch.ppt》由会员分享，可在线阅读，更多相关《Oracle数据库-处理数据ch.ppt（53页珍藏版）》请在三一办公上搜索。
1、处理数据,目标,通过本章学习，您将可以:使用 DML 语句向表中插入数据更新表中数据从表中删除数据将表中数据和并控制事务,数据控制语言,DML 可以在下列条件下执行:向表中插入数据修改现存数据删除现存数据事务是由完成若干项工作的DML语句组成的。,插入数据,DEPARTMENTS,新行,INSERT 语句语法,使用 INSERT 语句向表中插入数据。使用这种语法一次只能向表中插入一条数据。,INSERT INTOtable(column,column.)VALUES(value,value.);,插入数据,为每一列添加一个新值。按列的默认顺序列出各个列的值。在 INSERT 子句中随意列出列名
2、和他们的值。字符和日期型数据应包含在单引号中。,INSERT INTO departments(department_id,department_name,manager_id,location_id)VALUES(70,Public Relations,100,1700);1 row created.,INSERT INTOdepartmentsVALUES(100,Finance,NULL,NULL);1 row created.,INSERT INTOdepartments(department_id,department_name)VALUES(30,Purchasing);1 row
3、 created.,向表中插入空值,隐式方式:在列名表中省略该列的值。,显示方式:在VALUES 子句中指定空值。,INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)VALUES(113,Louis,Popp,LPOPP,515.124.4567,SYSDATE,AC_ACCOUNT,6900,NULL,205,100);1 row created.,插入指定的值,SYS
4、DATE 记录当前系统的日期和时间。,INSERT INTO employeesVALUES(114,Den,Raphealy,DRAPHEAL,515.127.4561,TO_DATE(FEB 3,1999,MON DD,YYYY),AC_ACCOUNT,11000,NULL,100,30);1 row created.,插入指定的值,加入新员工检查插入的数据,INSERT INTO departments(department_id,department_name,location_id)VALUES(,创建脚本,在SQL 语句中使用&变量指定列值。&变量放在VALUES子句中。,在 IN
5、SERT 语句中加入子查询。不必书写 VALUES 子句。子查询中的值列表应于 INSERT 子句中的列名对应。,INSERT INTO sales_reps(id,name,salary,commission_pct)SELECT employee_id,last_name,salary,commission_pct FROM employees WHERE job_id LIKE%REP%;4 rows created.,从其它表中拷贝数据,更新数据,EMPLOYEES,更新 EMPLOYEES 表,UPDATE 语句语法,使用 UPDATE 语句更新数据。可以一次更新多条数据。,UPDA
6、TEtableSETcolumn=value,column=value,.WHERE condition;,UPDATE employeesSET department_id=70WHERE employee_id=113;1 row updated.,使用 WHERE 子句指定需要更新的数据。如果省略WHERE子句，则表中的所有数据都将被更新。,更新数据,UPDATE copy_empSET department_id=110;22 rows updated.,UPDATE employeesSET job_id=(SELECT job_id FROM employees WHERE emp
7、loyee_id=205),salary=(SELECT salary FROM employees WHERE employee_id=205)WHERE employee_id=114;1 row updated.,在UPDATE语句中使用子查询,更新 114号员工的工作和工资使其与 205号员工相同。,UPDATE copy_empSET department_id=(SELECT department_id FROM employees WHERE employee_id=100)WHERE job_id=(SELECT job_id FROM employees WHERE empl
8、oyee_id=200);1 row updated.,在UPDATE语句中使用子查询,在 UPDATE 中使用子查询，使更新基于另一个表中的数据。,UPDATE employees*ERROR at line 1:ORA-02291:integrity constraint(HR.EMP_DEPT_FK)violated-parent key not found,UPDATE employeesSET department_id=55WHERE department_id=110;,更新中的数据完整性错误,不存在 55 号部门,从表DEPARTMENTS 中删除一条记录。,删除数据,DEPA
9、RTMENTS,DELETE 语句,使用 DELETE 语句从表中删除数据。,DELETE FROM tableWHERE condition;,使用WHERE 子句指定删除的记录。如果省略WHERE子句，则表中的全部数据将被删除。,删除数据,DELETE FROM departments WHERE department_name=Finance;1 row deleted.,DELETE FROM copy_emp;22 rows deleted.,DELETE FROM employeesWHERE department_id=(SELECT department_id FROM dep
10、artments WHERE department_name LIKE%Public%);1 row deleted.,在 DELETE 中使用子查询,在 DELETE 中使用子查询，使删除基于另一个表中的数据。,删除中的数据完整性错误,DELETE FROM departmentsWHERE department_id=60;,DELETE FROM departments*ERROR at line 1:ORA-02292:integrity constraint(HR.EMP_DEPT_FK)violated-child record found,You cannot delete a
11、row that contains a primary key that is used as a foreign key in another table.,在INSERT语句中使用子查询,INSERT INTO(SELECT employee_id,last_name,email,hire_date,job_id,salary,department_id FROM employees WHERE department_id=50)VALUES(99999,Taylor,DTAYLOR,TO_DATE(07-JUN-99,DD-MON-RR),ST_CLERK,5000,50);1 row
12、created.,在INSERT语句中使用子查询,Verify the results,SELECT employee_id,last_name,email,hire_date,job_id,salary,department_idFROM employeesWHERE department_id=50;,在DML语句中使用 WITH CHECK OPTION 关键字,使用子查询表示 DML 语句中使用的表WITH CHECK OPTION 关键字避免修改子查询范围外的数据,INSERT INTO(SELECT employee_id,last_name,email,hire_date,job
13、_id,salary FROM employees WHERE department_id=50 WITH CHECK OPTION)VALUES(99998,Smith,JSMITH,TO_DATE(07-JUN-99,DD-MON-RR),ST_CLERK,5000);INSERT INTO*ERROR at line 1:ORA-01402:view WITH CHECK OPTION where-clause violation,显式默认值,使用 DEFAULT 关键字表示默认值可以使用显示默认值控制默认值的使用显示默认值可以在 INSERT 和 UPDATE 语句中使用,显示使用默认
14、值,INSERT INTO departments(department_id,department_name,manager_id)VALUES(300,Engineering,DEFAULT);,UPDATE departments SET manager_id=DEFAULT WHERE department_id=10;,在插入操作中使用默认值:在更新操作中使用默认值:,合并语句,按照指定的条件执行插入或更新操作如果满足条件的行存在，执行更新操作；否则执行插入操作:避免多次重复执行插入和删除操作提高效率而且使用方便在数据仓库应用中经常使用,合并语句的语法,可以使用merge语句，根据指
15、定的条件进行插入或更新操作,MERGE INTO table_name table_alias USING(table|view|sub_query)alias ON(join condition)WHEN MATCHED THEN UPDATE SET col1=col_val1,col2=col2_val WHEN NOT MATCHED THEN INSERT(column_list)VALUES(column_values);,MERGE INTO copy_emp c USING employees e ON(c.employee_id=e.employee_id)WHEN MATC
16、HED THEN UPDATE SET c.first_name=e.first_name,c.last_name=e.last_name,.c.department_id=e.department_idWHEN NOT MATCHED THEN INSERT VALUES(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.hire_date,e.job_id,e.salary,mission_pct,e.manager_id,e.department_id);,合并语句举例,在对表COPY_EMP使用merge语句
17、，根据指定的条件从表 EMPLOYEES中插入或更新数据。,合并语句举例,MERGE INTO copy_emp c USING employees e ON(c.employee_id=e.employee_id)WHEN MATCHED THEN UPDATE SET.WHEN NOT MATCHED THEN INSERT VALUES.;,SELECT*FROM COPY_EMP;no rows selected,SELECT*FROM COPY_EMP;20 rows selected.,数据库事务,数据库事务由以下的部分组成:一个或多个DML 语句一个 DDL 语句一个 DCL 语
18、句,数据库事务,以第一个 DML 语句的执行作为开始以下面的其中之一作为结束:COMMIT 或 ROLLBACK 语句DDL 或 DCL 语句（自动提交）用户会话正常结束系统异常终了,COMMIT和ROLLBACK语句的优点,使用COMMIT 和 ROLLBACK语句,我们可以:确保数据完整性。数据改变被提交之前预览。将逻辑上相关的操作分组。,控制事务,ROLLBACK to SAVEPOINT B,ROLLBACK to SAVEPOINT A,ROLLBACK,UPDATE.SAVEPOINT update_done;Savepoint created.INSERT.ROLLBACK TO
19、 update_done;Rollback complete.,回滚到保留点,使用 SAVEPOINT 语句在当前事务中创建保存点。使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。,自动提交在以下情况中执行:DDL 语句。DCL 语句。不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话。会话异常结束或系统异常会导致自动回滚。,事务进程,提交或回滚前的数据状态,改变前的数据状态是可以恢复的执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正其他用户不能看到当前用户所做的改变，直到当前用户结束事务。DML语句所涉及到的行被锁定，其他用户
20、不能操作。,提交后的数据状态,数据的改变已经被保存到数据库中。改变前的数据已经丢失。所有用户可以看到结果。锁被释放，其他用户可以操作涉及到的数据。所有保存点被释放。,COMMIT;Commit complete.,改变数据提交改变,DELETE FROM employeesWHERE employee_id=99999;1 row deleted.INSERT INTO departments VALUES(290,Corporate Tax,NULL,1700);1 row inserted.,提交数据,数据回滚后的状态,使用 ROLLBACK 语句可使数据变化失效:数据改变被取消。修改前的
21、数据状态可以被恢复。锁被释放。,DELETE FROM copy_emp;22 rows deleted.ROLLBACK;Rollback complete.,语句级回滚,单独 DML 语句执行失败时，只有该语句被回滚。Oracle 服务器自动创建一个隐式的保留点。其他数据改变仍被保留。用户应执行 COMMIT 或 ROLLBACK 语句结束事务。,读一致性,读一致性为数据提供一个一致的视图。一个用户的对数据的改变不会影响其他用户的改变。对于相同的数据读一致性保证:查询不等待修改。修改不等待查询。,SELECT*FROM userA.employees;,读一致性,UPDATE employ
22、eesSET salary=7000WHERE last_name=Goyal;,数据块,回滚段,改变和未改变的数据,改变之前的数据,User A,User B,快照,锁,Oracle 数据库中，锁是:并行事务中避免资源竞争。避免用户动作。自动使用最低级别的限制。在事务结束结束前存在。两种类型:显示和隐式。,锁,两种模式:独占锁:屏蔽其他用户。共享锁:允许其他用户操作。高级别的数据并发性:DML:表共享，行独占Queries:不需要加锁DDL:保护对象定义提交或回滚后锁被释放。,总结,功能插入修正删除合并提交保存点回滚,语句INSERTUPDATEDELETEMERGECOMMITSAVEPO
23、INTROLLBACK,通过本章学习,您应学会如何使用DML语句改变数据和事务控制,输出 时间 会话1 会话2,t1,t2,t3,t4,t5,SELECT salary FROM employeesWHERE last_name=King;,24000,UPDATE employeesSET salary=salary+10000WHERE last_name=King;,24000,COMMIT;,34000,SELECT salary FROM employeesWHERE last_name=King;,SELECT salary FROM employeesWHERE last_name=King;,读一致性举例,
