首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL存储过程返回多个参数

问MySQL存储过程返回多个参数
EN

Stack Overflow用户
提问于 2016-05-11 21:49:44
回答 3查看 98关注 0票数 1

我是存储过程的新手。现在我有了一个可以工作的,但我想学习如何优化它。

代码语言:javascript
复制
CREATE DEFINER=`simonh`@`%` PROCEDURE `get_normalized_client_id`(IN source_id INT, IN source_division VARCHAR(255), IN source_currency VARCHAR(255), OUT NORMALIZED_ID INT, OUT NAME VARCHAR(255), OUT ADDRESS VARCHAR(255))
BEGIN

IF source_id > 100000 THEN SET source_id = source_id - 100000; END IF;

SELECT
CLIENT.NEW_GROUP_REFERENCE_NUMBER INTO NORMALIZED_ID
FROM ccis_vendors.client_id SOURCE
INNER JOIN ccis_vendors.receivable CLIENT ON CLIENT.id = SOURCE.ACCOUNT_ID
WHERE SOURCE.number = source_id AND SOURCE.division = source_division;

SELECT
CLIENT.name INTO NAME
FROM ccis_vendors.client_id SOURCE
INNER JOIN ccis_vendors.receivable CLIENT ON CLIENT.id = SOURCE.ACCOUNT_ID
WHERE SOURCE.number = source_id AND SOURCE.division = source_division;

SELECT
CONCAT(IFNULL(CLIENT.OPERATION_STREET,''), ' ', IFNULL(CLIENT.OPERATION_CITY,''), ' ', 
IFNULL(CLIENT.OPERATION_STATE_PROVINCE, ''), ' ', IFNULL(CLIENT.OPERATION_ZIP, '')) INTO ADDRESS
FROM ccis_vendors.client_id SOURCE
INNER JOIN ccis_vendors.receivable CLIENT ON CLIENT.id =  SOURCE.ACCOUNT_ID
WHERE SOURCE.number = source_id AND SOURCE.division = source_division;

END

正如您所看到的,我使用相同的查询3次来填充3个OUT参数。

我的问题是,有没有一种只用一个查询就能做到的方法?

谢谢。

编辑:我明白为什么INTO现在对我不起作用了。我把语法搞错了。

以下是工作版本:

代码语言:javascript
复制
CREATE PROCEDURE `t_get_normalized_client_id`(IN source_id INT, IN source_division VARCHAR(255), IN source_currency VARCHAR(255), OUT NORMALIZED_ID INT, OUT NAME VARCHAR(255), OUT ADDRESS VARCHAR(255))
BEGIN

IF source_id > 100000 THEN SET source_id = source_id - 100000; END IF;

SELECT
CLIENT.NEW_GROUP_REFERENCE_NUMBER, 
CLIENT.name,  
CONCAT(IFNULL(CLIENT.OPERATION_STREET,''), ' ',    IFNULL(CLIENT.OPERATION_CITY,''), ' ', 
IFNULL(CLIENT.OPERATION_STATE_PROVINCE, ''), ' ', IFNULL(CLIENT.OPERATION_ZIP, ''))

INTO 
NORMALIZED_ID,
NAME,
ADDRESS

FROM ccis_vendors.client_id SOURCE
INNER JOIN ccis_vendors.receivable CLIENT ON CLIENT.id = SOURCE.ACCOUNT_ID
WHERE SOURCE.number = source_id AND SOURCE.division = source_division;

END

谢谢大家!

EN

回答 3

Stack Overflow用户

发布于 2016-05-11 21:55:27

您可以将选择简化为单个选择。而且,我建议对参数使用一致的命名约定--这样就不太可能与列混淆:

代码语言:javascript
复制
DELIMITER $$
CREATE DEFINER=`simonh`@`%` PROCEDURE `get_normalized_client_id`(
    IN in_source_id INT,
    IN in_source_division VARCHAR(255),
    IN in_source_currency VARCHAR(255),
    OUT out_NORMALIZED_ID INT,
    OUT out_NAME VARCHAR(255),
    OUT out_ADDRESS VARCHAR(255)
)
BEGIN
    IF in_source_id > 100000 THEN
        SET in_source_id = in_source_id - 100000;
    END IF;

    SELECT out_NORMALIZED_ID := CLIENT.NEW_GROUP_REFERENCE_NUMBER,
           out_Name := CLIENT.NAME,
           out_Address := CONCAT_WS(' ', CLIENT.OPERATION_STREET, CLIENT.OPERATION_CITY,
                                    CLIENT.OPERATION_STATE_PROVINCE, CLIENT.OPERATION_ZIP
                                   )
    FROM ccis_vendors.client_id SOURCE INNER JOIN
         ccis_vendors.receivable CLIENT
         ON CLIENT.id = SOURCE.ACCOUNT_ID
    WHERE SOURCE.number = in_source_id AND SOURCE.division = in_source_division;

END;$$
DELIMITER ;

此外:

  • 我把IF放在了多行上。更清楚的是,END IF就在那里。通常,我将“end”紧跟在"beginnings“之后,以确保适当的closure.
  • CONCAT_WS()似乎是比CONCAT()更好的选择。另外,它还可以处理NULL值。
  • 我更喜欢:=的内联语法,而不是INTO。这真的只是一个风格问题。INTO用于其他目的,比如文件访问,所以我认为:=更清晰。
票数 1
EN

Stack Overflow用户

发布于 2016-05-11 21:55:58

你不能这样做吗?

代码语言:javascript
复制
 CREATE DEFINER=`simonh`@`%` PROCEDURE `get_normalized_client_id`(IN source_id INT, IN source_division VARCHAR(255), IN source_currency VARCHAR(255), OUT NORMALIZED_ID INT, OUT NAME VARCHAR(255), OUT ADDRESS VARCHAR(255))
    BEGIN

IF source_id > 100000 THEN SET source_id = source_id - 100000; END IF;

SELECT
CLIENT.NEW_GROUP_REFERENCE_NUMBER INTO NORMALIZED_ID,
CONCAT(IFNULL(CLIENT.OPERATION_STREET,''), ' ', IFNULL(CLIENT.OPERATION_CITY,''), ' ', 
IFNULL(CLIENT.OPERATION_STATE_PROVINCE, ''), ' ', IFNULL(CLIENT.OPERATION_ZIP, '')) INTO ADDRESS,
CLIENT.name INTO NAME
FROM ccis_vendors.client_id SOURCE
INNER JOIN ccis_vendors.receivable CLIENT ON CLIENT.id = SOURCE.ACCOUNT_ID
WHERE SOURCE.number = source_id AND SOURCE.division = source_division;


END
票数 1
EN

Stack Overflow用户

发布于 2016-05-11 21:56:47

在Begin和End语句之间,SELECT CLIENT.name INTO NAME,CLIENT.NEW_GROUP_REFERENCE_NUMBER INTO NORMALIZED_ID,CONCAT(IFNULL(CLIENT.OPERATION_STREET,''),‘',IFNULL(CLIENT.OPERATION_CITY,''),’',IFNULL(CLIENT.OPERATION_STATE_PROVINCE,''),‘',IFNULL(CLIENT.OPERATION_ZIP,''))到地址从ccis_vendors.client_id源内部连接ccis_vendors.receivable客户端上的CLIENT.id = SOURCE.ACCOUNT_ID,其中SOURCE.number = source_id和SOURCE.division = source_division;

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

https://stackoverflow.com/questions/37164704

复制
相关文章

相似问题

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