从php调用嵌套存储过程 [英] calling nested stored procedure from php
本文介绍了从php调用嵌套存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我试图从 php 调用一个存储过程，它又调用另一个存储过程.当我在命令行中运行存储过程调用时，它返回多个结果，而通过 php 调用只返回一个结果.这是我的程序
I am trying to call a stored procedure from php which in turn calls another stored procedure. When i run the stored procedure call in commandline it returns multiple result whereas while calling through php results in only one result. This is my procedure
//first procedure
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewalldiamondids`(
in pass_shape int,pass_symmetry varchar(255),in pass_polish varchar(255), in pass_first_carat double,in pass_last_carat double,in pass_clarity double,in pass_color int,
in pass_certificate int,in pass_fluorescence_id int,in pass_first_table_width double,in pass_last_table_width double,
in pass_first_depth double,in pass_last_depth double,
in pass_first_final_price double,in pass_last_final_price double)
block1:begin
declare first_carat double;
declare first_measure_height double;
declare first_measure_width double;
declare first_measure_depth double;
declare first_final_price double;
declare first_cut int;
declare first_clarity int;
declare first_color int;
declare first_table_width int;
declare first_depth double;
declare first_fluorescence_id int;
declare first_certificate int;
declare first_len_width_ratio double;
declare first_var int;
declare first_measure varchar(255);
declare first_diamond int;
declare second_diamond int;
declare no_more_rows boolean;
declare itemarray varchar(255);
declare ans int;
DECLARE curs CURSOR FOR select item_id from caratlane_enduser_v where shape=pass_shape and color = pass_color and fluorescence_id = pass_fluorescence_id and polish = pass_polish and clarity = pass_clarity and symmetry = pass_symmetry and certificate = pass_certificate and carat >= pass_first_carat and carat <= pass_last_carat and final_price >= pass_first_final_price and final_price <= pass_last_final_price and total_depth >= pass_first_depth and total_depth <= pass_last_depth and table_width >= pass_first_table_width and table_width <= pass_last_table_width;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
create table pair_find(first_id int,second_id int);
open curs;
first:loop
fetch curs into ans;
IF no_more_rows THEN
CLOSE curs;
LEAVE first;
END IF;
set first_measure_height=substring(first_measure,1,4);
set first_measure_width=substring(first_measure,6,4);
set first_measure_depth=substring(first_measure,11,14);
block2:begin
declare cur CURSOR FOR select item_id,carat,cut,clarity,color,certificate,table_width,total_depth,fluorescence_id,len_width_ratio,measurements,final_price from caratlane_enduser_v where item_id = ans;
open cur;
fetch cur into first_var,first_carat,first_cut,first_clarity,first_color,first_certificate,first_table_width,first_depth,first_fluorescence_id,first_len_width_ratio,first_measure,first_final_price;
set itemarray=first_var;
set itemarray=concat(itemarray,',');
call viewalldiamondinnerloop(first_var,first_carat,first_cut,first_clarity,first_color,first_certificate,first_table_width,first_depth,first_fluorescence_id,first_len_width_ratio,first_measure,first_final_price,itemarray);
close cur;
end block2;
end loop first;
end block1
//second procedure
viewalldiamondinnerloop:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `viewalldiamondinnerloop`(
in first_var int, in first_carat double,
in first_cut int,in first_clarity double,in first_color int,
in first_certificate int,in first_table_width double,
in first_depth double,in first_fluorescence_id int,
in first_len_width_ratio double,in first_measure varchar(255),
in first_final_price double,in itemarray varchar(255))
begin
declare first_id int;
declare second_id int;
declare pair_carat double;
declare pair_measure_height double;
declare pair_measure_width double;
declare pair_measure_depth double;
declare pair_final_price double;
declare pair_cut int;
declare pair_clarity int;
declare pair_color int;
declare pair_table_width double;
declare pair_depth double;
declare pair_fluorescence_id int;
declare pair_certificate int;
declare pair_len_width_ratio double;
declare pair_var int;
declare pair_measure varchar(255);
declare no_rows boolean;
DECLARE pair_curs CURSOR FOR select item_id,carat,cut,clarity,color,certificate,table_width,total_depth,fluorescence_id,len_width_ratio,measurements,final_price from caratlane_enduser_v where item_id = if(find_in_set(item_id,itemarray),0,item_id) and clarity = first_clarity and color = first_color and fluorescence_id = first_fluorescence_id and certificate = first_certificate and carat >= first_carat - 0.02 and carat <= first_carat + 0.02 and final_price >=first_final_price-0.08*first_final_price and final_price <= first_final_price+0.08*first_final_price and total_depth >= first_depth-0.01*first_depth and total_depth <= first_depth+0.01*first_depth and table_width >= first_table_width-0.01*first_table_width and table_width <= first_table_width+0.01*first_table_width;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_rows = TRUE;
open pair_curs;
second:loop
fetch pair_curs into pair_var,pair_carat,pair_cut,pair_clarity,pair_color,pair_certificate,pair_table_width,pair_depth,pair_fluorescence_id,pair_len_width_ratio,pair_measure,pair_final_price;
IF no_rows THEN
CLOSE pair_curs;
LEAVE second;
END IF;
set pair_measure_height=substring(pair_measure,1,4);
set pair_measure_width=substring(pair_measure,6,4);
set pair_measure_depth=substring(pair_measure,11,14);
select first_var,pair_var;
insert into pair_find(first_id,second_id) values(first_var,pair_var);
end loop second;
end
//php code
php code :
<?php
include_once '../app/Mage.php';
umask(0);
Mage::app();
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 'On');
$mysqli = new MySQLI('localhost','root','123456','caratlane_diamond_dev_test');
$shape=1;
$symmetry='Very Good';
$polish = 'Very Good';
$first_carat = 0;
$last_carat = 100;
$clarity = '1|2|3';
$color = '1|2|3';
$certificate = 1;
$fluorescence_id = 1;
$first_table_width = 0;
$last_table_width = 8000;
$first_total_depth = 0;
$last_total_depth = 8000;
$first_final_price = 0;
$last_final_price = 1000000;
$query=$mysqli->query("call viewalldiamondids(1,'Very Good','Very Good',0,100,1|2|3,1|2|3,1,1,0,8000,0,8000,0,1000000);");
$result= $mysqli->query("SELECT first_id,second_id from pair_find");
if($result->num_rows > 0)
{
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
{
print_r($row);
}
}
?>
推荐答案
在 PHP 中，您只查看第一个结果集.从命令行，它只是简单地吐出所有结果集.
From PHP you are only looking at the FIRST result set. From the command line, it simply spits out all result sets.
您上面的每个存储过程调用都会生成一个新的唯一结果集(可能有不同的字段等)
Each stored proc call you have above is generating a new unique result set (different fields possible etc)
要修复，您可以移至下一个结果集(如果存在):
To fix, you can move onto the next result set, if one exists:
使用odbc_next_result(资源$result_id)
或者，使用 Mysqli，您可以使用以下三个命令:
Alternatively, using Mysqli you can use these three commands:
$result = $connection->multi_query("select * from foo....");$result = $connection->store_result();$connection->next_result();
$result = $connection->multi_query("select * from foo...."); $result = $connection->store_result(); $connection->next_result();
以您的代码为例，您可以将输出结果的代码包装在 do..while 循环中，如下所示:
Using your code as an example, you can wrap your code that's outputting results inside a do..while loop like this:
$result= $mysqli->multi_query("SELECT first_id,second_id from pair_find");
do {
$result = $mysqli->store_result();
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
{
print_r($row);
}
} while ($mysqli->next_result());
http://php.net/manual/en/function.odbc-next-result.php
http://php.net/manual/en/mysqli.next-结果.php
这篇关于从php调用嵌套存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
