How to call store procedures in laravel 8?

Hello dev.

in this small article i will able to share with you a way to call store procedures in laravel 8. this tutorials provide you example of call store procedures in laravel 8.

how to call store procedures in laravel 8? for lern follow bellow article:

Stored Procedures

Stored procedures are handy for encapsulating database intensive operations. Consider Stored Procedures as an API for your database.

Laravel models are often one-to-one representations of a database table. So, they don’t support stored procedures out of the box.

Reading From a Stored Procedure

Let’s create a simple stored procedure to read from our user table.

MySQL Code

DROP PROCEDURE IF EXISTS `select_by_user_id`;
delimiter ;;
CREATE PROCEDURE `select_by_user_id` (IN idx int)
BEGIN
    SELECT * FROM users WHERE id = idx;
END
 ;;
delimiter ;

Laravel Code

// Assume #id = 1;
$model = new App\User();
$user = $model->hydrate(
    DB::select(
        'call select_by_user_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.

// Assume #id = 1;
$model = new App\User();
$user = DB::select(
    'call select_by_user_id($id)'
);

Writing to a Stored Procedure

MySQL Code

DROP PROCEDURE IF EXISTS `insert_user`;
delimiter ;;
CREATE PROCEDURE `insert_user` (IN uName varchar, IN uEmail varchar, IN uPassword varchar)
BEGIN
    INSERT INTO users (name, email, password) VALUES (uName, uEmail, uPassword);
END
 ;;
delimiter ;

Laravel Code

DB:raw(
    'call insert_user(?, ?, ?),
    [
        $request->input('name'),
    $request->input('email'),
    Hash::make($request->input('password')),
    ]
);

This Laravel code assumes you have done your validation via form request validation. (Never trust user input!)

In the stored procedure you will notice that we type cast our inputs (uName varchar), this helps ensure your data is correct.

Suggestion:

- DB:raw creates a new raw query expression, but does not return data. Use DB:select to return data.

- With proper planning and structure you can create a CRUD-like database level API for your app.

- Laravel code may not have to change when making schema changes or performance updated to the database.

- You can set permissions on your database so INSERT, UPDATE or DELETE can only be run from a stored procedure.

i'm hoping it assist you to, thanks for visit my article if you like my article then proportion together with your friend and social platform.