从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,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变量在CheckAddTag中存储CheckTagExists的结果?我期待的是某些东西

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

or something like that...

或类似的东西...

4 个解决方案

#1

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

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

#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参数。

#3

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 ;

#4

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

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

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