TD6T4jL9

1CREATE PROCEDURE `sp_interest_calculation_test`(
2    IN sub_type CHAR(1)
3)
4BEGIN
5    DECLARE s_ledger_id INT;
6    DECLARE s_start, s_end, s_tran INT DEFAULT 0;
7
8    **DROP TABLE IF EXISTS tmp_interest;
9    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_interest(
10        id int(11) NOT NULL AUTO_INCREMENT,
11        ledger_id INT UNSIGNED,
12        dr_amount INT,
13        cr_amount INT,
14        balance INT
15    );**
16
17    DECLARE cur_saving_acc CURSOR FOR 
18    SELECT SQL_CALC_FOUND_ROWS 1;
19
20    OPEN cur_saving_acc;
21
22    SET s_end = (SELECT FOUND_ROWS());
23
24    WHILE s_start<s_end DO
25    FETCH cur_saving_acc INTO s_ledger_id;
26
27     INSERT INTO tmp_interest(ledger_id)
28     SELECT s_ledger_id;
29
30     SELECT * FROM tmp_interest;
31
32     /*Interest calculation logic ends here */
33     SET s_start = s_start+1;
34
35     END WHILE;
36
37    CLOSE cur_saving_acc;
38
39END
40	
41CALL sp_interest_calculation_test ('A');
42	
43id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY