csdner:jijerry, MySQL 存储过程和函数

2024-01-06 09:48
文章标签 mysql 函数 过程 存储 database csdner jijerry

本文主要是介绍csdner:jijerry, MySQL 存储过程和函数，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MySQL 存储过程和函数
一、实验介绍
1.1 实验内容
本实验中将学习并实践 MySQL 中的存储过程和函数的创建及使用方法。

1.2 实验知识点
存储过程
函数
1.3 实验环境
课程使用的实验环境为 Ubuntu Linux 14.04 64 位版本。实验中会用到程序：

Mysql 5.5.50
Xfce终端
二、实验步骤
2.1 创建存储过程和函数
CREATE PROCEDURE和CREATE FUNCTION的语法：

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 typefunc_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
这些语句用于创建并存储子程序。要在MySQL 5.1中创建子程序，必须具有CREATE ROUTINE权限，并且ALTER ROUTINE和EXECUTE权限被自动授予它的创建者。

默认情况下，子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。

如果子程序名和SQL内部函数名一样，在定义子程序时，你需要在这个名字和随后括号中间插入一个空格，否则发生语法错误。在你之后调用子程序的时候也要插入。为此，即使有可能出现这种情况，我们还是建议最好避免给你的存储子程序取与已经存在的SQL函数一样的名字。

由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN（内部）参数。要指定为其它参数，可在参数名之前使用关键词 OUT或INOUT。

注意： 指定参数为IN， OUT， 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN）

下面是一个使用OUT参数的简单的存储过程的例子。例子为：在程序被定义的时候，用mysql客户端delimiter命令来把语句定界符从;变为//。这就允许用在程序体中的;定界符被传递到服务器而不是被mysql自己来解释。

mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END
-> //
mysql> delimiter ;
mysql> CALL simpleproc(@a);
mysql> SELECT @a;
当使用delimiter命令时，你应该避免使用反斜杠(‘\’)字符，因为那是MySQL的转义字符。

下列是一个例子，一个采用参数的函数使用一个SQL函数执行一个操作，并返回结果：

mysql> delimiter //
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
-> RETURN CONCAT(‘Hello, ‘,s,’!’);
-> //
mysql> delimiter ;
mysql> SELECT hello(‘world’);

如果在存储函数中的RETURN语句中返回一个类型不同于在函数的RETURNS子句中指定类型的值，返回值被强制转换为相对恰当的类型。比如，如果一个函数返回一个ENUM或SET值，但是RETURN语句返回一个整数，对于SET成员集合的相应的ENUM成员，从函数返回的值是字符串类型数据。

2.2 修改存储过程和函数
ALTER PROCEDURE和ALTER FUNCTION 语法：

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic …]

characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
这个语句可以被用来改变存储过程或函数的特征。

在ALTER PROCEDURE和ALTER FUNCTION语句中，可以指定一个以上的改变。

2.3 删除存储过程和函数
DROP PROCEDURE和DROP FUNCTION 语法：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
这个语句被用来移除一个存储过程或函数。即从服务器上移除一个制定的子程序。

IF EXISTS子句是一个MySQL的扩展。它防止如果程序或函数不存在产生的错误，并产生一个可以用SHOW WARNINGS查看的警告。

2.4 SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION
SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION：

SHOW CREATE {PROCEDURE | FUNCTION} sp_name
这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE，它返回一个可用来重新创建并且已命名的子程序的字符串。

例如以下语句返回test数据库内的hello程序信息：

mysql> SHOW CREATE FUNCTION test.hello\G

2.5 SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]
这个语句是一个MySQL的扩展。它返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期。如果没有指定样式，根据你使用的语句，所有存储过程和所有存储函数的信息都被列出。

例如：

mysql> SHOW FUNCTION STATUS LIKE ‘hello’\G

2.6 CALL语句
CALL sp_name([parameter[,…]])
CALL语句调用一个先前使用CREATE PROCEDURE创建的程序。

