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

本文主要是介绍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 存储过程和函数的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---