存储过程

关于存储过成

1.概要

• 存储过程是一组预定义的SQL语句集合。
• 这些语句被存储在数据库服务器中，并可以像调用函数一样被多次调用。
• 存储过程通常由一系列SQL语句、条件逻辑、循环和异常处理组成。

2.创建和调用的基本过程

• 创建存储过程。

  #delimiter用于更改语句分隔符(默认状态下为;)
  delimiter $ # 更改分隔符为$
  create procedure fun_name()
  begin
  	sql 语句
  end $
  delimiter;  # 将分隔符恢复为默认的分号
  

• 确定存储过程参数。

  • 存储过程可以接受输入参数和输出参数。
  • 存储过程可以接受输入参数和输出参数。

• 调用存储过程。

  # 使用CALL语句，后跟存储过程的名称和参数（如果有）
  call fun_name();
  
  #调用带有参数的存储过程
  call fun_name(@x); # 符号将参数声明为一个用户变量。
  select @x;
  

基本使用

0.使用 northwind 示例数据库

1.创建和调用存储过程

• 创建存储过程 all_data，查看 employees 表的所有数据

  delimiter $
  create procedure all_data()
  begin
  	select * from employees;
  end $
  delimiter ;
  
  call all_data();
  

• 创建存储过程 max_salary，查看 employees 表的最高工资

  delimiter $
  create procedure max_salary
  begin
  	select max(salary) as max_salary
  	from employees;
  end $
  delimiter ;
  
  call max_salary;
  

• 创建存储过程 min_salary，查看 employees 表的最低工资并将最低工资通过参数 minSalary 输出

  delimiter $
  create procedure min_salary (out minSalary double)
  # 参数作为输出（出口参数）
  begin
  	select min(salary) into minSalary
  	from employees;
  end $
  delimiter ;
  
  call min_salary(@minSalary);
  select @minSalary;
  

• 创建存储过程 get_salary_by_id，查看 employees 表某员工编号员工的工资，并用 emp_id 输入员工编号

  delimiter $
  create procedure get_salary_by_id(in emp_id int)
  #参数作为输入(入口参数)
  begin
  	select salary from employees
  	where employee_id = emp_id;
  end $
  delimiter ;
  
  call get_salary_by_id(103);
  

• 创建存储过程 get_salary_by_name，查看 employees 表的某员工的工资，入口参数 emp_name 员工名字，出口参数 salary 该员工的工资

  delimiter $
  create procedure get_salary_by_name(in name varchar(20), out s double(8, 2))
  begin
  	select salary into s
  	from employees
  	where first_name = name;
  end $
  delimiter ;
  
  set @name = 'Lex';
  call get_salary_by_name(@name, @salary);
  select @salary;
  

• 创建存储过程 get_mgr_name，查询 employees 表中某员工名字的领导的名字参数 name 同时作为输入的员工名字和输出的领导名字

  delimiter $
  create procedure get_mgr_name(inout name varchar(20))
  begin
    select first_name into name
    from employees
    where employee_id = (
      select manager_id
      from employees
      where first_name = name
    );
  end $
  delimiter ;
  
  set @name = 'Lex';
  call get_mgr_name(@name);
  select @name;
  

2.创建存储函数

• 创建存储函数 get_mgr_name，查询 employees 表中某员工名字的领导的名字

  delimiter $
  create function doget_mgr_name(name varchar(20))
  returns varchar(20)
  begin
    return(
      select first_name
      from employees
      where employee_id = (
        select manager_id
        from employees
        where first_name = name
      )
    );
  end $
  delimiter ;
  
  select doget_mgr_name('Lex');
  

• 创建存储函数 get_count_by_dept_id，查询 employees 表某部门编号的部门员工人数

  delimiter $
  create function get_count_by_dept_id(dept_id int)
  returns int
  begin
    return (select count(*) 
            from employees where department_id = dept_id
           );
  end $
  delimiter ;
  
  set @dept_id = 50;
  select get_count_by_dept_id(@dept_id);
  

3.查看存储过程和存储函数

• 查看创建代码

  show create procedure xxx;
  show create function xxx;
  

• 查看状态

  show procedure status where Name = 'all_data';
  show function status where Name = 'avg_salary';
  

• 查看对象信息

  select * from information_schema.routines
  where routine_name = 'avg_salary';
  

4.删除存储过程和存储函数

• 使用drop关键字。

• 示例

  drop function if exists avg_salary;
  drop procedure if exists get_mgr_name;
  

---

嗨，我的朋友，必须坚强，尽管充满艰难困苦和荆棘。 —— 海龟先生《微笑》