oracle的megre语法,Oraclemerge语法和Oraclewithcheckoption的用法-Oracle

Oraclemerge语法和Oraclewithcheckoption的用法。

Oracle DML语句注意事项

DML: Data Manipulation Language , 数据操纵语言;简单的说就是SQL中的增、删、改 等语句。 insert 语句中如果只写表名，给出的value值中必须显示的写出所有列的值，即使表的列可为空，也要显示的写出null值

单行对多行比较使用> all ,< any 判断 SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = ‘IT_PROG’) AND job_id <> ‘IT_PROG’;

删除一张表里面所有的数据 (1) delete from table_name (2)truncate table table_name; TRUNCATE 语句无法回滚，因此除非是单独执行，并非常确认，否则慎用

Oracle merge语法的使用 通过这个merge你能够在一个SQL语句中对一个表同时执行inserts和updates操作. 当然是update还是insert是依据于你的指定的条件判断的，Merge into可以实现用B表来更新A表数据，如果A表中没有，则把B表的数据插入A表. MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表。 语法如下 MERGE INTO [your table-name] [rename your table here] USING ( [write your query here] )[rename your query-sql and using just like a table] ON ([conditional expression here] AND […]…) WHEN MATHED THEN [here you can execute some update sql or something else ] WHEN NOT MATHED THEN [execute something else here ! ]

案例： merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) when matched then update set p.product_name = np.product_name when not matched then insert values (np.product_id, np.product_name, np.category) — 有对应产品则更新产品的名字，没有则插入一条新的记录

在Oracle 10g中MERGE有如下一些改进： 1、UPDATE或INSERT子句是可选的 2、UPDATE和INSERT子句可以加WHERE子句 3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表 4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

第一个和第四个改进有实际意义，例如 merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) when matched then update set p.product_name = np.product_name where np.product_name like ‘OL%’ when not matched then insert values (np.product_id, np.product_name, np.category) where np.product_name like ‘OL%’ — 只有新产品的名字是以OL开头的才执行插入和更新操作

delete只能和update配合，从而达到删除满足where条件的子句的纪录

merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) when matched then update set p.product_name = np.product_name where p.product_id = np.product_id delete where np.product_name like ‘OL%’ when not matched then insert values (np.product_id, np.product_name, np.category) 这里我们达到的目的就是 会把匹配的记录的prodcut_name更新到product里，并且把product_name开头为OL的删除掉。

merge into也是一个dml语句，和其他的dml语句一样需要通过rollback和commit 结束事务。 Merge是一个非常强大的功能，而且是我们需求里经常会用到的一个有用的功能，所以我们一定要好好的学习到。

Oracle with check option的用法 (1) 在insert语句中通过子查询指定查询目标，指定插入符合子查询where子句条件的数据 案例:只允许插入 部门ID为50的员工信息 INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50 WITH CHECK OPTION) VALUES (99998, ‘Smith’, ‘JSMITH’, TO_DATE(‘2017-03-01’, ‘yyyy-mm-dd’), ‘ST_CLERK’, 5000, 50); — 使用了with check option 插入的数据中的department_id必须为50 ，不能为空，也不能为其他值，有点作用

(2)作用在单张表的视图中，对视图数据的DML操作要满足视图中的where子句的限制条件

首先视图只操作它可以查询出来的数据，对于它查询不出的数据，即使基表有，也不可以通过视图来操作。 1.对于update,有with check option，要保证update后，数据要被视图查询出来 2.对于delete,有无with check option都一样 4.对于insert,有with check option，要保证insert后，数据要被视图查询出来 对于没有where 子句的视图，使用with check option是多余的。

这个选项可以确保数据库中正在修改的数据的完整性。如果在 INSERT 或 UPDATE 操作期间违反了条件，则返回 SQL 错误 案例：创建一个视图，只能查询出dept=10的数据 CREATE VIEW EMP_VIEW2(EMPNO,EMPNAME,DEPTNO,JOBTITLE,HIREDATE) AS SELECT ID,NAME,DEPT,JOB,HIREDATE FROM EMPLOYEE WHERE DEPT=10 WITH CHECK OPTION; 在对视图执行下面的更新语句时候会出错，违反了视图中的where子句的条件 UPDATE EMP_VIEW2 SET DEPT = 20 WHERE DEPT = 10;