前期数据准备:
create table employee ( num int(50),
d_id int(50),
name varchar(50),
age int(50),
sex varchar(50),
homeadd varchar(50)
);
insert into employee values(1,1001,'zhangsan',26,'nan','beijing');
insert into employee values(2,1001,'lisi',24,'nv','hunan');
insert into employee values(3,1002,'wangwu',25,'nan','jiangsu');
insert into employee values(4,1004,'aric',15,'nan','yingguo');
select * from employee;
create table department ( d_id int(50),
d_name varchar(50),
functione varchar(50),
address varchar(50)
);
insert into department values(1001,'keyanbu','yanfachanpin','3lou5hao');
insert into department values(1002,'shengchanbu','shengchanchanp','5louyiceng');
insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');
select * from employee;
select * from department;
执行语句:
delimiter&&
create procedure num_from_employee( IN emp_id INT,OUT count_num INT )
READS SQL DATA
begin
select count(*) into count_num from employee where d_id = emp_id;
end &&
delimiter ;
查询此存储过程状态:
show procedure status like 'num_from_employee';
查看此存储过程的定义:
show create procedure num_from_employee;
调用此存储过程:
call num_from_employee(1002,@n); /*call语句来调用存储过程*/
select @n; /*select语句来查看存储过程的输出值*/
删除此存储过程:
drop procedure num_from_employee;