如何从MySQL函数返回表 [英] How to return table from MySQL function

问题描述

CREATE FUNCTION myFunction(id INT) RETURNS TABLE  
BEGIN  
   RETURN SELECT * FROM board;  
END  

此查询给出以下错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE  

推荐答案

按照有关MySQL中用户定义函数的文档
您只能返回{STRING|INTEGER|REAL|DECIMAL}

As per documentation on user defined functions in MySQL
you can only return values of type {STRING|INTEGER|REAL|DECIMAL}

CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL}
    SONAME shared_library_name

如果要读取select结果集，则必须定义procedure，但不能定义function.

If you want to read a select resultset you have to define a procedure but not function.

DELIMITER //

DROP PROCEDURE IF EXISTS myProcedure //

CREATE PROCEDURE 
  myProcedure( id INT )
BEGIN  
   SELECT * FROM board
     -- add where condition if required
    WHERE Col_name = id
   ;  
END 
//

DELIMITER ;

您可以像这样调用过程

call myProcedure( 6 )

这将根据过程中使用的语句返回隐式对象.

That returns implicit objects based on the statements used in the procedure.

这篇关于如何从MySQL函数返回表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！