存储函数和存储过程的主要区别:
存储过程和函数能后将复杂的SQL逻辑封装在一起,应用程序无需关注存储过程和函数内部复杂的SQL逻辑,而只需要简单地调用存储过程和函数即可
一组预先编译的SQL语句的封装
执行过程:执行过程预先存储在MySQL服务器上,需要执行的时候,客户端只需要向服务器发出调用存储过程的命令,服务器端就可以把预先存储好的这一系列SQL语句全部执行
与视图,函数的对比:
分类
创建存储过程
DELIMITER $
CREATE PROCEDURE 存储过程名 (IN|OUT|INOUT 参数名 参数类型,...)
[characteristics]
BEGIN
存储过程体
END $DELIMITER ;
DELIMITER $ CREATE PROCEDURE select_all_data() BEGIN SELECT * FROM employees; END $ DELIMITER ;
调用存储过程
CALL select_all_data();
无参数无返回值
DELIMITER // CREATE PROCEDURE avg_employee_salary() BEGIN SELECT AVG(salary) FROM emp; END // DELIMITER ; CALL avg_employee_salary();
无参数有返回值
DELIMITER // CREATE PROCEDURE show_min_salart(OUT ms DOUBLE) BEGIN SELECT MIN(salary) INTO ms FROM emp; END // DELIMITER ; CALL show_min_salart(@ms); SELECT @ms;
有参数无返回值
DELIMITER // CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20)) BEGIN SELECT salary FROM emp WHERE last_name=empname; END // DELIMITER ; CALL show_someone_salary('Abel'); SET @empname='Abel'; CALL show_someone_salary(@empname)
有参数有返回值
DELIMITER // CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2)) BEGIN SELECT salary INTO empsalary FROM emp WHERE last_name=empname; END // DELIMITER ; SET @empname='Abel'; CALL show_someone_salary2(@empname,@empsalary); SELECT @empsalary;
带INOUT
DELIMITER // CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25)) BEGIN SELECT last_name FROM emp Where employee_id= ( SELECT manager_id FROM emp WHERE last_name=empname ); END // DELIMITER ; SET @empname='Abel'; CALL show_mgr_name(@empname); SELECT @empname;
如何调试
通过SELECT语句,把程序执行的中间结果查询出来,从而调试一个SQL语句的正确性。调试成功之后,把SELECT语句后移到下一个SQL语句,逐步推进查询下一个 SQL语句
MySQL允许用户自定义函数,自定义好了之后,调用方式与调用MySQL预定义的系统函数一样
创建存储函数
CREATE FUNCTION 函数名(参数名 参数类型)
RETURUNS 返回值类型
[characteristics]
BEGIN
函数体 #函数体中肯定有RETURN语句
END
调用存储函数
SELECT 函数名(实参列表)
练习一
DELIMITER // CREATE FUNCTION email_by_name() RETURNS VARCHAR(25) BEGIN RETURN ( SELECT email FROM emp WHERE last_name='Abel' ); END // DELIMITER ; SELECT email_by_name();
练习2
DELIMITER // CREATE FUNCTION email_by_id(emp_id INT) RETURNS VARCHAR(25) BEGIN RETURN ( SELECT email FROM emp WHERE employee_id=emp_id ); END // DELIMITER ; SELECT email_by_id(101); SET @emp_id=102; SELECT email_by_id(@emp_id);
查看
使用SHOW CREATE 语句 查看创建信息
SHOW CREATE PROCEDURE show_mgr_name\G; SHOW CREATE FUNCTION email_by_id\G;
使用SHOW STATUS 语句查看存储过程和函数的状态信息
SHOW PROCEDURE STATUS; SHOW PROCEDURE STATUS LIKE 'show_mgr_name' ; SHOW FUNCTION STATUS LIKE 'email_by_name' ;
从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='email_by_id' AND ROUTINE_TYPE='FUNCTION'; SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='show_min_salart' AND ROUTINE_TYPE='PROCEDURE';
修改存储过程与函数
修改存储过程或函数,不影响存储过程或函数功能,只是修改相关特征,使用ALTER语句实现
ALTER PROCEDURE|FUNCTION 存储过程或函数名 [characteristic ...]
删除存储过程或函数
DROP PROCEDURE|FUNCTION [IF EXISTS] 存储过程或函数名