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.

procedure_mysql

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.

You might also like…

PROCESAMIENTO AJAX EN SCRIPTCASE

Ajax en los formularios de Scriptcase puede usarse para recargar un campo de tipo de select basado e...

IMPORTAR IMAGENES DE LA BASE DE DATOS Y UTILIZAR CLASES DE BOOSTRAP

Como muchas personas ya saben, Bootstrap es una biblioteca de front-end libre y de código abierto p...

TENDENCIAS DE DESARROLLO WEB PARA 2017

Usted verá en este post tendencias para 2017 en Diseño Web, en el medio digital y en el desarrollo...

Comment this post

Get new posts, resources, offers and more each week.

We will use the information you provide to update you about our Newsletter and Special Offers. You can unsubscribe any time you want by clinck in a link in the footer of any email you receive from us, or by contacting us at sales@scriptcase.net. Learn more about our Privacy Police.