问题描述
我正在努力在另一个存储过程中调用一个存储过程.就像现在一样，存储过程从不将SELECT语句返回给PHP中的mysqli调用(但是在MySQL工作台中可以正常工作).
I am struggling to call a stored procedure within another stored procedure. Like it is now the stored procedure never return the SELECT statement back as a result to the mysqli call in PHP (but it works fine in MySQL workbench).
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_bid`(IN bid_in decimal(6,2),
IN ticker_in varchar(5),
IN share_amount_in BIGINT)
BEGIN
DECLARE company_id_var INT;
DECLARE highest_bid BIT;
DECLARE generated_bid_id BIGINT;
SET company_id_var = (SELECT ID
FROM Companies
WHERE Ticker = ticker_in);
-- Put the bid into the Bids table.
INSERT INTO Bids(company_id,bid,share_amount)
VALUES (company_id_var,bid_in,share_amount_in);
SET generated_bid_id = LAST_INSERT_ID();
CALL check_available_shares(bid_in,share_amount_in,generated_bid_id,@shares_left);
-- Check if the bid is higher than the current highest bid.
-- If so update the CurrentState table to have the new value.
UPDATE CurrentState SET buyer = bid_in
WHERE ID = company_id_var
AND buyer < bid_in;
IF (ROW_COUNT() = 1)
THEN
SELECT 1 AS highest_bid, @shares_left AS shares_left, CS.buyer, CS.seller, CS.last_price, CO.name, CO.ticker
FROM CurrentState CS, Companies CO
WHERE CS.id = CO.id
AND CO.ticker = ticker_in;
ELSE
SELECT 0 AS highest_bid, @shares_left AS shares_left, CS.buyer, CS.seller, CS.last_price, CO.name, CO.ticker
FROM CurrentState CS, Companies CO
WHERE CS.id = CO.id
AND CO.ticker = ticker_in;
END IF;
END
如果我从该存储过程中删除了"CALL"功能，它将在底部返回SELECT语句之一，但是如果我保留"CALL"功能，它将不返回任何内容.我已经尝试使用exec和execute，但是当我尝试在MySQL工作台中保存过程时，这只会给我语法错误.此问题与 https://stackoverflow.com /questions/23193085/mysqli-does-not-return-any-results-but-stored-procedure-does .但是似乎该问题可能与mysqli如何处理存储过程的执行有关，因为当我在mySQL workbench(?)中手动调用存储过程时，它可以正常工作.
If I remove the "CALL" function from this stored procedure it returns one of the SELECT statement at the bottom, but if I keep the "CALL" function it does not return anything.I have tried with exec and execute but that only gives me syntax error when I try to save the procedure within MySQL workbench. This problem is related to https://stackoverflow.com/questions/23193085/mysqli-does-not-return-any-results-but-stored-procedure-does.But it seems that that problem may be related to how mysqli handles the execution of the stored procedure since it works fine when I call the stored procedure manually in mySQL workbench(?)
推荐答案
几个小时把我的头撞在墙上后，我找到了解决方案.由于此存储过程调用了另一个使用游标的存储过程，因此我不得不使用$mysqli->multi_query()函数.这是我的工作方式:
After hours of banging my head against a wall I found a solution. Since this stored procedure calls another stored procedure which uses a cursor I had to use the $mysqli->multi_query() function. Here's how I got it working:
new_bid存储过程:
new_bid stored procedure:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_bid`(IN bid_in decimal(6,2),
IN ticker_in varchar(5),
IN share_amount_in BIGINT)
BEGIN
-- Store the company ID into a variable as this value will used more than once.
DECLARE company_id_var INT;
DECLARE ParamtoPass INT;
DECLARE highest_bid BIT;
DECLARE generated_bid_id BIGINT;
SET company_id_var = (SELECT ID
FROM Companies
WHERE Ticker = ticker_in);
-- Put the bid into the Bids table.
INSERT INTO Bids(company_id,bid,share_amount)
VALUES (company_id_var,bid_in,share_amount_in);
SET generated_bid_id = LAST_INSERT_ID();
-- EXECUTE check_available_shares bid_in, @share_amount_in,@generated_bid_id,@shares_left;
CALL check_available_shares (bid_in,@share_amount_in,generated_bid_id,@shares_left);
-- Check if the bid is higher than the current highest bid.
-- If so update the CurrentState table to have the new value.
UPDATE CurrentState SET buyer = bid_in
WHERE ID = company_id_var
AND buyer < bid_in;
IF (ROW_COUNT() = 1)
THEN
SELECT 1 AS highest_bid, @shares_left AS shares_left, CS.buyer, CS.seller, CS.last_price, CO.name, CO.ticker
FROM CurrentState CS, Companies CO
WHERE CS.id = CO.id
AND CO.ticker = ticker_in;
ELSE
SELECT 0 AS highest_bid, @shares_left AS shares_left, CS.buyer, CS.seller, CS.last_price, CO.name, CO.ticker
FROM CurrentState CS, Companies CO
WHERE CS.id = CO.id
AND CO.ticker = ticker_in;
END IF;
END
check_available_shares过程:
check_available_shares procedure:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `check_available_shares`(IN bid_in decimal(6,2),
IN share_amount_in int,
IN bid_id bigint,
OUT shares_left BIT)
BEGIN
DECLARE num_shares INT;
DECLARE selling_id INT;
DECLARE selling_price DECIMAL(6,2);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT share_amount,sell_price,ID
FROM ShareSell
WHERE sell_price <= bid_in
ORDER BY sell_price ASC, sell_timestamp ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur; /* open cursor */
read_loop: LOOP
FETCH NEXT FROM cur
INTO num_shares, selling_price, selling_id;
IF (done) THEN
LEAVE read_loop;
END IF;
IF ((share_amount_in - num_shares) > 0) THEN
SET share_amount_in = (share_amount_in - num_shares);
DELETE FROM ShareSell
WHERE ID = selling_id;
ELSEIF((share_amount_in - num_shares) < 0) THEN
UPDATE ShareSell
SET share_amount = (share_amount - share_amount_in)
WHERE ID = selling_id;
DELETE FROM Bids
WHERE ID = bid_id;
LEAVE read_loop;
ELSE
DELETE FROM ShareSell
WHERE ID = selling_id;
DELETE FROM Bids
WHERE ID = bid_id;
END IF;
SELECT num_shares,selling_price;
END LOOP;
CLOSE cur;
IF (share_amount_in > 0) THEN
UPDATE Bids
SET share_amount = share_amount_in
WHERE ID = bid_id;
SET shares_left = 1;
ELSE
SET shares_left = 0;
END IF;
END
最后是PHP调用:
$bid = 43.10;
$ticker = "GOS";
$share_amount = 1000;
if ($mysqli->multi_query("CALL new_bid($bid,'$ticker',$share_amount)"))
{
if ($result = $mysqli->store_result())
{
while ($row = $result->fetch_assoc())
{
print_r($row);
}
$result->close();
}
}
这篇关于如何在另一个存储过程(PHP和mysqli)中调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！