MySql stored procedure learning knowledge summary_MySQL

What is a stored procedure:

A stored procedure can be said to be a record set. It is a code block composed of some T-SQL statements. These T-SQL statement codes implement some functions like a method (add, delete, modify and query a single table or multiple tables). ), and then give this code block a name, and just call it when this function is used.

Benefits of stored procedures:

1. When the database performs actions, it is compiled first and then executed. However, a stored procedure is a compiled code block, so its execution efficiency is higher than that of T-SQL statements.

2. A stored procedure can replace a large number of T-SQL statements when the program interacts on the network, so it can also reduce the communication volume of the network and increase the communication rate.

3. Through stored procedures, users without permissions can indirectly access the database under control, thereby ensuring data security.

Summary: In short, stored procedures are a good thing and a must-have tool when doing projects. The basic syntax of stored procedures is introduced below.

1. Stored procedure syntax

CREATE PROCEDURE proc_name([IN|OUT|INOUT] param 数据类型)
BEGIN
statement
END

In the mysql command line, each statement must be separated by; (semicolon). The semicolon is the execution point of mysql. In order to write the stored procedure, use the delimiter // command to convert the separator to //.

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 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 procedures

DROP proc_name

Note: Another stored procedure can be called within a stored procedure, but it cannot be deleted.

The above is the summary of MySql stored procedure learning knowledge introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support of the website!