存储过程和函数知识点总结

一、简介

    存储程序分为存储过程和函数。

    存储过程是一组为了完成特定功能的SQL语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

二、创建存储过程和函数

 1.创建存储过程

create procedure avgPrice()
begin
select avg(price) from book;
end;

 2.创建存储函数

create function getName(sid int)
returns char(50)
return (select name from book where id=sid);

 3.变量的使用

  变量可以在子程序中声明并使用，作用范围是在begin...end中

  (1)定义  

# 如果不声明默认值，初始值为null
declare myparam int default 100;
declare var1,var2,var3 int;

  (2)赋值

set var1=10,var2=20;
set var3=var1+var2;

 4.定义条件和处理程序

  定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，这样可以增强存储程序处 理问题的能力。

  (1)定义条件

# 定义“error 1148(42000)”错误，名称为command_not_allowed
# 方法一：使用sqlstate_value
declare command_not_allowed condition for sqlstate '42000'
# 方法二：使用mysql_error_code
declare command_not_allowed condition for 1148

  (2)定义处理程序

# continue遇到错误不处理，exit遇到错误马上退出，undo遇到错误后撤回之前的操作，MySQL暂不支持
# @var_name表用户变量，与连接有关，当客户端退出时，该客户端连接的所有变量将自动释放
declare continue handler for sqlstate '42S02' set @info='no_such_table'
declare exit handler for sqlwarning set @info='error'

 5.光标的使用

  查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和函数中使用光标来逐条读取查询结果集中的记录。

  (1)声明

# 语法为 declare cursor_name cursor for select_statement
declare cursor_fruit cursor for select name,price from fruits

  (2)打开

open cursor_name

  (3)使用

# 将光标cursor_fruit中select语句查询出来的信息存入name和price，变量必须在前面已定义
fetch cursor_fruit into name,price

  (4)关闭

close cursor_name

 6.流程控制的使用

  流程控制语句用来根据条件控制语句的执行。

  (1) IF 

if val is null
 then select 'val is null';
 else select 'val is not null';
end if;

  (2) CASE  

case val
 when 1 then select 'val is 1';
 when 2 then select 'val is 2';
 else select 'val is else';
end case;

  (3) LOOP

declare id int default 0;
loop_name : loop
set id = id + 1;
if id>=10 then leave loop_name;
end if;
end loop loop_name;

  (4) REPEAT

# id<=10之前，重复执行循环过程
declare id int default 0;
repeat
set id = id + 1;
until id >= 10;
end repeat;

  (5) WHILE

declare i int default 0;
while i<10 
do set i = i + 1;
end while;

三、调用存储过程和函数

# 调用存储过程
call avgPrice()
# 调用存储函数
select getName(1)

四、查看存储过程和函数

 1. 查看状态

# 获取数据库中所有以字母‘a’开头的存储过程或函数的信息
show procedure | function status like 'a%'

 2.查看定义

show create procedure | function pro_name

 3.查看信息

select * from information_schema.Routines where routine_name='pro_name'

五、修改存储过程和函数

# 修改存储过程的定义，将读写权限改为modifies sql data，并指明调用者可以执行
alter procedure pro_name
modifies sql data
sql security invoker;

六、删除存储过程和函数

drop procedure | function pro_name