探索 MySQL 存储过程的进阶特性：自定义函数、表值参数和游标 (探索海洋的欧美动画片)

引言

存储过程是 MySQL 中强大的工具，允许用户封装复杂的数据库操作，提高可重用性和性能。除了基本特性外，MySQL 还提供了几个进阶特性，进一步增强了存储过程的功能性。这些特性包括自定义函数、表值参数和游标。本文将深入探讨这些特性的用法和优势。

自定义函数

自定义函数允许用户在存储过程中创建自己的函数，以便重复使用通用逻辑。这些函数可以接受参数並返回值。自定义函数的语法如下：```sqlCREATE FUNCTION function_name (parameter_list) RETURNS return_typeASBEGIN-- 函数体END```例如，以下自定义函数计算两个数字的加法： sqlCREATE FUNCTION add_numbers (a INT, b INT) RETURNS INTASBEGINRETURN a + b;END```自定义函数可以在存储过程中使用，就像内置函数一样：```sqlSELECT add_numbers(5, 10);```

表值参数

表值参数允许存储过程接受或返回一个表作为参数。这對於处理大量数据或执行复杂查询非常有用。表值参数的语法如下：```sqlCREATE PROCEDURE procedure_name (IN|OUT|INOUT table_name TABLE)```例如，以下存储过程使用表值参数將客户信息批量插入数据库：```sqlCREATE PROCEDURE insert_customers (IN customers TABLE)ASBEGININSERT INTO customer (name, age, address)SELECT name, age, addressFROM customers;END```

游标

游标是用于遍历结果集的机制。它们允许用户逐行访问数据，并根据需要执行操作。游标的语法如下：```sqlDECLARE cursor_name CURSOR FOR SELECT_statement;OPEN cursor_name;FETCH cursor_name INTO variable_list;CLOSE cursor_name;```例如，以下存储过程使用游标来打印一个表中的所有客户信息：```sqlCREATE PROCEDURE print_customersASBEGINDECLARE customer_cursor CURSOR FOR SELECT name, age, address FROM customer;OPEN customer_cursor;FETCH customer_cursor INTO customer_name, customer_age, customer_address;WHILE customer_name IS NOT NULL DOPRINT CONCAT(customer_name, ', ', customer_age, ', ', customer_address);FETCH customer_cursor INTO customer_name, customer_age, customer_address;END WHILE;CLOSE customer_cursor;END```

优势

使用这些进阶特性可以带来以下优势：代码重用：自定义函数允许您将通用逻辑封装为可重用的模块。批量处理：表值参数可用于对大量数据执行高效操作。灵活的遍历：游标提供了灵活的方式来遍历结果集並逐行处理数据。提高性能：通过使用这些特性，您可以优化存储过程，减少执行时间。

结论

MySQL 存储过程中的自定义函数、表值参数和游标是强大的工具，可显着增强存储过程的功能性。通过使用这些特性，您可以创建更灵活、更有效率的数据库解决方案。

---

mysql 与oracle中的存储过程及函数有什么区别，尽可能详细哦

本质上没区别。 只是函数有如：只能返回一个变量的限制。 而存储过程可以返回多个。 而函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行。 执行的本质都一样。 函数限制比较多，比如不能用临时表，只能用表变量．还有一些函数都不可用等等．而存储过程的限制相对就比较少由于我现在基本上是DBA的工作，因此平时也看一些数据库方面的书籍。 但是我一直对存储过程和函数之间的区别掌握不透。 我向来认为存储过程可以实现的操作，函数也一样可以实现。 最近，刚好大学的老师给我们上SQL-Server的课程，我对这个问题的疑惑终于慢慢解开。 今天晚上顺便看了些网上的资料，觉得以下分析比较合理：1. 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。 2. 对于存储过程来说可以返回参数，而函数只能返回值或者表对象。 3. 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 4. 当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。 Procedure cache中保存的是执行计划 (execution plan) ，当编译好之后就执行procedure cache中的execution plan，之后SQL SERVER会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个execution plan可能被使用的频率；其次是生成这个plan的代价，也就是编译的耗时。 保存在cache中的plan在下次执行时就不用再编译了。 存储过程和用户自定义函数具体的区别存储过程存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。 存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。 存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。 存储过程可包含程序流、逻辑以及对数据库的查询。 它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。 可以出于任何使用 SQL 语句的目的来使用存储过程，它具有以下优点：可以在单个存储过程中执行一系列 SQL 语句。 可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。 存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。 用户定义函数函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用。 Microsoft? SQL Server? 2000 并不将用户限制在定义为 Transact-SQL 语言一部分的内置函数上，而是允许用户创建自己的用户定义函数。 可使用 CREATE FUNCTION 语句创建、使用 ALTER FUNCTION 语句修改、以及使用 DROP FUNCTION 语句除去用户定义函数。 每个完全合法的用户定义函数名 (database___name) 必须唯一。 必须被授予 CREATE FUNCTION 权限才能创建、修改或除去用户定义函数。 不是所有者的用户在 Transact-SQL 语句中使用某个函数之前，必须先给此用户授予该函数的适当权限。 若要创建或更改在 CHECK 约束、DEFAULT 子句或计算列定义中引用用户定义函数的表，还必须具有函数的 REFERENCES 权限。 函数中的有效语句类型包括：DECLARE 语句，该语句可用于定义函数局部的数据变量和游标。 为函数局部对象赋值，如使用 SET 给标量和表局部变量赋值。 游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。 不允许使用 FETCH 语句将数据返回到客户端。 仅允许使用 FETCH 语句通过 INTO 子句给局部变量赋值。 控制流语句。 SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。 INSERT、UPDATE 和 DELETE 语句，这些语句修改函数的局部 table 变量。 EXECUTE 语句，该语句调用扩展存储过程。 在查询中指定的函数的实际执行次数在优化器生成的执行计划间可能不同。 示例为 WHERE 子句中的子查询唤醒调用的函数。 子查询及其函数执行的次数会因优化器选择的访问路径而异

数据库的存储过程，函数，触发器，游标，视图，索引等内容讲解

子程序

子程序包括存储过程、自定义函数、游标、触发器。 可以被编译和存储在数据库中，它具有模块化、重用性、可维护性、可扩展性、安全性等特点。 其目的是完成特定的功能，能被程序和客户端工具直接调用。 子程序也属于数据库对象，可以被授权能否执行。

存储过程

存储过程是一种存储复杂程序，方便外部程序调用的数据库对象。 是为了完成某个特定功能的 SQL 语句集合，用户可以通过存储过程的名字和参数进行调用。 MySQL 从 5.0 版本开始支持存储过程（Stored Procedure）。

创建和删除存储过程

创建存储过程，其关键语法如下：

CREATE PROCEDURE 存储过程名([IN|OUT|INOUT] 参数名 数据类型)BEGIN……END

对存储过程进行参数定义时，多个参数用?，?分割，共有三种参数类型：IN，OUT，INOUT：

IN: 参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不会影响调用环境的数据值；

OUT: 该值可在存储过程内部被改变，同时引起调用环境中数据值的改变；（有out需要 @变量 接收）

INOUT: 调用时指定，兼具?IN?和?OUT?类型参数的特点。

以?BEGIN?和?END?对过程体的开始和结束进行标识。

需要强调一点，MySQL 中存储过程默认以?;?作为结束符，如果不改变结束符，编译器会把存储过程当成 SQL 语句进行处理，因此编译过程会报错。 所以要事先用?DELIMITER //?声明当前的分隔符，其目的是让编译器把两个?//?之间的内容当作一个存储过程，使用?DELIMITER ;?则恢复结束符为?;?。

删除存储过程使用?DROP PROCEDURE?语句，其语法如下：

DROP PROCEDURE 存储过程名;函数

自定义函数是一种对 MySQL 的扩展，其用法和内置函数相同。 在前面文章，我们使用的函数是 MySQL 内置函数（已经写好的），直接调用即可完成某个特定功能，下面将会介绍 MySQL 自定义函数。

创建和删除自定义函数

创建自定义函数使用?CREATE FUNCTION?语句，语法如下：

CREATE FUNCTION 函数名([变量名1 变量类型1, ……, 变量名n 变量类型n]) RETURNS 数据类型BEGINsql语句;RETURN 值;END;

MySQL 安装完成后默认不允许创建自定义函数，需要在??配置文件中增加?log-bin-trust-FUNCTION-creators=1，然后重启数据库，使其具有创建函数的权限。 也可以在 MySQL 命令行中输入以下命令来解决。 ?SET GLOBAL log_bin_trust_function_creators = 1;。

删除自定义函数使用?DROP FUNCTION?语句，其语法如下：

DROP FUNCTION 函数名;

需要注意，删除自定义函数时，函数名后面不能加括号

游标

游标（CURSOR）是一个存储在 MySQL 服务器上面的数据库查询机制，类似于数组的下标。 使用游标后，可以逐步提取查询结果。

使用游标需要注意以下几点：

声明游标之后，必须先打开游标才能使用；

在游标结束之后，要关闭游标。

其使用的基本步骤如下：

声明游标，其语法如下；

declare 游标名 cursor for select_statement

打开游标

open 游标名

从游标中取值，使用 fetch 进行取值，语法如下：

fetch 游标名 into var1,var2,……

利用fetch将取到的一条记录中的字段赋值给多个变量。

关闭游标

close 游标名触发器（TRIGGER）

是一种特殊的存储过程，它在插入、修改或删除表中的数据时触发执行，拥有更精细、更复杂的数据控制能力。 MySQL 从 5.0 版本开始支持触发器。

举个例子，现有用户表和日志表。 当一个用户被创建时，我们用日志来记录用户的创建过程。 如果不使用触发器，则需要手动编写程序来实现；而一旦使用触发器，我们可以在信息插入用户表后，立刻触发对日志表的操作，使其记录创建用户的信息。

创建和删除触发器

创建触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_statement;

参数解释：

trigger_name：触发器名称，自己定义；

trigger_time：触发时机，只有两个值，before（某事件之前），after（某事件之后）；

trigger_event：触发事件，取值 INSERT（插入）、UPDATE（更新）、DELETE（删除）；

table_name：需要建立触发器的表名；

trigger_statement：触发器程序体，一条 SQL 语句或存储过程等；

查看触发器

使用?SHOW TRIGGERS;?命令查看所有触发器，因为触发器有自己的保存机制，显示出来的信息量比较大

删除触发器使用的语法如下：

DROP TRIGGER 触发器名;视图

视图是从一个或多个表中糅合出来的虚拟表。 一个视图并不包含真实的数据，它提供了另一个视角去查看或改变表中的数据。

打个比喻：把视图想象成一扇窗户，通过窗户往里看，我们只能看到一部分，而这部分就是数据库系统允许你看到的数据。 而不允许你看到的内容会被遮挡住，让你不可见。

使用视图可以提高我们对数据的操作效率，同时增加安全性：

提高效率：将经常使用复杂查询定义为视图，由于对视图的权限、语法解析都会被存储，就避免了重复解析；

增加数据安全性：通过视图，用户只能查询和更改指定的数据；

总之，使用视图的主要作用就是保障数据的安全性，同时提高查询效率。

创建视图

创建视图使用的语句是?CREATE View，完整语法看上去比较复杂，大家可以到官网上去查看。

DROP PROCEDURE 存储过程名;0查询视图

视图是一种虚拟的表，也符合 DQL 操作，视图的查询和表的查询相同，查看 city_VIEW 视图中的所有数据，其 SQL 语句如下：

DROP PROCEDURE 存储过程名;1删除视图

使用?DELETE FROM?子句可以删除视图，其语法如下：

DROP PROCEDURE 存储过程名;2更新视图数据

把 id 为 2 的城市人口更新为 。其 SQL 语句如下：

DROP PROCEDURE 存储过程名;3

查看视图和基表，结果如下：

DROP PROCEDURE 存储过程名;4

查看基表数据如下：

DROP PROCEDURE 存储过程名;5插入视图数据

给视图插入数据和表插入数据一样，使用?INSERT INTO?语句，我们在视图中插入一条 id 为 4080，名字为?test?，人口数量为 500 的记录，其 SQL 语句如下：

DROP PROCEDURE 存储过程名;6序列

前面我们在介绍主键的时候知道，主键必须是唯一的。 为了方便管理主键同时满足主键唯一性要求，我们把主键设置为自增长。 实现自增长需要用到序列。

序列就是一组有特定变化规律的整数，其最主要的用途就是创建主键，确保主键的唯一性。 序列是一个数据库对象，独立于表进行存储，可以为多个表使用。

目前 MySQL 是不支持类似建表或视图的方式来直接创建序列对象（Oracle 支持）。 虽然有?auto_increment?来实现自增长，但不能设置步长、起始值、是否循环等。 最重要的是，在 MySQL 中一张表只能有一个字段设置为自增长，如果我们需要两个或以上的字段实现自增长该怎么办呢？ 需要做一些间接处理。

新建表时设置起始值

下面我们在 demo 数据库下新创建一个表 demo2：

DROP PROCEDURE 存储过程名;7

插入新的值：

DROP PROCEDURE 存储过程名;8

可以看到起始值为 1000。

创建表后设置起始值

修改 demo2 表的起始值为 1500：

DROP PROCEDURE 存储过程名;9

这时如果再插入新值：

CREATE FUNCTION 函数名([变量名1 变量类型1, ……, 变量名n 变量类型n]) RETURNS 数据类型BEGINsql语句;RETURN 值;END;0

可以看到新的 id 起始值已经变为 1500 了 使用?AUTO_INCREMENT?属性时我们需要注意：

每一个表中只能有一个?AUTO_INCREMENT?列

同时该列必须要有?NOT NULL?属性来进行约束，在 MySQL 中会自动将?NOT NULL?约束隐式添加到列中。

AUTO_INCREMENT?列必需建立索引，可以为?PRIMARY KEY?或?UNIQUE?索引。

AUTO_INCREMENT?列具有以下属性：

默认起始值为 1，当在该列中插入?NULL?值或是在?INSERT?语句中忽略该值时，它的起始值将会自增 1。

如果使用?DELETE?语句删除最后插入的行，则 MySQL 可能会也可能不会重复使用删除的序列号，具体取决于表的存储引擎。 如 MySQL 默认的?InnoDB?就不会重用序列号。

如果使用?UPDATE?语句将?AUTO_INCREMENT?列中的值更新为已经存在的值，且该列具有唯一索引，则 MySQL 将发出重复键错误。

获得最后生成的序列号可以使用?LAST_INSERT_ID()?函数。

索引

所有数据操作可简单分为读操作（获取数据）和写操作（插入数据、修改数据、删除数据）。 一般情况下，读写比例在 10:1 左右，大量的读操作给数据库性能带来不小的考验。 因此，对查询语句的优化是重中之重，优化的关键就是利用好索引。

索引在 MySQL 中又叫作?键?，英文名?key?，是存储引擎用于快速找到记录的一种数据结构。 索引对于性能的提升非常关键，尤其是当表中的数据量越来越庞大的时候。 我们前面介绍约束使用到的?primary key?，其实就是一种索引，叫做主键索引。

举个简单的例子：我们把数据库比作汉语字典，那么索引就是这本字典的音序表，通过音序表可以快速查找到需要的汉字。 索引的目的就是为了提高查询效率。 在 MySQL 中常用的索引可以分为三类，分别是：普通索引、唯一索引、联合索引。

普通索引

普通索引使用关键字 INDEX 定义，根据建立索引的时机不同，书写方式有细微差别。分为以下 3 种情况：

创建表的时候创建索引；

创建表后创建索引；

修改表的时候添加索引。

唯一索引

唯一索引不仅加速查找，还具有约束性。

主键索引?primary key

唯一键索引?unique

联合索引

联合索引即为索引同时设置多个字段。

primary key(id,name)，联合主键索引

index(id,name)，联合普通索引 在建表的时候创建索引。其语法如下：

CREATE FUNCTION 函数名([变量名1 变量类型1, ……, 变量名n 变量类型n]) RETURNS 数据类型BEGINsql语句;RETURN 值;END;1

给已经存在的表中某字段添加索引，其语法如下：

CREATE FUNCTION 函数名([变量名1 变量类型1, ……, 变量名n 变量类型n]) RETURNS 数据类型BEGINsql语句;RETURN 值;END;2

修改表时创建索引，其语法如下：

ALTER TABLE 表名 ADD CREATE FUNCTION 函数名([变量名1 变量类型1, ……, 变量名n 变量类型n]) RETURNS 数据类型BEGINsql语句;RETURN 值;END;1

查看索引，借助表信息查看表中是否存在索引，其语法如下：

CREATE FUNCTION 函数名([变量名1 变量类型1, ……, 变量名n 变量类型n]) RETURNS 数据类型BEGINsql语句;RETURN 值;END;4

查看 teacher 表中的索引，其 SQL 语句如下：

CREATE FUNCTION 函数名([变量名1 变量类型1, ……, 变量名n 变量类型n]) RETURNS 数据类型BEGINsql语句;RETURN 值;END;5

删除索引，使用的关键字是DROP INDEX … on …，其语法如下：

CREATE FUNCTION 函数名([变量名1 变量类型1, ……, 变量名n 变量类型n]) RETURNS 数据类型BEGINsql语句;RETURN 值;END;6原文：

---

---

相关标签： 探索、 存储过程的进阶特性、 MySQL、 探索海洋的欧美动画片、 自定义函数、 表值参数和游标、

上一篇：MySQL存储过程与触发器协同工作以实现数据

下一篇：使用MySQL存储过程加速数据处理批量操作复