安徽省卫生计生网站医共体建设子域名在线查询

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

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

--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 empSET comm = CASE WHEN comm IS NULL THEN sal * 0.15 ELSE comm * 1.15 ENDWHERE deptno = 20;UPDATE empSET sal  = CASE WHEN sal IS NULL THEN 9988 ELSE sal * 1.15 ENDWHERE deptno = 20;--违反完整约束条件示例
update emp set deptno=99 where ename='PANDENG';--10.2.2 使用子查询更新多行记录
--UPDATE的相关子查询更新的示例
UPDATE emp xSET x.sal = (SELECT AVG(y.sal) FROM emp y WHERE y.deptno = x.deptno)WHERE x.ename = 'PANDENG';--在WHERE子句中使用相关子查询的示例
UPDATE emp aSET a.sal = a.sal * 1.1WHERE EXISTS (SELECT 1FROM salgrade bWHERE a.sal BETWEEN b.losal AND b.hisalAND GRADE = 5);--使用非相关子查询更新员工工资
UPDATE empSET sal = (SELECT sal FROM emp WHERE empno = 7782)WHERE empno = 7369;--使用子查询更新多列UPDATE emp xSET (x.sal, x.comm) = (SELECT AVG(y.sal), MAX(y.comm)FROM emp yWHERE y.deptno = x.deptno)WHERE x.empno = 7369;--使用子查询更新的更详细的例子（这个例子花了我半个多小时才理解，加深了我对相关子查询的理解）
UPDATE emp aSET deptno = (SELECT deptno FROM dept WHERE loc = 'NEW YORK'),(sal, comm) = (SELECT 1.1 * AVG(sal), 1.5 * AVG(comm)FROM emp bWHERE a.deptno = b.deptno)WHERE deptno IN (SELECT deptnoFROM deptWHERE 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, 750select * from emp for update;
rollback;             --由于没有一对一关系，因此在更新时将出现异常
UPDATE /*+BYPASS_UJVC*/  (SELECT a.empno, a.salFROM emp a, salgrade bWHERE a.sal BETWEEN b.losal AND b.hisalAND grade = 3) empsalSET empsal.sal = empsal.sal * 1.12;--使用内联视图更新的例子，正常使用（这个例子也挺有意思，可以实现两张表的局部同步）
--From子句中包含的表要包含主键，即，该表为键值保存表
UPDATE (SELECT x.sal  sal,y.sal  sal_history,x.comm comm,y.comm comm_historyFROM emp x, emp_history yWHERE 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_historyFROM emp_his_2 x, emp_history yWHERE x.empno = y.empno)SET sal_history = sal, comm_history = comm;--使用内联视图限制可更新的列（本语句暂未调试）
UPDATE (SELECT emp_id, fname, lname, dept_id, manager_emp_id FROM employee) empSET emp.manager_emp_id = 11WHERE emp.dept_id = 4;--使用内联视图限制可更新的列
UPDATE (SELECT empno, sal, comm FROM emp) empsalSET empsal.sal = empsal.sal * 1.1WHERE empsal.empno = 7369;--更新内联视图并不存在的列(本语句会将报错)
UPDATE (SELECT empno, sal, comm FROM emp) empsalSET empsal.ename='任伯安'WHERE empsal.empno = 7369;--违反WITH CHECK OPTION约束的UPDATE语句，更新行数：0
UPDATE (SELECT empno, sal, comm,deptnoFROM empWHERE deptno = (SELECT deptno FROM dept WHERE loc = '芝加哥') WITHCHECK OPTION) empsalSET empsal.sal = empsal.sal * 1.1WHERE deptno = 30;--未违反WITH CHECK OPTION约束的UPDATE语句，可成功更新记录
UPDATE (SELECT empno, sal, comm,deptnoFROM empWHERE deptno = (SELECT deptno FROM dept WHERE loc = '浦东' )WITHCHECK OPTION ) empsalSET empsal.sal = empsal.sal * 1.1WHERE 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子句的示例
DECLAREv_book_id   NUMBER;v_book_name VARCHAR2(100);
BEGIN--插入一本新的图书INSERT INTO booksVALUES(orders_seq.NEXTVAL, '康熙大帝', 3)RETURNING book_id INTO v_book_id;DBMS_OUTPUT.put_line('新插入的图书的book_id=' || v_book_id);--更新图书的名称UPDATE booksSET book_name = 'Oracle PL/SQL从入门到精通'WHERE book_id = 1021RETURNING book_name INTO v_book_name;DBMS_OUTPUT.put_line('更新的图书名称的book_name=' || v_book_name);--删除图书DELETE FROM booksWHERE book_name = '云图'RETURNING book_id INTO v_book_id;--显示图书信息DBMS_OUTPUT.put_line('删除的图书的book_id=' || v_book_id);
END;
/select * from books;