Information about MySQL and stored procedures

MySQL is "the most popular open database in the world," at least for the MySQL   Web site. But despite the popularity of MySQL, many companies are resisting the use of MySQL. There are several reasons for this, with some people mistakenly believing that open source is nothing more than a kid's block game, while others believe that anything free is never good. While these ideas may be misunderstood, there is a legitimate complaint that, unlike their counterparts, Oracle or   DB2, MySQL does not support stored procedures (stored   procedures).  

The latest MySQL   5.0 supports stored procedures. If you are not familiar with stored procedures, you can think of them as just a collection of SQL commands and program logic stored on the database server side. These stored procedures can be invoked by applications, enabling developers of all skill levels to build their own SQL.
The   stored procedure allows most of the database access logic to be separated from the program logic. One of the indirect benefits of using stored procedures is that the program code becomes smaller and easier to understand. Another advantage of stored procedures is that SQL can be precompiled, thus increasing the speed of the program. Because stored procedures contain program logic, more processing can be performed on the database server side. 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 the table is not directly accessible, the program can assign execution priority to the stored procedure. Unfortunately, at this time, MySQL does not support grant execution (GRANT   EXECUTE). This means that it is not possible to call a stored procedure that accesses the same table unless the program has permission to access the table. Using this feature is a bit of an interesting gamble.
Unlike Oracle or Microsoft related databases, MySQL and IBM's DB2 can follow the SQL: 2003 syntax for stored programs. In theory this means that if the database structure is the same, the stored program can be used in different databases.
Supported SQL claims that   although MySQL does not support stored programs, it does a lot of things, as shown in table A. In addition, stored   procedure   documentation(stored procedure documentation) describes many compatibility features available for PL/SQL for Oracle and   T-SQL for SQL   Server. My impression of stored procedure support is that it is slow to execute, to avoid any steps that affect large software development projects.
Table A

declares

describes

CREATE PROCEDURE

A stored procedure for creating a table in the MySQL database.

CREATE FUNCTION

Create a user-defined function, especially a stored procedure that returns data.

ALTER PROCEDURE

Changing a predefined stored procedure established with CREATE PROCEDURE does not affect the associated stored procedure or storage functionality. .

ALTER FUNCTION

Changing a pre-specified stored procedure established with CREATE FUNCTION does not affect the associated stored procedure or storage functionality. .

DROP PROCEDURE

Remove one or more stored procedures from the table in MySQL.

DROP FUNCTION

Remove one or more storage functions from the table in MySQL.

SHOW CREATE PROCEDURE

Returns the text of the predefined stored procedure created using CREATE PROCEDURE. This declaration is an MySQL extension to the SQL:2003 specification.

SHOW CREATE FUNCTION

Returns the text of a predefined stored procedure created using CREATE     FUNCTION. This declaration is an MySQL extension to the SQL:2003 specification.

SHOW PROCEDURE STATUS

Returns the properties of a predefined stored procedure, including name, type, creator, date of creation, and date of change. This declaration is an MySQL extension of the SQL:2003 specification.

SHOW FUNCTION STATUS

Returns the properties of a predefined stored function, including name, type, builder, date of creation, and date of change. This declaration is an MySQL extension of the SQL:2003 specification.

CALL

Invokes a predefined stored procedure created using CREATE PROCEDURE.

BEGIN ... END

Contains a set of executed multiple declarations.

DECLARE

Used to specify local variables, environments, handlers, and Pointers.

SET

Used to change the values of local and global server variables.

SELECT ... INTO

Used to store the columns that display variables.

OPEN

Used to open a pointer.

FETCH

Use a specific pointer to get the next column.

CLOSE

Used to close and open Pointers.

IF

An An if-then-else-end if declaration.

CASE ... WHEN

An case declaration of the structure

LOOP

A simple circular structure; You can exit using the LEAVE statement.

LEAVE

Used to exit IF, CASE, LOOP, REPEAT and WHILE statements.

ITERATE

Used to restart the loop.

REPEAT

The loop that is tested at the end.

WHILE

The loop that is tested 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. Also keep in mind that it is more important to have a few features that work well than to have a lot of features that make a lot of mistakes. I know it's a strange concept, but a lot of people in the community are sometimes confused by other concepts.