Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Accumulate from different sources Jump to:  
Category: >> Oracle PL/SQL >> Accumulate from different sources Bookmark and Share

<< lastnext >>

Snippet Name: Accumulate from different sources

Description: This code accumulates 100 units of part number 9999 from various storage bins

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 03rd, 2009

/*
** This code accumulates 100 units of part number 9999 from
** various storage bins.
*/
DECLARE
    CURSOR bin_cur(part_number NUMBER) IS SELECT amt_in_bin
        FROM bins
        WHERE part_num = part_number AND 
            amt_in_bin > 0
            ORDER BY bin_num
            FOR UPDATE OF amt_in_bin;
    bin_amt         bins.amt_in_bin%TYPE;
    total_so_far    NUMBER(5) := 0;
    amount_needed   CONSTANT NUMBER(5) := 100;
    bins_looked_at  NUMBER(3) := 0;
BEGIN
    OPEN bin_cur(9999);
    WHILE total_so_far < amount_needed LOOP
        FETCH bin_cur INTO bin_amt;
        EXIT WHEN bin_cur%NOTFOUND;
             /* If we exit, there's not enough to *
              * satisfy the order.                */
        bins_looked_at := bins_looked_at + 1;
        IF total_so_far + bin_amt < amount_needed THEN
            UPDATE bins SET amt_in_bin = 0
                WHERE CURRENT OF bin_cur;  
                    -- take everything in the bin
            total_so_far := total_so_far + bin_amt;
        ELSE        -- we finally have enough
            UPDATE bins SET amt_in_bin = amt_in_bin
                - (amount_needed - total_so_far)
                WHERE CURRENT OF bin_cur;
            total_so_far := amount_needed;
        END IF;
    END LOOP;
    CLOSE bin_cur;
    INSERT INTO temp VALUES (NULL, bins_looked_at, '<- bins looked at');
    COMMIT;
END;
/
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 332 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?