Create Stored Procedure In Mysql
Sep 25th, 2020 - written by Kimserey with .
A stored procedure in MySQL acts as a method stored in the database. It has a name and accepts a set of arguments and can be invoked via CALL statement. In this post we will look at how we can define a stored procedure, how the parameters and variables work, and lastly how we can define transactions and handle exceptions accordingly.
Create a Stored Procedure
Stored procedures in MySQL have the following format:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
DELIMITER //
CREATE PROCEDURE insertcustomer (
pFirstName VARCHAR(255),
pLastName VARCHAR(255),
pAge INT
)
BEGIN
INSERT INTO Customer
(
FirstName,
LastName,
Age
)
VALUES
(
pFirstName,
pLastName,
pAge
);
END //
DELIMITER ;
CREATE PROCEDURE followed with the procedure name and its parameters. The body of the procedure is defined in a compound statement starting with BEGIN and ending with END. A compound statement allows us to specify multiple statements to be executed in order. We will see later how we can ensure the atomicity of the change using a transaction.
Because we add a stored procedure by executing a SQL statement, we have to change the delimiter, originally ; to something else for example here // so that the statements within BEGIN ... END can have the proper delimiter. At the end, we set back the delimiter to ;.
Parameters
Parameters can be provided to a procedure, for example
1
2
3
4
5
CREATE PROCEDURE insertcustomer (
IN pFirstName VARCHAR(255),
IN pLastName VARCHAR(255),
IN pAge INT
)
The parameters start by an optional parameter mode IN|OUT|INOUT where IN specifies a value input. A value input gets copied before entering the procedure, if the parameter is modified within the procedure, it will not be reflected on the caller side. For example with the following stored procedure:
1
2
3
4
5
CREATE PROCEDURE `mysproc`(IN pValue INT)
BEGIN
SET pValue = 10;
END
If we call mysproc:
1
2
3
SET @pValue = 0;
CALL local.mysproc(@pValue);
SELECT @pValue;
The selection of @pValue will still return 0. As opposed to OUT which specifies an OUT parameter:
1
2
3
SET @pValue = 0;
CALL local.mysproc(@pValue);
SELECT @pValue;
@pValue here will be 10. An OUT parameter has a value of NULL on entering the stored procedure.
Lastly INOUT specifies a parameter which gets its value passed by the caller and allows the stored procedure to modify it.
1
2
3
4
5
CREATE PROCEDURE `mysproc`(INOUT pValue INT)
BEGIN
SET pValue = pValue + 10;
END
Then calling with 10:
1
2
3
SET @pValue = 10;
CALL local.mysproc(@pValue);
SELECT @pValue;
will set 20 on @pValue.
IN is used as default when parameter mode is not provided.
Variables
In our previous example when calling the stored procedure we used user-defined variables with SET @[name] = .... For stored procedure, we can use DECLARE to declare local variables. They must be defined at the top prior, any other statement.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
CREATE PROCEDURE insertcustomer (
pFirstName VARCHAR(255),
pLastName VARCHAR(255),
pAge INT,
PAddress VARCHAR(255)
)
BEGIN
DECLARE pUserId INT;
INSERT INTO Customer
(
FirstName,
LastName,
Age
)
VALUES
(
pFirstName,
pLastName,
pAge
);
SET pUserId = last_insert_id()
INSERT INTO Address
(
UserId,
Address
)
VALUES
(
pUserId,
pAddress
);
END
For example here we execute two inserts, the first one adding a customer, while the second insert inserts into the address table. At the beginning, we declare a local variable pUserId which we then set to the last_insert_id() in order to use it in the next insert.
A default for the value can also be added with DEFAULT for example DECLARE pUserId INT DEFAULT 0;.
Transaction
We saw that a stored procedure could include multiple statements (e.g. SELECT, INSERT, UPDATE, DELETE). In our previous example where we insert a customer then insert an address, if an error occurs on insert of address, the customer would still be inserted. In order to maintain the atomicity of the operation, we can use a transaction.
We can define a transaction with START TRANSACTION and use either COMMIT or ROLLBACK.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
CREATE PROCEDURE insertcustomer (
pFirstName VARCHAR(255),
pLastName VARCHAR(255),
pAge INT,
PAddress VARCHAR(255)
)
BEGIN
DECLARE pUserId INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
INSERT INTO Customer
(
FirstName,
LastName,
Age
)
VALUES
(
pFirstName,
pLastName,
pAge
);
SET pUserId = last_insert_id()
INSERT INTO Address
(
UserId,
Address
)
VALUES
(
pUserId,
pAddress
);
COMMIT;
END
We start the transaction and complete it with COMMIT or ROLLBACK.
Note that if we forget to end the transaction, there will be an open transaction after calling the stored procedure. For example if we start a transaction within a procedure and never complete it:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE PROCEDURE insertcustomer (
pFirstName VARCHAR(255),
pLastName VARCHAR(255),
pAge INT
)
BEGIN
START TRANSACTION;
INSERT INTO Customer
(
FirstName,
LastName,
Age
)
VALUES
(
pFirstName,
pLastName,
pAge
);
END
There will be an opened transaction which we can see by calling the transaction table:
1
SELECT * FROM information_schema.innodb_trx
This is important as any ROLLBACK following up the call of the procedure will rollback the changes made inside this procedure. So in order to complete a transaction, we have to handle both COMMIT and ROLLBACK.
For ROLLBACK, we define an exit handler using DECLARE EXIT HANDLER which is used to leave the stored procedure.
1
2
3
4
5
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
Inside the handler, we ROLLBACK the transaction and use RESIGNAL to re-throw the exception. If we omit to resignal the exception, the stored procedure will return successfully.
And that concludes today’s post!
Conclusion
In today’s post, we looked at how we could create a stored procedure in MySQL. We started by looking at how stored procedure were defined with an example, we then moved on to look at how parameters were passed into a stored procedure and how we could also use local variables inside a stored procedure. And we completed the post by looking at transactions. I hope you liked this post and I see you on the next one!