MySQL and stored procedure related information

MySQL is "the most popular open database in the world", at least for MySQL Web sites. However, no matter how popular MySQL is, many companies are resistant to MySQL usage. This phenomenon comes from several reasons, some of which mistakenly think that open source code is just like a child's building block game, while others think that nothing free of charge is not a good thing. These ideas may be misunderstandings, but there is a reasonable complaint, that is, unlike other corresponding products, such as Oracle or DB2, MySQL cannot support stored procedures (stored procedures ).

The latest MySQL 5.0 supports stored procedures. If you are not familiar with stored procedures, you can think that they are just a collection of SQL commands and Program Logic stored on the database server. These stored procedures can be called by applications so that developers of different technical levels can establish their own SQL.
The advantage is that stored procedures allow the majority of database access logic to be separated from program logic. One indirect advantage of using stored procedures is that the code of the program becomes smaller and easier to understand. Another advantage of stored procedures is that SQL can be pre-compiled, which increases the speed of the program. Because stored procedures contain program logic, more processing can be performed on the database server. Similarly, when an n-tier program is executed, the stored procedure is used to separate the data layer from the service layer.
Security is another advantage of stored procedures. When tables cannot be accessed directly, the program can allocate execution priority to stored procedures. Unfortunately, MySQL does not support "grant execute" at this time )". This means that, unless the program has the permission to access the table, it will not be able to call a stored procedure to access the same table. Using this feature is a bit like doing a very interesting gambling.
The standard is different from the related Oracle or Microsoft databases. MySQL and IBM DB2 can follow the SQL: 2003 syntax of the storage program. Theoretically, this means that if the database structure is the same, the storage program can be used in different databases.
Supported SQL statements, although MySQL does not support storage programs, can complete many tasks, as shown in table. In addition, MySQL's stored procedure documentation (stored procedure Document) describes many compatibility features of the PL/SQL and SQL Server T-SQL that can be used for Oracle. My impression of stored procedure support is that it is slow to execute to avoid any steps that affect large-scale software development projects.
Table

Statement Description CREATE PROCEDURE Create a table stored in the MySQL database. CREATE FUNCTION Create a user-defined function, especially the stored procedure of the returned data. ALTER PROCEDURE Change the pre-specified stored PROCEDURE created with create procedure without affecting the stored PROCEDURE or function .. ALTER FUNCTION Change the pre-specified stored procedure created using the create function without affecting the stored procedure or FUNCTION .. DROP PROCEDURE Delete one or more stored procedures from a MySQL table. DROP FUNCTION Delete one or more storage functions from the MySQL table. SHOW CREATE PROCEDURE Returns the text of a pre-specified stored PROCEDURE created using create procedure. This statement is an SQL: 2003 Standard MySQL extension. SHOW CREATE FUNCTION Returns the pre-specified Stored Procedure text created using the create function. This statement is an SQL: 2003 Standard MySQL extension. SHOW PROCEDURE STATUS Returns the features of a pre-specified stored procedure, including the name, type, creator, creation date, and change date. This statement is an SQL: 2003 Standard MySQL extension. SHOW FUNCTION STATUS Returns the features of a pre-specified storage function, including name, type, creator, creation date, and change date. This statement is an SQL: 2003 Standard MySQL extension. CALL Call a pre-specified stored PROCEDURE created using create procedure. BEGIN... END Contains multiple declarations executed by a group. DECLARE It is used to specify local variables, environments, processors, and pointers. SET Used to change the value of local and global server variables. SELECT... Used to store the column of the DISPLAY variable. OPEN Used to open a pointer. FETCH Use a specific pointer to obtain the next column. CLOSE Used to close and open pointers. IF An if-then-else-end if statement. CASE... WHEN Structure of a case statement LOOP A simple loop structure; you can use the LEAVE statement to exit. LEAVE Used to exit the IF, CASE, LOOP, REPEAT, and WHILE statements. ITERATE Used to re-start the loop. REPEAT The test cycle at the end. WHILE Testing cycle at the beginning. RETURNS Returns the value of a stored procedure.

MySQL 5.0 supports stored procedure statements.

It is important to remember that the current MySQL support for stored procedures is not as mature as Oracle, SQL Server, or DB2. Remember that it is more important to have a few features but operate well, which is much better than having a large number of features but having hundreds of errors. I know this is a strange concept, but many people in the community are sometimes confused by other concepts.