BEGIN
BEGIN is a command used in SQL to mark the start of a transaction block. This transaction block encapsulates a series of data manipulation statements that should be processed as a single unit of work. The operations within a BEGIN block, as a single unit, either all succeed or all fail, supporting the principles of atomicity in transaction processing.-BEGIN is commonly used with COMMIT, ROLLBACK, and SAVEPOINT commands to maintain data integrity and handle transactions.
Example
BEGIN;SELECT * FROM Employees;ROLLBACK;
Output
Empty set
Explanation
In this example, a transaction is initiated with BEGIN;
. A query is then made to SELECT * FROM Employees;
where all records from the ‘Employees’ table are requested. However, due to ROLLBACK;
, all changes are reverted and the transaction nullified, hence the output is an Empty set
.
Example
BEGIN;CREATE TEMPORARY TABLE temp_user( user_id INT GENERATED ALWAYS AS IDENTITY, username TEXT NOT NULL);INSERT INTO temp_user(username) VALUES('Anna');ROLLBACK;
Output
ROLLBACK
Explanation
In the example, a transaction is initiated with BEGIN
, followed by the creation of a temporary table temp_user
and insertion of a user ‘Anna’. However, the ROLLBACK
statement in the end discards all the operations within this transaction, meaning that the table temp_user
and the inserted data ‘Anna’ will not exist post transaction.
Example
BEGIN PRINT 'Hello, SQL Server!'END
Output
Hello, SQL Server!
Explanation
In SQL Server, the BEGIN
and END
keywords define a block of code. This block is treated as a single unit. The PRINT
statement within the code block outputs a string message. This example serves to illustrate the use of the BEGIN
END
block as well as output a simple greeting message to the console.
Example
DECLARE x NUMBER := 10;BEGIN x := x + 10; dbms_output.put_line('Value of x is: ' || TO_CHAR(x));END;
Output
Value of x is: 20
Explanation
In this example, we initialize variable x
with a value of 10. We then increase the value by 10. The dbms_output.put_line
function then prints out the new value of x
, which is 20.
Example
BEGIN;CREATE TABLE Seasons ( id INT, name TEXT);INSERT INTO Seasons (id, name) VALUES (1, "Summer");COMMIT;
Output
Query OK, 0 rows affected (0.01 sec)
Explanation
The example demonstrates a transaction where a new table Seasons
is created, and a row is inserted into it. This sequence of commands is wrapped within BEGIN;
and COMMIT;
, indicating that these commands need to be treated as a single transaction. If any command fails, the entire transaction should be rolled back, leaving the database unchanged.