在MySQL中使用存储过程时遇到两个问题 [英] Getting two issues while using stored procedure in MySQL

问题描述

下面是我正在进行利息计算的存储过程的示例代码.该代码不可执行，因为根据其在游标声明之前定义创建临时表块时发现的问题，但是如果我最近在游标声明之后定义相同的内容，则表示该代码执行成功.

Below is the sample code of my Stored Procedure in which I am working on for interest calculation. This code is not executable because according to finding its getting issue while defining creating temporary table block before the cursor declaration but if I define same thing recently after cursor declaration then it's executing successfully.

1-我的问题是我正在游标内使用该表，因此我必须在游标之后定义，否则我错过了任何事情?

1- My question is I am using that table inside cursor so I must have to define after cursor or I have missed anything ??

CREATE PROCEDURE `sp_interest_calculation_test`(
    IN sub_type CHAR(1)
)
BEGIN
    DECLARE s_ledger_id INT;
    DECLARE s_start, s_end, s_tran INT DEFAULT 0;

    **DROP TABLE IF EXISTS tmp_interest;
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_interest(
        id int(11) NOT NULL AUTO_INCREMENT,
        ledger_id INT UNSIGNED,
        dr_amount INT,
        cr_amount INT,
        balance INT
    );**

    DECLARE cur_saving_acc CURSOR FOR 
    SELECT SQL_CALC_FOUND_ROWS 1;

    OPEN cur_saving_acc;

    SET s_end = (SELECT FOUND_ROWS());

    WHILE s_start<s_end DO
    FETCH cur_saving_acc INTO s_ledger_id;

     INSERT INTO tmp_interest(ledger_id)
     SELECT s_ledger_id;

     SELECT * FROM tmp_interest;

     /*Interest calculation logic ends here */
     SET s_start = s_start+1;

     END WHILE;

    CLOSE cur_saving_acc;

END

2-成功执行上述存储过程(在游标声明后定义临时表)之后，调用SP时出现以下问题:

2- After successful execution of above stored procedure (defining temporary table after cursor declaration), I got the following issue when I call the SP:

CALL sp_interest_calculation_test ('A');

错误代码:1075 表定义不正确；只能有一个自动列，并且必须将其定义为键.

"Error Code : 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key"

这是因为我错过了将id定义为主键的原因，所以我只是用

It is because I missed to define id as primary key so I simply changed column with

id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY

我第一次在MySQL中使用存储过程，因此我很忙于解决上述问题，因此，请您描述一下上述问题及其原因，无论如何它都定义了自动编号列而不将其定义为主键?

I am using stored procedure in MySQL first time and it was hectic for me to fix the above issue so can you please describe me the above issues and their reasons and it there anyway to define auto number column without defining that as primary key ?

注意:尽管我正在处理块内的大量利息计算，但是在任何地方都不会使用参数sub_type，并且示例代码中的游标内没有执行更多的操作.

Note: Parameter sub_type is not used anywhere and there isn't more operations performed within cursor in the sample code though I am dealing with heavy interest calculation inside the block.

非常感谢您，希望大家对这个问题感兴趣. (我没有找到适合这些问题的确切信息).我也在寻找替代方案，只需简单地为每个分类账计算借方，贷方，余额，我就在单个查询中通过无循环进行了尝试，但执行从未结束.

Thank you very much in advance and hope all you will pay some interest in this question. (I didn't find anything exact for these issues). I am also looking for alternatives of it, simply I have to calculate debit,credit,balance for each ledger, I tried it by without loop in single query but execution never ends.

推荐答案

DECLARE仅在BEGIN ... END复合语句中允许使用，并且必须在其开头，在任何其他语句之前.

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

声明必须遵循一定的顺序.游标声明必须出现在处理程序声明之前.变量和条件声明必须出现在游标或处理程序声明之前.

Declarations must follow a certain order. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations.

http://dev.mysql.com/doc/refman/5.7/en/declare.html

那是限制.

现在，解决方法:添加嵌套的BEGIN ... END块.

Now, the workaround: add a nested BEGIN ... END block.

DELIMITER $$
CREATE PROCEDURE ...
BEGIN
  DECLARE ... INT ... -- variable
  CREATE TEMPORARY TABLE... -- following the declarations, no more declarations allowed, unless...
  BEGIN -- resets the scope, changes the rules, allows more declarations
    DECLARE ... INT ... -- variables
    DECLARE ... CURSOR ...
    DECLARE CONTINUE HANDLER ...
    OPEN ...
    ...
  END;
END $$

除非内部块中的另一个变量名称冲突，否则外部块中的所有变量仍在内部块的作用域内.

All the variables in the outer block are still in scope in the inner block, unless another variable in the inner block has a conflicting name.

外部块中的HANDLER也在内部块中的信号范围内，除非在那里声明了冲突的处理程序，在这种情况下，内部处理程序将捕获异常，而外部处理程序将捕获由内部处理程序抛出的任何内容.内部处理程序，包括RESIGNAL.

A HANDLER in the outer block is also in scope for signals in the inner block, unless a conflicting handler is declared there, in which case the inner handler will catch the exception and the outer handle will catch anything throw by the inner handler, including a RESIGNAL.

允许多个嵌套级别. thread_stack 的大小可能是一个因素，但是文档尚不清楚.自从将它设为默认值之前，我一直在运行262,144个字节的线程堆栈，并且从未遇到过限制.

Multiple nesting levels are allowed. The size of the thread_stack might be a factor, but the documentation is unclear. I've been running 262,144 byte thread stacks since before it was made the default, and have never encountered a limit.

这篇关于在MySQL中使用存储过程时遇到两个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！