Conditional Stored Prosedure

Created GetCustomerLevel 

masukan kode :

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(

    IN  pCustomerNumber INT, 

    OUT pCustomerLevel  VARCHAR(20))

BEGIN

    DECLARE credit DECIMAL(10,2) DEFAULT 0;

    SELECT creditLimit 

    INTO credit

    FROM customers

    WHERE customerNumber = pCustomerNumber;

    IF credit > 50000 THEN

        SET pCustomerLevel = 'PLATINUM';

    END IF;

END$$

DELIMITER ;

Jika sudah berhasil 

Hasil Output bisa dilihat di kolom Routine 

Melihat Data :

Masukan Kode : 

SELECT 

    customerNumber, 

    creditLimit

FROM 

    customers

WHERE 

    creditLimit > 50000

ORDER BY 

    creditLimit DESC;

Hasil Output :

 stored procedure for customer 141

Masukan Kode :

CALL GetCustomerLevel(141, @level);

SELECT @level;