在MySQL中使用存储过程时遇到两个问题

Question

下面是我正在进行利息计算的存储过程的示例代码。这段代码是不可执行的，因为根据在游标声明之前定义创建临时表块时发现的问题，但如果我最近在游标声明之后定义了相同的东西，那么它就成功执行了。

1-我的问题是，我在光标内使用该表，所以我必须在光标后定义，否则我错过了什么？？

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时遇到了以下问题：

CALL sp_interest_calculation_test ('A');

"错误代码：1075表格定义不正确；只能有一个自动列，并且必须将其定义为密钥">

这是因为我错过了将id定义为主键，所以我只是用更改了列

id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY

我第一次在MySQL中使用存储过程，修复上述问题对我来说很忙，所以你能描述一下上述问题及其原因吗？无论如何，定义自动编号列而不将其定义为主键吗？

注意：参数sub_type没有在任何地方使用，并且在示例代码中没有在游标内执行更多操作，尽管我正在处理块内的重兴趣计算。

事先非常感谢，希望大家对这个问题感兴趣。(对于这些问题，我没有找到任何确切的答案(。我也在寻找它的替代方案，简单地说，我必须计算每个分类账的借方、贷方和余额，我尝试过在单个查询中不循环，但执行永远不会结束。

Answer 1

DECLARE只允许在BEGIN ... END复合语句内部，并且必须在其开头，在任何其他语句之前。

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

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

这就是限制。

现在，解决方法是：添加一个嵌套的BEGIN ... END块

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 $$

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

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

允许多个嵌套级别。thread_stack的大小可能是一个因素，但文档尚不清楚。自从262144字节线程堆栈成为默认线程堆栈之前，我就一直在运行它，从未遇到过限制。

Answer 2

关于AUTO_INCREMENT设置的一些注意事项：

create table t1
(   ai int not null auto_increment,
    b int primary key
)ENGINE=InnoDB;
-- Error 1075: AI must be a key
create table t2
(   ai int not null auto_increment,
    b int primary key,
    key(ai)
)ENGINE=InnoDB;
-- This is successful
create table t3
(   ai int not null auto_increment,
    b int primary key,
    c int not null,
    key(ai,c)
)ENGINE=InnoDB;
-- This is successful
create table t4
(   ai int not null auto_increment,
    b int primary key,
    c int not null,
    key(c,ai)
)ENGINE=InnoDB;
-- Error 1075: AI must be a key (ai is not left-most in composite)
create table t5
(   ai int auto_increment primary key,
    b int not null,
    c int not null
)ENGINE=InnoDB;
-- Success: This is the PREDOMINANT way of doing it
create table t6
(   ai int not null AUTO_INCREMENT,
    b int not null,
    c int not null,
    PRIMARY KEY(c,ai)
)ENGINE=InnoDB;
-- Error 1075: AI must be a key (ai is not left-most in PK)
create table t7
(   ai int not null AUTO_INCREMENT,
    b int not null,
    c int not null,
    PRIMARY KEY(ai,c)
)ENGINE=InnoDB;
-- Success
create table t8
(   ai int not null AUTO_INCREMENT,
    b int not null,
    c int not null,
    KEY(ai),
    KEY(c,ai)
)ENGINE=InnoDB;
insert t8(b,c) values(1,2);
insert t8(b,c) values(33,44);
select * from t8;
+----+----+----+
| ai | b  | c  |
+----+----+----+
|  1 |  1 |  2 |
|  2 | 33 | 44 |
+----+----+----+

注意ENGINE=MyISAM的行为不同，例如允许AI最不左。

存储过程：

DROP PROCEDURE IF EXISTS `sp_interest_calculation_test`;
DELIMITER $$
CREATE PROCEDURE `sp_interest_calculation_test`(
    IN sub_type CHAR(1)
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE l_ledger_id INT;
    DECLARE dr_sum DECIMAL(14,2) DEFAULT 0;
    DECLARE l_dr_amount, l_cr_amount, l_balance DECIMAL(14,2);
    DECLARE cur_saving_acc CURSOR 
       FOR SELECT ledger_id, dr_amount, cr_amount, balance FROM tmp_interest order by id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    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 DECIMAL(14,2),
        cr_amount DECIMAL(14,2),
        balance DECIMAL(14,2),
        KEY(id)
    );
    INSERT tmp_interest (ledger_id,dr_amount,cr_amount,balance) VALUES
    (101,100,0,200),(102,140,0,340),(103,0,50,290);
    OPEN cur_saving_acc;
    read_loop: LOOP
        FETCH cur_saving_acc INTO l_ledger_id, l_dr_amount, l_cr_amount, l_balance;
        IF done THEN
          LEAVE read_loop;
        END IF;
        SET dr_sum=dr_sum+l_dr_amount;
    END LOOP;
    CLOSE cur_saving_acc;
    SELECT CONCAT('sum of debits=',dr_sum) as outCol;
END;$$
DELIMITER ;

测试：

call sp_interest_calculation_test('s');
+----------------------+
| outCol               |
+----------------------+
| sum of debits=240.00 |
+----------------------+

上面的CURSOR设置是使用LEAVE和处理程序执行FETCH循环的正确方式。游标循环很挑剔。不要直接使用done变量。你信任FETCH和它的处理程序。所以让它自己处理done吧。

CURSORS上的MySQL手册页。还要注意，游标的性能就像垃圾一样。和它们一起玩很有趣。他们可以让你摆脱困境。但当你使用它们时，你会让它的性能下降。