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

Solution 1:

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 $$
CREATEPROCEDURE ...
BEGINDECLARE ... INT ... -- variableCREATE TEMPORARY TABLE... -- following the declarations, no more declarations allowed, unless...BEGIN-- resets the scope, changes the rules, allows more declarationsDECLARE ... INT ... -- variablesDECLARE ... 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.

Solution 2:

Some notes about what is possible with AUTO_INCREMENT setup:

createtable t1
(   ai intnotnull auto_increment,
    b intprimary key
)ENGINE=InnoDB;
-- Error 1075: AI must be a keycreatetable t2
(   ai intnotnull auto_increment,
    b intprimary key,
    key(ai)
)ENGINE=InnoDB;

-- This is successfulcreatetable t3
(   ai intnotnull auto_increment,
    b intprimary key,
    c intnotnull,
    key(ai,c)
)ENGINE=InnoDB;
-- This is successfulcreatetable t4
(   ai intnotnull auto_increment,
    b intprimary key,
    c intnotnull,
    key(c,ai)
)ENGINE=InnoDB;
-- Error 1075: AI must be a key (ai is not left-most in composite)createtable t5
(   ai int auto_increment primary key,
    b intnotnull,
    c intnotnull
)ENGINE=InnoDB;
-- Success: This is the PREDOMINANT way of doing itcreatetable t6
(   ai intnotnull AUTO_INCREMENT,
    b intnotnull,
    c intnotnull,
    PRIMARY KEY(c,ai)
)ENGINE=InnoDB;
-- Error 1075: AI must be a key (ai is not left-most in PK)createtable t7
(   ai intnotnull AUTO_INCREMENT,
    b intnotnull,
    c intnotnull,
    PRIMARY KEY(ai,c)
)ENGINE=InnoDB;
-- Successcreatetable t8
(   ai intnotnull AUTO_INCREMENT,
    b intnotnull,
    c intnotnull,
    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:

DROPPROCEDURE IF EXISTS `sp_interest_calculation_test`;
DELIMITER $$
CREATEPROCEDURE `sp_interest_calculation_test`(
    IN sub_type CHAR(1)
)
BEGINDECLARE done INTDEFAULTFALSE;
    DECLARE l_ledger_id INT;
    DECLARE dr_sum DECIMAL(14,2) DEFAULT0;
    DECLARE l_dr_amount, l_cr_amount, l_balance DECIMAL(14,2);
    DECLARE cur_saving_acc CURSORFORSELECT ledger_id, dr_amount, cr_amount, balance FROM tmp_interest orderby id;
    DECLARE CONTINUE HANDLER FORNOT FOUND SET done =TRUE;

    DROPTABLE IF EXISTS tmp_interest;
    CREATE TEMPORARY TABLE IF NOTEXISTS tmp_interest(
        id int(11) NOTNULL 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.