How to Receive the Results of a Select Statement Called in a Stored Procedure in MySQL in the Stored Procedure

Call other stored procedures (B) from stored procedures (A). (B)We would like to receive the result of the select statement we called in (A). Is this possible in the first place?

Please tell me how to write (A) without correcting (B).

Assume (B) cannot be changed in a non-Cisco procedure.

Specifically, the following procedures (B) are available:

delimiter$
create procedure proc_B()
begin
    declare value int;
     /* (omitted) */
    select value;
end$
delimiter;

You want to call this procedure from another procedure (A) and receive the value of the variable value above.

delimiter$
create procedure proc_A()
begin
    declare value int;

    /* If you're just going to call me, that's fine.
    /* However, the select result is returned directly to the caller of (A).*/
    call proc_B;

    /* Syntax error when declaring to like a normal cursor */
    declare cursor for proc_B;

    /* This is also a syntax error. */
    /* Also, this does not support multiple-line return patterns.*/
    call proc_Binto value; 

end$
delimiter;

mysql

2022-09-29 22:50

2 Answers

Head office SO replied:

Attach the OUT parameter to the procedure on the called side and

CREATE PROCEDURE INNERPROC (OUT param 1 INT)
BEGIN
   insert into something;
   SELECT LAST_INSERT_ID() into param1;
END

If defined as

CREATE PROCEDURE OUTERPROC()
BEGIN
    CALL INNERPROC(@a);
    SELECT@a INTO variableinouterproc FROM dual;
END

By writing , @a appears to contain the results of innerproc.

2022-09-29 22:50

Declaring to like a regular cursor results in a syntax error

If you want to handle multiple lines like a cursor, I've heard that MySQL's server-side cursor is actually like a temporary table.

http://dev.mysql.com/doc/refman/5.6/en/cursor-restrictions.html

Then I think it's better to use the temporary table.

delimiter$

create procedure proc_B()
begin
    /* Temporary table to return results*/
    drop table if exists proc_B__result;
    create temporary table proc_B__result as
        select 1 union all select 2;
end$

create procedure proc_A()
begin
    declare value int;

    /* Cursor scanning the temporary table*/
    detail cursor for select * from proc_B__result;

    /* Run the procedure and insert the results into the temporary table*/
    call proc_B();

    /* Open the cursor and fetch the result of multiple lines*/
    open cur;
    fetch cur into value;
    fetch cur into value;
    close cur;
end$

delimiter;

call proc_A();

2022-09-29 22:50

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656