Getting two issues while using stored procedure in MySQL

Question

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- 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- 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');

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

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

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 ?

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.

Answer 1

Some notes about what is possible with AUTO_INCREMENT setup:

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

Note ENGINE=MyISAM behaves differently, such as allowing the AI to be non-left most.

Stored Proc:

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 ;

Test:

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

The above CURSOR setup is the proper way to do a FETCH loop with a LEAVE and a handler. Cursor loops are finicky. Don't directly mess with the done variable. You are trusting the FETCH and the handler with it. So let it deal with done all on its own.

MySQL Manual Page on CURSORS. Also note that Cursors perform like junk. They are fun to play with. They can get you out of a tricky situation. But you bring performance to its knees when you use them.

Answer 2

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

That's the restriction.

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.

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.

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.