MySQL 存储过程和函数(转)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 概述 一提到存储过程可能就会引出另一个话题就是存储过程的优缺点,这里也不做讨论,一般别人问我我就这样回答你觉得它好你就用它。因为mysql中存储过程和函数的语法非常接近所以就放在一起,主要区别就是函数必须有返回值(return),并且函数的参数只有IN类型而存储过程有IN、OUT、INOUT这三种类型。

概述

一提到存储过程可能就会引出另一个话题就是存储过程的优缺点,这里也不做讨论,一般别人问我我就这样回答你觉得它好你就用它。因为mysql中存储过程和函数的语法非常接近所以就放在一起,主要区别就是函数必须有返回值(return),并且函数的参数只有IN类型而存储过程有IN、OUT、INOUT这三种类型。

语法

创建存储过程和函数语法

复制代码
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
    proc_parameter:
    [ IN | OUT | INOUT ] param_name type
    
    func_parameter:
    param_name type
 
type:
    Any valid MySQL data type
 
characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'
 
routine_body:
    Valid SQL procedure statement or statements
复制代码

语法来自官方自带的参考手册,characteristic语法块是需要注意的地方,先用一个例子来介绍。

例子:

复制代码
#创建数据库
DROP DATABASE IF EXISTS Dpro;
CREATE  DATABASE Dpro
CHARACTER SET utf8
;

USE Dpro;

#创建部门表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(id INT NOT NULL PRIMARY KEY COMMENT '主键',
 name VARCHAR(20) NOT NULL COMMENT '人名',
 depid INT NOT NULL COMMENT '部门id'
);

#插入测试数据 INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100),(2,'王',101),(3,'张',101),(4,'李',102),(5,'郭',103);
#创建存储过程 DROP PROCEDURE IF EXISTS Pro_Employee; DELIMITER $$ CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT ) READS SQL DATA SQL SECURITY INVOKER BEGIN SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid; END$$ DELIMITER ;
#执行存储过程 CALL Pro_Employee(101,@pcount); SELECT @pcount;
复制代码

语法解释:

在创建存储过程的时候一般都会用DELIMITER$$.....END$$ DELIMITER ;放在开头和结束,目的就是避免mysql把存储过程内部的";"解释成结束符号,最后通过“DELIMITER ;”来告知存储过程结束。

主要解释characteristic部分:

LANGUAGE SQL:用来说明语句部分是SQL语句,未来可能会支持其它类型的语句。

[NOT] DETERMINISTIC:如果程序或线程总是对同样的输入参数产生同样的结果,则被认为它是“确定的”,否则就是“非确定”的。如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC,默认的就是NOT DETERMINISTIC(非确定的)CONTAINS SQL:表示子程序不包含读或写数据的语句。

NO SQL:表示子程序不包含SQL语句。

READS SQL DATA:表示子程序包含读数据的语句,但不包含写数据的语句。

MODIFIES SQL DATA:表示子程序包含写数据的语句。

SQL SECURITY DEFINER:表示执行存储过程中的程序是由创建该存储过程的用户的权限来执行。

SQL SECURITY INVOKER:表示执行存储过程中的程序是由调用该存储过程的用户的权限来执行。(例如上面的存储过程我写的是由调用该存储过程的用户的权限来执行,当前存储过程是用来查询Employee表,如果我当前执行存储过程的用户没有查询Employee表的权限那么就会返回权限不足的错误,如果换成DEFINER如果存储过程是由ROOT用户创建那么任何一个用户登入调用存储过程都可以执行,因为执行存储过程的权限变成了root)

COMMENT 'string':备注,和创建表的字段备注一样。

注意:在编写存储过程和函数时建议明确指定上面characteristic部分的状态,特别是存在复制的环境中,如果创建函数不明确指定这些状态会报错,从一个非复制环境将带函数的数据库迁移到复制环境的机器上如果没有明确指定DETERMINISTIC, NO SQL, or READS SQL DATA该三个状态也会报错。

报错示例

