代码之家 › 专栏 › 技术社区 › aSystemOverload

在存储过程中时无法更改大小写内的表

stored-procedures mysql

aSystemOverload · 技术社区 · 6 年前

我试图在MySQL上执行一个相当简单的存储过程,但它总是抛出错误。

还有其他 WHEN SQL Error 1064 in Statement #2: You have an error in your SQL Syntax... near ELSE CALL finance.sprProce... .

这个 finance.spProcessTrans_AddToLogs 很好,我在许多其他SP中使用了确切的语法。在之间处理的代码 什么时候 s很好,因为我已经单独测试过了。

我哪里做错了?我应该能够在一个情况下做这个改变表吗?

DELIMITER //
DROP PROCEDURE IF EXISTS finance.spProcessIndex;

CREATE PROCEDURE finance.spProcessIndex(
    IN tblName VARCHAR(50),
    IN actDesc VARCHAR(50)
    )
    BEGIN

    SET tblName = IFNULL(tblName, 'ERROR');     
    SET actDesc = IFNULL(actDesc, 'ERROR');     

    CASE
    WHEN actDesc='CREATE' THEN
        CASE
        WHEN tblName='tbl_transactions' THEN
            ALTER TABLE tbl_transactions 
                MODIFY TransactionID INT AUTO_INCREMENT PRIMARY KEY,
                ADD INDEX IDX_ProcessTrans_A (CustomerRef, TransMonth, Product, TransValue, RowReference, TransactionID,ProdInCust_Mnth_Same_SameProd_LowerVal),
                ADD INDEX IDX_tbl_transactions_product (Product(25)),
                ADD INDEX IDX_tbl_transactions_prodval (Product, TransValue);
        ELSE
            ALTER TABLE tbl_transactions_tmp_worker_aa 
                MODIFY TransactionID INT AUTO_INCREMENT PRIMARY KEY,
                ADD INDEX IDX_ProcessTrans_A (CustomerRef, TransMonth, Product, TransValue, RowReference, TransactionID,ProdInCust_Mnth_Same_SameProd_LowerVal),
                ADD INDEX IDX_tbl_transactions_product (Product(25)),
                ADD INDEX IDX_tbl_transactions_prodval (Product, TransValue);
        END;
    ELSE
        CALL finance.spProcessTrans_AddToLogs('spProcessIndex','BREAKPOINT','ERROR: Bad ACTION',CONCAT('A bad ACTION was specified {',actDesc,'}'));
    END;

    END//
DELIMITER ;

1 回复 | 直到 6 年前

wchiquito 6 年前

尝试:

DELIMITER //

-- DROP PROCEDURE IF EXISTS finance.spProcessIndex;
DROP PROCEDURE IF EXISTS finance.spProcessIndex//
.
.
.
CASE
  WHEN actDesc='CREATE' THEN
.
.
.
  CASE
    WHEN tblName='tbl_transactions' THEN
  .
  .
  .
  END CASE;
END CASE;
.
.
.

DELIMITER ;