存储过程
存储过程是一段代码,由存储在一个数据库的目录中、声明式的和过程式的sql语句组成,可以从一个程序、触发器或者另一个存储过程调用它从而激活它。
每个存储过程包含至少3部分:一个参数列表、一个存储过程、一个名字。
一个数据库中的存储过程的名字必须是唯一的,就像表的名字一样。
一个参数列表可以有0个、1个或多个参数,通过这些参数,过程就可以和外界联系。
存储过程支持3中参数类型:
1、输入参数IN:数据可以传递到存储过程;
2、输出参数OUT:数据可以由存储过程传到外界;
3、输入输出参数INOUT:既可以充当输入参数,也可以充当输出参数。
就像c语言函数一样,即使没有参数,过程名后面还是需要跟一对括号。
存储过程以begin开始end结束,且之间还可以嵌套begin-end块。
局部变量:
declare 变量列表 变量类型 [default 默认值]
存储过程不仅可以使用局部变量,还可以使用全局变量。
默认值不仅限于直接量,还可以是符合表达式,也可以是标量子查询。
?
1 2 3 4 5 6 7 8 |
mysql> delimiter // |
set语句
set用于给一个变量赋值。如:
?
1 2 3 |
set a = 1; |
leave语句
离开一个块(循环块或者语句块),类似于break;
如下,进入begin后立即离开。
?
1 2 3 4 |
mysql> create procedure test (in a integer) |
iterate语句
进入一个循环。
call语句
调用存储过程。
if-esle语句
格式:
if 条件 then 语句 ;
elseif 条件 then 语句;
esle 语句;
end if
?
1 2 3 4 5 6 7 8 9 |
mysql> create procedure test (in a integer) |
case语句
格式:
case
when 条件 then 语句;
when 条件 then 语句;
else 语句;
end case;
while 语句
格式:
while 条件 do
语句;
end while;
?
1 2 3 4 5 6 7 |
mysql> create procedure test (in a integer) |
repeat语句
格式:
repeat
语句;
until 条件 end repeat;
?
1 2 3 4 5 6 7 |
mysql> create procedure test (in a integer) |
loop语句
格式:
loop
if或case条件 leave loop;
语句;
end loop;
?
1 2 3 4 5 6 7 8 9 |
mysql> create procedure test (in a integer) |
select into 语句
用于将select的查询结果赋值给过程内的变量。
?
1 2 3 4 |
mysql> create procedure test (out b integer) |
现在student内有4条数据,调用test如下:
?
1 2 3 4 5 6 7 8 |
mysql> set @b = 0// |
如果select语句查询的结果包含有多行,直接使用into赋值时不可行的。比如:
?
1 2 3 4 |
mysql> create procedure test (out b integer) |
虽然语法正确,但是在调用时报错:
?
1 2 |
mysql> call test(@b)// |
怎么办?
使用游标访问多行数据
使用游标涉及到4个语句:
declare cursor(声明游标)、open cursor、fetch cursor(获取一行数据)、close cursor。
格式:
declare 游标名字 cursor for 表查询语句
如下:统计student表有多少行数据。
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> create procedure test (out a integer) |
declare handler语句
存储过程在执行时可能会出现错误,declare handler语句涌来解决当出现错误时应该怎么做。
格式:
declare handler for <条件> 处理办法
包括:continue、exit、undo
<条件>包括:、sqlwarning、not found、sqlexception
所以上面的“declare continue handler for not found set found = false;”就是当游标到达行尾是继续执行过程并且set found = false。
drop 语句
删除存储过程;
drop procedure [if exists] 过程名
存储函数
存储函数与存储过程很相似:都是由sql语句和过程式语句所组成的代码片段,可以从应用程序和sql语句调用。
区别:
1。存储函数可以拥有输入参数,但是不能拥有输出参数。存储函数本身就是输出参数。
2.存储函数的调用和调用熟悉的表两函数一样,不能使用一个call语句调用存储函数。
3.存储函数必须包含一个return语句。
格式:
create function 函数名(<参数列表>) return 返回类型
begin
函数体;
end
?
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> create function dd(ss char(20)) |
?
1 2 3 4 5 6 7 |
mysql> select * from student// |
?
1 2 3 4 5 |
mysql> create function dd(id int) |
?
1 2 3 4 5 6 7 |
mysql> select dd(1)// |