CALL语句可以用声明为OUT或INOUT参数的参数给它的调用者传回返回值。它也“返回”受影响的行数，客户端程序可以在SQL级别上通过调用ROW_COUNT()函数获得这个数，在C中是调用the mysql_affected_rows()这个 C API函数来获得。

2.7 BEGIN … END复合语句
[begin_label:] BEGIN
[statement_list]
END [end_label]
存储子程序可以使用BEGIN … END复合语句来包含多个语句。statement_list 代表一个或多个语句的列表。statement_list内每个语句都必须用分号（；）来结尾。

复合语句可以被标记。除非begin_label存在,否则end_label不能被给出，并且如果二者都存在,他们必须是同样的。

请注意，可选的[NOT] ATOMIC子句现在还不被支持。这意味着在指令块的开始没有交互的存储点被设置，并且在上下文中用到的BEGIN子句对当前交互动作没有影响。

使用多重语句需要客户端能发送包含语句定界符;的查询字符串。这个符号在命令行客户端被用delimiter命令来改变查询结尾定界符;（比如改为//）使得; 可被用在子程序体中。

2.8 DECLARE语句
DECLARE语句被用来把不同项目涵盖到一个子程序：局部变量，条件和处理程序 及光标。SIGNAL和RESIGNAL语句当前还不被支持。

DECLARE仅被用在BEGIN … END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。

光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。

2.9 存储过程中的变量
2.9.1 DECLARE局部变量
DECLARE var_name[,…] type [DEFAULT value]
这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。

局部变量的作用范围在它被声明的BEGIN … END块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。

2.9.2 变量SET语句
SET var_name = expr [, var_name = expr] …
在存储过程中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量。

在存储过程中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, …这样的扩展语法。其中不同的变量类型（局域声明变量及全局和集体变量）可以被混合使用。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。在那种情况下，此选项会被识别，但是也会被忽略。

2.9.3 SELECT … INTO语句
SELECT col_name[,…] INTO var_name[,…] table_expr
这个SELECT语法把选定的列直接存储到变量中。因此，只有单一的行可以被取回。

例如：

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
注意： SQL变量名不能和列名一样。如果SELECT … INTO这样的SQL语句包含一个对列的引用，并包含一个与列相同名字的局部变量，MySQL会把引用解释为一个变量的名字。例如，在下面的语句中，xname被解释为到xname 变量的引用而不是到xname列的引用：

CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
DECLARE xname VARCHAR(5) DEFAULT ‘bob’;
DECLARE newname VARCHAR(5);
DECLARE xid INT;

SELECT xname,id INTO newname,xid FROM table1 WHERE xname = xname;
SELECT newname;

END;
当这个程序被调用的时候，无论table.xname列的值是什么，变量newname将返回值‘bob’。

2.10 条件和处理程序
2.10.1 DECLARE条件
DECLARE condition_name CONDITION FOR condition_value

condition_value:
SQLSTATE [VALUE] sqlstate_value
| mysql_error_code
这个语句用于指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以之后被用在DECLARE HANDLER语句中。

2.10.2 DECLARE处理程序
DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement

handler_type:
CONTINUE
| EXIT
| UNDO

condition_value:
SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| mysql_error_code
这个语句用于指定每一个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句将被执行。

对一个CONTINUE处理程序，当前子程序的执行在执行处理程序语句之后继续。对于EXIT处理程序，当前BEGIN…END复合语句的执行被终止。UNDO 处理程序类型语句还不被支持。 SQLWARNING是对所有以01开头的SQLSTATE代码的速记。

NOT FOUND是对所有以02开头的SQLSTATE代码的速记。

SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

除了SQLSTATE值，MySQL错误代码也不被支持。

例如:

mysql> CREATE TABLE test.t (s1 int,primary key (s1));
mysql> delimiter //
mysql> CREATE PROCEDURE handlerdemo ()
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’ SET @x2 = 1;
-> SET @x = 1;
-> INSERT INTO test.t VALUES (1);
-> SET @x = 2;
-> INSERT INTO test.t VALUES (1);
-> SET @x = 3;
-> END;
-> //
mysql> CALL handlerdemo()//
mysql> SELECT @x//

