1. set the delimiter to $ 2. create stored procedure commissioncheck to do the following a. parameter list includes i. in employeenum int ii. inout commission varchar(4000)
b. Declare the following variables i. v_finished integer DEFAULT 0 ii. v_lines varchar(100) DEFAULT " iii. v_eFirst varchar(100) DEFAULT "" iv. v_eLast varchar(100) DEFAULT "" v. v_eAddress varchar(100) DEFAULT "" vi. v_eCity varchar(100) DEFAULT ""; vii. v_eState varchar(100) DEFAULT "" viii. v_eZip varchar(100) DEFAULT "" ix. v_retail decimal(8,2) DEFAULT
0.0 x. v_pay decimal(8,2) DEFAULT 0.0 xi. v_count integer DEFAULT 0 xii. __eight decimal(3,2) DEFAULT .08 xiii. v_ten decimal (3,2) DEFAULT .10 xiv. v_fifteen decimal(3,2) DEFAULT .15 xv. v_twenty decimal (3,2) DEFAULT .20
c. Declare cursor employee_cursor for the following join query i. Select 1. firstName from table employee 2. lastName from table employee 3. address from table employee 4. city from table cityState 5. state from table cityState 6. zipCode from table cityState 7. salePrice from table sale ii. Where column employeeId in table sale equals the value in parameter employeeNum d. Declare continue exception handler for not found set variable v_finished equal to 1 e. Open cursor employee_cursor f. Loop through the employee_cursor, using loop label get_employee i. Fetch employee_cursor into variables 1. v_eFirst 2. v_eLast 3. v_eAddress 4. v_eCity 5.v