Use of IN and OUT in Stored Procedures

Question

Use of IN and OUT in Stored Procedures

Navigation

#1 by (1 votes)

1

A procedure stored as such is a routine that contains the sql statement (s) to which it is accessed as follows

CALL nombrePA();

A simple example we have of a PA is like the following

DELIMITER //

CREATE PROCEDURE fetchAll()
        SELECT * FROM users;
//

I invoke or call the PA through CALL

CALL fetchAll();

EXPLANATION

A delimiter is created with the double diagonal // because the semicolon symbol contains the SQL statement inside the PA

However, we know that not all SQL statements are static (that is, they do not require dynamic values)

This is where the values of type come into use:

IN : These are the values that the PA expects to receive to grant them the query that it has inside, process it and return a result

OUT : This value exists in the AP but can be modified within this same

mysql sql mariadb

asked by element 10.11.2018 в 01:49

source

1 answer

Can a Java class be forced to implement a specific property? How to show a row of a table first?

score 1 · Accepted Answer

To be able to use dynamic values within a PA we must bear in mind it is also necessary to indicate the type of data to be processed;

EXAMPLE 1 WITH IN

The following code will work because the PA expects an input value of type VARCHAR and at the moment of invoking it we pass a value in text string format according to

CREATE PROCEDURE fetchAll(IN email VARCHAR(40));
CALL fetchAll("[email protected]");

EXAMPLE 2 WITH IN

For this example we will consult all the posts associated to a user by a specific id, where as we can notice in the WHERE we pass a dynamic value in the form of a variable called ìdDinamico :

DELIMITER //

CREATE PROCEDURE fetchAll1(IN idDinamico INT)
        SELECT nameUser, namePost
        FROM users
        JOIN posts ON users.id = posts.user_id
        WHERE users.id  = idDinamico;
//

CALL fetchAll1(1);

EXAMPLE 3 WITH OUT

In the following scenario, we will use a value OUT to be able to assign the total of posts assigned to a user in a variable called totalPosts making use of INTO

DELIMITER //

CREATE PROCEDURE fetchAll2(IN nameDinamico VARCHAR(40), OUT totalPosts INT)
        SELECT nameUser, COUNT(namePost) INTO nameDinamico, totalPosts
        FROM users
        JOIN posts ON users.id = posts.user_id
        WHERE users.nameUser  = nameDinamico;
//

CALL fetchAll2("gatito", @totalPosts);
SELECT @totalPosts;