MySql Stored Procedure Learning Knowledge Summary

What is a stored procedure:

A stored procedure is a record set. It is a block of T-SQL statements that perform functions like a method (add, delete, change, and search on a single table or multiple tables), and then give the block a name, which is called when this function is needed.

Benefits of stored procedures:

1. Because the database executes the action, it is compiled first and then executed. However, a stored procedure is a compiled block of code, so it executes more efficiently than ES11en-SQL statements.

2.1 Stored procedures can replace a lot of T-SQL statements when programs interact in the network, so it can also reduce the network traffic and improve the communication rate.

3. The stored procedure can make the user without authority access the database indirectly under control, so as to ensure the security of data.

Summary: In short, stored procedure is a good thing, in the project is a necessary tool, the following introduces the basic syntax of stored procedure.

1. Stored procedure syntax

CREATE PROCEDURE proc_name([IN|OUT|INOUT] param  The data type )
BEGIN
statement
END

Under the mysql command line, every 1 statement must be used; (semicolon). The semicolon is the execution point of mysql, which is converted to // by the delimiter // command for the purpose of writing stored procedures.

delimiter //

2. Write a simple procedure

mysql-> CREATE PROCEDURE hello()
-> BEGIN
-> SELECT "Hello World!";
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

1. Call the stored procedure: call proc_name

mysql-> CALL hello()//
+----------------------+
| it is a hello world. |
+----------------------+
| it is a hello world. |
+----------------------+
1 row in set (0.00 sec)

2. Delete stored procedure

DROP proc_name

Note: One stored procedure can be called from another stored procedure, but cannot be deleted.