MySQL - BEGIN ... END Compound Statement

BEGIN ... END Compound Statement

The BEGIN ... END syntax is used to create a compound statement. These compound statements contain a multiple set of statements. These statement starts with BEGIN and ends with END statements. Each statement in a compound statement ends with a semi colon (;) or the current statement delimiter.

stored procedures and functions, triggers, and events are the compound statement in MySQL. You can write a BEGIN ... END block with in another and we can also label these statements.

A compound statement can contain other blocks such as variable declarations, conditions, cursors, including loops and conditional tests.

Syntax

Following is the syntax of the BEGIN ... END Compound Statement −

[begin_label:] BEGIN
   [statement_list]
END [end_label]

Example 1

Assume we have created a table named Employee in MySQL database using the CREATE TABLE as shown below −

CREATE TABLE Employee(
   Name VARCHAR(255), 
   Salary INT NOT NULL, 
   Location VARCHAR(255)
);

Following is an example of a MySQL stored procedure. Here we are inserting a column in to the Employee table taking values from user. (through input parameters)

DELIMITER //
Create procedure myProcedure (
   IN name VARCHAR(30),
   IN sal INT,
   IN loc VARCHAR(45))
   BEGIN
      INSERT INTO Employee(Name, Salary, Location) VALUES (name, sal, loc);
   END // 
DELIMITER ;

Calling a stored procedure

Following statement calls the above created stored procedure.

CALL myProcedure ('Raman', 35000, 'Bangalore');

If you retrieve the contents of table, you can observe the newly inserted row as shown below −

select * from employee;

Output

The above query produces the following output −

Example 2

Suppose we have created a table named Emp in the database using the CREATE statement as shown below −

CREATE TABLE Emp(
   Name VARCHAR(255), 
   DOB DATE, 
   Location VARCHAR(255)
);

And we have inserted three records in the Emp table as −

INSERT INTO Emp VALUES 
('Amit', DATE('1970-01-08'), 'Hyderabad'),
('Sumith', DATE('1990-11-02'), 'Vishakhapatnam'),
('Sudha', DATE('1980-11-06'), 'Vijayawada');

Following query creates a function named getDob()which accepts the name of the employee, retrieves and returns the value of DOB column.

DELIMITER //
CREATE FUNCTION test.getDob(emp_name VARCHAR(50))
   RETURNS DATE
   DETERMINISTIC
   BEGIN
      declare dateOfBirth DATE;
      select DOB into dateOfBirth from test.emp where Name = emp_name;
      return dateOfBirth;
   END//
DELIMITER ;

If you call the function, you can get date of birth of an employee as shown below.

SELECT getDob('Amit');

Output

Following is the output of the above program −