带参数的MariaDB插入存储过程 [英] MariaDB Insert Stored Procedure With Parameter

问题描述

我将公司数据库从MySQL迁移到另一家托管公司，却不知道托管公司使用MariaDB，在尝试使用IN参数创建存储过程时，MariaDB将该参数视为columnn.请参阅下面的存储过程代码，并显示错误:

I migrated my company database from MySQL to another hosting firm not knowing the hosting firm uses MariaDB, upon try to create my stored procedure with my IN parameter, MariaDB is seeing the parameter as a columnn. See the stored procedure code below with the error :

    CREATE  PROCEDURE ADD_WITHDRAWAL_A(IN withdrawalcode_p VARCHAR(25), IN id_p VARCHAR(8), IN amount_p VARCHAR(12), IN datewithdrawn_p VARCHAR(35), IN approved_p VARCHAR(8))

     START TRANSACTION;

     INSERT INTO Withdrawals(WithdrawalCode, IDD, Amount, DateWithdrawn, Approved) 
     VALUES (withdrawalcode_p, id_p, amount_p, datewithdrawn_p, approved_p);

     UPDATE account SET AccountBalance = AccountBalance - amount_p WHERE IDD = id_p LIMIT 1;

     COMMIT;

    ***** AFTER RUNNING THE ABOVE CODE, MARIADB GAVE THIS ERROR :

    Error
    SQL query:

    INSERT INTO WithdrawalRequest( WithdrawalCode, IDD, Amount, DateWithdrawn, Approved ) 
    VALUES (withdrawalcode_p, id_p, amount_p, datewithdrawn_p, approved_p);

MySQL said: Documentation

#1054 - Unknown column 'withdrawalcode_p' in 'field list' 

列名是WithdrawalCode而不是'withdrawalcode_p'，'withdrawalcode_p'是传递给存储过程的参数，但是服务器将其视为字段名.我与托管公司进行了交谈，他们说他们的数据库是MariaDB，而不是MySQL.相同的代码在MySQL服务器中也有效.

The column name is WithdrawalCode and not 'withdrawalcode_p', 'withdrawalcode_p' is a parameter passed in to the stored procedure but the server is seeing it as a field name. I spoke with the hosting firm and they said their database is MariaDB and not MySQL. This same code worked in MySQL server.

在这里提供的任何有用的帮助，我将不胜感激.

I will appreciate any useful help rendered here.

推荐答案

您忘记设置非默认定界符并将过程主体包装到BEGIN/END中，这是必需的，因为它具有多个语句

You forgot to set non-default delimiters and to wrap the procedure body into BEGIN/END, which is necessary since it has more than one statement.

在您的情况下，发生的情况是使用主体START TRANSACTION​​创建的过程，其余过程被视为一组普通语句.相反，尝试

What happens in your case is that the procedure created with body START TRANSACTION, and the rest is considered to be a set of ordinary statements. Instead, try

DELIMITER $$

CREATE  PROCEDURE ADD_WITHDRAWAL_A(IN withdrawalcode_p VARCHAR(25), IN id_p VARCHAR(8), IN amount_p VARCHAR(12), IN datewithdrawn_p VARCHAR(35), IN approved_p VARCHAR(8))
BEGIN

     START TRANSACTION;

     INSERT INTO Withdrawals(WithdrawalCode, IDD, Amount, DateWithdrawn, Approved) 
     VALUES (withdrawalcode_p, id_p, amount_p, datewithdrawn_p, approved_p);

     UPDATE account SET AccountBalance = AccountBalance - amount_p WHERE IDD = id_p LIMIT 1;

     COMMIT;

END $$

DELIMITER ;

这篇关于带参数的MariaDB插入存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！