Introduction to Structured Query Language (SQL) – Part – 5

Commit

To finish the existing transaction as well as to make everlasting modifications as done by the transaction an individual can make use of COMMIT command. This command removes every save-point in the transaction as well as releases the transaction’s locks. An individual can also make use of this command to manually commit an in-doubt distributed transaction. COMMIT command by means of the FORCE clause is not supported in PL/SQL.

COMMIT [ WORK ] [ COMMENT ‘ My_Text ‘ | FORCE ‘ My_Text ‘ [ , integer ] ]

Where:

• WORK: It is maintained solely for agreement with normal Structured Query Language (SQL). The commands COMMIT as well as COMMIT WORK are the same.

• COMMENT: It stipulates that a comment to be linked with the in progress transaction. The ‘ My_Text ’ is a quoted literal of up to fifty (50) characters that Oracle saves in the data dictionary view DBA_2PC_PENDING along with the transaction ID when the transaction turn out to be in-doubt.

• FORCE: It manually commits an in-doubt dispersed transaction. The transaction is recognized by means of the ‘ My_Text ’ comprising of local otherwise global transaction ID. In the direction of finding the IDs of such type of transactions, an individual need to query the data dictionary view DBA_2PC_PENDING. An individual can make use of the integer to precisely allocate the transaction a System Change Number (SCN). If an individual ignore the integer, the transaction is committed by means of the current System Change Number (SCN).

Requirements of Commit

An individual need no rights to commit the present transaction. To manually commit a dispersed in-doubt transaction which an individual initially committed, an individual should have FORCE TRANSACTION system privilege. To manually commit a dispersed in-doubt transaction which was initially committed by a different end user, an individual should have FORCE ANY TRANSACTION system privilege.

For an instance to commit the current transaction, execute the below syntax:

SQL> COMMIT WORK;

Commit complete.

Rollback

To undo the last work completed in the existing transaction. An individual can also make use of this statement to manually undo the work completed by an in-doubt dispersed transaction.

ROLLBACK [ WORK ] [ TO [ SAVEPOINT ] My_SavePoint_Name | FORCE ‘ My_Text ‘ ]

Where:

• WORK: It is not mandatory as well as is provided for American National Standards Institute (ANSI) compatibility.

• TO: It rollbacks the existing transaction to the indicated save point. If an individual forget this clause, the ROLLBACK command rollbacks the total transaction.

• FORCE: It manually rollback an in-doubt dispersed transaction. The transaction is recognized by means of the ‘ My_Text ’ comprising of local otherwise global transaction ID. In the direction of finding the IDs of such type of transactions, an individual need to query the data dictionary view DBA_2PC_PENDING. ROLLBACK commands with the FORCE clause are not supported in PL/SQL.

Requirements of Rollback

An individual need no rights to roll back the present transaction. To manually roll back a dispersed in-doubt transaction which an individual initially committed, an individual should have FORCE TRANSACTION system privilege. To manually roll back a dispersed in-doubt transaction which was initially committed by a different end user, an individual should have FORCE ANY TRANSACTION system privilege.

For an instance to commit the current transaction, execute the below syntax:

SQL> ROLLBACK ;

Rollback completed.

Forming Database Users

This part of the article covers up the following information:

• Forming Database Administrators (DBA)

• Forming Database Users

The CREATE command is not only a part of the Data Control Language (DCL), but also the part Data Definition Language (DDL). This article addresses the CREATE command as it communicates to the formation of Database Administrators (DBA) as well as Database Users.

Forming Database Administrators (DBA)

Database safety is demarcated as well as organized by means of Database Administrators (DBAs). Contained by the possibility of database safety, Database Administrators (DBAs) are accountable for:

• Adding Database Users.

• Erasing Database Users.

• Allowing access to a particular database object.

• Controlling otherwise restricting access to database objects.

• Permitting database user’s privileges to view otherwise alter database objects.

• Altering otherwise revoking rights which have been given to the database users.

A database user who primarily forms a database turns out to be its default administrator. Thus, this first database user has the power to form additional administrator accounts for that particular database only. The OpenEdge Studio has two (2) approaches for forming Database Administrators (DBAs):

• In Structured Query Language (SQL), the Database Administrators (DBAs) practices the CREATE command to form a database user as well as then practices the GRANT command to offer the database user with managerial privileges.

• In Progress 4GL, a Database Administrators (DBAs) practices the Data Administration Tool to form additional administrators.

Forming Database Users

Make use of the subsequent code to create any new database user as given below:

CREATE USER ‘ My_UserName ‘ , ‘ My_Password ‘ ;

ALTER USER ‘ UserName ‘ , ‘UserNameNew ‘ , ‘ PasswordNew ‘ ;

Grant Command

The GRANT command can be castoff to let the database users have two (2) dissimilar kinds of rights:

1. Database wide privileges

2. Table specific privileges

When the REVOKE command is specified with CASCADE option, the access right from a particular database user is withdrawn and the other additional database users who had received the privileges from that particular database user is also withdrawn.

When the REVOKE command is specified with neither RESTRICT nor CASCADE options, the behavior remains same like the CASCADE option.

In the upcoming part we will be discussing the database wide privileges, different constrictions in structured query language, like check constrictions, not null constrictions, unique constrictions and the primary key constrictions.