如何创建MySQL存储过程，这是一个问题！且看大佬如何整理剖析

简单地说，存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理。本篇主要介绍如何创建存储过程和存储函数以及变量的使用，如何调用、查看、修改、删除存储过程和存储函数等。

存储程序可以分为存储过程和函数，MySQL 中创建存储过程和函数使用的语句分别是：CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用(即通过引用函数名)，也能返回标量值。存储过程也可以调用其他存储过程。

 

创建存储过程

创建存储过程，需要使用CREATE PROCEDURE语句，基本语法格式如下：

CREATE PROCEDURE sp name ( [ proc parameter])
[ characteristics…] routine_ body

CREATE PROCEDURE为用来创建存储函数的关键字： sp_ _name为存储过程的名称；proc_ parameter 为指定存储过程的参数列表，列表形式如下：

[N1 OUT 1 INOUT] param name type

其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_ name表示参数名称； type 表示参数的类型，该类型可以是MySQL数据库中的任意类型。

characteristics指定存储过程的特性，有以下取值：

• LANGUAGESQL:说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。
• [NOT] DETERMINISTIC:指明存储过程执行的结果是否确定。DETERMINISTIC 表示.结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOTDETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC.
• { CONTAINS SQL |NO SQL | READS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句； NO SQL表明子程序不包含SQL语句； READS SQL DATA说明子程序包含读数据的语句； MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
• SQL SECURITY { DEFINER |INVOKER }:指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。
• COMMENT 'string': 注释信息，可以用来描述存储过程或函数。

routine_ body是SQL代码的内容，可以用BEGIN.END来表示SQL代码的开始和结束。编写存储过程并不是件简单的事情，可能存储过程中需要复杂的SQL语句，并且要有创建存储过程的权限；但是使用存储过程将简化操作，减少冗余的操作步骤，同时，还可以减少操作过程中的失误，提高效率，因此存储过程是非常有用的，而且应该尽可能地学会使用。

下面的代码演示了存储过程的内容，名称为AvgFruitPrice,返回所有水果的平均价格，输入代码如下：

CREATE PROCEDURE AvgFruitPrice ()
BEGIN
SELECT AVG(f price) AS avgprice
FROM fruits;
END；

上述代码中，此存储过程名为AvgFruitPrice, 使用CREATE PROCEDURE AvgFruitPrice 0语句定义。此存储过程没有参数，但是后面的0仍然需要。BEGIN和END语句用来限定存储过程体，过程本身仅是一个简单的SELECT语句(AVG为求字段平均值的函数)。

创建查看fruits 表的存储过程，代码如下：

CREATE PROCEDURE Proc()
BEGIN
SELECT * FROM fruits；
END；

这行代码创建了一个查看fruits表的存储过程，每次调用这个存储过程的时候都会执行SELECT语句查看表的内容，代码的执行过程如下:

MySQL> DELIMITER //
MySQL> CREATE PROCEDURE Proc ()
-> BEGIN
-> SELECT ★FROM fruits;
-> END
//
Query OK, 0 rows affected (0.00 sec)
MySQL> DELIMITER ;

这个存储过程和使用SELECT语句查看表的效果得到的结果是一样的，当然存储过程也可以是很多语句的复杂组合，其本身也可以调用其他的函数来组成更加复杂的操作。

“DELIMITER //”语句的作用是将MySQL的结束符设置为//,因为MySQL默认的语句结束符号为分号;’，为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以“END//" 结束存储过程。存储过程定义完毕之后再使用“DELIMITER;”" 恢复默认结束符。DELIMITER 也可以指定其他符号作为结束符。

创建名称为CountProc的存储过程，代码如下:

CREATE PROCEDURE CountProc (OUT paraml INT)
BEGIN
SELECT COUNT(*) INTO paraml FROM fruits；
END；

上述代码的作用是创建-一个获取fruits 表记录条数的存储过程，名称是CountProc,COUNT(*)计算后把结果放入参数paraml中。代码的执行结果如下:

mysq1> DELIMITER 11
mysql> CREATE PROCEDURE CountProc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM fruits;
-> END //
Query OK, 0 rows affected (0.00 sec)
mysq1> DELIMITER ;

当使用DELIMITER命令时，应该避免使用反斜杠(“\' )字符，因为反斜线是MySQL的转义字符。

创建存储函数

创建存储函数，需要使用CREATE FUNCTION语句，基本语法格式如下:

CREATE FUNCTION func name ( [func_ parameter] )
RETURNS type
[characteristic ...] routine_ body

CREATE FUNCTION为用来创建存储函数的关键字; func_ _name 表示存储函数的名称;func_ parameter 为存储过程的参数列表，参数列表形式如下:

[ INIOUT 1 INOUT ] param name type

其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_ name表示参数名称; type 表示参数的类型，该类型可以是MySQL数据库中的任意类型。

RETURNS type 语句表示函数返回数据的类型; characteristic 指定存储函数的特性，取值与创建存储过程时相同，这里不再赘述。

创建存储函数，名称为NameByZip,该函数返回SELECT语句的查询结果，数值类型为字符串型，代码如下:

CREATE FUNCTION NameByZip ()
RETURNS CHAR(50)
RETURN (SELECT s_ name FROM suppliers WHERE S call= 148075') ;

创建一个存储函数， 参数定义为空，返回-一个INT类型的结果。代码的执行结果如下:

mysq1> DELIMITER //
mysql> CREATE FUNCTION NameByZip ()
-> RETURNS CHAR(50)
-> RETURN 设(SELECT s name FROM suppliers WHERE 8 call= 1480755);
-> //
Query OK，0 rows affected (0.12 sec)
mysq1> DELIMITER ;

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

指定参数为IN、ouT或INOUT只对PROCEDURE是合法的。(FUNCTION 中总是默认为IN参数)。RETURNS子句只能对FUNCTION做指定,对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

变量的使用

变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN..END程序中，本小节主要介绍如何定义变量和为变量赋值。

1、定义变量

在存储过程中使用DECLARE语句定义变量，语法格式如下:

DECLARE var name [, varname]. date type [ DEFAULT value] ;

var_ name为局部变量的名称。DEFAULT value子句给变量提供一个默认值。 值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果没有DEFAULT 子句，初始值为NULL.

定义名称为myparam的变量，类型为INT类型，默认值为100,代码如下：

DECLARE myparam INT DEFAULT 100;

2、为变量赋值

定义变量之后为变量赋值可以改变变量的默认值MySQL中使用SET语句为变量赋值，语法格式如下:

SET var_ name来expr[，var_ name = expr] ....

在存储程序中的SET语句是一-般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。

在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x,b=y, .这样的扩展语法。其中不同的变量类型(局域声明变量及全局变量)可以被混合起来。这也允许把局部变量和--些只对系统变量有意义的选项合并起来。

声明3个变量，分别为varl、var2 和var3,数据类型为INT,使用SET为变量赋值，代码如下:

DECLARE var1, var2， var3 INT;
SET var1 =10, var2 = 20;
SET var3 =varl+var2;

MySQL中还可以通过SELECT ... INTO为-一个或多个变量赋值，语法如下:

SELECT col name[,...] INTO var name[, ...] table_ expr;

这个SELECT语法把选定的列直接存储到对应位置的变量。col name 表示字段名称;var_ name表示定义的变量名称table_ _expr 表示查询条件表达式包括表名称和WHERE子句。

声明变量fruitname 和fruitprice, 通过SELECT ... INtO语句查询指定记录并为变量赋值，代码如下:

DECLARE fruitname CHAR(50) ;
DECLARE fruitprice DECIMAL(8,2) ;
SELECT f_name, f_price INTO fruitname, fruitprice
FROM fruits WHEREf id ='al' ;

以上就是小编整理的MySQL的创建存储过程，只是小编的个人理解，有哪里不准确的地方，还请大家多多指出，小编和大家一起共同进步~~~

喜欢小编请多多点赞评论分享，关注小编，后续小编会带来更多的内容更新，希望能够帮到大家更好的学习！！！