DELIMITER $$
    CREATE PROCEDURE PRO_GET_PRICE_ORDER_BYDATE(IN C_DATE DATE)
    BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE a INT DEFAULT 0;
    DECLARE mycursor CURSOR FOR SELECT DISTINCT order_id FROM xs_tb_price_order;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

    1. OPEN mycursor;<br />    REPEAT <br />        FETCH mycursor INTO a;<br />        IF NOT done THEN<br />        <br />        <br />        <br />        UPDATE XS_TB_DATE SET CUR_DATE=    C_DATE;

    UPDATE XS_TB_ORDER_DETAIL SET ZHAI_AMT1= (1+DATEDIFF(C_DATE,
    THIS_DATE)RATE/100)ZHAI_LEFT1/AMOUNT(AMOUNT- IFNULL(



    (SELECT
    IFNULL(SUM(BAL_MONEY),
    0)
    FROM
    XS_TB_PRICE_ORDER op
    WHERE
    op.ORDER_ID=a AND
    op.PI_DATE<=C_DATE
    AND op.RATE_DATE>C_DATE ),
    0) - IFNULL((SELECT
    IFNULL(SUM(BAL_MONEY),
    0)
    FROM
    XS_TB_PRICE_ORDER op
    WHERE
    op.ORDER_ID=a AND
    op.PI_DATE>C_DATE
    AND op.remark IS NULL ),
    0) )
    WHERE order_id=a;

     <br />        END IF;<br />    UNTIL done END REPEAT;<br />    CLOSE mycursor;<br />    END$$