MySQL 存储过程 WHERE

Question

我正在尝试设置插入行的过程并使用参数中的值来更改其他表中的值。但是……错误，而我和 Google = 没有解决方案……

INSERT INTO transakce (`id_transakce`, `akce_uzivatele_id`,            `typ_transakce`, `castka`, `datum_sazky`) 
VALUES (NULL, _akceuzivateleid ,_typ_transakce,_castka, _datum);

SET kreditUzivatele := SELECT ` kredit`  FROM `uzivatele` WHERE  uzivatele.id_uzivatele = _akceuzivateleid ;

SET kreditUzivatele := kreditUzivatele + _castka;
UPDATE `uzivatele` SET `kredit` := kreditUzivatele WHERE uzivatele.id_uzivatele =  _akceuzivateleid ;

One or more errors have occurred while processing your request: The following query has failed: "CREATE DEFINER=marek@% PROCEDURE creditCharge(IN _akceuzivateleid INT(9), IN _typ_transakce VARCHAR(20) CHARSET utf8, IN _castka INT, IN _datum TIMESTAMP) NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER INSERT INTO transakce (id_transakce, akce_uzivatele_id, typ_transakce, castka, datum_sazky) VALUES (NULL, _akceuzivateleid ,_typ_transakce,_castka, _datum); SET kreditUzivatele := SELECT kredit FROM uzivatele WHERE uzivatele.id_uzivatele = _akceuzivateleid ; SET kreditUzivatele := kreditUzivatele + _castka; UPDATE uzivatele SET kredit := kreditUzivatele WHERE uzivatele.id_uzivatele = _akceuzivateleid ; " MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET kreditUzivatele := SELECT kredit FROM uzivatele WHERE uzivatele.id_uzi' at line 4

屏幕: Before After

Answer 1

您可以尝试更改以下语句:

SET kreditUzivatele := SELECT ` kredit`  FROM `uzivatele` WHERE  uzivatele.id_uzivatele = _akceuzivateleid ;

对此:

DECLARE kreditUzivatele;
SELECT ` kredit` into kreditUzivatele FROM `uzivatele` WHERE  uzivatele.id_uzivatele = _akceuzivateleid ;

或者这个:

DECLARE kreditUzivatele;
SET kreditUzivatele = (SELECT ` kredit`  FROM `uzivatele` WHERE  uzivatele.id_uzivatele = _akceuzivateleid);