mysql的存储函数与存储过程

于 2023-05-08 11:01:31 发布
阅读量281 收藏
点赞数
分类专栏： mysql 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fengge55/article/details/130553891
版权

  1. 存储过程概述 

   我们前面所学习的 MySQL 语句都是针对一个表或几个表的单条 SQL 语句，但是在数据库的实际操作中，经常会有需要多条 SQL 语句处理多个表才能完成的操作。例如，为了确认学生能否毕业，需要同时查询学生档案表、成绩表和综合表，此时就需要使用多条 SQL 语句来针对这几个数据表完成处理要求。

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用CALL 存储过程名字即可自动完成。

常用操作数据库的 SQL 语句在执行的时候需要先编译，然后执行。存储过程则采用另一种方式来执行 SQL 语句。

含义：存储过程的英文是 Stored Procedure。它的思想很简单，就是一组经过预先编译的 SQL 语句的封装。

执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

好处：

  • 简化操作，提高了sql语句的重用性，减少了开发程序员的压力
  • 减少操作过程中的失误，提高效率
  • 减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）
  • 减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

和视图、函数的对比：

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的。

1.2 分类

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

  • 没有参数（无参数无返回）
  • 仅仅带 IN 类型（有参数无返回）
  • 仅仅带 OUT 类型（无参数有返回）
  • 既带 IN 又带 OUT（有参数有返回）
  • 带 INOUT（有参数有返回）

注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

2. 创建存储过程

2.1 语法分析

语法：

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	存储过程体
END

类似于Java中的方法：

修饰符 返回类型 方法名(参数类型 参数名,...){

	方法体;
}

说明：

1、参数前面的符号的意思

  • IN：当前参数为输入参数，也就是表示入参；

    存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。

  • OUT：当前参数为输出参数，也就是表示出参；

    执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。

  • INOUT：当前参数既可以为输入参数，也可以为输出参数。

2、形参类型可以是 MySQL数据库中的任意类型。

