wannabit who do you wanna bit

MySQL and stored procedures

MySQL is "The World's Most Popular Open Source Database," at least according to the MySQL Web site. But in spite of this popularity many corporations are resistant to adopting MySQL. There are several reasons for this, from the misguided belief that open source is the software equivalent of a child’s wood shop project to the belief that nothing free is ever good. There was, however, one valid complaint against MySQL—unlike its shrink-wrapped counterparts, such as Oracle or DB2, MySQL doesn’t support stored procedures.

Make that past tense—the latest developer release, MySQL 5.0, does support stored procedures. If you’re not familiar with stored procedures, they are collections of SQL commands and program logic stored on the database server. These stored procedures can then be invoked by application programs thus eliminating the need for programmers with varying degrees of skill to create their own SQL.

Advantages
Stored procedures allow most database access logic to be separated from the application logic. One of the indirect benefits of using stored procedures is that application code becomes smaller and easier to understand. Another advantage of stored procedures is that the SQL can be “pre-compiled” increasing the speed of the application. Because stored procedures contain program logic, more processing can take place on the database server, which can reduce the amount of bandwidth consumed sending data back to the application. Also, when implementing an n-tier application, stored procedures are used to separate the data layer from the server layer.

Security can be another advantage of stored procedures. Applications can be granted execute privileges to the stored procedures, while being unable to access the tables directly. Unfortunately, at this time, MySQL doesn’t support “GRANT EXECUTE”. That means unless the application has the authority to access a table, then calling a stored procedure that accesses the same table won’t work either. It’s a good bet that this feature is pretty high up on the “to do” list for a future release.

Standards
Unlike either of the shrink-wrapped Oracle or Microsoft relational databases, which do not follow the current SQL:2003 syntax for stored procedures, MySQL and IBM’s DB2 do conform to the syntax. Theoretically this means that, if the database structure is the same, stored procedures written for one will run on the other.

Supported SQL statements
Even though the paint isn’t quite dry on MySQL’s support of stored procedures, there’s enough to get many tasks done, as Table A shows. In addition, the MySQL stored procedure documentation indicates that there may be future compatibility for Oracle’s PL/SQL and SQL Server’s T-SQL. My general impression of stored procedure support is that it is proceeding slowly in order to avoid any missteps that often plague large software development projects.
Table A

Stored procedure statements supported in MySQL 5.0

It is important to remember that support of stored procedures in the current incarnation of MySQL isn't as mature as Oracle, SQL Server or DB2. Also remember that it’s more important to have a small number of features that work well rather than a ton of features that are, for lack of a better word, flaky. I know that it’s an odd concept, but maybe the folks in the open source community have struck upon an idea that was somehow missed by the rest of the world.
from:http://articles.techrepublic.com.com/5100-10878_11-5178706.html