Friday, August 9, 2024

SQL DCL and TCL

Data Control Language:

DCL commands are used to GRANT and TAKE BACK authority from any database user.
  • Grant
  • Revoke
Grant
 It is used to give user access privileges to a database.

 Example:
 GRANT SELECT, UPDATE ON MY TABLE TO SOME_USER;

REVOKE
It is used to take back permissions from the user.

Example:
REVOKE SELECT, UPDATE ON MY TABLE FROM USER1, USER2;


 Transaction Control Language
 TCL commands can only use with DML commands  INSERT, DELETE and UPDATE only.
 These operations are automatically committed in the database that's why they cannot be used while create  tables or dropping them
  • COMMIT 
  • ROLLBACK
  • SAVEPOINT
Commit: 
Commit command is used to save all the transaction the database. 
Syntax:
 COMMIT;
 
Example:
 DELETE FROM CUSTOMERS WHERE AGE = 25; 
COMMIT;

 Rollback
 Rollback command is used to undo transactions that have not already been saved to the database.
 
Syntax:

 ROLLBACK;

 Example:
 DELETE FROM CUSTOMERS WHERE AGE = 25; ROLLBACK;

 SAVEPOINT
It is used to roll transaction back to a certair point without rolling back th entire transaction.
 Syntax:
 SAVEPOINT SAVEPOINT_NAME;


No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...