SQL Reference

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.