mysql存储过程laravel,如何从Laravel中的mysql存储过程获取多个结果集

I want to fetch multiple result sets from a stored procedure in laravel. Is there a way I can do this?

Currently, I can get a single row's data using the below code:

$result = DB::statement('CALL user_login(' . $userId . ',"'

. $password . '",'

. '@success'.','

. '@first_Name'

);

$res = DB::select('select @success AS success, @first_Name AS firstName);

Here is my stored procedure:

DELIMITER //

DROP PROCEDURE IF EXISTS user_login//

create procedure user_login (IN userid VARCHAR(50),

IN password VARCHAR(50),

out success int,

OUT first_Name VARCHAR(255),

)

begin

declare count int(1);

set count =0;

select firstName, count(*)

into first_Name, count

from `tmc`.user where user_id = userid and pwd=password;

if count >0 then

set success =0;

else

set success=1;

end if;

end//

解决方案

I am using the following code and it works flawlessly. Change it to suit your needs.

public static function CallRaw($procName, $parameters = null, $isExecute = false)

{

$syntax = '';

for ($i = 0; $i < count($parameters); $i++) {

$syntax .= (!empty($syntax) ? ',' : '') . '?';

}

$syntax = 'CALL ' . $procName . '(' . $syntax . ');';

$pdo = DB::connection()->getPdo();

$pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);

$stmt = $pdo->prepare($syntax,[\PDO::ATTR_CURSOR=>\PDO::CURSOR_SCROLL]);

for ($i = 0; $i < count($parameters); $i++) {

$stmt->bindValue((1 + $i), $parameters[$i]);

}

$exec = $stmt->execute();

if (!$exec) return $pdo->errorInfo();

if ($isExecute) return $exec;

$results = [];

do {

try {

$results[] = $stmt->fetchAll(\PDO::FETCH_OBJ);

} catch (\Exception $ex) {

}

} while ($stmt->nextRowset());

if (1 === count($results)) return $results[0];

return $results;

}

Example call:

$params = ['2014-01-01','2014-12-31',100];

$results = APIDB::CallRaw('spGetData',$params);

The resulting call will be:

CALL spGetData(?,?,?)

If there is only one resultset, it will be returned as is. If there are more, it will return an array of result sets. The key is using $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);. Without it, a horrible SQLSTATE[HY000]: General error: 2053 exception will be thrown.

The try{} catch() block is used to eliminate the resultsets that cannot be fetched. Particularly, I have procedures that returns two resultsets, one as a result of an update (or other execute statements) and the last one as the real data. The exception thrown on fetchAll() with an execute query will be PDOException.

Warning: the function is not optimised. You can rewrite it with one single pass through the parameters.