Lab 4.2 Exercises


4.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL Block

Log into the CTA schema and enter the following series of commands. (Optionally, you can write the PL/SQL block in a text file and then run the script from the SQL*Plus prompt.)

 
 --  ch04_7a.sql BEGIN    INSERT INTO student       ( student_id, Last_name, zip, registration_date,         created_by, created_date, modified_by,         modified_date       )       VALUES ( student_id_seq.nextval, 'Tashi', 10015,                '01-JAN-99', 'STUDENTA', '01-JAN-99',                'STUDENTA','01-JAN-99'              );    SAVEPOINT A;    INSERT INTO student       ( student_id, Last_name, zip, registration_date,         created_by, created_date, modified_by,         modified_date       )       VALUES (student_id_seq.nextval, 'Sonam', 10015,               '01-JAN-99', 'STUDENTB','01-JAN-99',               'STUDENTB', '01-JAN-99'              );    SAVEPOINT B;    INSERT INTO student      ( student_id, Last_name, zip, registration_date,        created_by, created_date, modified_by,        modified_date      )       VALUES (student_id_seq.nextval, 'Norbu', 10015,               '01-JAN-99', 'STUDENTB', '01-JAN-99',               'STUDENTB', '01-JAN-99'              );    SAVEPOINT C;    ROLLBACK TO B; END; 
a)

If you issue the following command, what would you expect to see? Why?

 
 SELECT *   FROM student  WHERE last_name = 'Norbu'; 
b)

Try it. What happened ? Why?


Now issue

 
 ROLLBACK to SAVEPOINT A; 
c)

What happened?

d)

If you issue the following, what do you expect to see?

 
 SELECT last_name   FROM student  WHERE last_name = 'Tashi'; 
e)

Issue the command and explain your findings.


graphics/trick_icon.gif

SAVEPOINT is often used before a complicated section of the transaction. If this part of the transaction fails, it can be rolled back, allowing the earlier part to continue.


graphics/quote_icon.gif

It is important to note the distinction between transactions and PL/SQL blocks. When a block starts, it does not mean that the transaction starts. Likewise, the start of the transaction need not coincide with the start of a block.




Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net