Lab 4.2 Exercise Answers
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. |
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. |
|
No comments:
Post a Comment