MySQL的存储函数与存储过程实例代码剖析
|
这篇文章主要介绍了MySQL的存储函数与存储过程实例代码分析的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇MySQL的存储函数与存储过程实例代码分析文章都会有所收获，下面我们一起来看看吧。 MySQL存储过程与存储函数的相关概念 存储函数和存储过程的主要区别： 存储函数一定会有返回值的 存储过程不一定有返回值 存储过程和函数能后将复杂的SQL逻辑封装在一起，应用程序无需关注存储过程和函数内部复杂的SQL逻辑，而只需要简单地调用存储过程和函数即可 存储过程 一组预先编译的SQL语句的封装 执行过程：执行过程预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行 简化操作，提高了SQL语句的重用性，减少了开发程序员的压力 减少操作过程中的失误，提高效率 减少网路传输量，客户端不需要将所有的SQL语句通过网络发给服务器 减少SQL语句暴露在网上的风险，提高数据查询的安全性 与视图，函数的对比： 视图：是虚拟表，通常不对底层数据表直接操作 存储过程：程序化的SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理 相较于函数，存储过程没有返回值 分类 没有参数（无参数无返回） 仅仅带有IN 类型 （有参数无返回） 仅仅带OUT类型（无参数有返回） 即带IN又带OUT(有参数有返回) 带INOUT(有参数有返回) 创建存储过程 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 参数类型，FUNCTION 中总是默认为IN参数 RETURNS type 表示函数返回数据的类型，对于函数而言是强制的 characteristics 表示创建函数时指定的对函数的约束 函数题可以用BEGIN … END表示SQL代码的开始和结束。如果函数体只有一条语句，则可以省略BEGIN … 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); 存储函数与存储过程的对比 存储过程 PEOCEDURE 存储函数 FUNCTION 调用语法 CALL 存储过程 SELECT 存储函数 存储过程返回值可以有0个或对各 存储函数返回值只有一个 存储过程一般用于更新操作 存储函数一般用于查询结果为一个值并返回 存储函数可以放在查询语句中使用，存储过程则不行 存储过程功能更为强大，包括能够执行对表的操作（创建表，删除表）和事务操作，这些功能是存储函数并不具备的 存储过程和函数的查看修改删除 查看 使用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] 存储过程或函数名 关于“MySQL的存储函数与存储过程实例代码分析”这篇文章的内容就介绍到这里，感谢各位的阅读！ （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
