存储过程 - 想读取 OUTPUT 参数和选择结果集

Question

我有以下存储过程：

CREATE DEFINER=`CNX`@`%` PROCEDURE `sp_Facturatie_OpenstaandeBetalingen_Get`(OUT spResult varchar(200))

BEGIN

DECLARE exit handler for SQLEXCEPTION

 BEGIN

  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 

   @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;

  SET spResult = left(CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text),200);

 END;

SET spResult = 'TRUE';

/*SELECT @spResult;*/

SELECT *

FROM   Web_tblAfspraakBetalingen AB

       LEFT OUTER JOIN Web_tblBetaalStatus BS

                    ON AB.betalingbetaalstatusid = BS.betaalstatusid

       LEFT OUTER JOIN Web_tblBetaalMethode BM

                    ON AB.betalingbetaalmethodeid = BM.betaalmethodeid

WHERE  BS.betaalstatusomschrijving <> 'Betaald'

ORDER  BY betalingid ASC;

END

我想在 PHP 中使用 PHP 中的 MySqli 以 Procedural 风格调用这个存储过程。在 PHP 中尝试了很多不同的代码。有人可以建议我如何做到最好吗？

Answer 1

我想分享一下我如何克服这个问题并找到解决方案的最终结果。MySQL存储过程：CREATE DEFINER=`CN20100686`@`%` PROCEDURE `sp_Facturatie_OpenstaandeBetalingen_Get`()BEGINDeclare spResult nvarchar(200);DECLARE exit handler for SQLEXCEPTION /*declared an exception handler, which the result is put into a select*/&nbsp;BEGIN&nbsp; &nbsp;GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,&nbsp;&nbsp; &nbsp;@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;&nbsp; SET spResult = left(CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text),200);&nbsp; SELECT spResult;&nbsp;END;SELECT&nbsp; AB.BetalingAfspraakID,&nbsp; &nbsp; &nbsp; &nbsp; AB.BetalingDatum,&nbsp; &nbsp; &nbsp; &nbsp; Round(AB.BetalingBedrag,2) BetalingBedrag,&nbsp; &nbsp; &nbsp; &nbsp; BS.BetaalStatusOmschrijving,&nbsp; &nbsp; &nbsp; &nbsp; BM.BetaalMethodeOmschrijving,FROM&nbsp; &nbsp;Web_tblAfspraakBetalingen AB&nbsp; &nbsp; &nbsp; &nbsp;LEFT OUTER JOIN Web_tblBetaalStatus BS&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON AB.betalingbetaalstatusid = BS.betaalstatusid&nbsp; &nbsp; &nbsp; &nbsp;LEFT OUTER JOIN Web_tblBetaalMethode BM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON AB.betalingbetaalmethodeid = BM.betaalmethodeidWHERE&nbsp; BS.betaalstatusomschrijving <> 'Betaald'ORDER&nbsp; BY betalingid ASC;END在 PHP 中，我做了以下事情：检查结果集是否返回零行 - 表示存储过程已正确执行，但未找到结果，否则检查结果集是否有一个名为“spResult”的字段。如果是这样，请在屏幕上读取并填充结果，否则根据需要处理结果集。$sproc = 'sp_Facturatie_OpenstaandeBetalingen_Get()';$result = mysqli_query($link,"CALL " . $sproc) or die("mysqli_query failure: " . mysqli_error());$rowcount=mysqli_num_rows($result);&nbsp; &nbsp; if ($rowcount == 0) {echo "No records found.";}&nbsp; &nbsp; if ($result)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; $rowcounter = 'first';&nbsp; &nbsp; &nbsp; &nbsp; while ($row = mysqli_fetch_array($result))&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (substr($row["spResult"],0,5) == 'ERROR')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo "Error occured on executing " . $sproc . ": " . $row["spResult"];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; mysqli_free_result($result);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; exit();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if ($rowcounter == 'first')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //printing here my table header&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo "&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<b>List</b>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<table border=1 style='min-width:100%'>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<tr>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <td width='80%'>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Payments:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</td>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<td width='20%'>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</td>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</tr>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Count open payments: " . $rowcount;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $rowcounter = 'next';&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $BetalingAfspraakID = $row["BetalingAfspraakID"];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $BetalingDatum = $row["BetalingDatum"];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $BetalingBedrag = $row["BetalingBedrag"];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $BetaalStatusOmschrijving = $row["BetaalStatusOmschrijving"];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $BetaalMethodeOmschrijving = $row["BetaalMethodeOmschrijving"];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //display results as you wish&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo "<tr>";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo "<td>";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo "<b>Afspraak</b> " . $BetalingAfspraakID . "</br>";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo "Amount: " . $BetalingBedrag . "</br>";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo "Status: " . $BetaalStatusOmschrijving;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo "</td>";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo "</tr>";&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; // free result set&nbsp; &nbsp; &nbsp; &nbsp; mysqli_free_result($result);&nbsp; &nbsp; }&nbsp; &nbsp; else&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; echo "Error occured!" . mysqli_error($link);&nbsp; &nbsp; &nbsp; &nbsp; exit();&nbsp; &nbsp; }我希望有人觉得这个答案有用；当然可以进一步改进:)