Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 7

Trying to keep pace with work and porting Firebird stored procs to MySQL can be really hard ... anyway here are two sp's translated:

Firebird procedure to list some stats for departments by department head:

SET TERM ^ ;
CREATE PROCEDURE SUB_TOT_BUDGET (
HEAD_DEPT Char(3) )
RETURNS (
TOT_BUDGET Decimal(12,2),
AVG_BUDGET Decimal(12,2),
MIN_BUDGET Decimal(12,2),
MAX_BUDGET Decimal(12,2) )
ASBEGIN
SELECT SUM(budget), AVG(budget), MIN(budget), MAX(budget)
FROM department
WHERE head_dept = :head_dept
INTO :tot_budget, :avg_budget, :min_budget, :max_budget;
SUSPEND;
END^
SET TERM ; ^

So Firebird seems to be a bit more verbose, with complete declaration of parameters.
Here is the MySQL translation:

DELIMITER $

DROP PROCEDURE IF EXISTS `employee`.`sub_tot_budget` $$
CREATE PROCEDURE `employee`.`sub_tot_budget` (IN head_dept char(3))
BEGIN
SELECT SUM(d.budget), AVG(d.budget), MIN(d.budget), MAX(d.budget)
FROM department d
WHERE head_dept = head_dept;
END $$

DELIMITER ;

As you can see syntax is quite similar but more compact.

Now another one, a procedure to list the first five languages required for a job:

SET TERM ^ ;
CREATE PROCEDURE SHOW_LANGS (
CODE Varchar(5),
GRADE Smallint,
CTY Varchar(15) )
RETURNS (
LANGUAGES Varchar(15) )
ASDECLARE VARIABLE i INTEGER;
BEGIN
i = 1;
WHILE (i <= 5) DO BEGIN SELECT language_req[:i] FROM joB WHERE ((job_code = :code) AND (job_grade = :grade) AND (job_country = :cty) AND (language_req IS NOT NULL)) INTO :languages; IF (languages = ' ') THEN /* Prints 'NULL' instead of blanks */ languages = 'NULL'; i = i +1; SUSPEND; END END^ SET TERM ; ^

Probably the employee.fdb database hasn't changed from the old Firebird 1 (and InterBase) days, note the workaround used to get the first 5 languages, it's now superseded by the FIRST ... SKIP clause introduced in Firebird 1.5 which is equivalent to MySQL's LIMIT clause.

The MySQL version is:

DELIMITER $

DROP PROCEDURE IF EXISTS `employee`.`show_langs` $$
CREATE PROCEDURE `employee`.`show_langs` (IN code varchar(5), IN grade SMALLINT, IN cty varchar(15))
BEGIN
SELECT j.language_req FROM job j
WHERE j.job_code = code AND j.job_grade = grade AND j.job_country = cty
LIMIT 5;
END $$

DELIMITER ;

The sintax is similar again, still I find Firebird procs a bit more readable ...

Another easy take:


SET TERM ^ ;
CREATE PROCEDURE GET_EMP_PROJ (
EMP_NO Smallint )
RETURNS (
PROJ_ID Char(5) )
ASBEGIN
FOR SELECT proj_id
FROM employee_project
WHERE emp_no = :emp_no
INTO :proj_id
DO
SUSPEND;
END^
SET TERM ; ^

Becomes:

DELIMITER $$

DROP PROCEDURE IF EXISTS `employee`.`get_emp_proj` $$
CREATE PROCEDURE `employee`.`get_emp_proj` (IN empno SMALLINT)
BEGIN
SELECT proj_id FROM employee_project WHERE emp_no = empno;
END $$

DELIMITER ;