OCP-1Z0-051-V9.02-172题

172. The SQL statements executed in a user session are as follows:

SQL> CREATE TABLE product     

(pcode  NUMBER(2),      

pname  VARCHAR2(10));

SQL> INSERT INTO product  VALUES (1, 'pen');

SQL> INSERT INTO product  VALUES (2,'pencil');

SQL> SAVEPOINT a;

SQL> UPDATE product SET pcode = 10 WHERE pcode = 1;

SQL> SAVEPOINT b;

SQL> DELETE FROM product  WHERE pcode = 2;

SQL> COMMIT;

SQL> DELETE FROM product WHERE pcode=10;

Which two statements describe the consequences of issuing the ROLLBACK TO SAVE POINT a

command in the session? (Choose two.)

A. The rollback generates an error. 

B. No SQL statements are rolled back.

C. Only the DELETE statements are rolled back.

D. Only the second DELETE statement is rolled back.

E. Both the DELETE statements and the UPDATE statement are rolled back.

Answer: AB

 答案解析：

参考：http://blog.csdn.net/rlhua/article/details/12885143

由于COMMIT命令提交后之前建立的保存点都变成无效的了。

ROLLBACK TO SAVEPOINT a;命令报错后，只会影响本身这条语句，不会影响其它语句，所以第二个DELETE语句不会回滚。

实验验证：

sh@TEST0910> create table product

  2  (pcode number(2),

  3  pname varchar2(10));

 

Table created.

 

sh@TEST0910> INSERT INTO product  VALUES (1, 'pen');

 

1 row created.

 

sh@TEST0910> INSERT INTO product  VALUES (2,'pencil');

 

1 row created.

 

sh@TEST0910>  SAVEPOINT a;

 

Savepoint created.

 

sh@TEST0910> UPDATE product SET pcode = 10 WHERE pcode = 1;

 

1 row updated.

 

sh@TEST0910>  SAVEPOINT b;

 

Savepoint created.

 

sh@TEST0910> DELETE FROM product  WHERE pcode = 2;

 

1 row deleted.

 

sh@TEST0910>  COMMIT;

 

Commit complete.

 

sh@TEST0910> select * from product;

 

     PCODE PNAME

---------- ----------

        10 pen

 

sh@TEST0910>  DELETE FROM product WHERE pcode=10;

 

1 row deleted.

 

sh@TEST0910> ROLLBACK TO SAVE POINT a

  2  ;

ROLLBACK TO SAVE POINT a

                 *

ERROR at line 1:

ORA-00933: SQL command not properly ended