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

问题描述

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

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屋！