mySQL中的存储过程出错了

I want to make a stored procedure for my mySQL server.

我想为mySQL服务器创建一个存储过程。

Here is my procedure

这是我的程序

CREATE PROCEDURE ClientInsertProcedure (nomClient CHAR(40), userID INT(4), tel CHAR(10), codePostal CHAR(6), noCivique CHAR(6), r CHAR(30), v CHAR(30), cred DECIMAL(6, 2))
BEGIN
INSERT INTO Entreprise(nom_entreprise, telephone, code_postal, no_civique, rue, ville, credit) VALUES(nomClient, tel, codePostal, noCivique, r, v, cred) 
    ON DUPLICATE KEY UPDATE telephone = tel, code_postal = codePostal, no_civique = noCivique, rue = r, ville = v, credit = cred
        WHERE nom_entreprise = nomClient;
END;

It keep saying that i have an error on last line near END;

它一直说我在END附近的最后一行有错误;

I've tried with DELIMITER // and it has change nothing. I also went to mySQL website and i took the syntax there. I also looked Here. Is there any problem with the syntax itself or am i just doing it wrong?

我试过DELIMITER //它没有任何改变。我也去了mySQL网站,我在那里采用了语法。我也看了看这里。语法本身有什么问题,还是我做错了?

3 个解决方案

#1

Presumably you want an update on nom_entreprise = nomClient. If so, you need a unique index/constraint on that column:

大概你想要对nom_entreprise = nomClient进行更新。如果是这样,您需要在该列上使用唯一索引/约束:

create unique index idx_ClientInsertProcedure_nomClient
    on ClientInsertProcedure(nomClient);

Then, this should work:

然后,这应该工作:

CREATE PROCEDURE ClientInsertProcedure (nomClient CHAR(40), userID INT(4), tel CHAR(10), codePostal CHAR(6), noCivique CHAR(6), r CHAR(30), v CHAR(30), cred DECIMAL(6, 2))
BEGIN
    INSERT INTO Entreprise(nom_entreprise, telephone, code_postal, no_civique, rue, ville, credit)
        VALUES(nomClient, tel, codePostal, noCivique, r, v, cred) 
        ON DUPLICATE KEY UPDATE telephone = tel, code_postal = codePostal, no_civique = noCivique, rue = r, ville = v, credit = cred;
END;

As a general rule of advice, you should name parameters to stored procedures and functions with something like v_ so you can readily distinguish them from columns in tables.

作为一般建议规则,您应该使用类似v_的命令将参数命名为存储过程和函数,以便您可以轻松地将它们与表中的列区分开来。

#2

you can try move away code inside, only

你可以尝试在里面移走代码

CREATE PROCEDURE ClientInsertProcedure (nomClient CHAR(40), userID INT(4), tel CHAR(10), codePostal CHAR(6), noCivique CHAR(6), r CHAR(30), v CHAR(30), cred   
DECIMAL(6, 2))

BEGIN
END;

if no error, that mean your insert with problem

如果没有错误,那意味着您的插入有问题

#3

Here is how i got the code working :

这是我如何使代码工作:

I used DELIMITER

我用了DELIMITER

As pointed out by Gordon Linoff I wasn't needing a WHERE statement as the item was automatically selected

If so, you need a unique index/constraint on that column

如果是这样,您需要在该列上使用唯一索引/约束

The spacing after DELIMITER is VERY important

DELIMITER之后的间距非常重要

 DELIMITER //

CREATE PROCEDURE sp_ClientInsert (v_nomClient CHAR(40), v_userID INT(4), v_tel CHAR(10), v_codePostal CHAR(6), v_noCivique CHAR(6), v_rue CHAR(30), v_ville CHAR(30), v_cred DECIMAL(6, 2))
BEGIN
INSERT INTO Entreprise(nom_entreprise, telephone, code_postal, no_civique, rue, ville, credit) VALUES(v_nomClient, v_tel, v_codePostal, v_noCivique, v_rue, v_ville, v_cred) 
    ON DUPLICATE KEY UPDATE telephone = v_tel, code_postal = v_codePostal, no_civique = v_noCivique, rue = v_rue, ville = v_ville, credit = v_cred;
END //

DELIMETER ;

After some research the DELIMITER keyword make the code act as a single block. On the mySQL website, it specifies that the string "//" (without quotes) after DELIMITER is replace by ";" (without quotes)

经过一些研究后,DELIMITER关键字使代码充当单个块。在mySQL网站上,它指定DELIMITER后面的字符串“//”(不含引号)替换为“;” (不含引号)

The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

分隔符更改为//以使整个定义作为单个语句传递给服务器,然后还原到;在调用该过程之前。这使得;过程体中使用的分隔符将被传递给服务器,而不是由mysql本身解释。

This enable a block of code to be execute as a whole.

这使得一个代码块可以作为一个整体执行。

Thanks for the help.

谢谢您的帮助。

EDIT Here is a link that explain INSERT INTO... ON DUPLICATE KEY UPDATE provided by Yu Yenken

编辑这是一个链接,解释INSERT INTO ...由Yu Yenken提供的重复关键更新

#1