从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.

三个过程:TryAddTag，CheckTagExists和AddTag.

Three procedures: TryAddTag, CheckTagExists, and AddTag.

TryAddTag是我在其他代码(例如PHP等)和其他两个过程之间的中介，所以这是被调用的过程.

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 ;

我的问题源于此，并使用@doesTagExist.

My problems stem from this and use of @doesTagExist.

-- Check if tag already exists CALL CheckTagExists(tagName, @doesTagExist);

使用这些变量之一的正确方法是吗?和/或，如何使用DECLARE变量在TryAddTag中存储CheckTagExists的结果?我期望有类似的东西

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'); ...

或类似的东西...

推荐答案

您的存储过程因我的喜好而有些过分设计-保持简单:)

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

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

<?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<br/>refresh me...", $tagID); } catch(exception $ex){ ob_clean(); //handle errors and rollback $conn->rollback(); echo sprintf("oops error - %s<br/>", $ex->getMessage()); } // finally $conn->close(); ob_end_flush(); ?>