Summary: in this tutorial, we will show you step by step how to develop the first MySQL stored procedure by using CREATE PROCEDURE statement. In addition, we will show you how to call the stored procedures from SQL statements.
Writing the first MySQL stored procedure
We are going to develop a simple stored procedure named GetAllProducts()
to help you get familiar with the syntax. The GetAllProducts()
stored procedure selects all products from theproducts
table.
开发一个简单的存储过程GetAllProducts()帮助熟悉存储过程的语法,该函数返回表products的所有的产品。
Launch the mysql
client tool and type the following commands:
1
2
3
4
5
6
|
DELIMITER
/
/
CREATE
PROCEDURE
GetAllProducts
(
)
BEGIN
SELECT
*
FROM
products
;
END
/
/
DELIMITER
;
|
Let’s examine the stored procedure in greater detail:
- The first command is
DELIMITER //
, which is not related to the stored procedure syntax. TheDELIMITER
statement changes the standard delimiter which is semicolon (;
) to another. In this case, the delimiter is changed from the semicolon(;
) to double-slashes//
. Why do we have to change the delimiter? Because we want to pass the stored procedure to the server as a whole rather than lettingmysql
tool to interpret each statement at a time. Following theEND
keyword, we use the delimiter//
to indicate the end of the stored procedure. The last command (DELIMITER;
) changes the delimiter back to the standard one. - 第一个命令DELIMITER//,与存储过程的语法无关。该声明改变标准的分隔符分号为其他符号。在这个例子中,分隔符从分号变为双斜杠。为什么改变分隔符?因为我们希望将存储过程作为一个整体传送给服务器,而不是让mysql工具翻译每个语句。在END命令后,我们使用//暗示存储过程的结束。最后DELIMITER;将分隔符变为标准分隔符。
- We use the
CREATE PROCEDURE
statement to create a new stored procedure. We specify the name of stored procedure after theCREATE PROCEDURE
statement. In this case, the name of the stored procedure isGetAllProducts
. We put the parentheses after the name of the stored procedure. - 使用CREATE PROCEDURE 声明创建一个新的存储过程。在create procedure 后指定存储过程名称。在这个例子中,存储过程的名称为GetAllProducts。在存储过程名称后添加括号。
- The section between
BEGIN
andEND
is called the body of the stored procedure. You put the declarative SQL statements in the body to handle business logic. In this stored procedure, we use a simple SELECT statement to query data from theproducts
table.
It’s kind of tedious to write the stored procedure in mysql
client tool, especially when the stored procedure is complex. Most of the GUI tools for MySQL allow you to create new stored procedures via an intuitive interface.
在mysql客户端工具中写存储过程是冗长乏味的,特别是复杂的存储过程。大多数mysql的图形用户界面工具可以直观的创建存储过程
For example, in MySQL Workbench, you can create a new stored procedure as follows:
We have created a new stored procedure. Now, it’s time to learn how to use it.
Calling stored procedures
In order to call a stored procedure, you use the following SQL command:
调用存储过程,可以使用 call 存储过程名称()
1
|
CALL
STORED_PROCEDURE_NAME
(
)
|
You use the CALL
statement to call a stored procedure e.g., to call the GetAllProducts
stored procedure, you use the following statement:
1
|
CALL
GetAllProducts
(
)
;
|
If you execute the statement above, you will get all products in the products
table.
In this tutorial, you have learned how to write a simple stored procedure by using the CREATE PROCEDURE
statement and call it by using the CALL
statement.