Error Code: 1418. 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服务器中开启了二进制日志选项log-bin,slave会从master复制数据,而一些操作,比如function所得的结果在master和slave上可能不同,所以存在潜在的安全隐患。因此,在默认情况下回阻止function的创建。

解决办法有两种:

复制代码
1.将log_bin_trust_function_creators参数设置为ON,这样一来开启了log-bin的MySQL Server便可以随意创建function。这里存在潜在的数据安全问题,除非明确的知道创建的function在master和slave上的行为完全一致。
  设置该参数可以用动态的方式或者指定该参数来启动数据库服务器或者修改配置文件后重启服务器。需注意的是,动态设置的方式会在服务器重启后失效。
  mysql> show variables like 'log_bin_trust_function_creators';
  mysql> set global log_bin_trust_function_creators=1;
  另外如果是在master上创建函数,想通过主从复制的方式将函数复制到slave上则也需在开启了log-bin的slave中设置上述变量的值为ON(变量的设置不会从master复制到slave上,这点需要注意),否则主从复制会报错。

2.明确指明函数的类型
  1 DETERMINISTIC 不确定的
  2 NO SQL 没有SQl语句,当然也不会修改数据
  3 READS SQL DATA 只是读取数据,当然也不会修改数据
比如:CREATE DEFINER=`username`@`%` READS SQL DATA FUNCTION `fn_getitemclock`(i_itemid bigint,i_clock int,i_pos int) RETURNS int(11)...
这样一来相当于明确的告知MySQL服务器这个函数不会修改数据,因此可以在开启了log-bin的服务器上安全的创建并被复制到开启了log-bin的slave上。
复制代码

修改存储过程函数语法

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
 
characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

删除存储过程函数语法

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

查看存储过程和函数

1.查看存储过程状态

 
 
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
show procedure status like 'Pro_Employee' \G

2.查看存储过程和函数的创建语法

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

SHOW CREATE PROCEDURE Pro_Employee \G;

3.查看存储过程和函数详细信息

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='Pro_Employee' \G;

总结

存储过程和函数语法不难理解,但是往往存储过程中不单单只包含这种简单的查询语法,还会嵌套循环语句、变量、报错处理、事务等,下一篇文章会单独讲变量,将变量的知识加入到存储过程,包括变量的声明和报错处理,欢迎关注。

http://www.cnblogs.com/chenmh/p/5201473.html

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
25天前
|
JSON 关系型数据库 MySQL
Mysql(5)—函数
MySQL提供了许多内置的函数以帮助用户进行数据操作和分析。这些函数可以分为几类,包括聚合函数、字符串函数、数值函数、日期和时间函数、控制流函数等。
68 1
Mysql(5)—函数
|
6天前
|
关系型数据库 MySQL Serverless
MySQL函数
最常用的MySQL函数,包括聚合函数,字符串函数,日期时间函数,控制流函数等
|
9天前
|
存储 SQL NoSQL
|
9天前
|
SQL NoSQL 关系型数据库
|
1月前
|
存储 SQL 关系型数据库
MySQL 存储函数及调用
MySQL 存储函数及调用
35 3
|
1月前
|
存储 关系型数据库 MySQL
MySQL 存储过程返回更新前记录
MySQL 存储过程返回更新前记录
44 3
|
1月前
|
存储 SQL 关系型数据库
MySQL 存储过程错误信息不打印在控制台
MySQL 存储过程错误信息不打印在控制台
50 1
|
20天前
|
关系型数据库 MySQL 数据库
mysql中tonumber函数使用要注意什么
在处理这类转换操作时,考虑周全,利用提供的高性能云服务器资源,可以进一步提升数据库处理效率,确保数据操作的稳定性和安全性,尤其是在处理大量数据转换和运算密集型应用时。
66 0
|
24天前
|
关系型数据库 MySQL 数据处理
企业级应用 mysql 日期函数变量,干货已整理
本文详细介绍了如何在MySQL8.0中使用DATE_FORMAT函数进行日期格式的转换,包括当日、昨日及不同时间段的数据获取,并提供了实际的ETL应用场景和注意事项,有助于提升数据处理的灵活性和一致性。
37 0
|
1天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
13 4
下一篇
无影云桌面