Procedure with Cursor
1: DELIMITER $$
2: DROP PROCEDURE IF EXISTS `dbName`.`myProc1` $$
3: CREATE PROCEDURE `myProc1`()
4: BEGIN
5: DECLARE l_id INT;
6: DECLARE l_rate INT;
7: DECLARE no_more_rate INT;
8: DECLARE bal_rate_csr CURSOR FOR
9: SELECT distinct rate
10: FROM tmp1;
11: DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rate=1;
12: DROP TABLE IF EXISTS tmp2;
13: create TABLE tmp2 as
14: select *,0 bqty from tmp1;
15: truncate table tmp2;
16: SET no_more_rate=0;
17: OPEN bal_rate_csr;
18: bal_rate_loop:WHILE(no_more_rate=0) DO
19: FETCH bal_rate_csr INTO l_rate;
20: IF no_more_rate=1 THEN
21: LEAVE bal_rate_loop;
22: END IF;
23: set @tt=0;
24: insert into tmp2(rdate,dsc,prd_id,rec_qty,cons_qty,bal_qty,rate,rowid,row_count,balqty,bqty)
25: select *,@tt:=@tt+bal_qty+rec_qty-cons_qty as balqty from tmp1
26: where rate=l_rate
27: order by row_count;
28: END WHILE bal_rate_loop;
29: CLOSE bal_rate_csr;
30: SET no_more_rate=0;
31: END $$
32: DELIMITER ;
Procedure with Cumulative Sum, Auto Increment Column in Select Line & Procedure Call with a procedure
1: DELIMITER $$
2: DROP PROCEDURE IF EXISTS `dbName`.`sp_product_ledger` $$
3: CREATE PROCEDURE `sp_product_ledger`(prdid int,FromDate DateTime, ToDate DateTime,brcode varchar(45) )
4: BEGIN
5: DROP TEMPORARY TABLE IF EXISTS tmp;
6: create temporary table tmp as
7: /* Opening Qty,Rate,Value */
8: select DATE_SUB(FromDate, INTERVAL 1 DAY) rdate,'Carried Over Balance' dsc,r.prd_id,0 rec_qty,0 cons_qty,(r.qty-IFNULL(c.qty,0)) bal_qty,r.rate,0 rowid
9: from
10: (SELECT d.prd_id,sum(d.qty) qty,d.rate FROM challan c, challan_details d
11: where c.br_code=brcode and d.prd_id=prdid and c.id=d.chn_id and c.chn_date<FromDate
12: group by d.prd_id,d.rate) r
13: left outer join
14: (SELECT d.prd_id,sum(d.qty) qty,d.rate FROM comsumed c,consumed_details d
15: where d.prd_id=prdid and c.id=d.cons_id and c.cons_date<FromDate
16: group by d.prd_id,d.rate) c
17: on r.prd_id=c.prd_id and r.rate=c.rate;
18: /* Report Month Receive Qty,Rate,Value Start*/
19: insert into tmp(rdate,dsc,prd_id,rec_qty,cons_qty,bal_qty,rate,rowid)
20: SELECT c.chn_date rdate,'Receive' dsc,d.prd_id prd_id,d.qty rec_qty,0 cons_qty,0 bal_qty,d.rate, '1' rowid
21: FROM challan c, challan_details d
22: where c.br_code=brcode and d.prd_id=prdid and c.id=d.chn_id and
23: c.chn_date>=FromDate and c.chn_date<=ToDate;
24: /* Report Month Receive Qty,Rate,Value End*/
25: /* Report Month Consumed Qty,Rate,Value Start*/
26: insert into tmp(rdate,dsc,prd_id,rec_qty,cons_qty,bal_qty,rate,rowid)
27: SELECT c.cons_date rdate,'Issue' dsc,d.prd_id prd_id,0 rec_qty,d.qty cons_qty,0 bal_qty,d.rate, 2 rowid
28: FROM comsumed c, consumed_details d
29: where c.br_code=brcode and d.prd_id=prdid and c.id=d.cons_id and
30: c.cons_date>=FromDate and c.cons_date<=ToDate;
31: /* Report Month Receive Qty,Rate,Value End*/
32: /*Report Select Query Start */
33: /* select prd_id,p.prd_name,qty opn_qty,(qty*rate) opn_amt,rec_qty,cons_qty,rate,(qty+rec_qty-cons_qty) cls_qty,((qty+rec_qty-cons_qty)*rate) cls_amt
34: from tmp t,product p
35: where t.prd_id=p.id;
36: */
37: /*DECLARE cur11 CURSOR FOR SELECT rec_qty,cons_qty,bal_qty,rate from tmp ;*/
38: set @a=0;
39: set @tt=0;
40: set @i =0;
41: DROP TABLE IF EXISTS tmp1;
42: create TABLE tmp1 as
43: select *,@a:=@a+1 as row_count,@tt:=@tt+bal_qty+rec_qty-cons_qty as balqty from tmp
44: order by rdate,rowid;
45: /* set @tg=0;
46: select @tg:=count(*) ff from tmp1;
47: WHILE @i < @tg DO
48: set @i =@i+1;
49: set @tot=0;
50: set @ltot=0;
51: select @ltot:=sum(bal_qty) from tmp1 where rowid=0 and rate=(select rate from tmp1 where row_count=@i);
52: select @tot:=sum(@ltot+sum(rec_qty-cons_qty)) from tmp1 where row_count<=@i
53: and rate = (select rate from tmp1 where row_count=@i);
54: update tmp1
55: set bal_qty = @tot
56: where row_count=@i;
57: END WHILE;
58: */
59: CALL myProc1();
60: select rdate,dsc,rec_qty,cons_qty,bqty bal_qty,cons_qty*rate debit,rec_qty*rate credit,bqty*rate bal_amt,rate from tmp2
61: order by row_count;
62: /*Report Select Query EStart */
63: drop temporary table if exists tmp;
64: /*drop table if exists tmp1;
65: drop table if exists tmp2;*/
66: END $$
67: DELIMITER ;