mysql支持3条用来创建循环的语句_MySql学习笔记——存储过程

今天复习了一下mysql的一些知识，之前学习的时候是看视频，但是没有做什么笔记，所以现在通过看书把以前学习的回顾一下，加深一下印象。

存储过程定义

简单的来说，可以定义一段程序放在数据库中，这样的程序称为存储过程，它是最重要的数据库对象之一。存储过程实质上就是一段代码，可以由声明式SQL语句(如create,update和select等)和过程式SQL语句(如if-then-else)组成。

存储过程的优点

存储过程在服务器端运行，执行速度快。

存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需要从高速缓冲存储器中调用已编译好二进制代码执行，提高系统性能。

确保数据安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。

存储过程的创建、调用、删除和修改

1.创建存储过程

语法：

create procedure sp_name ([proc_parameter[,....]])

[characteristic...] routine_body

其中，proc_parameter的参数如下：

[ in | out | inout] param_name type

说明：

sp_name: 存储过程的名称，默认在当前数据库中创建。若要在特定的数据库中创建，要在名称前面加上数据库的名称，格式为：db_name.sp_name。名称要尽量避免与MYSQL的内置函数相同，否则会发生错误。

proc_parameter：存储过程的参数。

param_name为参数名，

type为参数的类型，有多个参数时用逗号隔开。MySql的存储过程支持三种类型的参数：输入参数、输出参数和输入/输出参数，关键字分别是IN、OUT和INOUT。输入参数使数据可以传递给一个存储过程。当需要一个答案或结果的时候，可以使用输出参数。输入输出两者都可以充当。

例子：

delimiter $$

create PROCEDURE delete_user(in us int(10))

begin

DECLARE num int(4);

set num = us - 1;

delete from user where id = us;

end $$

delimiter;

其中delimiter $$是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“#”，两个“￥”等。使用delimiter命令时，应该尽量避免使用反斜杠(“/”)字符，因为那是Mysql的转义字符。

过程体

在存储过程体中可以使用所有的sql语句类型，包括所有的DDL、DCL和DML语句。

1.局部变量

语法：

declare var_name[,...] type [default value]

例子：

declare num int(4);

declare str,str2 varchar(6);

局部变量和用户变量的区别在于：局部变量前面没有使用@符号，局部变量在其所在的begin..end语句块处理完就消失了，而用户变量存在于整个会话当中。

2.使用SET语句赋值

要给局部变量赋值可以使用SET语句。语法如下

SET var_name = expr [,var_name = expr]……

set num = 1, str = 'hello';

3.select...into语句

使用这个语句可以把选定的列值直接存储到变量中。因此返回的结果只能有一行。

语法：

select col_name[,...] into var_name[,...] table_expr

说明：

col_name是列名。

var_name是要赋值的变量名。

table_expr是select语句中的from子句及后面的部分。

该语句只能在存储过程体中使用。

4.流程控制语句

在MySql中，常见的过程式SQL语句可以用在一个存储过程体中。如：IF语句、CASE语句、LOOP语句、WHILE语句、ITERATE语句和LEAVE语句

IF语句

IF－THEN－ELSE语句可以根据不同的条件执行不同的操作。

语法：

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list]...

[ELSE statement_list]

END IF

说明：

search_condition是判断条件。

statement_list中包含一个或多个sql语句。当search_condition的条件为真时，就执行相应的sql语句。

例子：

delimiter $$

create procedure compar(in k1 integer, in k2 integer, out k3 char(6))

BEGIN

if k1>k2 THEN

set k3 = '大于';

elseif k1=k2 then

set k3 = '等于';

else

set k3 = '小于';

end if;

end $$

delimiter;

CASE语句

语法：

CASE case_value

WHEN when_value THEN statement_list

[ WHEN when_value THEN statement_list]...

[ ELSE statement_list]

END CASE

一个case语句经常可以充当一个IF-THEN-ELSE语句。

例子：

delimiter $$

create procedure abc(in str varchar(4), out sex varchar(4))

begin

case str

when 'm' then set sex = '男';

when 'f' then set sex = '女';

else set sex = '无';

end case;

end $$

delimiter ;

循环语句

MySql支持3条用来创建循环的语句：WHILE、REPEAT和LOOP语句，在存储过程中可以定义0个、1个或多个循环语句。

WHILE语句语法为：

[begin_label: ]

WHILE search_condition DO

statement_list

END WHILE [end_label]

说明：理解这些语句可以通过java中的知识理解。

例子：

delimiter $$

create procedure dowhile()

begin

declare v1 in default 5;

while v1 > 0 do

set v1 = v1-1;

end while;

end $$

delimiter ;

REPEAT语句语法如下：

[begin_label: ]

REPEAT

statement_list

UNTIL search_condition

END REPEAT [end_label]

说明：REPEAT语句首先执行statement_list中的语句，然后判断search_condition是否为真，为真则停止循环，不为真则继续循环。

例子：

repeat

v1=v1-1;

until v1<1;

end repeat;

repeat和while语句的区别在于：REPEAT先执行语句，后进行判断；而WHILE是先判断条件为真时才执行语句。

LOOP语句语法如下：

[begin_label: ]

LOOP

statement_list

END LOOP [end_label]

说明：LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造，statement_list是需要重复执行的语句。在循环体内的语句一直重复至循环被退出，退出时通常伴随着一个LEAVE语句。

LEAVE语句经常和BEGIN...END或循环一起使用，结构如下：

LEAVE label

例子：

delimiter $$

create procedure doloop()

begin

set @a=10;

label: loop

set @a=@a-1;

if @a<0 then

leave label;

end if;

end loop label;

end $$

delimiter ;

存储过程的调用

语法：

call sp_name([parameter[,...]])

例子：判断两个数的大小

delimiter $$

create procedure compar(in k1 integer, in k2 integer, out k3 char(6))

BEGIN

if k1>k2 THEN

set k3 = '大于';

elseif k1=k2 then

set k3 = '等于';

else

set k3 = '小于';

end if;

end $$

delimiter;

call compar(4,4,@k3);

select @k3;

结果

存储过程的删除

语法：

drop procedure [if exists] sp_name

例子：

drop procedure if exists compar;

存储过程的修改

修改存储过程可以用alter procedure语句。

语法：

alter procedure sp_name[characteristic...] 其中characteristic是存储过程创建时特征。

小结

主要把存储过程定义，创建、调用、修改和删除简单回顾一下，这块知识还是很重要的。