一、存储过程和函数的引入

存储过程和函数是在数据库中定义一些 SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的 SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句。而且，存储过程和函数是在 MySQL 服务器中存储和执行的，可以减少客户端和服务器端的数据传输；

存储过程通过CALL来调用。函数可以在SQL语句中出现，和使用内建函数是一样的。

二、存储过程和函数的创建、删除、修改和查看

创建

存储过程
CREATE PROCEDURE sp_name ([ [ IN | OUT | INOUT ] param_name type [,...]])
[characteristic ...] routine_body

CREATE FUNCTION sp_name ([param_name type[,...]])
RETURNS type
[characteristic ...] routine_body

characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

Characteristic 参数有多个取值。其取值说明如下：
LANGUAGE SQL：说明 routine_body 部分是由 SQL 语言的语句组成，这也是数据库系统默认的语言。

[ NOT ] DETERMINISTIC ：指明存储过程的执行结果是否是确定的。DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。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’ ：注释信息；

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

如果子程序名和内建的SQL函数名一样，定义子程序时，你需要在这个名字和随后括号中间插入一个空格，否则发生语法错误。

routine_body 包含合法的SQL过程语句。可以使用复合语句语法（“BEGIN ... END复合语句”）。复合语句可以包含声明，循环和其它控制结构语句。

和Trigger一样创建时，在创建之前用delimiter修改分隔符，创建之后在将分隔符恢复到原来的
删除

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
修改

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

这个语句可以被用来改变一个存储程序或函数的特征
查看
SHOW CREATE {PROCEDURE | FUNCTION} sp_name
这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名子程序的确切字符串

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

三、存储过程中的变量

定义变量

DECLARE var_name[,...] type [DEFAULT value]

这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。

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

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

SELECT col_name[,...] INTO var_name[,...]
FROM table_name WHERE condition
SQL变量名不能和列名一样。如果SELECT ... INTO这样的SQL语句包含一个对列的参考，并包含一个与列相同名字的局部变量，MySQL当前把参考解释为一个变量的名字。

四、游标的使用

声明光标

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

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

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

关闭光标

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

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

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

五、流程控制

IF语句

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list] ...

[ELSE statement_list]

END IF
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
LOOP语句

[begin_label:] LOOP
statement_list
END LOOP [end_label]

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

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

LEAVE label

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

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

[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

REPEAT语句内的语句或语句群被重复，直至search_condition 为真。
WHILE语句

[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]

WHILE语句内的语句或语句群被重复，直至search_condition 为真。

六、数据备份与还原

数据备份

1）使用 mysqldump 命令备份

mysqldump -u username -p dbname table1 table2 ... > BackupName.sql

dbname 参数表示数据库的名称；table1 和 table

2）参数表示表的名称，没有该参数时将备份整个数据库； BackupName.sql 参数表示备份文件的名称，文件名前面可以加上一个绝对路径。通常以 sql 作为后缀。

1.2 使用 sqlyog 图形工具备份
数据还原

2）使用 mysql 命令还原

Mysql -u root -p [dbname] < backup.sql

dbname 参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表示还原该数据库下的表。不指定数据库名时，表示还原特定的一个数据库。而备份文件中有创建数据库的语句。

3）使用 sqlyog 图形工具还原

秒客网

MySQL 存储过程和函数以及数据恢复和备份

一、存储过程和函数的引入

二、存储过程和函数的创建、删除、修改和查看

创建

删除

修改

查看

三、存储过程中的变量

定义变量

赋值

四、游标的使用

声明光标

打开光标

使用光标

关闭光标

五、流程控制

IF语句

CASE语句

LOOP语句

LEAVE语句

ITERATE语句

REPEAT语句

WHILE语句

六、数据备份与还原

数据还原

相关文章