Hi Guys,
Now let's see example of how to create and use stored procedure in laravel. We will talk about laravel migration create stored procedure. you can understand a concept of how to write stored procedure in laravel.
I will give simple example with solution. you will learn how to call mysql stored procedure in laravel. In this example i will create mysql stored procedure using laravel migration and we will call stored procedure using laravel eloquent. you can easily use it with laravel 6, laravel 7 and laravel 8 version.
In this example we will create stored procedure call "get_posts_by_userid" that will return posts of given user id. so let's see bellow simple example:
Create Stored Procedure using Migration:
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreatePostProcedure extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
$procedure = "DROP PROCEDURE IF EXISTS `get_posts_by_userid`;
CREATE PROCEDURE `get_posts_by_userid` (IN idx int)
BEGIN
SELECT * FROM posts WHERE user_id = idx;
END;";
\DB::unprepared($procedure);
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
}
}
Call Stored Procedure:
Route::get('call-procedure', function () {
$postId = 1;
$getPost = DB::select(
'CALL get_posts_by_userid('.$postId.')'
);
dd($getPost);
});
Output:
Array
(
[0] => stdClass Object
(
[id] => 5
[title] => Laravel 8 Image Upload Example
[body] => Laravel 8 Image Upload Example
[created_at] => 2021-05-01 06:00:03
[updated_at] => 2021-05-01 06:00:03
[user_id] => 1
)
[1] => stdClass Object
(
[id] => 6
[title] => Laravel 8 Crud Example
[body] => Laravel 8 Crud Example
[created_at] => 2021-05-01 06:14:05
[updated_at] => 2021-05-01 06:14:05
[user_id] => 1
)
)
It will help you....