3、characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

  • LANGUAGE SQL：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。

  • [NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。

  • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。

    • CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
    • NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
    • READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
    • MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
    • 默认情况下，系统会指定为CONTAINS SQL。

  • SQL SECURITY { DEFINER | INVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。

    • DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
    • INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
    • 如果没有设置相关的值，则MySQL默认指定值为DEFINER。

  • COMMENT 'string'：注释信息，可以用来描述存储过程。

4、存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END

编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。

1. BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
3. SET：赋值语句，用于对变量进行赋值。
4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

5、需要设置新的结束标记

DELIMITER 新的结束标记

因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

比如：“DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符。

示例：

DELIMITER $

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
[characteristics ...]
BEGIN
	sql语句1;
	sql语句2;

END $

#0.准备工作

CREATE DATABASE dbtest15;

USE dbtest15;

CREATE TABLE employees
AS
SELECT * 
FROM atguigudb.`employees`;

CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;

SELECT * FROM employees;

SELECT * FROM departments;

#1. 创建存储过程

#类型1：无参数无返回值

#举例1：创建存储过程select_all_data()，查看 employees 表的所有数据

DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN
    SELECT * FROM employees;
END $

DELIMITER ;

#2. 存储过程的调用

CALL select_all_data();

#举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资

DELIMITER //

CREATE PROCEDURE avg_employee_salary()
BEGIN 
    SELECT AVG(salary) FROM employees;
    
END //

DELIMITER ;

#调用
CALL avg_employee_salary();


#举例3：创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值。

DELIMITER //

CREATE PROCEDURE show_max_salary()
BEGIN
    SELECT MAX(salary)
    FROM employees;
END //

DELIMITER ;

#调用
CALL show_max_salary();

#类型2：带 OUT
#举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资
#通过OUT参数“ms”输出

DESC employees;

DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
    SELECT MIN(salary) INTO ms
    FROM employees;
END //

DELIMITER ;

#调用
CALL show_min_salary(@ms);

#查看变量值
SELECT @ms;

#类型3：带 IN
#举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，
#并用IN参数empname输入员工姓名。

DELIMITER //

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
    SELECT salary FROM employees
    WHERE last_name = empname;
END //

DELIMITER ;

#调用方式1
CALL show_someone_salary('Abel');
#调用方式2
SET @empname := 'Abel';
CALL show_someone_salary(@empname);


SELECT * FROM employees WHERE last_name = 'Abel';

#类型4：带 IN 和 OUT
#举例6：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，
#并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。

DELIMITER //

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
BEGIN
    SELECT salary INTO empsalary
    FROM employees
    WHERE last_name = empname;
END //

DELIMITER ;

#调用
SET @empname = 'Abel';
CALL show_someone_salary2(@empname,@empsalary);

SELECT @empsalary;


#类型5：带 INOUT
#举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，
#输出领导的姓名。

DESC employees;

DELIMITER $

CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN

    SELECT last_name INTO empname
    FROM employees
    WHERE employee_id = (
                SELECT manager_id
                FROM employees
                WHERE last_name = empname
                );
    
END $

DELIMITER ;

#调用
SET @empname := 'Abel';
CALL show_mgr_name(@empname);

SELECT @empname;

一：存储函数与存储过程的区别

1.存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。

2.存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。

3.存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；

4.存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。

5.存储过程可以调用存储函数、但函数不能调用存储过程。

6.存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.

参数说明：
（1）func_name ：存储函数的名称。
（2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。
（3）RETURNS type：指定返回值的类型。
（4）characteristic：可选项，指定存储函数的特性。
（5）routine_body：SQL代码内容。

（6）函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END

create database mydb9_function;
-- 导入测试数据
use mydb9_function;
set global log_bin_trust_function_creators=TRUE; -- 信任子程序的创建者

-- 创建存储函数-没有输输入参数
drop function if exists myfunc1_emp;
delimiter $$
create function myfunc1_emp()
returns int
    LANGUAGE SQL
	NOT DETERMINISTIC
	READS SQL DATA
	SQL SECURITY DEFINER
	COMMENT '查询xx'
begin
    declare cnt int default 0;
    select count(*) into  cnt from emp;
    return cnt;
end $$
delimiter ;

-- 调用存储函数
select myfunc1_emp();

 列子：

DELIMITER $
CREATE FUNCTION get_count()
RETURNS INT
    LANGUAGE SQL
	NOT DETERMINISTIC
	READS SQL DATA
	SQL SECURITY DEFINER
	COMMENT '查询部门人员'
BEGIN
  DECLARE emp_num INT;
  SELECT COUNT(`employee_id`) INTO emp_num FROM employees;
  RETURN emp_num;
END $

DELIMITER ;


#调用存储函数
SELECT get_count();

# 举例1：创建存储函数，名称为email_by_name()，参数定义为空，
#该函数查询Abel的email，并返回，数据类型为字符串型。

DELIMITER //

CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
    DETERMINISTIC
    CONTAINS SQL
    READS SQL DATA
BEGIN
    RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //

DELIMITER ;

#调用
SELECT email_by_name();

SELECT email,last_name FROM employees WHERE last_name = 'Abel';

#举例2：创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，
#并返回，数据类型为字符串型。

#创建函数前执行此语句，保证函数的创建会成功
SET GLOBAL log_bin_trust_function_creators = 1;

#声明函数

DELIMITER //

CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)

BEGIN
    RETURN (SELECT email FROM employees WHERE employee_id = emp_id);

END //

DELIMITER ;


#调用
SELECT email_by_id(101);

SET @emp_id := 102;
SELECT email_by_id(@emp_id);


#举例3：创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的
#员工人数，并返回，数据类型为整型。

DELIMITER //

CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT

BEGIN
    RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
    
END //

DELIMITER ;

#调用
SET @dept_id := 50;
SELECT count_by_id(@dept_id);

二。存储过程


#3. 存储过程、存储函数的查看

#方式1. 使用SHOW CREATE语句查看存储过程和函数的创建信息

SHOW CREATE PROCEDURE show_mgr_name;

SHOW CREATE FUNCTION count_by_id;

#方式2. 使用SHOW STATUS语句查看存储过程和函数的状态信息

SHOW PROCEDURE STATUS;

SHOW PROCEDURE STATUS LIKE 'show_max_salary';

SHOW FUNCTION STATUS LIKE 'email_by_id';

#方式3.从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_salary' AND ROUTINE_TYPE = 'PROCEDURE';

#4.存储过程、函数的修改

ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER
COMMENT '查询最高工资';

#5. 存储过程、函数的删除

DROP FUNCTION IF EXISTS count_by_id;

DROP PROCEDURE IF EXISTS show_min_salary;

fengge55
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql用函数调用存储过程_mysql 函数 调用 存储过程
weixin_39585974的博客
02-02 804
Mysql高级之存储过程原文:Mysql高级之存储过程概念类似于函数,就是把一段代码封装起来,当要执行这一段代码的时候,可以通过调用该存储过程来实现.在封装的语句体里面,可以用if/else,case,while等控制结构.可以进行sql编程.在mysql中,存储过程和函数...文章杰克.陈2014-11-14657浏览量Mysql函数(function)|存储过程(procedure)func...
mysql 创建函数set global log bin trust function creators TRUE
trigfj的博客
11-11 1728
mysql 创建函数set global log bin trust function creators TRUE
mysql 创建函数set global log_bin_trust_function_creators=TRUE;
weixin_30736301的博客
06-02 2032
<pre name="code" class="html">set global log_bin_trust_function_creators=TRUE; delimiter $$ CREATE DEFINER=`zjzc_app`@`%` FUNCTION `loadTreeByParent`(begin_sn INT) RETURNS varchar(600) CHAR...
mysql 开启bin-log 执行函数报错
絮落锦乡的博客
05-20 648
DETERMINISTIC 不确定的 NO SQL 没有SQl语句，当然也不会修改数据 READS SQL DATA 只是读取数据，当然也不会修改数据 MODIFIES SQL DATA 要修改数据 CONTAINS SQL 包含了SQL语句 1、在mysql数据库中执行以下语句 临时生效重启后失效 set global log_bin_trust_function_creators=TRUE; 2、 在配置文件/etc/my.cnf的[mysqld]配置 log_bin_trust_...
MySQL参数log_bin_trust_function_creators介绍
weixin_33725272的博客
10-09 1038
  MySQL的有个参数log_bin_trust_function_creators，官方文档对这个参数的介绍、解释如下所示：     log_bin_trust_function_creators Command-Line Format --log-bin-trust-function-creators System Variable Name log_bin_trust_f...
mysql存储函数以及函数过程
最新发布
10-25
mysql+零基础+mysql存储函数以及函数过程知识点
理解MySQL存储过程和函数
09-10
MySQL存储过程和函数是数据库管理中的重要组成部分，它们允许开发者编写可重用的代码段，提高效率并减少网络流量。本篇文章将深入探讨这两个概念，以及如何在MySQL中创建和使用它们。 首先，存储过程是一组预编译的...
Mysql存储过程和函数区别介绍
09-10
MySQL中的存储过程和函数是两种不同的数据库编程工具，它们各自有着独特的特性和用途。下面将详细阐述这两者之间的差异。 1. **定义与用途** - **存储过程** 是一组预编译的SQL语句，由用户定义并保存在数据库中，...
MySQL存储过程和函数使用总结
12-14
如果有函数从其他类型的数据库迁移到MySQL，那么可能因此需要将函数改造成存储过程。 　为什么使用存储过程和函数 　在完成一个逻辑操作时，有时会执行多条SQL语句，此外这些SQL语句的执行顺序也不是固定的，它会...
mysql存储过程和存储函数
03-30
mysql存储过程，存储函数练习，里面有详细代码，供大家参考
mysql自定义函数
微不足道的小博
12-20 1384
文章目录1、开启log_bin_trust_function_creators2、函数基本格式3、无参函数4、有参函数5、函数体内设置局部变量6、删除函数 1、开启log_bin_trust_function_creators 当开启二进制日志后，如果变量log_bin_trust_function_creators为OFF，那么创建或修改存储函数就会报 ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READ
log_bin_trust_function_creators变量解释
cx136295988的博客
10-10 2296
这是我们开启了bin-log, 我们就必须指定我们的函数是否是 1 DETERMINISTIC 不确定的 2 NO SQL 没有SQl语句，当然也不会修改数据 3 READS SQL DATA 只是读取数据，当然也不会修改数据 4 MODIFIES SQL DATA 要修改数据 5 CONTAINS SQL 包含了SQL语句 其中在function里面，只有 DETERMINIST
mysql 开启bin-log日志，存储过程报错
qq_37820157的博客
05-28 157
this function has none of deterministic no sql or reads sql data in ist
【无标题】在MySQL数据库中创建函数(Function)
weixin_61718665的博客
07-26 2295
ifcurr_d!=CURRENT_DATE()then#日期不是当天，将curr_value（当前序列号值）设为初始值（start_value）,将日期更新为当天。setnew_v=curr_v+step_v;#每调用一次函数，将curr_value=curr_value+增加值。ifnew_v>max_vthen#超过最大值，给他返回null。修改log_bin_trust_function_creators。curr_date当前日期，即今日日期。...
MySQL数据库 日志管理
Yosigo_的博客
07-19 389
MySQL数据库日志分类 错误日志 首次启动会提示错误日志位置 # 查询错误日志存放路径 [root@db01 ~]# mysqladmin -uroot -p123 variables | grep -w log_error 或者 [root@db01t ~]# mysql -uroot -p123 mysql> show variables like '%log_error%'; 配置错误日志（默认就是启用的） 相当于把/var/log/messages(系统日志)中mysql服务端
MySQL获取汉字拼音首字母
wangli1281的博客
10-13 2990
MySQL获取汉字拼音首字母 1、注意事项 注意：创建函数前要确保log_bin_trust_function_creators为on状态，可以通过以下语句来启用 set global log_bin_trust_function_creators=TRUE; or set global log_bin_trust_function_creators=1; 否则会出现以下错误： [Err] 1418 - This function has none of DETERMINISTIC, NO
mysql日志管理
givenchy_yzl的博客
07-19 128
一、日志的分类 日志种类 作用 错误日志 记录mysql服务器启动、关闭及运行错误的信息 事务日志 1、redo log 重做日志 2、undo log回滚日志 查询日志 记录查询的信息 慢查询日志 记录执行时间超过指定时间的操作 二进制日志 又称bin log日志，以二进制文件的方式记录数据库中除select以外的操作 中继日志 备库将主库的二进制日志复制到自己的中继日志中，从而在本地中进行重放 通用日志 审计那个账号、在哪个时段、做了哪些事件 二、错误
解决MySQL创建UDF报错ERROR 1418的正确姿势
我只是无聊
07-03 314
MySQL创建UDF ERROR 1418 ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) 搜索资料，发现大部分资料是这么写
mysql存储过程与存储函数实验小结
05-11
MySQL存储过程和存储函数是用来封装一组 SQL 语句并且可以在应用程序中调用的代码块。它们可以帮助我们简化复杂的 SQL 查询，并且可以提高数据库性能和安全性。在实验过程中，我们学习了如何创建存储过程和存储函数...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值