StoredProcedure for Mysql

mysql_storedprocedure.sql

DELIMITER //
DROP PROCEDURE IF EXISTS addnewemployee //
CREATE PROCEDURE addnewemployee 
(
IN eno     int(11),
IN ebdate  date,
IN efname  varchar(14),
IN elname  varchar(16),
IN egender enum('M', 'F'),
IN ehdate  date
)
BEGIN
  INSERT INTO employees(emp_no, birth_date, first_name, last_name, gender, hire_date) 
  values (eno, ebdate, efname, elname, egender, ehdate);
END//
DELIMITER ;

DELIMITER //
DROP PROCEDURE IF EXISTS gettotalnumberofemployee //
CREATE PROCEDURE gettotalnumberofemployee
(
IN ebdate date,
IN egender enum,
OUT emcount int
)
BEGIN
 select count(*) into emcount from employees where birth_date < ebdate and gender = egender;
END //
DELIMITER;


-- INOUT Example
DELIMITER //
DROP PROCEDURE IF EXISTS getemployeebyid //
CREATE PROCEDURE getemployeebyid
(
IN enum int(11),
OUT emno int(11),
OUT bdate date,
OUT fname varchar(14),
OUT lname varchar(16),
OUT gend enum('M', 'F'),
OUT hdate date
)
BEGIN
 select emp_no into emno from employees where emp_no = enum;
 select birth_date into bdate from employees where emp_no = enum;
 select first_name into fname from employees where emp_no = enum;
 select last_name into lname from employees where emp_no = enum;
 select gender into gend from employees where emp_no = enum;
 select hire_date into hdate from employees where emp_no = enum;
END //
DELIMITER ;

DELIMITER //
DROP PROCEDURE IF EXISTS registeremployee //
CREATE PROCEDURE registeremployee
(
IN eno int(11),
IN bdate date,
IN fname varchar(14),
IN lname varchar(16),
IN gend enum('M', 'F'),
IN hdate date,
OUT uid int(11)
)
BEGIN
 insert into newemployees(emp_no, birth_date, first_name, last_name, gender, hire_date)
 values(eno, bdate, fname, lname, gend, hdate);
 select id into uid from newemployees where emp_no = eno;
END //
DELIMITER ;

-- Resultsets
DELIMITER //
DROP PROCEDURE IF EXISTS getEmployeesByDept //
CREATE PROCEDURE getEmployeesByDept
(
    IN dnum VARCHAR(11)
    -- OUT em_dept VARCHAR(255),
    -- OUT finished INTEGER(11)
)
BEGIN
-- DECLARE cursor_name CURSOR FOR
SELECT e.emp_no as Emp_Number, e.first_name as Emp_Firstname, s.salary as Emp_Salary, d.dept_no as Emp_Dept
FROM employees e
  INNER JOIN dept_emp d ON e.emp_no = d.emp_no
  INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE d.dept_no = dnum
ORDER BY e.last_name;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
-- OPEN cursor_name;
-- FETCH cursor_name INTO em_dept;
-- CLOSE cursor_name;
END //
DELIMITER ;