mysql -u root -pUSE test;SHOW PROCEDURE STATUS WHERE Db='test';CREATE PROCEDURE hi() SELECT 'hello';
CALL hi();
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)
DELIMITER $$
CREATE PROCEDURE multi_demo()
BEGIN
SELECT 'first result set';
SELECT 'second result set';
SELECT 1 AS num UNION SELECT 2 UNION SELECT 3;
END $$
DELIMITER ;
需使用 mysqli 擴展:
$mysqli = new mysqli('localhost', 'root', 'pass', 'test');
if ($mysqli->multi_query('CALL multi_demo()')) {
do {
if ($res = $mysqli->store_result()) {
while ($row = $res->fetch_assoc()) print_r($row);
$res->free();
}
} while ($mysqli->next_result());
}
$mysqli->close();
舊 mysql 擴展不支援多結果集,會報錯 can't return a result set in the given context。
CREATE ROUTINEEXECUTESHOW CREATE PROCEDURE