Lab 4.2 Exercise Answers | Oracle PL[s]SQL by Example

This section gives you some suggested answers to the questions in Lab 4.2, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.

4.2.1 Answers

a)	If you issue the following command, what would you expect to see? Why? SELECT * FROM student WHERE last_name = 'Norbu';
A1:	Answer: You will not be able to see any data because the ROLLBACK to (SAVEPOINT) B has undone the last insert statement where the student 'Norbu' was inserted.
b)	Try it. What happened ? Why?
A2:	Answer: When you issue this command, you will get the message " `no rows selected` ."

Three students were inserted in this PL/SQL block. First, Sonam in SAVEPOINT A, then Tashi in SAVEPOINT B, and finally Norbu was inserted in SAVEPOINT C. Then when the command ROLLBACK to B was issued, the insert of Norbu was undone.

Now issue

 ROLLBACK to SAVEPOINT A;

c)	What happened?
A1:	Answer: The insert in SAVEPOINT B was just undone. This deleted the insert of Tashi who was inserted in SAVEPOINT B.
d)	If you issue the following, what do you expect to see? SELECT last_name FROM student WHERE last_name = 'Tashi';
A2:	Answer: You will see the data for Tashi.
e)	Issue the command and explain your findings.
A3:	Answer: You will see one entry for Tashi, as follows : LAST_NAME ------------------------- Tashi Tashi was the only student that was successfully entered into the database. The ROLLBACK to SAVEPOINT A undid the insert statement for Norbu and Sonam.

A Single PL/SQL Block Can Contain Multiple Transactions

For Example:

 Declare v_Counter NUMBER; BEGIN v_counter := 0; FOR i IN 1..100 LOOP v_counter := v_counter + 1; IF v_counter = 10 THEN COMMIT; v_counter := 0; END IF; END LOOP; END;

In this example, as soon as the value of v_counter becomes equal to 10, the work is committed. So, there will be a total of 10 transactions contained in this one PL/SQL block.