mysql - MySQL存储过程：如何缩短语句？

Question

只是想就在 MySQL 数据库中创建存储过程向您寻求帮助。这是我第一次创建存储过程。

很可能，我曾经创建视图，但这次，我需要在 mysql 数据库中实现一个存储过程。我在这里创建了一个简单的，但我认为它在声明中是多余的。有没有人可以帮我缩短声明？

有了这个，它可以帮助我学习更多关于存储过程的知识。请参阅代码以供参考。非常感谢您的帮助。

DELIMITER $$

USE `doris_master_data`$$

DROP PROCEDURE IF EXISTS `webserver_service_order`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `webserver_service_order`(
IN xselection BIGINT(8),
IN xreceived_from_date BIGINT(8),
IN xreceived_to_date BIGINT(8))
BEGIN

IF xselection=0 THEN

SELECT DISTINCT
so.service_code,
sl.service_name,
xx.Total_Services
FROM 
service_order so
INNER JOIN services_lists sl ON so.service_code=sl.service_code
LEFT OUTER JOIN 
(
    SELECT
    a.service_code,
    COUNT(DISTINCT CONCAT(a.service_code,'-',a.service_order_code)) AS Total_Services
    FROM
    service_order a
    WHERE
    (a.service_order_date_received BETWEEN xreceived_from_date AND xreceived_to_date)
    AND a.service_code_is_active=1
    AND a.requirement_code_is_active=1
    GROUP BY a.service_code
) AS xx ON so.service_code=xx.service_code
WHERE
(so.service_order_date_received BETWEEN xreceived_from_date AND xreceived_to_date)
AND so.service_code_is_active=1
AND so.requirement_code_is_active=1;

ELSE
SELECT DISTINCT
so.service_code,
sl.service_name,
xx.Total_Services
FROM 
service_order so
INNER JOIN services_lists sl ON so.service_code=sl.service_code
LEFT OUTER JOIN 
(
    SELECT
    a.service_code,
    COUNT(DISTINCT CONCAT(a.service_code,'-',a.service_order_code)) AS Total_Services
    FROM
    service_order a
    WHERE
    (a.service_order_date_received BETWEEN xreceived_from_date AND xreceived_to_date)
    AND a.service_code_is_active=1
    AND a.requirement_code_is_active=1
    GROUP BY a.service_code
) AS xx ON so.service_code=xx.service_code
WHERE
so.service_code=xselection AND
(so.service_order_date_received BETWEEN xreceived_from_date AND xreceived_to_date)
AND so.service_code_is_active=1
AND so.requirement_code_is_active=1;

END IF;


END$$

DELIMITER ;

标签： mysqlstored-procedures