存储过程

什么是存储过程

存储过程与批处理类似，就是将一条或者多条SQL封装起来。

为什么使用存储过程

1. 封装SQL操作，便于使用
2. 向存储过程的使用者屏蔽底层的实现，安全
3. 提高性能

创建存储过程

建表语句如下

create table employee (
	id int primary key,
	name varchar(20) not null,
	department int not null,
	age int default 0
);

create table department(
	id int primary key,
    name varchar(20) not null,
    manager int
);

alter table employee add constraint fk_department foreign key(department) references department(id);
alter table department add constraint fk_manager foreign key(manager) references employee(id);

insert into department(id, name) values(1001, "研发部"), (1002, "测试部"), (1003, "人力资源部");

insert into employee
values(1, "明", 1001, 18), (2, "红", 1001, 19), (3, "刚", 1001, 20), (4, "张三", 1002, 18), (5, "李四", 1002, 19), (6, "王二", 1002, 20), (7, "刘", 1003, 18), (8, "关", 1003, 19), (9, "张", 1003, 20);

update department set manager = 1 where id = 1001;
update department set manager = 4 where id = 1002;
update department set manager = 7 where id = 1003;

创建存储过程

DELIMITER //
create procedure department_managers()
BEGIN
    select department.id as department_id, department.name as department_name, manager as manager_id, employee.name as manager_name
    from department left join employee
    on department.manager = employee.id;
END //
DELIMITER ;

调用它试试

call department_managers();

创建可以接受参数的存储过程

查询某个部门的主管的id

DELIMITER //
create procedure department_manager(in dpt_id int, out m_id int)
BEGIN
    select manager
    into m_id
    from department left join employee
    on department.manager = employee.id
    where department.id = dpt_id;
END //
DELIMITER ;

调用它试试

call department_manager(1001, @manager_id);
select @manager_id;

查看存储过程的创建语句

show create procedure department_manager;

删除存储过程

drop procedure department_manager;

[外链图片转存中…(img-5fKfXFwL-1610376580581)]

删除存储过程

drop procedure department_manager;