Creating procedures in MySQL

As well as the functions in programming, procedures are used in the database to store features that may later be used, providing thus reuse of codes in the database. Queries, inserts, updates, and removal of records in tables are done through an editor, whose codes will be lost at the end of the session and, if necessary use them later, they will have to be rewritten.

In the procedures, the commands are stored in the same format as a method. Each procedures is stored in a specific location within the database and can be used as often as needed.

In MYSQL, procedures are created using the following syntax:

CREATE PROCEDURE ‘[name of the procedure]‘ ([Parameters])

BEGIN

[Commands]

END

You can work with procedures that process parameters, that return consultations, that work with transactions and a series of other situations. All that need to be done in a database is possible with the use of procedures.

To present some example, we will create a simple procedure that will return a query from a code informed by a user:

CREATE PROCEDURE ‘sp_list_product’ (IN _id INT)

BEGIN

SELECT *

FROM products

WHERE id_product = _id

END

Observe the procedures involving parameters: in such cases, one must indicate the type of variable that will be informed in the procedure call. This, in turn, must be compatible with the type of query or action that will be done. Moreover, it is necessary to indicate the “mode” of the parameter before the name, and they can be:

IN: Indicates that the parameter is only for receiving values and cannot be used for feedback;
OUT: Indicates an output parameter. In this case, it is not passed any value in the call procedure;
INOUT: This type of parameter can be used for both purposes (input and output).

Another important point is regarding the CREATE syntax. After the procedure is created, you must use the ALTER command so that subsequent amendments are applied. If the command is not modified, the database reports an error indicating that there is already a procedure created by that name.

As for naming, using “sp_” before the name of the procedure helps to standardize the database.

Finally, the procedure call in the database’s management screen will be as follows:

call sp_list_product(1)

If the parameter is not informed or the informed parameter is of the wrong type, an error message will appear.

PROCEDURES IN SCRIPTCASE

In Scriptcase it is possible to use customized procedures in forms, for example. You just need to create them directly in the database and then display it in the tool.

Check out more content on our blog!
Learn all about Scriptcase.