MySQL is "the most popular open database in the world", at least for MySQL Web sites. But no matter how popular MySQL is, many companies are resisting the use of MySQL. The phenomenon comes from a number of reasons, some of whom mistakenly believe that open source code is just the equivalent of a child's building block game, while others think any free stuff is not a good thing. While these ideas may be misleading, there is a sense of complaint that is not the same as other products, such as Oracle or DB2,MYSQL, which cannot support stored procedures (stored procedures).
The latest MySQL 5.0 can support stored procedures. If you're not familiar with stored procedures, you can assume they're just a collection of SQL commands and program logic stored on the database server side. These stored procedures can be invoked by the application, enabling developers of different levels of technology to build their own SQL.
Advantage stored procedures allow most of the database access logic to be separated from program logic. One of the indirect advantages of using stored procedures is that the code for the program becomes smaller and easier to understand. Another advantage of stored procedures is that SQL can be precompiled, thereby increasing the speed of the program. Because stored procedures contain program logic, more processing can be done on the database server side. Similarly, when a n-tier program is executed, the stored procedure is used to separate the data tier from the service layer.
Security is another advantage of stored procedures. When a table is not directly accessible, the program can assign execution precedence to the stored procedure. Unfortunately, at this time, MySQL does not support "Grant execution (Grant execute)." This means that unless the program has permission to access the table, invoking a stored procedure that accesses the same table is not possible. Using this feature is a bit like doing a very interesting gamble.
The standard is different from Oracle's or Microsoft's database, and MySQL and IBM's DB2 can follow the sql:2003 syntax of the stored program. In theory this means that if the database structure is the same, the stored program can be used in different databases.
Supported SQL declarations Although MySQL does not support stored programs, it can accomplish many tasks, as shown in table A. In addition, MySQL's stored procedure documentation (stored procedure documentation) describes many of the compatible features of T-SQL that can be used for Oracle Pl/sql and SQL Server. My impression of support for stored procedures is that it is slow to execute, in order to avoid any steps that affect large software development projects.
Table A
Statement |
Describe |
CREATE PROCEDURE |
Create a stored procedure for a table stored in the MySQL database. |
CREATE FUNCTION |
Create a user-defined function, especially a stored procedure that returns data. |
ALTER PROCEDURE |
Change the predefined stored procedures established with the Create PROCEDURE, which do not affect the associated stored procedure or storage functionality. |
ALTER FUNCTION |
Changes the predefined stored procedures that are established with the CREATE function and do not affect the associated stored procedure or storage functionality. |
DROP PROCEDURE |
Deletes one or more stored procedures from the MySQL table. |
DROP FUNCTION |
Remove one or more stored functions from the MySQL table. |
Show CREATE PROCEDURE |
Returns the text of a predefined stored procedure established using the Create PROCEDURE. This statement is a MySQL extension of the SQL:2003 specification. |
Show CREATE FUNCTION |
Returns the text of a predefined stored procedure established using the CREATE function. This statement is a MySQL extension of the SQL:2003 specification. |
Show PROCEDURE STATUS |
Returns the attributes of a predefined stored procedure, including name, type, creator, build date, and date of change. This statement is a MySQL extension of the SQL:2003 specification. |
Show FUNCTION STATUS |
Returns the attributes of a predefined storage function, including name, type, creator, date established, and date of change. This statement is a MySQL extension of the SQL:2003 specification. |
Call |
Invokes a predefined stored procedure that was established using the CREATE procedure. |
BEGIN ... End |
Contains a set of multiple declarations for execution. |
DECLARE |
Used to specify local variables, environments, processors, and pointers. |
SET |
Used to change the values of local and global server variables. |
SELECT ... Into |
The column used to store display variables. |
OPEN |
Used to open a pointer. |
FETCH |
Use a specific pointer to get the next column. |
Close |
Used to close and open the pointer. |
IF |
An an if-then-else-end if statement. |
Case ... When |
The 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 the while statement. |
Iterate |
Used to restart the loop. |
REPEAT |
The loop at the end of the test. |
While |
The loop to test at the beginning. |
RETURNS |
Returns the value of a stored procedure. |
|
MySQL 5.0 supports stored procedure statements.
Importantly, keep in mind that the current MySQL support for stored procedures is not oracle,sql server or DB2 mature. It's also important to remember that having a small number of features but doing it well is much better than having a lot of features but being a bit more error-ridden. I know this is a strange concept, but many people in the community are sometimes confused by other concepts.
