Zolf
asked on
Stored Procedure help
Hi there,
I have this SP and want to understand the logic as to how it is working. i will appreciate if someone can explain it.
I have this SP and want to understand the logic as to how it is working. i will appreciate if someone can explain it.
CREATE PROCEDURE updeditQuantity(p_qty int, p_contractmfgid int, p_rawmaterialid int)
BEGIN
DECLARE c_id, c_qty INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR
SELECT id, quantity
FROM inventory_rawmaterial_contractmfg
WHERE contractmfgid =p_contractmfgid AND rawmaterialid=p_rawmaterialid
ORDER BY quantity;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO c_id, c_qty;
IF done or (p_qty = 0) THEN
LEAVE read_loop;
END IF;
IF (p_qty >= c_qty) then
set p_qty = p_qty - c_qty;
update inventory_rawmaterial_contractmfg set quantity=0,dateupdated=now() where id=c_id;
ELSE
update inventory_rawmaterial_contractmfg set quantity=quantity+p_qty,dateupdated=now() where id=c_id;
set p_qty = 0;
END IF;
END LOOP;
END
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your comments.
Can you please elaborate on your following points withvan eg.
Can you please elaborate on your following points withvan eg.
There is no guarantee that the ELSE branch is executed.
Whether the ELSE branch is executed depends on that there must be a row left in the cursor.
Simple sample: The filter returns five rows in the cursor, every row has the quantity 5, the parameter quantity is 30. Then the ELSE branch is not executed.
Simple sample: The filter returns five rows in the cursor, every row has the quantity 5, the parameter quantity is 30. Then the ELSE branch is not executed.
LOL @Ste5an,
I surely understand that, it was funny.
I surely understand that, it was funny.
@theGhost_k8: Sorry to tell you this, but these comments make the procedure worse. They only state the obvious and clutter the code, making it less readable. E.g. there is absolutely no information in the following comment:
ASKER
Take this scenario and can you do a run of the different conditions values I will get as result
for e.g.
CALL updeditQuantity(100012.0,16,168)
CALL updeditQuantity(100012.0,16,172)
CALL updeditQuantity(2780.3335,16,205)
CALL updeditQuantity(8341.0012,16,210)
CALL updeditQuantity(2780.3335,16,216)
CALL updeditQuantity(100012.0,16,159)
and here i have increased rawmaterialid id 172 to be more then value in table and 168 less then in the table
CALL updeditQuantity(1000,16,168)
CALL updeditQuantity(900000,16,172)
CALL updeditQuantity(2780.3335,16,205)
CALL updeditQuantity(8341.0012,16,210)
CALL updeditQuantity(2780.3335,16,216)
CALL updeditQuantity(100012.0,16,159)