Stored Procedure
MySQL 5.0 finally introduces functionality for Stored Procedures. So what exactly are stored procedures?
A stored procedure is simply a procedure that is stored on the database server.
It is a some sql statments.
A stored procedure is a set of SQL statements stored on the server that takes in
certain arguments and processes that code with those arguments at execution time
A stored procedure is a set of SQL statements stored on the server that takes in certain arguments and
processes that code with those arguments at execution time.
It can be invoked simply by making a call to the procedure with the correct arguments.
The difference between stored procedures and other sets of SQL statements is that stored procedures
reside on the server and are pre-compiled.
Why we use that:
They wil lrun in all enviroments and there is no need to recreate the logics.It makes no
differnce on application environment, it remains consistent.
They can reduce network traffics. there is no need to send result sets and new
queries back and forth from application server to database server.
1. Better performance Stored procedures are faster because they are pre-compiled SQL code. This reduces the “Compile and Execute” step to just “Execute’ in most cases. Also, only the call to the stored procedure needs to be sent to the server instead of chunks of information – this reduces the information that needs to be sent to the server and acts like a call to a remote procedure. This is an advantage when it comes to code that is called repeatedly. However, the load on the server is another point to consider since most of the processing will now be done on the server.
2. Easier to maintain Since all the SQL can now be stored on the server, it is easier to make changes to the stored procedure than to a bunch of SQL statements distributed all over the application.
3. Security Although the use of stored procedures is not by itself a guarantee of security, it can be used to create an environment where applications and users can only access database tables through the stored procedures, instead of giving them direct access to the tables. The benefit is a layer of abstraction.
4. Optimization When a SQL statement is parsed by the server, it is optimized internally by the server. If a bunch of SQL statements are sent to the server, repeatedly, they have to be optimized each time. The SQL statements in the stored procedure that is in memory have to only be optimized once and an execution plan is created for the SQL statements in the stored procedure.
Example:
mysql> create stored procedure my_prodc() select “tets”
mysql> call my_proc()
Parameters:
there are 3 parameters
IN: The default. This parameter is passed to the procedure, and can change inside the procedure,
but remains unchanged outside.
OUT: No value is supplied to the procedure (it is assumed to be NULL), but it can be modified inside
the procedure, and is available outside the procedure.
INOUT: The characteristics of both IN and OUT parameters. A value can be passed to the procedure,
modified there as well as passed back again.
Mastery of stored procedures does require knowledge of session variables.
Most of you probably know how to use session variables already, but if not, the concept is simple.
