数据库存储过程简单介绍

<>存储过程

存储过程就是一条或多条SQL语句的集合 当对数据库进行一系列的操作时 存储过程就可以将这些复杂的操作封装成一个代码块 以便重复使用
大大减少了数据库开发人员的工作量

<>创建存储过程

创建存储过程的基本语法
CREATE PROCEDURE 存储过程名称([存储过程的参数列表]) [存储过程的特性]routine_body
存储过程的参数列表语法如下
([[IN|OUT|INOUT]参数名 参数类型])
* IN : 输入参数 表示该参数的值必须在调用存储过程时指定 在存储过程中修改该参数的值不能被返回 为默认值
* OUT: 输出参数 该值可在存储过程内部被改变 并可返回
* INOUT: 输入输出参数 调用时指定 并且可被改变和返回
存储过程的特性取值

* LANGUAGE SQL: 说明routine_body部分是由SQL语句组成的 当前系统支持的语言为SQL SQL是LANGUAGE的唯一值
* [NOT]DETERMINISTIC: 指明存储过程执行的结果是否确定 . 默认NOT DETERMINISTIC (每次执行存储过程时
相同的输入可能得到不同的输出) DETERMINISTIC 相反
* {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA} : 指明子程序使用SQL语句的限制.
默认指定CONTAINS SQL
* CONTAINS SQL: 表明子程序包含SQL语句 但是不包含读写数据的语句
* NO SQL 表明子程序不包含SQL语句
* READS SQL DATA : 说明子程序包含读写数据的语句
* MODIFIES SQL DATA 表明子程序包含写数据的语句
* SQL SECURITY{DEFINER|INVOKER}: 指明谁有权限来执行 默认系统指定DEFINER
* DEFINER : 表示只有定义者才能执行
* INVOKER 表示拥有权限的调用者可以执行
* COMMENT’string’ : 注释信息 可以用来描述存储过程
routime_body 是SQL代码的内容 可以用BEFIN…END来表示SQL代码的开始和结束

<>创建存储过程语法保存解决方案

使用delimiter命令短暂替换结束符 (替换后一定要转换回来)
delimiter // CREATE PROCEDURE Padf() BEGIN SELECT * FROM tb_spu; END//
delimiter ;
<>变量的使用

<>定义变量

语法
DECLARE [变量名称列表] [变量类型][DEFAULT 默认值]
* 变量名称: 可以定义多个变量 但是后面的类型只能定义一个
例如创建a b两个变量(定义名称不要和我一样 没有意义的定义 =w=) 类型为INT 默认值为 100
DECLARE a,b INT(10)DEFAULT 100
<>变量赋值
SET 变量名称=10[,变量名称=10,...]
* 使用SET进行赋值即可 可以同时赋值多个变量 使用 , 隔开
扩展 : MySQL中还可以通过SELECT…INTO为一个或者多个变量进行赋值

语法格式
SELECT 字段名称 INTO 变量名称 查询条件表达式
例如 : 声明变量a b 查询指定记录为变量赋值
DECLARE a,b INT; SELECT grade,gender INTO a,b FORM student WHERE name='jack';
<>定义条件和处理程序

在开发中 我们经常需要对特定的条件进行处理 这些条件可以联系到错误以及子程序中的一般流程控制 定义条件是事先定义程序执行过程中遇到的问题
处理程序定义了在遇到这些问题时应当采取的处理方式 并且保证存储过程在遇到警告或错误时能继续执行

<>定义条件

语法
DECLARE 定义条件名称 CONDITION FOR [条件类型];
条件类型:

* SQLSTATE[VALUE] : 长度为5的字符串类型错误代码
* 定义数值: 数值类型的错误代码
例如 定义一个ERROR1148(42000)错误 名称为command_not_allowed
## 方法一 DECLARE command_not_allowed CONDITION FOR SQLSTATE'42000'; ## 方法二
DECLARE command_not_allowed CONDITION FOR 1148;
<>定义处理程序

