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
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.
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.