MYSQL存储函数和存储过程

前言

MYSQL知识不仅仅只是大家在大学所接触老师所讲的那些简单的增删改查，用户名更改，权限授予收回等等。MYSQL也拥有除了本身所拥有函数还拥有自定义函数的功能。由于大家平时都会使用外部的程序设计实现在数据中定义函数和过程的功能，因此有一些同学可能对此不是很了解。本篇博客不是讲解数据库已经定义好的函数，而是如何通过用户自己来定义函数，如下便开始数据库的讲解。

函数存储

试试水

1. 先用查看数据库函数的命令查看数据库中有哪些函数。

SHOW FUNCTION STATUS;

2. 创建存储函数（里边的一些关键词作用，请大家继续往下看）

DELIMITER //
CREATE FUNCTION parm_test (instructor_Id VARCHAR(20)) 
RETURNS VARCHAR (20) 
BEGIN
RETURN (
	SELECT
		`name`
	FROM
		instructor
	WHERE
		instructor.ID = instructor_Id
);
END //
DELIMITER ;

3. 查看函数库中时候创建

SHOW FUNCTION STATUS;

4. 使用自定义函数

SELECT parm_test('10101');

5. 删除函数

DROP FUNCTION parm_test;

6. 总结
   以上便是存储函数定义、使用、删除、查看的一个全过程。大家可能觉得此函数很简单，但大家不要忘了学习数据库不是为了简单实现一个数据查找，复杂的函数可以实现使用外部语言实现的同样功能。大家有没有随博主一起探索数据库魅力的兴趣呢！

语法讲解

上边的例子便是实现存储函数的定义等一系列操作，这里就不再举例。此处主要是讲解定义存储函数语法关键词的解释，和注意事项。

DELIMITER（定界符）

在执行SQL语句时，大家都会在语句的结尾加上分号（;）。大学接触数据库结构化语言的时候，老师就会告诉大家，这是提示MYSQL数据库可以执行这条语句了。看到此处，细心的同学可能就会猜到定界符在这里的作用了。大家请看博主编写的自定义存储函数，大家会发现begin…end之间的SQL语句中有分号。前文已经提到数据库默认看到分号后便会执行该语句，那么后边的语句怎么办呢？定界符的出现就解决这种问题。

• 作用
  1、更改SQL语句结束标识，让解释器能够根据用户自己的使用习惯，来设计结束标识。
  2、主要是用在SQL语句中含有默认的结束标识符，但SQL语句没有编写完成且不需要默认结束标识符后执行。
• 注意
  使用定界符后，要将定界符修改会默认结束符，博主在上边的SQL语言使用的是//来实现定界，若定义后不修改回原来的结束标识符，那么在后续SQL语言执行时会遇到一些问题，就如博主若不修改回原来的结束标识符，在编写其他SQL语言时若想注释标注一下，那么博主就会遇到莫名其妙的语法错误，这是因为博主的结束标识符更改。

create function 名字 (参数名称 参数类型)

CREATE FUNCTION 是创建存储函数的关键词这里不去过多描述，大家记住就可以了。名字是指函数的名字，大家命名规范就行，没什么可以说的。主要我们讲解一下参数和类型：

• 函数参数（参数名称和参数类型）
  参数类型必须与传入参数数据属性类型相同。就如(instructor_Id VARCHAR(20)) VARCHAR(20) 必须要与instructor.ID 的类型相同。
• 总结：存储函数的参数就像大家在使用C、C++、java语言编写函数传递的参数思想一样，没什么深度。

begin…end

相当于高级语言C、C++、java中的{}，是SQL函数体的开始和结束。

变量（局部变量和会话变量）

局部变量

局部变量只能在 begin…end范围内有效，这也相当于大家在C等其他语言中自定义函数（test()）体中的int sum;一样。sum变量只能在test()函数体中有效，无法应用于其他函数体中。

1.如何声明局部变量
使用DECLARE关键字声明，模式如下：DECLARE a int 该语句表示声明一个int类型的a变量。
2.如何给变量设置默认值（default）
有时大家会给声明的变量设置默认值，模式如下：DECLARE a int default 2该语句表示声明一个int类型的a变量且a值为2。

• 如下便是示例，大家可以模仿测试一下：

DELIMITER //
CREATE PROCEDURE test_fuhao()
BEGIN
   DECLARE a CHAR(10) DEFAULT 'out';
   BEGIN
     DECLARE a CHAR(10) DEFAULT 'in';
     SELECT a;
   END;
   SELECT a;
END//
DELIMITER ;

查询结果：

CALL test_fuhao()

查询结果


结果表名只在某一个begin…end范围内有效。

全局变量（会话变量）

