Transaction Control Language - To study the various TCL commands namely commit, rollback and savepoint. - Computer Programming

Latest

C C++ Java Python Perl Programs Examples with Output -useful for Schools & College Students

Wednesday, October 27, 2010

Transaction Control Language - To study the various TCL commands namely commit, rollback and savepoint.

DESCRIPTION

COMMIT: This command saves all the transactions to the database since the last commit or rollback command.

ROLLBACK: This command is used to undo the transactions that have not been already saved to the database.It can be used to undo transactions since the last commit or rollback command.

SAVEPOINT: This command is a point in transaction that you can roll the transaction back to without rolling back the entire transmission.

CREATE THE TABLE ‘ITYR’

SQL> create table ityr(ename varchar(15),eid number(5),salary number(5));

Table created.

PROGRAM

SQL> set serveroutput on;
SQL> declare
  2  t number(6);
  3  n number(6);
  4  s number(6);
  5  begin
  6  insert into ityr values('a',100,19000);
  7  insert into ityr values('b',102,1000);
  8  s:=&s;
  9  n:=&n;
 10  savepoint a;
 11  update ityr set salary=salary+2000 where eid=s;
 12  update ityr set salary=salary+1500 where eid=n;
 13  select sum(salary) into t from ityr;
 14  if(t>20000)
 15  then
 16   rollback to a;
 17  else
 18  dbms_output.put_line('no updation');
 19  end if;
 20  end ;
 21  /
Enter value for s: 100
old   8: s:=&s;
new   8: s:=100;
Enter value for n: 102
old   9: n:=&n;
new   9: n:=102;

PL/SQL procedure successfully completed.

DISPLAYING THE UPDATED TABLE

SQL> select * from ityr;

ENAME                  EID     SALARY
--------------- ---------- ----------
a                      100      19000
b                      102       1000

No comments:

Post a Comment