mysql存储过程的返回值在哪里设置_从MySQL存储过程返回值

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

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

ob_start();

try{

$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);

$conn->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();

?>