举例1:创建存储过程select_all_data(),查看 emps 表的所有数据
DELIMITER //
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM employees;
END //
DELIMITER //
举例2:创建存储过程avg_employee_salary(),返回所有员工的平均工资
DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGIN
SELECT AVG(salary) AS avg_sal FROM employees ;
END //
DELIMITER //
举例3:创建存储过程show_max_salary(),用来查看“emps”表的最高薪资值。
DELIMITER //
CREATE PROCEDURE show_max_salary()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '查看最高薪资'
BEGIN
SELECT MAX(salary) FROM emps;
END //
DELIMITER ;
举例5:创建存储过程show_someone_salary(),查看“emps”表的某个员工的薪资,并用IN参数empname输入员工姓名。
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
SELECT salary FROM employees WHERE last_name=empname;
END //
DELIMITER ;
举例6:创建存储过程show_someone_salary2(),查看“emps”表的某个员工的薪资,并用IN参数empname输入员工姓名,用OUT参数empsalary输出员工薪资。
DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
BEGIN
SELECT salary INTO empsalary FROM employees WHERE last_name=empname;
END //
DELIMITER ;
举例7:创建存储过程show_mgr_name(),查询某个员工领导的姓名,并用INOUT参数“empname”输入员工姓名,输出领导的姓名。
DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
BEGIN
SELECT last_name INTO empname FROM employees WHERE employee_id=
(SELECT manager_id FROM employees WHERE last_name=empname);
END //
DELIMITER ;