如何从Laravel中的mysql存储过程中获取多个结果集 [英] how to fetch multiple result set from a mysql stored procedure in laravel

问题描述

我想从laravel中的存储过程中获取多个结果集.有办法吗? 目前，我可以使用以下代码获取单行数据:

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;
}

示例调用:

$params = ['2014-01-01','2014-12-31',100];
$results = APIDB::CallRaw('spGetData',$params);

产生的呼叫将是:

CALL spGetData(?,?,?)

如果只有一个结果集，它将按原样返回.如果还有更多，它将返回结果集数组.密钥正在使用$pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);.没有它，将引发可怕的SQLSTATE[HY000]: General error: 2053异常.

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.

try {} catch()块用于消除无法获取的结果集.特别是，我有一些过程返回两个结果集，一个结果集是更新(或其他execute语句)的结果，最后一个结果集为真实数据.通过执行查询在fetchAll()上引发的异常将是PDOException.

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.

这篇关于如何从Laravel中的mysql存储过程中获取多个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！