• 存储过程与函数
  • 存储过程的定义
  • 存储过程的创建
  • 存储过程的操作
  • 自定义函数

存储过程与函数

存储过程的定义

• 运行效率高
• 降低网络通信量
• 业务逻辑封装,减少代码的修改

存储过程的创建

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

• create procedure 关键字
• proc_parameter 参数列表 [in|out|INOUT] param_name type
• characteristic 特性列表
  • language sql
  • [not] deterministic 执行结果是否确认,默认不确定
  • contains sql | no sql | reads sql data | modifies sql data
  • sql security {definer|invoker} 执行权限,默认是定义的人
  • comment 'string' 注释
• routine_body sql代码的内容,多条语句用begin end表示开始结束

存储过程的操作

语句中的[]代表可有可无;{A|B|C}代表多选一,不能省略

#定义
create procedure avg_emp(in deptno ,out avgage float)
begin
select avg(empage) into avgage from emp where dno=deptno;
end
#调用
call avg_emp(1,@aa);
select @aa;
#查看
show procedure status [like 'xxx']
#查看2
show create procedure sp_name
#查看3
select * from information_schema.routines where routine_name='sp_name';
# 修改特性
alter procedure sp_name 
modifies sql data 
sql security invoker;
#删除
drop procedure sp_name;

自定义函数

简化版的存储过程,一定个有返回值 return出来
create function sp_name ([func_parameter[,...]]) returns type [characteristic...] routine_body;
例子:

#定义
create function age_emp() returns int 
begin 
return (
    select emp_age from emp where empno=1 
)
end
# 调用
select age_emp();
# 变量
declare var_name[,...] type [default value];
declare empdept char(10) default '财务部';
declare var1, var2 int;
declare var3 varchar(6); 
set var1=4,var2=6,var3='我的个神'
# 变量 赋值
# 一个set 可以赋值多个变量
set var_name1=expr1[,var_name2=expr2]...
# select ... into ... 也可以给变量赋值
select col_name into var_name 
# 流程控制
if xxx then xxx 
[elseif xxx then xxx] 
[else xxx]
end if;
#case 语句
case xxx
when aaa then AAA
when bbb then BBB
when ccc then CCC
else DDD
end case;
# loop 语句,类似于for循环
[begin_label:] loop
statement_list
end loop [end_label]
# leave 调出循环,类似于break
decalre ss int default 0;
add_sum: loop
ss=ss+1
if ss>50 then leave add_sum
end if 
end loop add_sum;
# iterate 跳过当前循环,类似于continue
# repeat语句,类似于do循环
[begin_label:] repeat
statement_list
until search_condition
end repeat [end_label];
declare aa int default 0;
repeat
set aa=aa+1;
until aa>50;
end repeat;
# while ,跟js中while循环一样
[begin_label:] while search_condition do 
statement_list
end while [end_label];
# 光标(游标)的使用,返回多条数据的时候需要
# 声明光标
declare cursor_name cursor for select_statement;
declare cursor_name cursor for select * from emp where gender='女';
# 打开光标
open cursor_name;
# 使用光标,这个步骤一般放在循环中
fetch cursor_name into var_1,var_2,var_3,var_4;
# 关闭光标
close cursor_name;

# 定义条件与处理程序,可以理解成 error try catch 
# 定义条件 condition
declare condition_name condition for condition_value
condition_value:
sqlstate [value] sqlstate_value| mysql_error_code
# 定义Error 1120(输入的参数类型)错误 ,类似于 throw '输入的命令有错误'
declare command_not_find condition for sqlstate '输入的命令有错误';
declare command_not_find condition for 1120;
# 定义处理程序
declare handler_type handler for condition_value[,...] sp_statement;
# handler_type {continue|exit|undo}
# condition_value 的取值
# 1. sqlstate [value] sqlstate_value 
# 2. condition_name 匹配定义的condition
# 3.sqlwarning 匹配01开头的错误码
# 4.not found  匹配02开头的错误码
# 5.sqlexpection 匹配没有被sqlwarning not found 匹配的错误
# 6.mysql_error_code 匹配具体的错误码


#创建过程 查看某年龄段的雇员人数,并统计年龄和
DELIMITER //
create procedure sum_age (in min_age int,in max_age int,out count_age int)
begin 
declare temp float;
declare empage cursor for select emp_age from emp;
#declare exit handler for not found close empage;
select count(*) into count_age from emp where emp_age>min_age and emp_age<max_age;
set @sum=0;
open empage;
repeat
fetch empage into temp;
if temp>min_age and temp <max_age then set @sum=@sum+temp;
end if;
until 0 
end repeat;
close empage;
end;//

drop procedure sum_age;//
show procedure status like 'sum_age';//
show create procedure sum_age;//
call sum_age(30,40,@cc);//
select @cc,@sum;//