首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL存储过程的循环结果集

问MySQL存储过程的循环结果集
EN

Stack Overflow用户
提问于 2014-05-08 13:47:51
回答 1查看 418关注 0票数 0

这是我的存储函数,它从多个表中获取完整的地址

代码语言:javascript
复制
DELIMITER $$

CREATE FUNCTION `get_Address` (Id INT)
RETURNS varchar (250)
BEGIN
DECLARE address varchar (250);

DECLARE state varchar (250);
DECLARE gov varchar (250);
DECLARE city varchar (250);
DECLARE hood varchar (250);
DECLARE street varchar (250);
DECLARE building varchar (250);
DECLARE floor varchar (250);

declare cr1 cursor FOR
SELECT 
    s.state_Name,
    g.governorate_Name,
    c.city_Name,
    h.hood_Name,
    st.street_Name,
    a.address_Building,
    a.address_Floor
FROM
    addresses AS a
        INNER JOIN
    states AS s ON s.state_Id = a.state_Id
        INNER JOIN
    governorates AS g ON g.governorate_Id = a.governorate_Id
        INNER JOIN
    cities AS c ON c.city_Id = a.city_Id
        INNER JOIN
    hoods AS h ON h.hood_Id = a.hood_Id
        INNER JOIN
    streets AS st ON st.street_Id = a.street_Id
WHERE
    a.address_Id = Id;

OPEN cr1;

FETCH cr1 INTO state, gov, city , hood , street, building ,floor;

CLOSE cur1;
RETURN address;
END

我需要这样的东西

代码语言:javascript
复制
address = state + gov + city + hood + street + building + floor;

我需要返回所有结果集,用PHP创建完整地址,或者循环结果,在MYSQL中创建完整地址并返回varchar

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2014-05-08 14:15:56

您不能在MySQL中返回结果集,因为我们没有任何这样的数据类型可用。

您可以使用

代码语言:javascript
复制
concat_ws(" ",state , gov , city , hood , street , building , floor) as address 

如果您需要它一次,直接在查询中。

如果您要使用这几次,最好是制作一个汇总表。

但这里可能有另一个解决方案:

1)创建临时表

2)获取结果并将其输入临时表。

3)为地址调用tmp表

我的意思是:

代码语言:javascript
复制
DELIMITER $$
DROP PROCEDURE IF EXISTS get_Address$$
CREATE PROCEDURE `get_Address` (IN Id INT)
BEGIN
  CREATE TABLE tmp_address(ID INT,address VARCHAR(255));

  INSERT INTO tmp_address
  SELECT 
    a.address_Id,
    CONCAT_WS(" ",s.state_Name,
        g.governorate_Name,
        c.city_Name,
        h.hood_Name,
        st.street_Name,
        a.address_Building,
        a.address_Floor) as address
  FROM
    addresses AS a
        INNER JOIN
    states AS s ON s.state_Id = a.state_Id
        INNER JOIN
    governorates AS g ON g.governorate_Id = a.governorate_Id
        INNER JOIN
    cities AS c ON c.city_Id = a.city_Id
        INNER JOIN
    hoods AS h ON h.hood_Id = a.hood_Id
        INNER JOIN
    streets AS st ON st.street_Id = a.street_Id
  WHERE
    a.address_Id = Id;

END$$
DELIMITER ;

希望能帮上忙!)

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/23543662

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档