Question

MYSQL stored procedures - Creation, Queries and data insertions

Answer 1

A stored procedure (Store Procedure), is a small algorithm in SQL language that is stored next to the database and allows to perform tasks on this data.

The Advantages of stored procedures are:

- They can be accessed from different external programs, if the need to publish the structure of the database.

- They can be reused , therefore we will gain time by being programmed and tested.

Use stored mysql procedures, with phpmyadmin

We will begin the work of creating and consulting stored procedures with the phpmyadmin tool, but anyone who supports SQL queries from MYSQL 5.0 can be used.
In this case we will take the database of a car or vehicle agency.

1) We enter phpmyadmin and from there to the database

There are 2 type engines that handle data in Mysql

• MyISAM: default engine, very fast for queries, does not provide data integrity, nor referential protection. Ideal systems with many queries

• InnoDB: provides referential protection and data integrity in addition to blocking records, ideal if you are going to insert, edit or delete a lot of information constantly. Generally for stored procedures it is better to use InnoDB.

In the SQL tab we create our first stored procedure to check the types of vehicles. Write to the text field of sql.

CREATE PROCEDURE consult_type_home () ---> name of the procedure
SELECT * FROM vehicle_type ---> SQL to be resolved

If we execute the SQL query, we will receive a success message when the procedure is created.

To see the procedures created from the SQL tab, consult the SHOW PROCEDURE STATUS command, which will not show all the stored procedures.

Now we will show how to execute the stored procedure with the CALL command procedure_name (each programming language has its own library to access a stored procedure but they are all similar.

Here we can see the result of executing the stored procedure CALL pa_tipo_vehiculo, returned the result and nobody sees what commands have been executed.

In the following example we will list vehicles but by brand, the procedure would be:
CREATE PROCEDURE pa_vehiculos_por_marca (marca varchar (50))
SELECT * FROM
vehicles, brands
WHERE vehiculos.marca = marcas.id
AND brands.brand = brand

To the name of the procedure we add a variable to be able to look for example vehicles Honda brand
To execute the example we call the stored procedure in a SQL tab
CALL pa_vehiculos_por_marca ("Honda")
CALL pa_vehiculos_por_marca ("Ford")

You can also use the stored procedures for insertion tasks, example a procedure to record data from a client

CREATE PROCESS pa_cliente_insertar (
vname VARCHAR (64),
Vungal noun (64)
)

INSERT INTO client (name, surname) VALUES (vname, surname );

To use it we call it in the following way
CALL pa_cliente_insertar ( 'José', 'Gonzales');

Another could be Consult quantity of provinces
CREATE PROCEDURE `pa_provinces_quantity` ()
SELECT COUNT (*) as provincias FROM provinces

To remove any procedure, use DROP PROCEDURE procedure_name
Calls to procedures from different languages.
In PHP , assuming that the data comes from a form

$ mysqli = new mysqli ("localhost", "root", "root");
$ mysqli-> select_db ("auto-agency");
$ mysqli-> query ("CALL pa_cliente_insertar ('$ nombre', '$ surname')");

Now we execute the procedure stored in Java (the code has been cut )

conn = ConnectionMySQL.connect ("127,0,0,1", "root", "*******", "root");
CallableStatement Procedure = conn.prepareCall ("{CALL pa_client_insertar ('$ name', '$ surname'))}");
Procedure.setString ("vname", $ name);
Procedure.setString ("surname", $ surname);
Procedure.execute ();
connM.commit ();

In this way it is demonstrated that the same procedures have been used in different environments and languages, in a transparent manner for the user. The procedure topic is extensive but anyone with knowledge of SQL will be able to investigate and achieve great achievements with the use of stored procedures, thus being able to optimize their projects in time and data security.

---