Examples and Notes by Week

↑ Up to Parent directory
View Create_GetCustomerLevel.sql code.	Open/Save Create_GetCustomerLevel.sql.
View Create_GetCustomerLevel2.sql code.	Open/Save Create_GetCustomerLevel2.sql.
View Create_GetCustomerLevel3.sql code.	Open/Save Create_GetCustomerLevel3.sql.
View Create_GetCustomerShipping.sql code.	Open/Save Create_GetCustomerShipping.sql.
View Create_GetCustomers.sql code.	Open/Save Create_GetCustomers.sql.
View Create_GetOfficeByCountry.sql code.	Open/Save Create_GetOfficeByCountry.sql.
View Create_GetOrderCountByStatus.sql code.	Open/Save Create_GetOrderCountByStatus.sql.
View Create_GetTotalOrde_sessionr.sql code.	Open/Save Create_GetTotalOrde_sessionr.sql.
View Create_GetTotalOrder.sql code.	Open/Save Create_GetTotalOrder.sql.
View GetAllProducts.sql code.	Open/Save GetAllProducts.sql.
View LoopDemo.sql code.	Open/Save LoopDemo.sql.
View exit_handler.sql code.	Open/Save exit_handler.sql.
View while.sql code.	Open/Save while.sql.

The code for Create_GetCustomerLevel3.sql is:
DROP PROCEDURE IF EXISTS GetCustomerLevel3; DELIMITER $$ CREATE PROCEDURE GetCustomerLevel3( IN pCustomerNumber INT, OUT pCustomerLevel VARCHAR(20)) BEGIN DECLARE credit DECIMAL(10,2) DEFAULT 0; #SET pCustomerLevel = 'LEAD'; # Sets a default value. SELECT creditLimit INTO credit FROM customers WHERE customerNumber = pCustomerNumber; IF credit > 50000 THEN SET pCustomerLevel = 'PLATINUM'; ELSEIF credit <= 50000 AND credit > 10000 THEN SET pCustomerLevel = 'GOLD'; ELSE SET pCustomerLevel = 'SILVER'; END IF; END$$ DELIMITER ; CALL GetCustomerLevel3(141, @level); SELECT @level; CALL GetCustomerLevel3(103, @level); SELECT @level; CALL GetCustomerLevel3(447, @level); SELECT @level;