The stored procedures are very useful to encapsulate database operations and save time in development. When you need to maintain a database with many transactions and some confidentiality, you can have a team of developers who do code without knowing how the database is. For this we will use stored procedures Example We create a database for a real estate and a table for real estate (we use few fields to not generate extensive code and that may confuse) CREATE TABLE IF NOT EXISTS `real estate` ( `id` int (11) NOT NULL AUTO_INCREMENT, `typemovable` varchar (100) DEFAULT '0', `price` decimal (10,2) DEFAULT '0.00', `description` text, PRIMARY KEY (`id`) ) ENGINE = MyISAM AUTO_INCREMENT = 195 DEFAULT CHARSET = latin1;
I create a new home stored procedure to insert real estate into the database. When defining it, I assign it as a parameter the data it must accept, in this case, property, price and description, each with its mysql format, I also write the INSERT action
Then we create a html data entry form to enter the values ​​of the new properties.
[color = # 000000] Here we send the information to a file save.php that will call the stored procedure and it will record the data. [/ color]
[color = # 000000] To do this we must previously have created the code to connect to the database. [/ color]
[color = # 000000] Create a file config.php [/ color] <? php
/ / Chain of connection to database
$ link = mysql_connect ('localhost', 'user', 'password');
/ / connection to database
if (! $ link) {
die ('Not connected:'. mysql_error ());
}
// Select database
$ db = 'dbReal Estate';
if (! mysql_select_db ($ db)) {
die ('Error:'. mysql_error ());
}?>
In the image we can see how the stored procedure is called by the SQL CALL statement CALL new house (parametro1, parametro2, ...)
We create another procedure stored in the database to consult by type of property. CREATE procedure listarinmueble (type varchar (150))
[indent = 1]
SELECT * FROM real estate WHERE property LIKE type In addition to using it in php we can make the query from any software that supports sql and mysql in this case. We test the stored procedure listmobile by type in this case from the mysql Heidisql administration software CALL listarinmueble ('house') The result can be seen below as it has filtered by type of property.
We can also create a stored procedure to delete data where we will send as parameter the id [color = # 000000] CREATE procedure removeinfurniture (idinmueble INT) [/ color]
[color = # 000000] SELECT * FROM real estate WHERE id = idinmueble [/ color] Then we call the stored procedure as follows CALL removemovable (172) The interesting thing about this is that if we were database administrators of a very complex system, we could make available to the programmers, only the list of procedures and not the structure of the database, which provides greater confidentiality. Allowing them to interact with the data but not with the structure, even if they can only read but not modify. This would be a topic for large database systems with many people interacting where security and confidentiality is important. The best known databases that support stored procedure are mysql, firebird, ms sql server, oracle, db2 postgresql, sybase (first to create a database transaction language for sql called Transact-SQL).