Store Procedure.

DELIMITER $$

USE `mlm`$$

DROP PROCEDURE IF EXISTS `pendingResolve`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pendingResolve`(IN userName VARCHAR(30))
BEGIN
    
    DECLARE amount DEC(10,2) DEFAULT 0.00;
    DECLARE id INT(10)   DEFAULT 10;
    DECLARE total  DEC(10,2) DEFAULT 0.00;
    DECLARE v_finished INTEGER DEFAULT 0;
    DECLARE pendingCursor CURSOR FOR SELECT payment,id FROM payment_history WHERE paymentFor=userName AND POSITION<6 AND STATUS=0 ;    
    
    
    — declare NOT FOUND handler
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET v_finished = 1;
    OPEN pendingCursor;
    getBalance: LOOP
        FETCH pendingCursor INTO amount,id;
        IF v_finished = 1 THEN
            LEAVE getBalance;
        END IF;
        — build email list
        SET total = total +amount;
        
                UPDATE payment_history
                SET    STATUS=1
                WHERE  id=id    ;            
        
    END LOOP getBalance;
    CLOSE pendingCursor;
    
    UPDATE user_information
    SET
       userBalance         = userBalance+total
       ,UserPendingBalance= UserPendingBalance-total;
    
    SELECT total;
    
    
    END$$

DELIMITER ;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s