语法
DECLARE 错误处理方式 HANDLER FOR 错误类型 程序语句段
错误处理方式

* CONTINUE : 表示遇到错误不处理 继续执行
* EXIT : 表示遇到错误马上退出
* UNDO : 表示遇到错误后撤回之前的操作 (MySQL暂时不支持这样的操作)
错误类型

* SQLSTATE[VALUE] : 包含5个字符的字符串错误值
* 定义错误名称: 表示DECLARE CONDITION定义错误条件名称
* SQLWARNING 匹配所有以01开头的SQLSTATE错误代码
* NOT FOUND 匹配所有以02开头的SQLSTATE错误代码
* SQLEXCEPTION 匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
* 数值类型错误 : 匹配数值类型错误代码
例如:
## 方法一 错误类型为CONTINUE 如果遇到错误为'42s02'则执行CONTINUE 并且输出 ## NO_SUCH_TABLE DECLARE
CONTINUE HANDLER FOR SQLSTATE '42s02' SET @info='NO_SUCH_TABLE' ##方法二
先定义no_such_table条件 遇到1146错误就执行CONTINUE操作 DECLARE no_such_table CONDITION FOR
1146; DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='ERROR'; ## 方法三
使用SQLWARNING捕获所有以01开头的字符串错误值 然后执行EXIT操作 并且输出## ERROR DECLARE EXIT HANDLER FOR
SQLWARNING SET @info='ERROR' ## 方法四 使用NOT FOUND捕获所有以02开头的字符串错误值 然后执行EXIT操作 并且输出
## NO_SUCH_TABLE DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE'
## 方法五 使用SQLEXCEPTION捕获所有没有被NOT FOUND或SQLWARNING捕获到的错误 然后执行EXIT操作 并且输出ERROR
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR' ## 方法六 捕获错误数值类型为1146的错误
执行CONTINUE 并且输出NO_SUCH_TABLE DECLARE CONTINUE HANDLER FOR 1146 SET
@info='NO_SUCH_TABLE'
<>光标的声明

语法 :
DECLARE 光标名称 CURSOR FOR select_statement
* select_statement 表示SELECT语句内容 返回一个用于创建光标的结果集
例如: 声明一个名为cursor_student的光标
DECLARE cursor_student CURSOR FOR select s_name,s_gender FROM student;
<>光标的使用

使用光标之前首先要打开光标

语法
OPEN 光标名称; FETCH 光标名称 INTO var_name[,var_name]...
* var_name 表石将光标中的SELECT语句查询出来的信息存入该参数中 需要注意的是 var_name必须在声明光标之前就定义好
例如 使用名称为cursor_student 将查询出来的信息存入s_name和s_gender中
FETCH cursor_student INTO s_name,s_gender;
光标的关闭

使用完光标后要将光标关闭

语法
CLOSE 光标名称
<>流程控制的使用

流程控制用于将多个SQL语句划分或组合成符合业务逻辑的代码块

MySQL中流程控制语句包括: IF语句 CASE语句 LOOP语句 WHILE语句 LEAVE语句 ITERATE语句 REPEAT语句和WHILE语句

<>IF语句

IF语句是指如果满足某种条件 就根据判断的结果为TRUE或FALSE执行相应的语句

语法
IF 判断条件 THEN SQL语句列表 [ELSEIF 判断条件 THEN SQL语句列表] [ELSE SQL语句列表] END IF
* SQL语句列表 : 包括一个或多个语句
需要注意的是MySQL中还有一个IF()函数 他不同于这里描述的IF语句

示例:
IF val IS NULL THEN SELECT 'val is NULL' ELSE SELECT 'val is not NULL' END IF;
上述代码中 判断val值是否为空 如果val值为空 输出字符串 “val is NULL” 反之输出 “val is not NULL”

IF语句和IF函数都需要NED IF来结束IF语句

<>CASE语句