注意到，@x是3，这表明MySQL被执行到程序的末尾。如果DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’ SET @x2 = 1;这一行不存在，第二个INSERT因PRIMARY KEY强制而失败之后，MySQL可能已经采取默认(EXIT)路径，导致SELECT @x可能返回2。

2.11 光标
简单光标在存储过程和函数内被支持。语法如同在嵌入的SQL中。当前的光标是不敏感的，分为只读的以及不滚动的。光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。

例如：

CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = 1;

OPEN cur1;
OPEN cur2;

REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;

CLOSE cur1;
CLOSE cur2;
END
2.11.1 声明光标
DECLARE cursor_name CURSOR FOR select_statement
这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。

其中select_statement中的SELECT语句不能有INTO子句。

2.11.2 光标OPEN语句
OPEN cursor_name
这个语句用于打开先前声明的光标。

2.11.3 光标FETCH语句
FETCH cursor_name INTO var_name [, var_name] …
这个语句用指定的打开光标读取下一行（如果有下一行的话），并且向前移动光标指针。

2.11.4 光标CLOSE语句
CLOSE cursor_name
这个语句关闭先前打开的光标。

如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。

2.12 流程控制构造
2.12.1 IF语句
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] …
[ELSE statement_list]
END IF
IF实现了一个基本的条件构造。如果search_condition求值为真，相应的SQL语句列表被执行。如果没有search_condition匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。

请注意，还有有一个IF() 函数，它不同于这里描述的IF语句。

2.12.2 CASE语句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] …
[ELSE statement_list]
END CASE
Or:

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] …
[ELSE statement_list]
END CASE
存储过程的CASE语句实现一个复杂的条件构造。如果search_condition求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行。

2.12.3 LOOP语句
[begin_label:] LOOP
statement_list
END LOOP [end_label]
LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直循环被退出，退出循环通常伴随着一个LEAVE语句。

LOOP语句可以被标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们必须是同样的。

2.12.4 LEAVE语句
LEAVE label
这个语句被用来退出任何被标注的流程控制构造。它和BEGIN … END或循环一起被使用。

2.12.5 ITERATE语句
ITERATE label
ITERATE只可以出现在LOOP，REPEAT， 和WHILE语句内。ITERATE意思为：“再次循环”。

例如：

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN ITERATE label1; END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END
2.12.6 REPEAT语句
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
REPEAT语句内的语句或语句群被重复，直至search_condition为真。

REPEAT 语句可以被标注。除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的。

例如：

mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
-> END
-> //
mysql> CALL dorepeat(1000)//
mysql> SELECT @x//
2.12.7 WHILE语句
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
WHILE语句内的语句或语句群被重复，直至search_condition为真。

WHILE语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的。

例如：

CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO
…
SET v1 = v1 - 1;
END WHILE;
END

————————————————
版权声明：本文为CSDN博主「jijerry」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/jijerry/article/details/70903739

这篇关于csdner:jijerry, MySQL 存储过程和函数的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/575952。

相关文章

MySQL复合查询从基础到多表关联与高级技巧全解析

MySQL复合查询从基础到多表关联与高级技巧全解析

《MySQL复合查询从基础到多表关联与高级技巧全解析》本文主要讲解了在MySQL中的复合查询,下面是关于本文章所需要数据的建表语句,感兴趣的朋友跟随小编一起看看吧... 目录前言：1.基本查询回顾：1.1.查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J1.2.按照部门
阅读更多...
Kotlin运算符重载函数及作用场景

Kotlin运算符重载函数及作用场景

