Example: Write Siatement using IF condition to select employees salary equal or greater than 10000. SELECT SSN, GENDER, IF(Salary>10000, "MORE", "LESS") FROM EMPLOYEE; 2. Local Variables Used to store values of any type within a stored programs - They are LOCAL because they can only be referenced within the same block they were declared in. MYSQL Server also supports Global - Variables[system-variables] Every Local variable must be defined by the DECLARE statement :Definition of Variable contain DECLARE VarName data_type [DEFAULT value] - Assignment of a value to a variable is done by one of two: The special form of SELECT statement ....SELECT ..... INTO user_var_name FROM The SET statement SET user_var_name =expr[, user_var_name = expr] ... Example: SET @passing=66; 1. only setting a variable SELECT @ namel:-'Hana' as name_;.-.2. or setting and retrieving a value as we use select alone SET@ssn=2; ROUTINES o stored procedures Stored routines are .functions o A stored procedure is invoked using the CALL statement. A procedure does not have a return value but can modify its parameters for later inspection by the caller. It can also generate result sets to be returned to the elient program A stored function is used much like a built-in function. you invoke it in an expression and it returns a value during expression evaluation - User-Defined Stored Procedure When a stored procedure is created, an optional list of parameters can be defined Stored Procedures can optionally return a value Stored Procedure Creation Syntax: CREATE PROCEDURE sp_name ([ IN | OUT | INOUT ] param_name type [] ) routine_body sp_name: Procedure Name IN parameter: passes a value into a procedure OUT Parameter: passes a value from the procedure back to the caller INOUT Parameter: parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns Example: Create procedure to calculate the number of followers for certain Neighborhood. USE e_volunteersys; SS Delimiter CREATE PROCEDURE NoOfFollowers(IN Nigh_Name varchar(20), OUT No_of_followers int) CALL Syntax: CALL sp_name([parameter [,]]) CALL sp_name[O] :Example ; CALL NoOfFollowers ('Alfaisalyah',(a Followers) 'SELECT@Followers as 'Number of Followers Functions return a value CREATE FUNCTION func_name ( param_name type [,]) RETURNS Type routine_body RETURNS: can be declared to use any valid data type Final Statement in the function block must be RETURN Example: Create a Function that calculates the number .of activities for a certain event ;USE e_volunteersys $ Delimiter CREATE FUNCTION Event_Activities(event_name varchar(20) ) RETURNS INT BEGIN ;DECLARE No_Activities int SELECT COUNT(AName) INTO No_Activities ;FROM Activity WHERE EName = event_name ;RETURN No_Activities \$\$;END Tasks/Assignments(s): 1. Write and call a procedure that takes the first name of the employee and print the results. 2. Write and call a procedure that returns the total number of employees. 3. Write and call a function that takes three integers and return the total number of the sum