DCL Commands : Data Control Language Commands

DCL Commands are used for controlling the data. The DCL Commands are –

  1. COMMIT Command
  2. ROLLBACK Command
  3. GRANT Command
  4. REVOKE Command

The COMMIT Command

The COMMIT Command is a transactional command which is used to save changes invoked by a transaction to the database. It saves all the transaction to the database since the last COMMIT or ROLLBACK.

Syntax
COMMIT [WORK];

where COMMIT is the keyword and the WORK is optional keyword and it is used to make the commend more user friendly.

Example :
DELETE FROM Employee WHERE SALARY > 40000.00;
COMMIT;

The first statement deletes the records of the Employee whose salary>40000.00, a COMMIT command is issued to save the changes to the database, completing the transaction.

The ROLLBACK Command

It is used to undo transactions until the last COMMIT or ROLLBACK command was issued or say that have not already been saved to the database.

Syntax :
ROLLBACK [WORK];
Example :
DELETE FROM Employee WHERE Salary>40000.00;
ROLLBACK;

First statement delete the records of all the employees whose salary is greater than 40000.00. But the second statement remove the effect of first one and rollback/undo the changes made by it. Hence, there will be no effect on Employee table.

The GRANT Command

As a system would have several users, it is necessary to define what may be accessed by each user. Granting of privileges is done with the GRANT command.

Syntax :
GRANT <privileges> ON <table/view_name>
TO <user_name_list>/PUBLIC

This SQL Command allows the specified privileges (such as SELECT/INSERT,UPDATE,DELETE on some or all columns or ALL PRIVILEGES) on the specified table/view to the users as specified or to all users (PUBLIC).

The REVOKE Command

The REVOKE Command withdraws granted privileges and is of similar format as GRANT.


Previous Home Next
DML Commands Integrity Constraints

     

Incoming search terms:

  • dcl commands

Leave a Reply