做了一个实验, 对于10g增强的“DELETE WHERE”进行了测试, 结果完全和LZ设想的一样, 也就是“DELETE WHERE 针对的应该是更新后的记录,通俗点说也就是DELETE WHERE判断的数据集范围是被UPDATE后的那些数据”, 关于这点以前还真没有注意, 这还真是一个陷阱, 有些问题还是需要亲自做了一个实验才能得出正确的结论!
SQL> select * from test1;
OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- ------------------------------ ------------------- -------
55417 F_TEST VALID
55652 FUNCTION1 VALID
52971 SP_MI_LOG_ANALYSIS INVALID
52973 GET_ENAME VALID
53808 AL_REPORT_FIRST VALID
53088 FUNC1 VALID
55887 PARSE VALID
53187 DYNAMICQUERY VALID
8 rows selected
SQL> select * from test_obj;
OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- ------------------------------ ------------------- -------
55417 F_TEST FUNCTION VALID
55652 FUNCTION1 FUNCTION VALID
52971 SP_MI_LOG_ANALYSIS PROCEDURE INVALID
52973 GET_ENAME FUNCTION VALID
53808 AL_REPORT_FIRST PACKAGE VALID
53088 FUNC1 FUNCTION VALID
55887 PARSE PROCEDURE VALID
53187 DYNAMICQUERY PROCEDURE VALID
8 rows selected
SQL>
SQL> MERGE INTO test1 a
2 USING test_obj b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.object_type = b.object_type
6 DELETE WHERE (b.status = 'VALID');
Done
SQL> select * from test1;
OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- ------------------------------ ------------------- -------
52971 SP_MI_LOG_ANALYSIS PROCEDURE INVALID
SQL> rollback;
Rollback complete
SQL> select * from test1;
OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- ------------------------------ ------------------- -------
55417 F_TEST VALID
55652 FUNCTION1 VALID
52971 SP_MI_LOG_ANALYSIS INVALID
52973 GET_ENAME VALID
53808 AL_REPORT_FIRST VALID
53088 FUNC1 VALID
55887 PARSE VALID
53187 DYNAMICQUERY VALID
8 rows selected
SQL>
SQL> MERGE INTO test1 a
2 USING test_obj b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE
6 SET a.object_type = b.object_type
7 WHERE b.status = 'VALID'
8 DELETE WHERE (b.status = 'VALID');
Done
SQL> select * from test1;
OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- ------------------------------ ------------------- -------
52971 SP_MI_LOG_ANALYSIS INVALID
SQL>