DBMS SQL DAY 13

DAY -13

3.     TCL: Transaction Control Language

These commands are to keep a check on other commands and their affect on the database. These commands can annul changes made by other commands by rolling back to original state. It can also make changes permanent.

Command	Description
1. Commit	to permanently save
2. Rollback	to undo change
3. Savepoint	to save temporarily
Transaction Control Language(TCL) commands are used to manage transactions in database.These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions.         1.      Commit command Commit command is used to permanently save any transaaction into database. Following is Commit command's syntax, commit          2.      Rollback command This command restores the database to last commited state. It is also use with savepoint command to jump to a savepoint in a transaction. Following is Rollback command's syntax, rollback to savepoint-name;          3.      Savepoint command savepoint command is used to temporarily save a transaction so that you can rollback to that point whenever necessary. Following is savepoint command's syntax, savepoint savepoint-name; Example of Savepoint and Rollback Following is the class table, ID NAME 1 abhi 2 adam 4 alex Lets use some SQL queries on the above table and see the results. INSERT into class values(5,'Rahul'); commit; UPDATE class set name='abhijit' where id='5'; savepoint A; INSERT into class values(6,'Chris'); savepoint B; INSERT into class values(7,'Bravo'); savepoint C; SELECT * from class; The resultant table will look like, ID NAME 1 abhi 2 adam 4 alex 5 abhijit 6 chris 7 bravo Now rollback to savepoint B rollback to B; SELECT * from class; The resultant table will look like ID NAME 1 abhi 2 adam 4 alex 5 abhijit 6 chris Now rollback to savepoint A rollback to A; SELECT * from class; The result table will look like ID NAME 1 abhi 2 adam 4 alex 5 abhijit

---

---

---

4.     DCL: Data Control Language

 Data control language provides command to grant and take back authority.

Command	Description
1. Grant	grant permission of right
2. Revoke	take back permission.
Data Control Language (DCL) is used to control privilege in Database. To perform any operation in the database, such as for creating tables, sequences or views we need privileges. Privileges are of two types, System: creating session, table etc are all types of system privilege. Object: any command or query to work on tables comes under object privilege. DCL defines two commands, Grant: Gives user access privileges to database. Revoke: Take back permissions from user. To Allow a User to create Session  grant create session to username; To Allow a User to create Table grant create table to username; To provide User with some Space on Tablespace to store Table alter user username quota unlimited on system; To Grant all privilege to a User grant sysdba to username To Grant permission to Create any Table grant create any table to username To Grant permission to Drop any Table grant drop any table to username To take back Permissions revoke create table from username