mysql存储过程多个select 的问题

Question

我的项目是 springboot + mybatis + mysql
java项目中调用 mysql 的存储过程查询获取数据，存储过程代码如下

create
    definer = root@`%` procedure p_product_order_storage()
BEGIN
    # 2021年8月30日 08:39:01
    # 成品库存表
    # 后来追加了成品盘点单功能，计算库存要从最后一次盘点数据开始往后计算：
    # 最后一次盘点的数据+该日期之后的所有

    # 获得最后一个盘点单
    select @lastId := max(id)
    from product_order_bill where bill_type='PD';

    # 获取最后一个盘点单的盘点日期，拿该日期之后的单据数据计算库存
    select @lastDate := scdate from product_order_bill where id = @lastId;


    select t.product_coded as pf_code,t1.pf_name as product_name,
           t1.pf_specification as specification,
           t1.pf_material as material,
           t1.line as line,t1.corrugated as corrugated,t.amount
    from (
        select tt.product_coded,sum(tt.amount) as amount
        from (
            select product_coded,amount from product_order_bill_dtl where parent_id = @lastId
            Union ALL
            select b.product_coded,ifnull(b.amount,0) * c.access_mode as amount
            from product_order_bill a
            left join product_order_bill_dtl b on a.id = b.parent_id
            left join bill_type c on a.bill_type = c.id
            where date_format(a.order_date,'%Y-%m-%d') > date_format(@lastDate,'%Y-%m-%d')
        ) as tt
        group by tt.product_coded
    ) as t
    left join product_file t1 on t.product_coded = t1.pf_coded
    where t.amount != 0
    ;

END;

java端只接受到了第一个select的结果，其实前面两个 select 都是查询必要的数据为最后一个 select 服务的，想要 java 获取的是最后一个 select 返回的数据，要怎么做？

Answer 1

java里面是不是有个多结果集的概念？就是多个ResultSet, 第一个取完数据后， 取第二个。

Answer 2

可以使用存储过程的out参数将需要的内容返回，
可以试试下面的

create
    definer = root@`%` procedure p_product_order_storage(
    out pf_code varchar(20), 
    out product_name varchar(20),
    out specification varchar(20),
    out line varchar(20),
    out corrugated varchar(20),
    out amount varchar(20))
BEGIN
    # 2021年8月30日 08:39:01
    # 成品库存表
    # 后来追加了成品盘点单功能，计算库存要从最后一次盘点数据开始往后计算：
    # 最后一次盘点的数据+该日期之后的所有

    # 获得最后一个盘点单
    select @lastId := max(id)
    from product_order_bill where bill_type='PD';

    # 获取最后一个盘点单的盘点日期，拿该日期之后的单据数据计算库存
    select @lastDate := scdate from product_order_bill where id = @lastId;


    select t.product_coded into pf_code,
           t1.pf_name into product_name,
           t1.pf_specification into specification,
           t1.pf_material into material,
           t1.line into line,
           t1.corrugated into corrugated,
           t.amount into amount
    from (
        select tt.product_coded,sum(tt.amount) as amount
        from (
            select product_coded,amount from product_order_bill_dtl where parent_id = @lastId
            Union ALL
            select b.product_coded,ifnull(b.amount,0) * c.access_mode as amount
            from product_order_bill a
            left join product_order_bill_dtl b on a.id = b.parent_id
            left join bill_type c on a.bill_type = c.id
            where date_format(a.order_date,'%Y-%m-%d') > date_format(@lastDate,'%Y-%m-%d')
        ) as tt
        group by tt.product_coded
    ) as t
    left join product_file t1 on t.product_coded = t1.pf_coded
    where t.amount != 0
    ;

END;