New Features of MySQL5 (Stored Procedure)

Supporting stored procedures is an important new feature in MySQL 5. Although some users do not want to encapsulate the stored procedures that reflect the business logic in the database, most database administrators prefer to use the stored procedure function in the database, because stored procedures have many benefits: users can reuse code and change control-and

Supporting stored procedures is an important new feature in MySQL 5. Although some users do not want to encapsulate the stored procedures that reflect the business logic in the database, most database administrators prefer to use the stored procedure function in the database, because stored procedures have many benefits: users can reuse code and change control-and

Supporting stored procedures is an important new feature in MySQL 5. Although some users do not want to encapsulate the stored procedures that reflect the business logic in the database, most database administrators prefer to use the stored procedure function in the database, because stored procedures have many advantages:
Users can reuse code and change control
-Unlike writing a business logic flow into multiple applications, you can use many applications to call this process immediately by writing a stored procedure once, to implement specific business logic processes. Database administrators can also use standard management functions to process database resources of different versions, such as database structures and security permissions.
Quick Performance
-The administrator can use the cyclic structure to execute multiple SQL statements in the stored procedure. However, the previous application can only execute one SQL statement at a time, which significantly improves the efficiency, you can also write complex SQL statements into a stored procedure. users who are not familiar with SQL statements can directly call the stored procedure, this avoids errors that may occur when writing complex SQL statements.
Easier security management features
-For a complex database serving a large number of different users, it is quite time-consuming to assign the permission to use a large number of data objects to different users. After using the stored procedure, you can assign permissions to tasks at the process level. For example, if a user's SQL query statement needs to access 10 different tables, you need to allocate 10 different table permissions for the user. After using the stored procedure, you only need to assign the permission to the stored procedure containing the SQL query statement once.
Reduces network communication traffic
-If you write data to a single stored procedure and put it on the server after multiple network calls, you can call the stored procedure once to reduce excessive network communication traffic.
Like the DB2 database, the stored procedures in MySQL5 fully comply with the ansi SQL 2003 standard, which is very convenient for developers and database administrators to learn and use, and the return results of select query statements are also intuitive, no dedicated call package or reference cursor is required. This is similar to Microsoft SQLserver and sybase databases. The following is an example of the output:

mysql> delimiter //mysql> create procedure top_broker()    -> select a.broker_id,    ->        a.broker_first_name,    ->        a.broker_last_n    ->        sum(broker_commission) total_commissions    ->   from broker a,    ->        client_transaction b    ->   where a.broker_id=b.broker_id    -> group by a.broker_id,    ->        a.broker_first_name,    ->        a.broker_last_name    -> order by 4 desc;    -> //Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> call top_broker();

498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 222 alt = "" src = "http://www.68idc.cn/help/uploads/allimg/150526/1333244a3-0.gif" width = 600 border = 0>

To process standard query output, MySQL5 stored procedures support many common development structures, such:

Input/output parameters; variable definition; cycle with EXIST check; logical condition judgment (if, case, etc.); condition processing handle; stored procedure calls stored procedure; supports the "Commit/revoke" function and data definition statements for transaction processing database tables.

Database developers and administrators can use create, alter, drop, and grant to perform specific operations on the stored procedure in MySQL 5. In addition to the special stored procedure for obtaining metadata, they can also perform the stored procedure using the following methods:

Use the show procedure status function to query the mysql. proc built-in table. Use information_schema data dictionary, another new feature of mysql5.

(51CTO. COM tutorial)