数据库串烧（三）Oracle SQL高级用法

1.merge into的使用

语法：merge into 目标表 a
 
           using 源表 b
 
           on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)  
 
           when matched then 操作1（update set a.更新字段=b.字段）
 
           when  not macthed then 操作2 （ insert (字段1,字段2……) values (值1,值2……) ）

作用：通常用于判断一个表中是否拥有源表相应匹配的字段值，用以进行相应的数据库操作

范例：构建dept表的复制表dept_test,如果查到复制表的部门id和源表的部门id相同则更新数据，如果复制表的部门id与源表部门id不匹配，则将新的数据插入至复制表中。

2.递归查询

语法：select * | 字段 from 表名

start with 字段=值

connect by prior 父字段=子字段（子字段=父字段）

作用：通常用于判断一个表中存在自连接的情况，查询出整个的树状结构

范例：查询emp表中smith这个人的层级关系。

3.数据库锁

当多个用户同时操作同一个资源的时候，会产生并发的问题。解决的办法就是在一个事务中加锁，阻止其他的事务进行操作。

根据保护的对象不同，Oracle数据库锁可以分为以下几大类：    

1、DML锁（data locks，数据锁），用于保护数据的完整性；    

2、DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；    

3、内部锁和闩（internal locks and latches），保护数据库的内部结构。

我们基本上经常见到的都是DML锁。

DML锁

DML锁主要包括TM锁和TX锁，其中TM锁称为意向锁或表级锁，TX锁称为行级锁或事务锁。我们可以认为Oracle只有如下6种LMODE的锁，只是根据锁定的对象不同而有不同的名称，如6号的X锁，既可以是用于锁表的TM锁，也可以是TX锁，也可以是DDL锁。

TM

TX

读永远不会阻止写。但有唯一的一个例外，就是select ...for update。

写永远不会阻塞读。当一行被修改后，Oracle通过回滚段提供给数据的一致性读。

范例1：查询锁

当一个用户对一个数据行执行更新操作且没有提交的时候，其他用户进行相关操作将进行等待，因为oracle已自动加锁

 其中最后两行就是刚才的操作中所加的锁。只有当该用户commit或者rollback的时候，才会解锁。

范例2：强制解锁

如果发生一个用户进行update等操作且事务未及时提交或回滚的时候，其他用户的操作将陷入无休止的等待，为了避免这种情况的发生，可以采用系统管理员账户强制解锁。

（1）通过v$lock获取该锁的sid(上面的例子中sid为213)

（2）通过v$session查询该sid对应的serial#

(3)执行alter system kill session 'sid,serial#';