Is it possible to return multiple values ​​from mysql function?

Question

I have three tables, and I want to summarize all the columns of the results of each table and return these values ​​from the function that I define, to complete the whole process. Is it possible to return these three swimming merges from the same mysql function?

Answer 1

My dirty decision: 1. Combining values ​​in a string. 2 returns a string. 3 Splitting returns a string to values. I suppose it's not elegant, and I'm sure this has limitations, but it works for simple cases.

It is also necessary to create a splitting function because Mysql does not perform this function:

First edit your function.

CREATE FUNCTION yourFunctionWith2valuesForReturning() BEGIN DECLARE var1 VARCHAR(255); DECLARE var2 VARCHAR(255); // Your function logic here. Assign values to var1 and var2 RETURN CONCAT_WS('|', var1, var2); END 

Now you can split the return value that calls this function:

 CREATE FUNCTION SPLIT_STR(x VARCHAR(510), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255) DETERMINISTIC BEGIN RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, ''); END 

To get the values

 SET value1 = SPLIT_STR(returnedVAlue, '|', 1); SET value2 = SPLIT_STR(returnedVAlue, '|', 2); 

Notes:

You can use a string as a delimiter, fe: '| sep | ' or the other you want.

The SPLIT_STR function is a tuple http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

See also this question about line splitting in mysql: Split Varchar by character in MySQL

Answer 2

The correct way to do this is to use a stored procedure:

 CREATE PROCEDURE stored_proc(IN arg1 INT, IN arg2 INT, OUT retVal1 FLOAT, OUT retVal2 FLOAT, OUT retVal3 FLOAT) 

Then you can assign variables with

 SELECT x*y INTO retVal1; ... 

and call the stored procedure using @variables to access them:

 CALL stored_proc(1, 2, @retVal1, @retVal2, @retVal3);