《Kotlin运算符重载函数及作用场景》在Kotlin里,运算符重载函数允许为自定义类型重新定义现有的运算符(如+-…)行为,从而让自定义类型能像内置类型那样使用运算符,本文给大家介绍Kotlin运算... 目录基本语法作用场景类对象数据类型接口注意事项在 Kotlin 里，运算符重载函数允许为自定义类型重
阅读更多...
Spring三级缓存解决循环依赖的解析过程

Spring三级缓存解决循环依赖的解析过程

《Spring三级缓存解决循环依赖的解析过程》：本文主要介绍Spring三级缓存解决循环依赖的解析过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、循环依赖场景二、三级缓存定义三、解决流程（以ServiceA和ServiceB为例）四、关键机制详解五、设计约
阅读更多...
spring IOC的理解之原理和实现过程

spring IOC的理解之原理和实现过程

《springIOC的理解之原理和实现过程》：本文主要介绍springIOC的理解之原理和实现过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、IoC 核心概念二、核心原理1. 容器架构2. 核心组件3. 工作流程三、关键实现机制1. Bean生命周期2.
阅读更多...
Redis实现分布式锁全解析之从原理到实践过程

Redis实现分布式锁全解析之从原理到实践过程

《Redis实现分布式锁全解析之从原理到实践过程》：本文主要介绍Redis实现分布式锁全解析之从原理到实践过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、背景介绍二、解决方案（一）使用 SETNX 命令（二）设置锁的过期时间（三）解决锁的误删问题（四）Re
阅读更多...
Linux搭建单机MySQL8.0.26版本的操作方法

Linux搭建单机MySQL8.0.26版本的操作方法

《Linux搭建单机MySQL8.0.26版本的操作方法》：本文主要介绍Linux搭建单机MySQL8.0.26版本的操作方法,本文通过图文并茂的形式给大家讲解的非常详细,感兴趣的朋友一起看看吧... 目录概述环境信息数据库服务安装步骤下载前置依赖服务下载方式一：进入官网下载，并上传到宿主机中，适合离线环境
阅读更多...
MySQL主从同步延迟问题的全面解决方案

MySQL主从同步延迟问题的全面解决方案

《MySQL主从同步延迟问题的全面解决方案》MySQL主从同步延迟是分布式数据库系统中的常见问题,会导致从库读取到过期数据,影响业务一致性,下面我将深入分析延迟原因并提供多层次的解决方案,需要的朋友可... 目录一、同步延迟原因深度分析1.1 主从复制原理回顾1.2 延迟产生的关键环节二、实时监控与诊断方案
阅读更多...
SQLyog中DELIMITER执行存储过程时出现前置缩进问题的解决方法

SQLyog中DELIMITER执行存储过程时出现前置缩进问题的解决方法

《SQLyog中DELIMITER执行存储过程时出现前置缩进问题的解决方法》在SQLyog中执行存储过程时出现的前置缩进问题,实际上反映了SQLyog对SQL语句解析的一个特殊行为,本文给大家介绍了详... 目录问题根源正确写法示例永久解决方案为什么命令行不受影响？最佳实践建议问题根源SQLyog的语句分
阅读更多...
慢sql提前分析预警和动态sql替换-Mybatis-SQL

慢sql提前分析预警和动态sql替换-Mybatis-SQL

《慢sql提前分析预警和动态sql替换-Mybatis-SQL》为防止慢SQL问题而开发的MyBatis组件,该组件能够在开发、测试阶段自动分析SQL语句,并在出现慢SQL问题时通过Ducc配置实现动... 目录背景解决思路开源方案调研设计方案详细设计使用方法1、引入依赖jar包2、配置组件XML3、核心配
阅读更多...
MySQL数据库约束深入详解

MySQL数据库约束深入详解

《MySQL数据库约束深入详解》：本文主要介绍MySQL数据库约束,在MySQL数据库中,约束是用来限制进入表中的数据类型的一种技术,通过使用约束,可以确保数据的准确性、完整性和可靠性,需要的朋友... 目录一、数据库约束的概念二、约束类型三、NOT NULL 非空约束四、DEFAULT 默认值约束五、UN
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2