oracle如何查看pdb,oracle12c查看pdb账号oracle中MERGE语句和多表关联的update简析

MERGE是用来一次操作实现INSERT,UPDATE,DELETE的复合语句。

语法：

MERGE INTO TABLE/VIEW USING (TABLE/VIEW/SUBQUERY) ON (condition)

[WHEN METCHED THEN UPDATE SET COLUMN=expr/DEFAULT [WHERE condition]

www.2cto.com       [DELETE  WHERE condition]]

[WHEN NOT MATCHED THEN INSERT [(column,…)] VALUES(v,…) [WHERE condition]]

[LOG ERRORS [ INTO TABLE (…)] [REJECT LIMIT num/UNLIMITED]]

说明：

1、更新视图时不能使用DEFAULT值。

2、不能更新ON中的关联字段。

例子：

1

MERGE INTO bonuses D

2

USING (SELECT employee_id, salary, department_id FROM employees

3

WHERE department_id = 80) S

4   www.2cto.com

ON (D.employee_id = S.employee_id)

5

WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01

6

DELETE WHERE (S.salary > 8000)

7

WHEN NOT MATCHED oracle账号THEN INSERT (D.employee_id, D.bonus)

8

VALUES (S.employee_id, S.salary*0.1)

9

WHERE (S.salary <= 8000);

在update语句中，通常只能update一张表，对于多表关联更新，update语句限制关联字段必须有惟一索引。

例如：

1

update (select a.city_name,b.city_name as new_name

2

from customers a,

3

tmp_cust_city b

4  www.2cto.com

where b.customer_id=a.customer_id

5

)

6

set city_name=new_name

现在，可以使用merge来实现这个功能：

1

MERGE INTO a USING b  ON (b.customer_id=a.customer_id )

2

WHEN MATCHED THEN UPDATE SET a.city_name=b.city_name

作者 hulubo