储存过程

封装查询员工张旭的信息

delimiter $$
CREATE PROCEDURE search_zhangxu_info()
BEGIN

SELECT * FROM staff WHERE staff.`name` = '张旭'

END $$
delimiter;

封装通过姓名查询员工的信息

delimiter $$
CREATE PROCEDURE search_name_info(IN in_name VARCHAR(10))
BEGIN

SELECT * FROM staff WHERE staff.`name` = in_name;

END $$
delimiter;

封装通过姓名查询员工的工作部门名称的存储过程

delimiter $$
CREATE PROCEDURE search_section_info_from_name(IN in_name VARCHAR(10), OUT out_section VARCHAR(20))
BEGIN

SELECT section.section_title INTO out_section FROM staff INNER JOIN section ON section.section_id = staff.section_id WHERE staff.name = in_name;

END $$
delimiter;

封装通过姓名查询员工的薪资，计算对于员工姓名、年薪的存储过程

delimiter $$
CREATE PROCEDURE search_section_info_from_name(IN in_name VARCHAR(10), OUT out_money VARCHAR(20))
BEGIN

DECLARE money_data FLOAT4;

SELECT money INTO money_data FROM staff WHERE staff.name = in_name;

SET out_money = money_data * 12;

END $$
delimiter;