Stored procedures handle complex database operations. They work like an API for the database. Models in Laravel handle the one-to-one database structure. Therefore, they cannot handle complex database procedures. Lets go through the steps on how to use a stored procedure in Laravel.
Example of Stored Procedure in Laravel
Read Data
The SQL code for a stored procedure is shown below.
DROP PROCEDURE IF EXISTS `select_by_cust_id`;
delimiter ;;
CREATE PROCEDURE `select_by_cust_id` (IN idx int)
BEGIN
SELECT * FROM custs WHERE id = idx;
END
;;
delimiter ;Now we will check how to use this stored procedure in Laravel.
// Suppose #id = 1;
$model = new App\Cust();
$cust= $model->hydrate(
DB::select(
'call select_by_cust_id($id)'
)
);By Hydrating the raw query to the model, we can return an Eloquent Model.
If you need to load a stored procedure that does not require hydration to a model, you can simply call the raw query and return an array of your results.
When you hydrate a raw query to a model, you can return an Eloquent Model. You can also call a stored procedure without hydration. Here you need to simple call a raw query and return it with an array of results, as shown below.
// Suppose #id = 1;
$model = new App\Cust();
$cust = DB::select(
'call select_by_cust_id($id)'
);This simple stored procedure will help to read information from the customers table.
Insert Data
Let us consider another example to insert data in a database table using a stored procedure in Laravel. The SQL code will be as follows.
DROP PROCEDURE IF EXISTS `insert_cust`;
delimiter ;;
CREATE PROCEDURE `insert_cust` (IN cName varchar, IN cEmail varchar, IN cPassword varchar)
BEGIN
INSERT INTO custs (name, email, password) VALUES (cName, cEmail, cPassword);
END
;;
delimiter ;The Laravel code will be as follows.
DB:raw(
'call insert_cust(?, ?, ?),
[
$request->input('name'),
$request->input('email'),
Hash::make($request->input('password')),
]
);The Laravel code will assume that your validation is already done using the form request validation. The stored procedure casts inputs, i.e. (cName varchar). This will help ensure customer name entered is accurate and is of varchar type.
