Create and Use Stored Procedure in Laravel

What is a Stored Procedure?

A stored procedure is a set of SQL statements that are stored in a relational database management system after compilation so that it can be reused and shared by multiple programs.

Part 1: How to create the Stored Procedure in MySql?

For this example, I am using 5.7.24 — MySQL Community Server (GPL) and MySql Workbench. You may check your version with SELECT VERSION(); command.

DELIMITER $$DROP PROCEDURE IF EXISTS GetUsers$$CREATE PROCEDURE  GetUsers() 
BEGIN
SELECT * FROM users;
END$$
DELIMITER ;
CALL GetUsers();
DELIMITER $$DROP PROCEDURE IF EXISTS GetUserByID$$CREATE PROCEDURE  GetUserByID(
IN `uid` BIGINT
)
BEGIN
SELECT * FROM users WHERE id = uid;
END$$
DELIMITER ;
CALL GetUserByID(5);

Part 2: How to use the Stored Procedure in Laravel?

In controller use Illuminate\Support\Facades\DB;

<?phpnamespace App\Http\Controllers;use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
class UserController extends Controller
{
public function getUsers()
{
$allUsers = DB::select('call GetUsers()');
return $allUsers;
}
public function getUserByID()
{
$user = DB::select('call GetUserByID(?)',[5]);
return $user;
}
}

How to create a migration for procedures?

To achieve this just create a DB::unprepared command in a migration file’s up() function as follows

public function up()
{
DB::unprepared(
'CREATE PROCEDURE GetUsers()
BEGIN
SELECT * FROM users;
END;'
);
DB::unprepared(
'CREATE PROCEDURE GetUserByID(
IN `uid` BIGINT
)
BEGIN
SELECT * FROM users WHERE id = uid;
END;'
);
}
public function down()
{
DB::unprepared(
'DROP PROCEDURE IF EXISTS GetUsers;'
);
DB::unprepared(
'DROP PROCEDURE IF EXISTS GetUserByID;'
);
}

Software Developer @ mditech.net