Bagaimana untuk mengagregat 2 procuderes menjadi 1 dalam MySQL?

Question

Saya mempunyai dua program yang kelihatan hampir sama. Seorang menerima Lokasi dan Harga dan menjalankan operasi, seorang lagi menerima Pengalaman dan Harga. < /p>

Yang pertama:

-- 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 ;

Kedua:

-- 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 ;

Seperti yang anda lihat, hanya klausa WHERE sahaja yang telah berubah. Dalam MySQL, adakah mungkin untuk mengagregatkan kedua-dua prosedur ini menjadi satu? Kerana saya mempunyai kira-kira 5 program yang kelihatan sama tetapi ia hanya berubah Klausa WHERE, saya rasa menjengkelkan untuk melakukan proses berasingan untuk setiap kes.

Answer 1

Anda boleh menggunakan IFNULL. Lulus experience 或 id_location 值并使用 NULL sebagai nilai lain.

Adalah juga amalan yang baik untuk mempunyai skema penamaan (di sini in_-prefix) untuk parameter supaya parameter berbeza daripada nama lajur.

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

Sebagai contoh, anda boleh menggunakan ini:

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;

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

NOTA - SP anda kini mempunyai 5 parameter dan bukannya 4.

PS. SP anda mengandungi satu pernyataan SQL - jadi BEGIN-END dan DELIMITER tidak diperlukan.

PPS. Menggunakan pendekatan yang serupa, anda boleh mencipta SP yang menggunakan satu, kedua-duanya, atau kedua-dua syarat. Contohnya, boleh jadi:

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

Pariti kuasa beli. Jika anda ingin mempunyai 2 fungsi berasingan tetapi mempunyai salinan kod (contohnya, nama fungsi ini telah digunakan dalam sekumpulan kod), maka anda boleh melakukan ini:

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);