mysql——使用——存储过程——示例

最新推荐文章于 2026-01-07 14:05:42 发布

原创最新推荐文章于 2026-01-07 14:05:42 发布 · 102 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #java #python #sql

本文详细介绍了一种使用SQL创建存储过程的方法，并通过具体实例展示了如何在数据库中使用存储过程进行数据查询。从创建表、插入数据到定义并调用存储过程，再到查看输出和删除存储过程，全程步骤清晰，适合初学者学习。

前期数据准备：

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;