I am a noobie to stored procedures. Now I have one that works, but I'd like to learn how to optimize it.
我是存储过程的noobie。现在我有一个可以工作的,但是我想学习如何优化它。
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
As you can see, I am using the same query 3 times to populate 3 OUT parameters.
如您所见,我使用相同的查询3次来填充3个参数。
My question is, is there a way to do it with only one query?
我的问题是,是否有一种方法可以只用一个查询来完成?
Thanks.
谢谢。
EDIT: I see why INTO didn't work for me now. I got the syntax wrong.
编辑:我明白为什么INTO现在不适合我了。我搞错语法了。
Here's the working version:
这里的工作版本:
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
Thanks everyone!
谢谢大家!
3 个解决方案
#1
1
Can't you do it like this?
你不能这样做吗?
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
#2
1
You can simplify the selects down to a single one. And, I would recommend using consistent naming conventions for parameters -- so there is unlikely to be confusion with columns:
您可以将选择简化为单个选择。而且,我建议对参数使用一致的命名约定——因此不太可能与列混淆:
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 ;
In addition:
此外:
- I put the
IF
on multiple lines. It is clearer that theEND IF
is there. In general, I put "end"s of things immediately under the "beginnings" to ensure proper closure. - 我把IF写在多行上。更清楚的是,如果有的话,就会结束。一般来说,我把事情的“结束”放在“开始”下面,以确保恰当的结束。
-
CONCAT_WS()
seems a better option thanCONCAT()
. As a bonus, it also handlesNULL
values. - CONCAT_WS()似乎比CONCAT()更好。此外,它还处理空值。
- I prefer the inline syntax of
:=
rather thanINTO
. This is really just a matter of style.INTO
is used for other purposes, such as file access, so I think:=
is clearer. - 我更喜欢:=的内联语法,而不是INTO语法。这真的只是风格问题。INTO用于其他目的,例如文件访问,所以我认为:=更清楚。
#3
0
Between you Begin and End statements 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, '')) 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;
在开始和结束语句之间选择CLIENT. NAME, CLIENT。NEW_GROUP_REFERENCE_NUMBER转换为NORMALIZED_ID、CONCAT(IFNULL(CLIENT. operation_street, ")、'、IFNULL(CLIENT. operation_city, ")、'、IFNULL(客户端)。OPERATION_STATE_PROVINCE, "), '', IFNULL(客户端。)到ccis_vendor的地址。client_id源内部连接ccis_vendor。应收客户端。id =来源。ACCOUNT_ID来源。number = source_id和SOURCE。部门= source_division;
#1
1
Can't you do it like this?
你不能这样做吗?
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
#2
1
You can simplify the selects down to a single one. And, I would recommend using consistent naming conventions for parameters -- so there is unlikely to be confusion with columns:
您可以将选择简化为单个选择。而且,我建议对参数使用一致的命名约定——因此不太可能与列混淆:
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 ;
In addition:
此外:
- I put the
IF
on multiple lines. It is clearer that theEND IF
is there. In general, I put "end"s of things immediately under the "beginnings" to ensure proper closure. - 我把IF写在多行上。更清楚的是,如果有的话,就会结束。一般来说,我把事情的“结束”放在“开始”下面,以确保恰当的结束。
-
CONCAT_WS()
seems a better option thanCONCAT()
. As a bonus, it also handlesNULL
values. - CONCAT_WS()似乎比CONCAT()更好。此外,它还处理空值。
- I prefer the inline syntax of
:=
rather thanINTO
. This is really just a matter of style.INTO
is used for other purposes, such as file access, so I think:=
is clearer. - 我更喜欢:=的内联语法,而不是INTO语法。这真的只是风格问题。INTO用于其他目的,例如文件访问,所以我认为:=更清楚。
#3
0
Between you Begin and End statements 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, '')) 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;
在开始和结束语句之间选择CLIENT. NAME, CLIENT。NEW_GROUP_REFERENCE_NUMBER转换为NORMALIZED_ID、CONCAT(IFNULL(CLIENT. operation_street, ")、'、IFNULL(CLIENT. operation_city, ")、'、IFNULL(客户端)。OPERATION_STATE_PROVINCE, "), '', IFNULL(客户端。)到ccis_vendor的地址。client_id源内部连接ccis_vendor。应收客户端。id =来源。ACCOUNT_ID来源。number = source_id和SOURCE。部门= source_division;