MySQL 存储过程的返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4362722/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return value from MySQL stored procedure
提问by Dan
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 bestway. So here's what I've got.
所以我终于决定开始学习如何使用存储过程，虽然我确实让它们工作，但我不确定我是否正确地做 - 也就是。的最佳方式。所以这就是我所拥有的。
Three procedures: TryAddTag, CheckTagExists, and AddTag.
三个过程：TryAddTag、CheckTagExists 和 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 是一个过程，它是我在其他代码（例如 PHP 等）和其他两个过程之间的中介，因此这是被调用的过程。
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.
我的问题源于这个和@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 变量将 CheckTagExists 的结果存储在 TryAddTag 中？我期待一些类似的东西
...
DECLARE doesTagExist BOOL;
SET doesTagExist = CheckTagExist('str');
...
or something like that...
或类似的东西...
回答by Jon Black
your stored procedure is a little over-engineered for my liking - keep it simple :)
根据我的喜好，您的存储过程有点过度设计 - 保持简单:)
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
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();
?>
回答by bobobobo
Stored PROCEDUREScan return a resultset. The last thing you SELECTin a stored procedure is available as a resultset to the calling environment.. Stored FUNCTIONScan return only a single result primitive.
You may also mark your parameters as INOUTparameters.
StoredPROCEDURES可以返回一个结果集。 您SELECT在存储过程中的最后一件事可作为调用环境的结果集。. StoredFUNCTIONS只能返回一个结果原语。
您还可以将您的参数标记为INOUTparameters。
回答by rMX
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 ;
回答by fred
DECLARE doesTagExist BOOL;
SET CheckTagExist('str',doesTagExist);
is the correct way of doing it with just store procedures. There are no 'regular' return values.
是仅使用存储过程执行此操作的正确方法。没有“常规”返回值。
