mysql存储函数参数 [英] mysql stored function parameter

问题描述

这是我第一次开始创建存储功能，因此遇到了一些问题.目前，我使用SELECT test();调用该函数(test现在是函数名称).我想向该函数发送一个数字(用户名ID)并返回用户名.

I have just started to create a stored function this is my first time so I am having a few problems. Currently I call the function using SELECT test(); (test is the function name for now). I want to send a number to the function (username ID) and have the username returned.

我通过使用SELECT test(1);进行此工作.1是表中用户的ID.这似乎在返回用户名时起作用，但是如果我输入任何数字，也会返回相同的用户名.

I have this working by using SELECT test(1); 1 is the ID of a user in the table. This seems to work as the username is returned, but if I type in any number the same username is returned also.

BEGIN 

 DECLARE new_username VARCHAR(90);    

    SELECT `username` INTO  new_username FROM `users` WHERE `ID` = ID;

 return new_username;
END

我将参数设置为ID int.

我是否正确地认为关键字INTO会将用户名的值放入变量new_username中?如果我在没有INTO的情况下运行它，则会收到错误消息:

Am I right in thinking that the keyword INTO will put the value of the username into the variable new_username ? If I run it without the INTO I get the error:

不允许从函数返回结果集

Not allowed to return a result set from a function

我是否在此方面犯了任何明显的错误，我希望我没有完全错误地这样做.感谢您的任何建议:).

Have I made any obvious mistakes in this, I hope I havent done it totally wrong. Thanks for any advice :).

我刚刚在表中添加了几行，现在出现错误:

Edit : I just added a few more rows into my table , I now get the error:

结果包含多于一行

Result consisted of more than one row

完整的sql版本:

CREATE DEFINER=`elliotts`@`%` FUNCTION `test`(ID int) 
RETURNS varchar(32) CHARSET latin1
BEGIN 

    DECLARE new_username VARCHAR(32);

    SELECT `username` 
      INTO new_username 
      FROM `users` 
     WHERE `ID` = ID;

    return new_username;

END

推荐答案

使用:

DROP FUNCTION IF EXISTS `example`.`test` $$
CREATE FUNCTION `example`.`test` (param INT) RETURNS VARCHAR(32)
BEGIN

  DECLARE new_username VARCHAR(32);

    SELECT `username`
      INTO new_username
      FROM `users`
     WHERE `ID` = param;

    RETURN COALESCE(new_username, 'Username not found');

END $$

请注意，RETURN值的VARCHAR长度与变量匹配，该变量应与您要返回的列长度匹配.

Mind that the VARCHAR length of the RETURN value matches the variable, which should match the column length you want to return.

这篇关于mysql存储函数参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！