Function and Procedures

 

===============================================

DELIMITER //

CREATE PROCEDURE GetNameByID(IN employeeId int(4))

BEGIN

SELECT * FROM employee WHERE empNo = employeeId;

END //

DELIMITER ;

call GetNameByID(2001);

===============================================

DELIMITER //

CREATE PROCEDURE CountByDept(IN deptName VARCHAR(25), OUT total INT)

BEGIN

SELECT count(empNo) INTO total FROM employee WHERE dept = deptName;

END //

DELIMITER ;

call CountByDept('HR',@total);

select @total;

===============================================

DELIMITER //

CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))

BEGIN

SET count = count + inc;

END //

DELIMITER ;

SET @counter = 1;

CALL set_counter(@counter,1); -- 2

CALL set_counter(@counter,1); -- 3

CALL set_counter(@counter,5); -- 8

SELECT @counter; -- 8

===============================================

DROP FUNCTION IF EXISTS countByDeptFn;

DELIMITER //

CREATE FUNCTION countByDeptFn(deptName VARCHAR(25)) RETURNS int DETERMINISTIC

BEGIN

DECLARE total INT;

SELECT count(empNo) into total FROM employee WHERE dept = deptName;

RETURN total;

END //

DELIMITER ;

select countByDeptFn('HR') as EmpCount;