会话变量全局可用，不必用DECLARE声明。变量名前以@来表明其为全局变量。

• 如下为例子：

DELIMITER //
CREATE PROCEDURE test_quanju()
BEGIN 
   SET @t='in';
   BEGIN
      SET @t='out';
      SELECT @t;
   END;
   SELECT @t;
END;//
DELIMITER ;

查询结果：

call test_quanju();

变量赋值的其他方法

1. set语法：set t=1;。
2. select … into … from … where …语法：select id into instructor_id from instructor where instructor.id = ‘10101’。

调用函数

大家创建了函数那么如何调用呢？相信大家细看开篇的例子便会发现调用函数很简单只需要select后跟函数名有参数的加上参数没有参数的便不用添加。跟高级语言C语言等其他语言调用其他方法一样，只不过SQL结构化语言需要select声明调用。

存储过程

试试水

1. 先查看存储过程都有哪些

SHOW PROCEDURE STATUS;

2. 先删除掉博主自己创建的存储过程

DROP PROCEDURE test_quanju;

3. 再次查看存储过程

SHOW PROCEDURE STATUS;

4. 创建过程

DELIMITER //
CREATE PROCEDURE test_produce(in instructor_id VARCHAR(20),OUT instructor_name VARCHAR(20))
BEGIN
  SELECT name INTO instructor_name FROM instructor WHERE instructor.ID=instructor_id;
END;//
DELIMITER ; 

5. 查看创建是否成功

SHOW PROCEDURE STATUS;

6. 如何使用创建的过程

CALL test_produce('10101',@instructor_name);

SELECT  @instructor_name;

7. 总结
   以上便是函数存储过程的创建，删除，查找，使用的全过程。大家可能会发现与函数存储的创建、删除、查找、使用有类似的地方，学这些不是为了简单的学习，而是去考虑学习这些能在以后的科研中起到什么作用。

语法讲解

上边的例子便是实现存储过程的定义等一系列操作，这里就不再举例。此处主要是讲解定义存储过程语法关键词的解释，和注意事项。

CREATE PROCEDURE 名字 (输入输出类型 参数名称 参数类型)

以上这几个关键词没什么可说的，主要是来讲输入输出类型，其他的与存储函数相似，大家可自行理解。

输入输出类型

输入输出类型，看这几个字，再看前边的例子，大家便会了解到该类型的表示符号有IN、OUT这两种，但事情往往不是大家所想。下边博主会简单的介绍一下：

符号	解释
IN	表示输入参数
OUT	表示输出参数
INOUT	可以即表示输入有表示输出参数

调用存储过程

MYSQL的存储过程调用与SQL标准的调用不同，这里的@作用是将数值取出放入相应的变量，最后select取出来。

查看存储过程和存储函数

SHOW STATUS语句

语法如下：SHOW PROCEDURE|FUNCTION STATUS 名字; 用于查看存储函数和存储过程的状态。

• 例子

SHOW PROCEDURE STATUS;

SHOW FUNCTION STATUS;

SHOW CREATE语句

语法如下：SHOW CREATE PROCEDURE|FUNCTION 名字。用于查看存储函数和存储过程的状态。

• 例子

SHOW CREATE PROCEDURE test_produce

SHOW CREATE FUNCTION parm_test

修改存储函数和存储过程

有时大家想修改已经定义好的存储函数和存储过程，因此这节便简单介绍如何修改已经定义好的存储函数和存储过程。MYSQL通过ALTER FUNCTION|PROCEDURE 修改存储函数和存储过程。修改语法如下：
ALTER FUNCTION|PROCEDURE 名字
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFINES SQL DATA
SQL SECURITY {DEFINER | INVOKER }
COMMENT ‘大佬’
如下表格对其进行详细说明：

参数	说明
CONTAINS SQL	表示子程序包含SQL语句，但不包含读写数据的语句
NO SQL	表示子程序不包含SQL语句
READS SQL DATA	表示子程序包含读数据的语句
MODIFINES SQL DATA	表示子程序写数据的语句
SQL SECURITY {DEFINER INVOKER }	表明权限执行，DEFINER 表明只有定义者自己才能够执行，INVOKER 表明调用者可以执行
COMMENT ‘大佬’	注释信息

博主简单举一个例子：

ALTER FUNCTION parm_test
MODIFIES SQL DATA
SQL SECURITY INVOKER

删除存储函数和存储过程

DROP FUNCTION|PROCEDURE 名字 这里不在举例，大家可查看前面的试试水。

总结

MYSQL函数存储和函数过程在一般的数据使用中，大家可能不太接触到它，但其对图像和几何对象特别有用，希望大家能够好好学习数据库。如上便是博主的一个学习总结。希望各位大佬喜欢，顺便点个赞吧！