mysql存储过程返回值_从MySQL存储过程返回值

So I've finally decided to get around to learning how to use stored procedures, and although I do have them working, I'm unsure if I'm doing it correctly - aka. the best way. So here's what I've got.

Three procedures: TryAddTag, CheckTagExists, and AddTag.

TryAddTag is the procedure that is my intermediary between other code (eg. PHP, etc...) and the other two procedures, so this is the one that gets called.

TryAddTag

DELIMITER //

CREATE PROCEDURE TryAddTag(

IN tagName VARCHAR(255)

)

BEGIN

-- Check if tag already exists

CALL CheckTagExists(tagName, @doesTagExist);

-- If it does not exist, add it

IF @doesTagExist = FALSE THEN

CALL AddTag(tagName);

END IF;

END //

DELIMITER ;

AddTag

DELIMITER //

CREATE PROCEDURE AddTag(

IN tagName VARCHAR(255)

)

BEGIN

INSERT INTO

tags

VALUES(

NULL,

tagName

);

END //

DELIMITER ;

CheckTagExists

DELIMITER //

CREATE PROCEDURE CheckTagExists(

IN

tagName VARCHAR(255),

OUT

doesTagExist BOOL

)

BEGIN

-- Check if tag exists

SELECT

EXISTS(

SELECT

*

FROM

tags

WHERE

tags.NAME = tagName

)

INTO

doesTagExist;

END //

DELIMITER ;

My problems stem from this and use of @doesTagExist.

-- Check if tag already exists

CALL CheckTagExists(tagName, @doesTagExist);

Is the the correct way to use one of these variables? And/or, how can I use a DECLARE'd variable to store the result of CheckTagExists within TryAddTag? I expected something along the lines of

...

DECLARE doesTagExist BOOL;

SET doesTagExist = CheckTagExist('str');

...

or something like that...

解决方案

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

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

?>