源码-Oracle数据库管理-第十章-数据表操作-Part 3（更新数据）

数据库玩的就是数据的增删改查，这一节就是学习如何“改”数据的。

这里的例子还是很精彩的，让我的数据库技能又一次得到了提升！

--TBC 2016-10-09
--10.2 更新数据
--10.2.1 使用Update语句
--简单的UPDATE语句示例
UPDATE emp SET deptno=30 WHERE empno=5093;
select * from emp where deptno=20;

--多字段的UPDATE语句更新示例(原来=号后面可连接一个case when语句啊，长见识了，哈哈)
UPDATE emp
   SET comm = CASE WHEN comm IS NULL THEN sal * 0.15 ELSE comm * 1.15 END
 WHERE deptno = 20;

UPDATE emp
   SET sal  = CASE WHEN sal IS NULL THEN 9988 ELSE sal * 1.15 END
 WHERE deptno = 20;

--违反完整约束条件示例
update emp set deptno=99 where ename='PANDENG';

--10.2.2 使用子查询更新多行记录
--UPDATE的相关子查询更新的示例
UPDATE emp x
   SET x.sal = (SELECT AVG(y.sal) FROM emp y WHERE y.deptno = x.deptno)
 WHERE x.ename = 'PANDENG';

--在WHERE子句中使用相关子查询的示例
UPDATE emp a
   SET a.sal = a.sal * 1.1
 WHERE EXISTS (SELECT 1
          FROM salgrade b
         WHERE a.sal BETWEEN b.losal AND b.hisal
           AND GRADE = 5);


--使用非相关子查询更新员工工资
UPDATE emp
   SET sal = (SELECT sal FROM emp WHERE empno = 7782)
 WHERE empno = 7369;

--使用子查询更新多列
 UPDATE emp x
    SET (x.sal, x.comm) = (SELECT AVG(y.sal), MAX(y.comm)
                             FROM emp y
                            WHERE y.deptno = x.deptno)
  WHERE x.empno = 7369;

--使用子查询更新的更详细的例子（这个例子花了我半个多小时才理解，加深了我对相关子查询的理解）
UPDATE emp a
   SET deptno = (SELECT deptno FROM dept WHERE loc = 'NEW YORK'),
       (sal, comm) = (SELECT 1.1 * AVG(sal), 1.5 * AVG(comm)
                             FROM emp b
                            WHERE a.deptno = b.deptno)
 WHERE deptno IN (SELECT deptno
                    FROM dept
                   WHERE loc = 'DALLAS'
                      OR loc = '上海浦东');
                      
select * from emp where deptno in (select deptno from dept where loc = 'DALLAS' OR loc = '上海浦东');  
select * from dept;      

select * from emp where deptno=20 or deptno=60; 
SELECT 1.1 * AVG(sal), 1.5 * AVG(comm)
                             FROM emp where deptno=20; --52400, 3939
SELECT 1.1 * AVG(sal), 1.5 * AVG(comm)
                             FROM emp where deptno=60; --12302, 450
SELECT 1.1 * AVG(sal), 1.5 * AVG(comm)
                             FROM emp where deptno=10; --5500, 750
                             
select * from emp for update;
rollback;             

--由于没有一对一关系，因此在更新时将出现异常
UPDATE /*+BYPASS_UJVC*/  (SELECT a.empno, a.sal
        FROM emp a, salgrade b
       WHERE a.sal BETWEEN b.losal AND b.hisal
         AND grade = 3) empsal
   SET empsal.sal = empsal.sal * 1.12;
   

--使用内联视图更新的例子，正常使用（这个例子也挺有意思，可以实现两张表的局部同步）
--From子句中包含的表要包含主键，即，该表为键值保存表
UPDATE (SELECT x.sal  sal,
               y.sal  sal_history,
               x.comm comm,
               y.comm comm_history
          FROM emp x, emp_history y
         WHERE x.empno = y.empno)
   SET sal_history = sal, comm_history = comm;

select * from emp_history;

--没有主键的列进行更新，将出现错误（本语句暂未调试）
UPDATE (SELECT x.sal  sal,
               y.sal  sal_history,
               x.comm comm,
               y.comm comm_history
          FROM emp_his_2 x, emp_history y
         WHERE x.empno = y.empno)
   SET sal_history = sal, comm_history = comm;
   

--使用内联视图限制可更新的列（本语句暂未调试）
UPDATE (SELECT emp_id, fname, lname, dept_id, manager_emp_id 

  FROM employee) emp

SET emp.manager_emp_id = 11

WHERE emp.dept_id = 4;

--使用内联视图限制可更新的列
UPDATE (SELECT empno, sal, comm FROM emp) empsal
   SET empsal.sal = empsal.sal * 1.1
 WHERE empsal.empno = 7369;
 
--更新内联视图并不存在的列(本语句会将报错)
UPDATE (SELECT empno, sal, comm FROM emp) empsal
   SET empsal.ename='任伯安'
 WHERE empsal.empno = 7369;

--违反WITH CHECK OPTION约束的UPDATE语句，更新行数：0
UPDATE (SELECT empno, sal, comm,deptno
          FROM emp
         WHERE deptno = (SELECT deptno FROM dept WHERE loc = '芝加哥') WITH
         CHECK OPTION) empsal
   SET empsal.sal = empsal.sal * 1.1
 WHERE deptno = 30;


--未违反WITH CHECK OPTION约束的UPDATE语句，可成功更新记录
UPDATE (SELECT empno, sal, comm,deptno
          FROM emp
         WHERE deptno = (SELECT deptno FROM dept WHERE loc = '浦东' )WITH
         CHECK OPTION ) empsal
   SET empsal.sal = empsal.sal * 1.1
 WHERE deptno = 30;

select * from dept;


--10.2.3 使用returning 子句 
--RETURNING子句的功能
INSERT INTO <table> (c1, c2, .., cn) VALUES (v1, v2, .., vn) RETURNING <expression> INTO <variables>
UPDATE <table> SET (c1) = (v1), (c2) = (v2), (cn) = (vn) WHERE <condition> RETURNING <expression> INTO <variables>
DELETE FROM <table> WHERE <condition> RETURNING <expression> INTO <variables>

--RETURNING INTO子句的示例
DECLARE
  v_book_id   NUMBER;
  v_book_name VARCHAR2(100);
BEGIN
  --插入一本新的图书
  INSERT INTO books
  VALUES
    (orders_seq.NEXTVAL, '康熙大帝', 3)
  RETURNING book_id INTO v_book_id;
  DBMS_OUTPUT.put_line('新插入的图书的book_id=' || v_book_id);
  --更新图书的名称
  UPDATE books
     SET book_name = 'Oracle PL/SQL从入门到精通'
   WHERE book_id = 1021
  RETURNING book_name INTO v_book_name;
  DBMS_OUTPUT.put_line('更新的图书名称的book_name=' || v_book_name);
  --删除图书
  DELETE FROM books
   WHERE book_name = '云图'
  RETURNING book_id INTO v_book_id;
  --显示图书信息
  DBMS_OUTPUT.put_line('删除的图书的book_id=' || v_book_id);
END;
/

select * from books;