MySQL Stored procedures
Do you want to know “what’s new” in version 5 of MySQL? There are new features added to this version like stored procedures, triggers, views, information_schema. For those who use MySQL for databases administration this is a very good news.
For a better understanding of the stored procedures importance I will give a definition and an example. A stored procedure is a subroutine available to applications accessing a relational database system. A stored procedure has a name, a list of parameters and SQL statements. Bellow is a simple procedure whose name is test and has an integer parameter. This procedure insert in person table the value “major” or “unknown” depending “a” variable. In this type of procedures we can use instructions like “IF ELSE END IF” like in the example below.
CREATE PROCEDURE test (IN a INTEGER)
BEGIN
DECLARE age CHAR(10);
IF a = 18 THEN
SET age = 'major';
ELSE
SET age = 'unknown';
END IF;
INSERT INTO person VALUES (age);
END
Why Stored Procedures
Stored procedures are something new for MySQL but this concept isn’t new for other DBMSs. The syntax is almost the same and there are people with experience, there are articles, tutorials and forums about stored procedure.
The performance is one of the greatest advantages of stored procedures. Stored procedures are faster because they are pre-compiled SQL code. MySQL server has some caching advantages. If you have a repetitive task with some requirements my advice is to call a procedure stored on 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.
Another advantage of stored procedures is portability. If you write your stored procedure in SQL, you should know that it will run on every platform that MySQL runs on, without obliging you to install an additional runtime-environment package, or set permissions for program execution in the operating system. This is an SQL advantage rather than an external language like Java, C++, PHP.
Since the procedures are stored on the server there is an easy maintenance and we can make changes in one place rather than to a lot of SQL statements distributed all over the application.
We can create procedures who can permit the users to access some databases, rather than giving them full access. So this can be like security measurements.
Part II will come!
Post a comment
RSS/XML