CASE语句有两种语法格式

语法一:
CASE 判断表达式 WHEN 判断表达式可能的值 THEN SQL语句列表 [WHEN 判断表达式可能的值 THEN SQL语句列表]... [ELSE
SQL语句列表] END CASE
例如 判断val值是等于1还是等于2 或者两个都不相等
CASE val WHEN 1 THEN SELECT 'val is 1'; WHEN 2 THEN SELECT 'val is 2'; ELSE
SELECT 'val is not 1 or 2'; END CASE;
语法二 :
CASE WHEN 判断条件 THEN SQL语句列表 [WHEN 判断条件 THEN SQL语句列表] [ELSE SQL语句列表] END CASE;
需要注意的是 : 这里讲解的用在存储过程里的CASE语句与流程控制函数里描述的SQL CASE表达式中的CASE语句有些不同 :
存储过程中的CASE语句不能有ELSE NULL子句 并且END CASE代替END来终止

<>LOOP语句

LOOP内的语句一直重复执行知道跳出循环语句

语法
[loop_label:]LOOP statement_list END LOOP[loop_lable]
* loop_label 标识符效果 可以跳出循环
例如 :
DECLARE id INT DEFAULT 0; add_loop:LOOP SET id =id+1; IF id>=10 THEN LEAVE
add_loop; END IF; END LOOP add_loop;
上述代码中 循环执行id加一操作 但id值大于或等于10时 使用LEAVE语句退出循环

<>LEAVE语句

LEAVE语句用于退出任何被标记的流程控制构造

语法
LEAVE 标识符
<>IFERATE语句

IFERATE意思是再次循环 IFERATE语句用于将执行顺序转到语句段开头处
ITERATE 标识符
例如 :
CREATE PROCEDURE doiterate() BEGIN DECLARE p1 INT DEFAULT 0; my_loop: LOOP SET
p1=p1+1; IF p1<10 THEN IFERATE my_loop; ELSEIF p1>20 THEN LEAVE my_loop; END
IF; SELECT 'ip is between 10 and 20'; END LOOP my_loop; END
上述代码中 p1初始值为0 如果p1的值小于10时 重复执行p1加一操作 如果p1大于10小于20时 打印"ip is between 10 and 20
" 如果p1大于20时执行LEAVE语句 退出循环

<>REPEAT语句

REPEAT语句是创建一个带有条件判断的循环过程

语法
[标识符:]REPEAT SQL语句列表 UNTIL 判断条件 END REPEAT[标识符]
例如:
DECLARE id INT DEFAULT 0; REPEAT SET id =id+1; UNTIL id>=10; END REPEAT;
(应该不介绍也能看的懂吧 = =)

<>WHILE语句

WHILE语句是一个带有判断条件的循环语句 与REPEAT语句不同 WHILE语句对判断条件先进行判断

语法:
[标识符:]WHILE 判断条件 DO SQL语句列表 END WHILE[标识符]
例如:
DECLARE i INT DEFAULT 0; WHILE i>10 DO SET i=i+1; SELECT 'i Less than 10' END
WHILE
<>存储过程的使用

相信通过上面的学习 你已经能够定义出一个完整的存储过程

使用存储过程可以使程序执行效率更高 安全性更好 增强程序的可重用性和维护性

<>调用存储过程

语法
CALL sp_name([parameter[,...]])
* sp_name 是存储过程的名称
* parameter 存储过程的参数
<>查看存储过程

SHOW STATUS语句查看存错过程的状态
SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']
这个语句是一个MySQL的扩展 它返回子程序的特征 如 数据库 名字 类型 创建者等

* PROCEDURE和FUNCTION分别表示查看存储过程和函数
* LIKE 语句表示匹配的名称
例如
SHOW PROCEDURE STATUS LIKE 'C%'\G

• « 上一篇：matlab多行注释
• » 下一篇：数据库基础 快速学习 理解 一张图理解数据库（附带相关资源）第一天