从MySQL存储过程返回值 [英] Return value from MySQL stored procedure

问题描述

所以我终于决定开始学习如何使用存储过程，尽管我确实使它们起作用了，但是我不确定自己是否正确地进行了操作. 最佳方式.所以这就是我所拥有的.

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

这篇关于从MySQL存储过程返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！