DCL Data control Language , TCL -Transaction Control Language

DCL – Data control language…

GRANT – Using this command we can give the object grant access(select,update,delete,insert,all) to different users.

Syntax:

Grant privileges on object to user;

Ex:  Grant all on xx_student_stg to scott;

      Grant select,update on xx_student_stg to scott;

      Grant select,update,insert on xx_student_stg to scott;

      Grant delete,select on xx_student_stg to scott;

REVOKE: Using this command we revoke the given previliges to the object from the user.

Syntax:

Revoke privileges on object from user;

EX:  Revoke all on xx_student_stg from scott.

TCL – Transaction control language

These commands play important role only when action takes place on the table using DML Commands.
Commit: This is used only after any DML Operations performed on a table in order to insert or delete or update the data permanently...
Rollback: This is used only after any DML Operations performed on a table in order to remove the previous transactions taken place on any table...
Note: After Commit there is no point of using rollback.
        DDL Commands are Auto commit...(very important to remember this point)

Lets do an exercise by creating a table and insert some records and play along with the commit and roll back commands...
Create table TEST1(A NumberB Varchar2(10));

è Table Created.

Insert 2 records in to the above table
Insert into TEST1 Values(10,’KIRAN_1’);
Insert into TEST1 Values(20,’KIRAN_2’);
Now run à select * from TEST;
o/p  à 2 records Found.
Now close the session and re open a new session and re run the select stmt
Select * from TEST1;
o/p  à No Data  Found.
Because we didnot commit the transactions they were not stored in the table permanently.They were stored only at session level.
Do the same exercise again but this time run the command commit before closing the session and then when we re open a new session we can see all the records because they were stored permanently in to the table as we committed.
Now insert 3 records in to the table
Insert into TEST1 Values(30, ’KIRAN_3’);
Then run the select statement as below
Select * from TEST1;
O/Pà 3 records found.
Run the command ROLLBACK
Now again when we re run the select statement as below
Select * from TEST1;
o/p à2 records Found.
The 1 record was roll backed, that means it has been removed from the table.
Now Lets do a small exercise along with the TRUNCATE, DROP, DELETE TABLE...
Lets work out with the same test table...
Insert into TEST1 Values(10,’KIRAN_1’);
Insert into TEST1 Values(20,’KIRAN_2’);

 COMMIT;

Select * from TEST1;

o/pà 2 records found

Now when i run the command

 TRUNCATE TABLE TEST1;

è It deletes all the data from the table permanently as it is as DDL Command and it is Auto commit.But the structure of the table remains as it is...

è Now try to view the table using DESC Command where you can see the table structure

è DESC TEST;

Now again re insert the 2 records in to the table and commit them...

Lets use delete command now.

Delete from TEST1;

The above Statement will delete all the records from the table but we need to commit it and also using delete command we can delete only some records depending on condition by using where condition as below..

Delete from TEST1 Where a = 10; -- this will delete only one record and then commit it;

Now again reinsert 2 records in to the table and Commit it.

Run the below command

DROP Table TEST1;

This will drop the structure of the table and data permanently from the data base;

Now try to view the table using

DESC TEST1;

It shows the o/p as object TEST1 does not exist;

Note: Where clause can be used only for select, update and delete commands…

Truncate table test1 where a = 10; -- nowhere clause can be used because  truncate is a ddl command