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)
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,
DECLARE cur_saving_acc CURSOR FOR
SELECT SQL_CALC_FOUND_ROWS 1;
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 * FROM tmp_interest;
/*Interest calculation logic ends here */
SET s_start = s_start+1;
CALL sp_interest_calculation_test ('A');
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
DECLAREis permitted only inside a
BEGIN ... ENDcompound 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.
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.
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
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.