MySQL stored procedures and functions

MySQL stored procedures and functions
Stored Procedures and functions: 1. create a stored procedure and function: stored procedure: delimiter $ create procedure proc_name () BEGIN query statement; // remember the plus sign END $ delimiter; function: delimiter $ create function func_name (attribute field data type) returns data type // the data type is the same as the data type of the attribute field queried in the following query statement BEGINreturn (query statement) END $ delimiter; 2. use stored procedures and functions: stored procedures: call proc_name; functions: select func_name (parameter); 3. view the stored procedure: show procedure status; show function status; show create procedure proc_name; show create function func_name; 4. repair Change: alter procedure procedure_name [characteristic…] Alter function function_name [characteristic…] 5. delete: drop procedure proce_name; drop function func_name; example: drop procedure if exists emplayee_count; DELIMITER $ # create procedure emplayee_count (out num integer) BEGIN # DECLARE the variable DECLARE emplayee_sal INTEGER; DECLARE flag INTEGER; # DECLARE the cursor declare cursor_emplayee cursor for select sal FROM t_employee; declare continue handler for not found set flag = 1; # SET end flag = 0; set num = 0; '# OPEN the cursor OPEN cursor_emplayee; # traverse the result set fetch cursor_emplayee INTO emplayee_sal pointed by the cursor; WHILE flag <> 1 DOIF emplayee_sal> 999 THENSET num = num + 1; end if; FETCH cursor_emplayee INTO emplayee_sal; end while; # CLOSE the cursor CLOSE cursor_emplayee; END $ DELIMITER; How to Use: call emplayee_count (@ count); select @ count;