MySQL存储过程查询

根据tbl_employees创建存储过程：employeebyid(IN iemployee_id CHAR(10), OUT oname VARCHAR(15), OUT oyear INT)

要求：根据输入的职工编号(iemployee_id )，返回该id对应的姓名(oname )、工龄(oyear，当前年份-参加工作的年份时间)。

测试数据：查询IN参数为01007的信息，OUT 参数的返回结果分别为 杨子盈 33。

img

CREATE PROCEDURE employeebyid(IN iemployee_id CHAR(10), OUT oname VARCHAR(15), OUT oyear INT)
BEGIN
  SELECT first_name, last_name, YEAR(CURDATE()) - YEAR(hire_date) AS years_worked
  INTO oname, @last_name, oyear
  FROM employees
  WHERE emp_no = iemployee_id;
  
  SET oname = CONCAT(oname, ' ', @last_name);
END;