«

MySQL的存储函数与存储过程实例代码分析

时间:2024-8-5 09:34     作者:韩俊     分类: 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_nameG;
SHOW  CREATE FUNCTION email_by_idG;

使用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

热门推荐