从MySQL存储过程返回值-ReturnvaluefromMySQLstoredprocedure

10  

your stored procedure is a little over-engineered for my liking - keep it simple :)

你的存储过程有点过度设计,我喜欢 - 保持简单:)

MySQL

drop table if exists tags;
create table tags
(
tag_id int unsigned not null auto_increment primary key,
name varchar(255) unique not null
)
engine=innodb;

drop procedure if exists insert_tag;

delimiter #

create procedure insert_tag
(
in p_name varchar(255)
)
proc_main:begin

declare v_tag_id int unsigned default 0;

    if exists (select 1 from tags where name = p_name) then
        select -1 as tag_id, 'duplicate name' as msg; -- could use multiple out variables...i prefer this
        leave proc_main;
    end if;

    insert into tags (name) values (p_name);

    set v_tag_id = last_insert_id();

    -- do stuff with v_tag_id...

    -- return success
    select v_tag_id as tag_id, 'OK' as msg; 

end proc_main #

delimiter ;

PHP

autocommit(FALSE); // start transaction

    // create the tag

    $name = 'f00';

    $sql = sprintf("call insert_tag('%s')", $conn->real_escape_string($name));

    $result = $conn->query($sql);
    $row = $result->fetch_array();
    $result->close();
    $conn->next_result();

    $tagID = $row["tag_id"]; //  new tag_id returned by sproc

    if($tagID <0) throw new exception($row["msg"]);

    $conn->commit(); 

    echo sprintf("tag %d created
refresh me...", $tagID);

}
catch(exception $ex){
    ob_clean(); 
    //handle errors and rollback
    $conn->rollback();
    echo sprintf("oops error - %s
", $ex->getMessage()); 
}

// finally
$conn->close();
ob_end_flush();
?>

2  

Stored PROCEDURES can return a resultset. The last thing you SELECT in a stored procedure is available as a resultset to the calling environment.. Stored FUNCTIONS can return only a single result primitive.
You may also mark your parameters as INOUT parameters.

存储的PROCEDURES可以返回结果集。您在存储过程中选择的最后一项内容可用作调用环境的结果集。存储的FUNCTIONS只能返回单个结果基元。您也可以将参数标记为INOUT参数。

1  

If you want this:

如果你想要这个:

DECLARE doesTagExist BOOL;
SET doesTagExist = CheckTagExist('str');

then you should use functions:

那么你应该使用函数:

DELIMITER //
 CREATE FUNCTION CheckTagExists(
   tagName VARCHAR(255)
 )
 BEGIN
   DECLARE doesTagExist BOOL;

 -- Check if tag exists
 SELECT
  EXISTS(
   SELECT
    *
   FROM
    tags
   WHERE
    tags.NAME = tagName
  )
 INTO
  doesTagExist;

  RETURN doesTagExist;
END //
DELIMITER ;

0  

DECLARE doesTagExist BOOL; 
SET CheckTagExist('str',doesTagExist); 

is the correct way of doing it with just store procedures. There are no 'regular' return values.

只使用存储过程是正确的方法。没有“常规”返回值。