9更新及事务处理
<h3>准备工作：复制表</h3>
<pre><code>CREATE TABLE myemp AS SELECT * FROM emp ;
SELECT * FROM TAB ;
SELECT * FROM myemp;</code></pre>
<h3>数据的增加操作</h3>
<h4>插入数据两种方法</h4>
<blockquote>
<ul>
<li>INSERT INTO 表名 [(列1，列2...列n)] VALUES(值1，值2....值n)；</li>
<li>INSERT INTO 表名 [(列1，列2...列n)] 子查询 ；</li>
</ul>
</blockquote>
<pre><code>-- 增加两条数据
SELECT * FROM myemp FOR UPDATE ;
INSERT INTO myemp (empno,job,hiredate,ename,mgr,sal,comm,deptno) VALUES(8888,'CLERK',SYSDATE,'李兴华',7369,800,100,20);
--简便写法，不推荐使用，因为字段要对应、后期维护都不方便
INSERT INTO myemp VALUES(8899,'魔乐科技','MANAGER',7369,to_date('1981-09-19','yyyy-mm-dd'),1000,100,20);
-- 增加两条数据，有空值的。
INSERT INTO myemp(empno,ename,job,hiredate,sal) VALUES(6612,'李楠','CLERK',to_date('1989-9-19','yyyy-mm-dd'),600);
INSERT INTO myemp VALUES(6616,'李楠','CLERK',NULL,to_date('1989-09-19','yyyy-mm-dd'),1000,NULL,NULL);
--增加子查询结果数据，详细、简便写法
INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno) SELECT * FROM emp WHERE deptno=20 ;
INSERT INTO myemp SELECT * from emp WHERE deptno=10 ;</code></pre>
<h5>多表插入</h5>
<ul>
<li>作用：一条INSERT语句可以完成向多张表的插入任务（Multitable insert）。有两种形式：insert all与insert first，</li>
</ul>
<p><a href="https://www.cnblogs.com/zhaochunyi/p/10867733.html">多表插入教程</a></p>
<h3>数据更新操作</h3>
<pre><code>UPDATE myemp SET sal=3000,comm=500 WHERE empno=7369 ;
SELECT * FROM myemp WHERE empno=7369 ;
SELECT * FROM myemp WHERE sal<(SELECT AVG(sal) FROM myemp);
UPDATE myemp SET sal=sal*1.2 WHERE sal<(SELECT AVG(sal) FROM myemp);
UPDATE myemp SET sal=sal*1.1 ;
--新知识，使用已有的数据更新数据表
UPDATE myemp SET(job,sal,hiredate)=(SELECT job,sal,hiredate FROM myemp WHERE empno=7839) WHERE empno=7369 ;
SELECT * FROM myemp WHERE empno=7839 OR empno=7369;</code></pre>
<h3>数据的删除操作</h3>
<pre><code>DELETE FROM myemp WHERE empno=7566 ;
SELECT * FROM myemp WHERE empno=7566 ;
DELETE FROM myemp WHERE deptno=30 ;
SELECT * FROM myemp WHERE deptno=30 ;
DELETE FROM myemp WHERE empno IN (7369,7566,7788);
SELECT * FROM myemp WHERE empno IN (7369,7566,7788);
DELETE FROM myemp WHERE to_char(hiredate,'yyyy')='1987';
SELECT * FROM myemp WHERE to_char(hiredate,'yyyy')='1987';
DELETE FROM myemp WHERE sal=(SELECT MAX(sal) FROM myemp);
SELECT * FROM myemp WHERE sal=(SELECT MAX(sal) FROM myemp);</code></pre>
<h3>事务处理，P218</h3>
<pre><code>DELETE FROM myemp ;
INSERT INTO myemp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO myemp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO myemp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO myemp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO myemp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO myemp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO myemp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO myemp VALUES (7788,'SCOTT','ANALYST',7566,to_date('19-04-1987','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO myemp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO myemp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO myemp VALUES (7876,'ADAMS','CLERK',7788,to_date('23-05-1987','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO myemp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO myemp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO myemp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
select * from myemp where months_between(sysdate,hiredate)/12>32;
select months_between(SYSDATE,hiredate)/12 FROM myemp;</code></pre>
<h3>事务处理</h3>
<ul>
<li>事务四个特性：原子性、一致性、隔离性(独立性)、持久性</li>
</ul>
<pre><code>--在SQLPlus窗口完成以下操作
--在第一个窗口：
SELECT COUNT(*) FROM myemp; --14
DELETE FROM myemp; --完成
SELECT COUNT(*) FROM myemp; --0
ROLLBACK;/COMMIT; --提交或者回滚
--在第二个窗口：
SELECT COUNT(*) FROM myemp;--14
--在第一个窗口提交前，并不会影响第二个窗口（其他窗口的数据）</code></pre>
<p><strong>insert 的数据没有同步：</strong>
是由事务造成的。对每个SESSION来说，每一个数据库的更新操作在事务没有被提交之前都只是保存在缓冲区中，并不会真正向数据库中发出命令，如有问题可以回滚。</p>
<pre><code>savepoint sp_a;--设置存储点，存储点名为sp_a
rollback to sp_a ;--滚回到指定的存储点
set autocommit NO/OFF ;--设置自动提交，自动同步到不同的会话。</code></pre>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/62b8fdea218971674fe159bde433e518?showdoc=.jpg" alt="" /></p>
<h3>锁</h3>
<blockquote>
<p>利用事务可以保证数据完整性及有效性，但是事务的核心却是锁。</p>
</blockquote>
<p><strong>锁：</strong>
某个SECSSION在更新数据，会将操作的数据表/数据进行锁定，其他的SESSION唯有等待该SESSION提交事务或者回滚事务后才能进行操作，这个等待的过程就可以称之为锁。</p>
<p><strong>示例：</strong>
如下图，第一个 SESSION 执行了 <code>for update</code> ，在第一个 SESSION 提交或者回滚之前，第二个 SESSION 执行 <code>for update</code>只能停留在等待界面。</p>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/30cae866d1198c54e8874a2146fcf73f?showdoc=.jpg" alt="" /></p>
<h4>锁分为两类：行级锁定、表级锁定</h4>
<ul>
<li>行级锁定：对当前事务中的一行数据以独占的方式进行锁定，在其他事务结束前，一直在等待，如上图所示。</li>
<li>表级锁定：对整张数据表进行锁定，只允许当前事务访问数据表，其他事务无法访问。</li>
</ul>
<blockquote>
<p>行级锁定： update\insert\delete\select ... for update。</p>
</blockquote>
<p>示例：
<img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/adacff6a48c00493513a44d249d952f1?showdoc=.jpg" alt="" /></p>
<blockquote>
<p>表级锁定：明确地使用 <code>LOCL TABLE</code>语句手工锁定表，语句复杂，锁定类型比较多。P227</p>
</blockquote>
<p>示例：
<code>LOCK TABLE myemp in SHARE MODE NOWAIT;--锁定表，指定锁定类型为SHARE共享锁</code></p>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/fd3ec545a4b6f354c2d4fd142bb738e4?showdoc=.jpg" alt="" /></p>
<h4>解除锁定</h4>
<p>展示锁定：</p>
<ul>
<li>使用for update，并且不提交。</li>
<li>再使用for update 出现等待状态。</li>
<li>使用管理员账号sys，到v$locked_object 中查session_id，再到v$session 中查找锁定的session_id 对就的serial#，这两个字段在解除锁定时要用到。</li>
</ul>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/728df77b1e25e4c8b66b0e773a83aeef?showdoc=.jpg" alt="" /></p>
<p>使用到的SQL:</p>
<pre><code>select * from myemp where empno=1234 for update ;//第一个session 中执行
select * from myemp where empno=1234 for update ;//第二个session 中执行
select session_id,oracle_username,process from v$locked_object ;//sys用户下，第三个session 执行
select sid,serial#,username,lockwait,status from v$session where sid in ('32','69') ;//sys用户下，第三个session 执行
alter system kill session '69,39651';//在第三个session 执行杀死会话，参数是查出来的。
// 输入System altered.</code></pre>
<p>被杀死的竟然是第二个session :
<img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/fbcc524af640e9ff5bd7e9fae6559db4?showdoc=.jpg" alt="" /></p>
<p><strong>总结：</strong></p>
<ul>
<li>DML操作有三种：insert into / update /delete.</li>
<li>每个连接到数据库的用户用session 表示，每个session 有独立的事务，事务的主要控制命令是commit/rollback .</li>
<li>当多个事务操作同一资源会出现锁死情况，锁分两种，行级锁定、表级锁定。</li>
<li>事务的四大特性：原子性、一致性、隔离性、持久性。</li>
</ul>