CODEWITHSUNDEEP
mysqlstored-proceduresdatabase-cursor
rahul shalgar
rahul shalgar

Reputation: 1358

how to read data from CURSOR - mysql stored procedure

I have implemented the CURSOR inside mysql stored procedure... I am getting challenge while reading the cursor... Basically I want to read column value from cursor & store the same into a variable.. but I am getting the variable value as NULL... What would be the solution... Please help me.. Thanks in advance...

My code is like below,

delimiter //

CREATE PROCEDURE placeOrder(IN cartId INT)
BEGIN

   DECLARE countitem INT DEFAULT 0;
   DECLARE productId INT;
   DECLARE quantity INT;
   DECLARE itemDicountAmt DOUBLE DEFAULT 0;
  DECLARE v_finished INT DEFAULT 0;


 DEClARE cart_cursor CURSOR FOR 
 SELECT ProductId, Quantity FROM TBL_SHOPPING_CART_ITEM;

 -- declare NOT FOUND handler
 DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET v_finished = 1;

 OPEN cart_cursor;

 get_cart: LOOP

 FETCH cart_cursor into productId, quantity;

 IF v_finished = 1 THEN 
 LEAVE get_cart;
 END IF;

 -- build email list
 insert into debugtable select concat('PRODUCT :', productId);
  insert into debugtable select concat('QUANTITY :', quantity);
 END LOOP get_cart;

 CLOSE cart_cursor;

END//

delimiter ;

OUTPUT:

tmptable NULL NULL

As I am having one record in my shopping cart table...

Upvotes: 0

Views: 1551

Answers (1)

wchiquito
wchiquito

Reputation: 16551

See C.1 Restrictions on Stored Programs :: Name Conflicts within Stored Routines.

Option 1:

...
/*DEClARE cart_cursor CURSOR FOR 
 SELECT ProductId, Quantity FROM TBL_SHOPPING_CART_ITEM;*/
DEClARE cart_cursor CURSOR FOR 
 SELECT
   TBL_SHOPPING_CART_ITEM.ProductId,
   TBL_SHOPPING_CART_ITEM.Quantity
 FROM TBL_SHOPPING_CART_ITEM;
...

Option 2:

...
/*DECLARE productId INT;
DECLARE quantity INT;*/
DECLARE _productId INT;
DECLARE _quantity INT;
...
/*FETCH cart_cursor into productId, quantity;*/
FETCH cart_cursor into _productId, _quantity;
...
/*insert into debugtable select concat('PRODUCT :', productId);
insert into debugtable select concat('QUANTITY :', quantity);*/
insert into debugtable select concat('PRODUCT :', _productId);
insert into debugtable select concat('QUANTITY :', _quantity);
...

Upvotes: 1

Related Questions