mysql存储过程和函数

存储过程概述

分类

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

1. 没有参数(无参数无返回)
2. 仅仅带IN类型(有参数无返回)
3. 仅仅带OUT类型(无参数有返回)
4. 既带IN又带OUT(有参数有返回)
5. 带INOUT(有参数有返回)

注意： IN、OUT和INOUT都可以在一个存储过程中带多个。

创建存储过程

语法分析

# 语法
create procedure 存储过程名(in|out|INOUT 参数名 参数类型,...)
[characteristics ...]
begin
存储过程体
end

类似于Java中的方法

修饰符  返回类型  方法名(参数类型 参数名,....){
	方法体;
}

说明：

代码举例

# 存储过程和函数
# 1.创建存储过程
# 举例1： 创建存储过程select_all_data(),查看emp表的所有数据
# 自定义结束符为$
delimiter $
create procedure select_all_data()
begin
    select * from emp;
end $
# 恢复结束符为;
delimiter ;

# 存储过程的调用
call select_all_data();

无参数无返回的存储过程

# 创建存储过程 avg_employee_salary(),返回所有员工的平均工资
delimiter //
create procedure avg_employee_salary()
begin
    select avg(sal) avgSal from emp;
end //
delimiter ;
# 存储过程调用
call avg_employee_salary();

带输出参数的存储过程

# 带out
# 创建存储过程show_min_salary(),查看emp表的最低薪资值，并将最低薪资通过OUT参数`ms`输出
delimiter $
# 声明输出参数名称  参数类型  ms double
create procedure show_min_salary(out ms double)
begin
    select min(sal) into ms from emp;
end $
delimiter ;
# 调用
# @变量名称
call show_min_salary(@ms);
# 查看变量值
select @ms;

带输入参数的存储过程

# 创建存储过程show_someone_salary(),查看emp表的某个员工的薪资，
# 并用in参数empname输入员工姓名
delimiter $
create procedure show_someone_salary(in empname varchar(20))
begin
    select sal from emp where ename=empname;
end $
delimiter ;
# 存储过程的调用
# 调用方式1
call show_someone_salary('CLARK');
# 调用方式2
set @empname := 'SMITH';
call show_someone_salary(@empname);

带输入和输出参数的存储过程

# 类型：带IN和OUT
# 创建存储过程show_some_salary2(),查看emp表的某个员工的薪资
# 并用in参数empname输入员工姓名，用out参数empsalary输出员工薪资
delimiter $
create procedure show_some_salary2(in empname varchar(20),out empsalary double)
begin
    -- into:给变量赋值,
    select sal into empsalary from emp where ename=empname;
    select empsalary;
end $
delimiter ;
# 调用存储过程
set @empname :='SMITH';
set @empsalary :=0;
call show_some_salary2(@empname,@empsalary);

带INOUT的参数的存储过程

# 带INOUT的存储过程
# 创建存储过程show_mgr_name(),查看emp表的某个员工的领导的姓名，使用INOUT参数empname输入员工姓名，
# 输出领导的姓名
delimiter $
create procedure show_mgr_name(inout empname varchar(20))
begin
    select ename
    into empname
    from emp where empno =(
        select mgr from emp where ename=empname
    );
    select empname;
end $
delimiter ;

# 存储过程的调用
set @empname :='SMITH';
call show_mgr_name(@empname);
# 查询变量的值
select @empname;

存储函数的使用

使用函数可以对数据进行各种处理操作，极大的提高用户对数据库的管理效率。
MySQL支持自定义函数，定义好了之后，调用方式与调用MySQL预定的系统函数一样

语法分析

学过的函数:Lengrh,Substr,Concat等
语法格式

# 存储函数
# 创建存储函数，名称为job_by_name(),参数定义为空
# 该函数查询SMITH的job,并返回数据类型为字符串型
delimiter $
create function job_by_name()
returns varchar(9)
    deterministic
    contains sql
    reads sql data
begin
    return (select job from emp where ename='SMITH');
end $
delimiter ;

# 调用存储函数
select job_by_name();

# 创建函数之前执行下列语句，保证函数的创建成功
set global log_bin_trust_function_creators=1;
# 创建存储函数，名称为comm_by_empno(),参数为emp_no,该函数查询empno的comm,并返回，数字类型为字符串型
delimiter $
create function comm_by_empno(emp_no int)
returns varchar(20)
begin
    return (select comm from emp where empno=emp_no);
end $
delimiter ;

# 函数的调用
select comm_by_empno('7521');

存储过程和存储函数的比较

存储过程和函数的查看、修改、删除

查看

使用show create 语句查看存储过程和函数的创建信息

show create {procedue | function } 存储过程名或函数名;

show create procedure show_mgr_name;
show create function job_by_name;

使用show status语句查看存储过程和函数的状态信息

基本语法机构如下：

show {procedure | function } status like 'patter'

# 使用show status语句查看存储过程和函数的状态信息
show procedure status ;
show procedure status like 'show_mgr_name';
show function status like 'comm_by_empno';

从information_schema.Routines表中查看存储过程和函数的信息

语法格式如下：

select * from information_schema.ROUTINES
where ROUTINE_NAME ='存储过程或函数的名' [And Routine_type={'PROCEDURE|FUNCTION'}];

# 从information_schema.Routines表中查看存储过程和函数的信息
select * from information_schema.ROUTINES
where ROUTINE_NAME ='comm_by_empno' and ROUTINE_TYPE = 'FUNCTION';

修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用alter

# 存储过程、函数的修改
alter procedure show_min_salary
sql security invoker
comment '查询最低工资';

删除存储过程和函数

-- 删除存储过程show_some_salary2
drop procedure if exists show_some_salary2;

存储过程优点和缺点

优点

缺点