Examples and Notes by Week

↑ Up to Parent directory
View Create_GetCustomerLevel_proc.sql code.	Open/Save Create_GetCustomerLevel_proc.sql.
View Create_and_test_beforeDelete_trig.sql code.	Open/Save Create_and_test_beforeDelete_trig.sql.
View Create_before_employee_update_trig.sql code.	Open/Save Create_before_employee_update_trig.sql.
View Create_createEmailList_proc.sql code.	Open/Save Create_createEmailList_proc.sql.
View Create_employees_audit_table.sql code.	Open/Save Create_employees_audit_table.sql.
View Create_trigger1.sql code.	Open/Save Create_trigger1.sql.
View Create_trigger2.sql code.	Open/Save Create_trigger2.sql.
View Create_trigger3.sql code.	Open/Save Create_trigger3.sql.
View CustomerLevel_Func.sql code.	Open/Save CustomerLevel_Func.sql.
View Test_before_employee_update.sql code.	Open/Save Test_before_employee_update.sql.
View notes.txt code.	Open/Save notes.txt.
View outputEmailList.sql code.	Open/Save outputEmailList.sql.

The code for Create_createEmailList_proc.sql is:
DROP PROCEDURE IF EXISTS createEmailList; DELIMITER $$ CREATE PROCEDURE createEmailList ( INOUT emailList varchar(4000) ) BEGIN DECLARE finished INTEGER DEFAULT 0; DECLARE fn varchar(100) DEFAULT ""; DECLARE ln varchar(100) DEFAULT ""; DECLARE emailAddress varchar(100) DEFAULT ""; -- declare cursor for employee email DEClARE curEmail CURSOR FOR SELECT firstName, lastName, email FROM employees order by lastName DESC, firstName DESC; -- declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; OPEN curEmail; getEmail: LOOP FETCH curEmail INTO fn, ln, emailAddress; IF finished = 1 THEN LEAVE getEmail; END IF; -- build email list SET emailList = CONCAT(fn," ",ln," <",emailAddress,">; ",emailList); END LOOP getEmail; CLOSE curEmail; END$$ DELIMITER ;