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

╰+攻爆jí腚メ 2023-10-02 08:48 58阅读 0赞

前期数据准备：
    
    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 ;

![535f91ed5e8a8826e2105bde4217b672.png][]

查询此存储过程状态：

show procedure status like 'num_from_employee';

![d34a3dc2cd75e648541e6423a76547dd.png][]

查看此存储过程的定义：

show create procedure num_from_employee;

![1460ec5134b2967bdf729751eff13561.png][]

调用此存储过程：

call num_from_employee(1002,@n);               /*call语句来调用存储过程*/

select @n; /*select语句来查看存储过程的输出值*/

![9a421ab6c7d5195532896ddbb61d8984.png][]

删除此存储过程：

drop procedure num_from_employee;

![33c19d4f448dbd39361b5b550e2a56ad.png][]

[535f91ed5e8a8826e2105bde4217b672.png]: https://img-blog.csdnimg.cn/img_convert/535f91ed5e8a8826e2105bde4217b672.png
[d34a3dc2cd75e648541e6423a76547dd.png]: https://img-blog.csdnimg.cn/img_convert/d34a3dc2cd75e648541e6423a76547dd.png
[1460ec5134b2967bdf729751eff13561.png]: https://img-blog.csdnimg.cn/img_convert/1460ec5134b2967bdf729751eff13561.png
[9a421ab6c7d5195532896ddbb61d8984.png]: https://img-blog.csdnimg.cn/img_convert/9a421ab6c7d5195532896ddbb61d8984.png
[33c19d4f448dbd39361b5b550e2a56ad.png]: https://img-blog.csdnimg.cn/img_convert/33c19d4f448dbd39361b5b550e2a56ad.png