Mysql 存储过程学习笔记

基本语法：
一、创建存储过程

create procedure name()
begin
sql
end

二、调用存储过程

call name()

注意：存储过程名称后面必须加括号

三、删除存储过程

drop procedure name

注意：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

四、其他常用命令

show procedure status

显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

show create procedure name

显示某个存储过程的详细信息

---

变量定义：
自定义变量：

DECLARE i INT;
SET i=1;

DECLARE i INT DEFAULT 1;

变量分为用户变量和系统变量，系统变量又分为会话级变量和全局级变量
用户变量名一般以@开头，滥用用户变量会导致程序难以理解及管理。

---

流程控制：
一、分之结构

• if
• case
  二、循环结构
• for循环
• while循环
• loop循环
• repeat until循环

通常可以给区块起别名：

lable:begin
sql
end lable;

使用leave lable可以跳出区块，执行后面的代码

---

输入和输出
mysql存储过程包括三种类型参数，IN,OUT,INOUT

IN输入参数
表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT输出参数
该值可在存储过程内部改变，并可返回

INOUT输入输出参数
调用时指定，可被改变也可返回

---

变量的使用
1、在存储过程中定义变量

DELCARE Index INT DEFAULT 1;

上面定义了一个局部变量Index，并提供了一个默认值1。值除了可以被声明为一个常数外，还可以被指定为一个表达式。
2、为变量赋值

DELCARE Index INT;
SET Index=10;

也可以通过SELECET…INTO赋值

DEClARE NAME CHAR(50)
SELECT NAME INTO　NAME FROM table

定义条件和处理程序

1、定义条件

DECLARE CONDITION_NAME CONDITION FOR [CONDITION_TYPE]
[CONDITION_TYPE]:
SQLSTATE[VALUE] sqlstate_value |mysql_error_code

参数解释：

• CONDITION_NAME 条件名称
• CONDITION_TYPE 条件类型
• sqlstate_value 长度为5的字符串错误代码
• mysql_error_code 为数值类型错误代码

这个语句指定需要特殊处理条件，他将一个名字和指定的错误条件关联起来。这个名字在被定义处理程序的DECLARE HANDLER语句中

//使用sqlstate_value定义
DECLARE command_error CONDITION FOR SQLSTATE '42000'

//使用mysql_error_code
DECLARE command_error CONDITION FOR SQLSTATE 1140

2、定义处理条件

DECLARE handler_type HANDLER FOR
contidion_value[,...] sp_statement
handler_type:
CONTINU | EXIT | UNDO
condition_value:
SQLSTATE [VALUE] sqlstate_value|
condition_name | SQLWARNING
|NOT FOUND | SQLEXCEPTION |mysql_error_code

参数解释：

• handler_type :参数指明错误的处理方式，该参数有3个取值，这三个取值分别是CONTINUE、EXIT和UNDO。CONTiNUE表示遇到错误不进行处理，继续向下访问；EXIT表示遇到错误后马上退出；UNDO表示遇到错误后撤回之前的操作，MYSQL暂不支持这种。

通常情况下，执行过程中遇到错误应停止执行，并撤回之前的操作，但MYSQL现在还不支持UNDO操作，所以遇到错误最好执行EXIT操作，如果能够事先预测错误类型，并进行相应处理，那么可以采用CONTINUE操作。

• condition_value参数指明错误类型，有六个值可取：
  

• sqlstate_value和 mysql_error_code同条件定义中意思相同
• condition_name是DECLARE定义的条件名称
• SQLWARING表示01开头的sqlstate_value值
• NOT FOUND表示02开头的sqlstate_value值
• SQLEXCEPTION表示所有没有被SQLWARING或NOT FOUND捕获的sqlstate_value值
• sp_statement表示一些存储过程或函数的执行语句

//方法一:sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
SET @info='Not Find';

//方法二:mysql_error_code
DECLARE CONTINUE HANDLER FOR 1148 
SET @info='Not Find';

//方法三:先定义条件，然后使用
DECLARE not_find CONDITION FOR 1146;
DECLARE CONTINUE HANDLER for not_find
SET @info='Not Find';

//方法四:SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING
SET @info='ERROR';

//方法五:NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND
SET @info='Not Find';

//方法六:SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET @info='ERROR';

