Stored Procedure in MySQL || MySQL Stored Procedure for Select/Insert/Update

In this tutorial, you will learn how to work with STORED PROCEDURE in MySQL to perform multiple actions/tasks.

CREATE TABLE IN MYSQL

---------------------------------------

Table structure for table `tbl_test`

CREATE TABLE `tbl_test` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`phone` varchar(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE PROCEDURE FOR SELECT Statement

-------------------------------------------------------------------

This query returns all data from the sample database table.

SELECT * FROM tbl_test;


The following statement creates a new stored procedure that wraps the query:

DELIMITER //
CREATE PROCEDURE USP_GetAllDATA()
BEGIN
SELECT * FROM tbl_test;
END //

DELIMITER ;

Executing the stored procedure

---------------------------------------

To execute a stored procedure, you use the CALL statement:
syntax:- CALL ProcedureName(Arguments);

This example illustrates how to call the USP_GetAllDATA() stored procedure:

CALL USP_GetAllDATA();

This procedure returns all data from the sample database table

CREATE PROCEDURE FOR INSERT Statement

-------------------------------------------------------------------

This query using for insert data from the sample database table.

INSERT INTO tbl_test(name, phone)
              VALUES(P_NAME,P_PHONE);

The following statement creates a new stored procedure that wraps the query:

DELIMITER //
CREATE PROCEDURE USP_AddDATA (
IN `P_NAME` VARCHAR(528) CHARSET utf8,
IN `P_PHONE` VARCHAR(12) CHARSET utf8)
BEGIN
INSERT INTO tbl_test(name, phone)
VALUES(P_NAME,P_PHONE);
END //
DELIMITER;

Executing the stored procedure

---------------------------------------

This example illustrates how to call the USP_AddDATA() stored procedure:

CALL USP_AddDATA('Ankit','0000000000');

Here we are inserting two parameters.

CREATE PROCEDURE FOR SELECT/INSERT/UPDATE Statement

--------------------------------------------------------------------------------------------

It means one procedure can perform multiple taxes.

DELIMITER $$
--

-- Procedures
--
CREATE PROCEDURE `USP_MINIMASTER`
(
IN `P_ACTION` VARCHAR(3),
IN `P_ID` INT,
IN `P_NAME` VARCHAR(528) CHARSET utf8,
IN `P_PHONE` VARCHAR(12) CHARSET utf8)
BEGIN
IF(P_ACTION='S') THEN
SELECT * FROM tbl_test;
END IF;
IF(P_ACTION='A') THEN
START TRANSACTION;
INSERT INTO tbl_test(name, phone)
VALUES(P_NAME,P_PHONE);
COMMIT;
END IF;
IF(P_ACTION='U')THEN
UPDATE tbl_test SET name=P_NAME WHERE id = P_ID;
END IF;
END$$
DELIMITER ;

Executing the stored procedure

---------------------------------------

In this example we will see how to call the USP_MINIMASTER stored procedure:

CALL USP_ MINIMASTER ('A','','Ankit','0000000000');   --INSERT

CALL USP_ MINIMASTER ('S','','','');                  --SELECT

CALL USP_ MINIMASTER ('U','1','',);                    --UPDATE