PHP 抓MYSQL PROC OUTPUT

edited 三月 2014 in 資料庫
您好:我參考範例,建立proc GetCustomerLevel,可以執行
DELIMITER $$
 drop procedure if EXISTS GetCustomerLevel $$
CREATE PROCEDURE GetCustomerLevel(
    in  p_customerNumber int(11), 
    out p_customerLevel  varchar(10)
   )
BEGIN
 
    IF p_customerNumber > 50000 THEN
    SET p_customerLevel = 'PLATINUM';
    ELSEIF (p_customerNumber <= 50000 AND p_customerNumber >= 10000) THEN
        SET p_customerLevel = 'GOLD';
    ELSEIF p_customerNumber < 10000 THEN
        SET p_customerLevel = 'SILVER';
    END IF;
 
END$$

CALL GetCustomerLevel(50001,@level);
SELECT @level AS level;


接下來, PHP 要去取OUTPUT值,但卻沒有值?
我用print_r($r); 去顯示資料,也是沒有值?
<?php
 
require_once 'dbconfig.php';
 
$customerNumber = 103;
try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    // execute the stored procedure
    $sql = 'CALL GetCustomerLevel(:id,@level)';
    $stmt = $conn->prepare($sql);
 
    $stmt->bindParam(':id', $customerNumber, PDO::PARAM_INT);
    $stmt->execute();
    $stmt->closeCursor();
    // execute the second query to get customer's level
    $r = $conn->query("SELECT @level AS level")->fetch(PDO::FETCH_ASSOC);
    if ($r) {
        echo sprintf('Customer #%d is %s', $customerNumber, $r['level']);
    }
} catch (PDOException $pe) {
    die("Error occurred:" . $pe->getMessage());
}
請問者該如何解決?
謝謝!

評論

Sign In or Register to comment.