• 第一种方法遇到sqlstate_value值为42000的时候，执行CONTINUE操作，并输出“Not Find”
• 第二种方法遇到mysql_error_code值为1148的时候，执行CONTiNUE操作，并输出“Ｎot Find”
• 第三种方法先定义条件，然后调用条件。先定义了not_find条件，遇到1146就执行CONTINUE，并输出”Not Find”
• 第四种方法遇到SQLWARING捕获所以以01开头的sqlstate_value值，然后执行EXIT操作，并输出“ERROR”信息
• 第五种方法遇到NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并输出“Not Find”
• 第六种方法遇到SQLEXCEPTION。SQLEXCEPTION捕获所有没被SQLWARING或NOT_FOUND捕获的sqlstate_value值，然后执行EXIT操作，并输出“ERROR”

---

光标

查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。
光标的使用包括，声明光标，打开光标，使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。
一、声明光标
基本语法：

DECLARE cursor_name CURSOR FOR select_statement;

参数说明：

• cursor_name 表示光标的名称；select_statement表示select语句，返回一个用于创建光标的结果结
  例子：

DELCARE cur_article CUROR FOR SELECT TITLE,AUTHOR FROM ARTICLE;

二、打开光标
语法：

OPEN cursor_name;

例子：

OPEN cur_article;

三、使用光标
语法：
MYSQL中使用FETCH关键字来使用光标。

FETCH cursor_name INTO var_name,[var_name...];

参数说明：

• cursor_name是光标名称

• var_name是讲光标中的select语句查询出来的信息存入改参数中。var_name必须在声明光标之前就定义好。

  例子：

FETCH cur_article INTO art_title,art_author;

这里讲cur_article中select语句查询出来的信息存入art_title和art_author中，他俩必须在前面已经定义。

四、关闭光标
语法：

CLOSE cursor_name;

MYSQL中光标只能在存储过程和函数中使用。

---

流程控制

一、IF语句
语法：

IF search_condition THEN statement_list
[ELSEIF search_condition then statement_list]
[ELSE statement_list]
END IF

参数说明：

• search_contion：条件语句
• statement_list：执行语句
  例子：

IF score>90 THEN SET @level='A'
ELSEIF score>80 THEN SET @level='B'
ELSE SET @level='C'
END IF;

二、case语句

语法：

CASE case_value
 WHEN value THEN statement_list
[WHEN value THEN statement_list]...
[ELSE statement_list]
END CASE;

参数说明：

• case_value：表示条件判断的变量
• value：表示变量的取值
• statement_list：表示执行语句
  还可以这样写：

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list]...
[ELSE statement_list]
END CASE;

参数说明：

• search_condition：表示条件判断语句
• statement_list:表示执行语句
  例子：

CASE score
WHEN 90 THEN SET @level='A';
ELSE SET @level='B';
END CASE;

或者

CASE
WHEN score>90 THEN SET @level='A';
ELSE SET @level='B';
END CASE;

三、LOOP语句
LOOP语句可以是某些特定的语句重复执行，LOOP语句没有停止循环的语句，必须遇到LEAVE语句等才能停止循环。
语法：

[label:]LOOP
statement_list
END LOOP [label]

参数说明：

• label：用来表示循环开始和结束
• statement_list：执行语句
  例子：

add_score:LOOP
SET @score=@score+5;
IF @score>100 THEN
LEAVE add_score;
END IF;
END LOOP add_score;

如果没有LEAVE则会造成无限循环

四、LEAVE和ITERATE
这两个都是用来跳出循环用的。
不同的是LEAVE跳出的是整个循环，ITEARATE跳出的是当前循环，进入下一次循环。
ITEARATE只能出现在LOOP、REPEAT、WHILE语句内。
语法：

LEAVE label

ITERATE label

五、REPEAT
REPEAT是有条件控制的循环语句，满足特定条件时跳出循环。

[label:]REPEAT
statement_list
UNTIL search_condiition
END REPEAT

参数说明：

• label：标志
• statement_list：执行语句
• search_condition：判断语句，满足条件时跳出循环
  例子：

REPEAT
SET @num=@num+5;
UNTIL @num>100
END REPEAT;

六、while
语法：

[label:]while search_condition DO
statement_list
END WHILE [label]

参数说明：

• label：标志
• statement_list：执行语句
• search_condition：判断语句，满足该条件时继续执行，与REPEAT刚好相反
  例子：

WHILE @score<100 DO
SET @score=@score+5;
END WHILE;

---

在mysql中使用call语句来调用存储过程，存储方法则个MySQL内部函数的使用方法是一样的。
执行存储过程需要拥有EXECUTE权限，权限的信息存在information_schema数据库下面的USER_PRIVILEGES中。
存储过程通过CALL语句来调用

CALL name(param[,...]);

---

查看存储过程状态

SHOW PROCEDURE STATUS [LIKE 'name']

这个只能查看存储过程操作哪一个数据库，存储过程名称、类型、谁定义的、创建和修改时间、字符编码等信息。
如果要查看具体定义需要使用show create