如何在MySQL中将2个procuderes聚合为1个？

Question

我有两个看起来几乎相同的程序。一个人收到位置和价格并进行操作，另一个人收到体验和价格。< /p>

第一个：

-- Returns: service providers in given location and price
DELIMITER &&  
CREATE PROCEDURE get_service_providers_location_price (IN id_location INT,IN price DOUBLE,IN limite INT, IN inicio INT)  
BEGIN  
    SELECT user.idUser, user.name,user.lastActivity,user.active,serviceprovider.description,location.name AS location, location.cordsX, location.cordsY, file.image FROM user
    INNER JOIN location ON user.idLocation = location.idLocation
    INNER JOIN file ON user.idUser = file.idUser
    INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
    INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
        WHERE user.type = 3 AND user.idLocation = id_location  
            AND (category_has_serviceprovider.price <= price OR category_has_serviceprovider.price IS NULL) and serviceprovider.idSubscription != 1 
    ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC LIMIT limite OFFSET inicio;
END &&  
DELIMITER ;

第二个：

-- Returns: service providers in given experience and price
DELIMITER &&  
CREATE PROCEDURE get_service_providers_experience_price (IN experience INT, IN price DOUBLE,IN limite INT, IN inicio INT)  
BEGIN  
    SELECT user.idUser, user.name,user.lastActivity,user.active,serviceprovider.description,location.name AS location, location.cordsX, location.cordsY, file.image FROM user
    INNER JOIN location ON user.idLocation = location.idLocation
    INNER JOIN file ON user.idUser = file.idUser
    INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
    INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
        WHERE user.type = 3
            AND (category_has_serviceprovider.price <= price OR category_has_serviceprovider.price IS NULL) AND category_has_serviceprovider.experience >= experience and serviceprovider.idSubscription != 1 
    ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC LIMIT limite OFFSET inicio;
END &&  
DELIMITER ;

如您所见，只有 WHERE 子句发生了变化。在 MySQL 中，是否可以将这两个过程聚合为一个？因为我有大约 5 个看起来相同的程序，但它只是改变了 WHERE 子句，我发现为每种情况执行单独的过程很烦人。

Answer 1

您可以使用 IFNULL。传递 experience 或 id_location 值并使用 NULL 作为另一个值。

为参数制定命名方案（此处为 in_-prefix）也是一个好习惯，以便参数与列名称不同。

DELIMITER &&  
CREATE PROCEDURE get_service_providers_experience_price (
in_experience INT, 
in_id_location INT, 
in_price DOUBLE,
in_limite INT, 
in_inicio INT
)  
BEGIN  
SELECT 
  user.idUser, 
  user.name,
  user.lastActivity,
  user.active,
  serviceprovider.description,
  location.name AS location, 
  location.cordsX, 
  location.cordsY, 
  file.image 
FROM user
  INNER JOIN location ON user.idLocation = location.idLocation
  INNER JOIN file ON user.idUser = file.idUser
  INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
  INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
WHERE user.type = 3
  AND (category_has_serviceprovider.price <= in_price OR category_has_serviceprovider.price IS NULL) 
  AND category_has_serviceprovider.experience >= IFNULL(in_experience, category_has_serviceprovider.experience)
  AND user.idLocation = IFNULL(id_location, user.idLocation)
  AND serviceprovider.idSubscription != 1 
ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC 
LIMIT in_limite OFFSET in_inicio;

END 
&&  
DELIMITER ;

Answer 2

例如，您可以使用这个：

CREATE PROCEDURE get_service_providers_price (IN experience INT,IN id_location INT,IN price DOUBLE,IN limite INT, IN inicio INT)  
BEGIN  
SELECT user.idUser, user.name,user.lastActivity,user.active,serviceprovider.description,location.name AS location, location.cordsX, location.cordsY, file.image FROM user
INNER JOIN location ON user.idLocation = location.idLocation
INNER JOIN file ON user.idUser = file.idUser
INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
WHERE user.type = 3 
  AND (category_has_serviceprovider.price <= price OR category_has_serviceprovider.price IS NULL) 
  and serviceprovider.idSubscription != 1 
  AND CASE WHEN experience IS NOT NULL
           THEN category_has_serviceprovider.experience >= experience
           ELSE user.idLocation = id_location  
           END
ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC LIMIT limite OFFSET inicio;

如果提供 IN 经验 INT 设置为某个值，则应用它的条件。如果您为此参数提供 NULL，则应用 IN id_location INT 的条件。

注意 - 现在你的 SP 有 5 个参数，而不是 4 个。

PS。您的 SP 包含一条 SQL 语句 - 因此不需要 BEGIN-END 和 DELIMITER。

PPS。使用类似的方法，您可以创建 SP，它可以同时应用其中一个条件，也可以同时应用这两个条件，或者都不应用。例如，它可以是：

AND CASE WHEN experience IS NOT NULL AND id_location IS NOT NULL  -- apply both parameters filtering
         THEN category_has_serviceprovider.experience >= experience AND user.idLocation = id_location
         WHEN experience IS NOT NULL   -- apply filtering by experience  only
         THEN category_has_serviceprovider.experience >= experience
         WHEN id_location IS NOT NULL   -- apply filtering by location only
         THEN user.idLocation = id_location
         ELSE 1    -- not filter, return all rows
         END

购买力平价。如果您想要有 2 个独立的函数但有一份代码副本（例如，这些函数名称已在一堆代码中使用），那么您可以这样做：

CREATE PROCEDURE get_service_providers_location_price (IN id_location INT,IN price DOUBLE,IN limite INT, IN inicio INT)
CALL get_service_providers_price (NULL, id_location, price, limite, inicio);

CREATE PROCEDURE get_service_providers_experience_price (IN experience INT, IN price DOUBLE,IN limite INT, IN inicio INT)
CALL get_service_providers_price (experience, NULL, price, limite, inicio);