使用MySQL Workbench的存储过程 [英] Stored Procedures Using MySQL Workbench

问题描述

对于环境而言，这是一个新问题，我对添加到代码末尾的一行有疑问.我要遵循的指南是:

Very new to the environment, I have a question about a line that's added to the end of my code. The guide I'm following is:

http://net.tutsplus.com/tutorials/an -存储过程介绍/

如果有人对MySQL存储过程有更好的了解，那么我就耳熟能详了.

If anyone has a better one regarding MySQL stored procedures, I'm all ears.

在我询问之前，这是我正在使用的环境: 操作系统:Windows 7/WAMP(MySQL 5.5.24)/MySQL Workbench

Before I ask, this is the environment I'm using: OS: Windows 7 / WAMP (MySQL 5.5.24) / MySQL Workbench

我被要求定义一个定界符；就我而言，我坚持使用默认的"$$".

I'm instructed to define a delimiter; in my case I'm sticking with the default '$$.'

我创建的存储过程是:

DELIMITER $$
CREATE PROCEDURE test.`p2` ()
LANGUAGE SQL
DETERMINISTIC
COMMENT 'Adds "nson" to first and last names in the record.'
BEGIN
SELECT 'Hello World';
END $$

应用此存储过程并进入查看屏幕时，我看到添加了一行新代码；

When I apply this stored procedure and I get the review screen, I see a new line of code added;

在底部:

DELIMITER ;

这条经纬线；之所以添加它，是因为DELIMITER语句宣布了一个可在其中使用定义的定界符($$)的块，从而最终关闭了该块?

This lats line; is it added because the DELIMITER statement announces a block within which the defined delimiters ($$) can be used and thus closes the block in the end?

推荐答案

在使用内置过程编辑器时，MySQL Workbench添加了一些额外的命令:

When using the builtin procedure editor, MySQL Workbench adds a few extra commands:

USE `test`; // <----------
DROP procedure IF EXISTS `p2`;  // <----------

DELIMITER $$
USE `test`$$ // <----------
CREATE PROCEDURE test.`p2` ()
LANGUAGE SQL
DETERMINISTIC
COMMENT 'Adds "nson" to first and last names in the record.'
BEGIN
SELECT 'Hello World';
END $$

DELIMITER ; // <----------

这些命令与存储过程的语法并不严格相关，它们只是一种商品，其他MySQL客户端(例如HeidiSQL或官方命令行实用程序)不会添加这些命令.最后的定界符更改可能是重置，以避免将来在同一连接上的语句出现问题.

Those commands are not strictly related to the stored procedures syntax, they're merely a commodity—other MySQL clients (such as HeidiSQL or the official command line utility) will not add them. The last delimiter change is probably a reset to avoid problems in future statements on the same connection.

您需要更改定界符，以指示客户端有关过程代码的开始和结束位置.问题在于过程主体通常是SQL语句的集合，因此省略定界符更改将使MySQL认为您正在尝试运行一系列语句，第一个语句是:

You need to change the delimiter in order to instruct the client about where the procedure code starts and end. The problem is that the procedure body is normally a collection of SQL statements so omitting the delimiter change would make MySQL think that you are attempting to run a series of statements, the first of which would be this:

CREATE PROCEDURE test.`p2` ()
LANGUAGE SQL
DETERMINISTIC
COMMENT 'Adds "nson" to first and last names in the record.'
BEGIN
SELECT 'Hello World';

使用DELIMITER $$告诉MySQL您的完整语句从CREATE变为END.它只是语法糖:DELIMITER甚至不是SQL关键字.例如，HeidiSQL为GUI提供了一个带有文本框的文本框，您可以在其中编写过程主体，因此不需要DELIMITER解决方法.

With DELIMITER $$ you are telling MySQL that your full statement goes from CREATE to END. It's just syntactic sugar: DELIMITER is not even a SQL keyword. HeidiSQL, for instance, provides a GUI with a text box where you write the procedure body, thus you don't need the DELIMITER workaround.

这篇关于使用MySQL Workbench的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！