How to use a WHILE loop until all values in a column to be zero? pl/sql

Question

For example, let's say that we have kids table and candy table .

create table kids( kid_name varchar2(20), money number);
create table candy( candy_name varchar(20), price number);

And I want to create a procedure(sales) that will make a sale for a random kid_name buys a random candy_name. In every sale kids money are decreasing until all kids money := 0.

How can I express it with while loop?

[How to get records randomly from the oracle database?](https://stackoverflow.com/questions/9868409/how-to-get-records-randomly-from-the-oracle-database) — Abra, Nov 23 '21 at 12:28

score 1 · Accepted Answer · answered Nov 23 '21 at 13:08

Unless the kids have an amount of money that is an exact multiple of the price of the candy bars (or the candy store is willing to sell candy for a lower price when the kids don't have enough money) then you are unlikely to reach 0.

For example, if you have the tables:

CREATE TABLE kids (kid_name, money) AS
SELECT 'Alice', 1.23 FROM DUAL UNION ALL
SELECT 'Beryl', 2.34 FROM DUAL UNION ALL
SELECT 'Carol', 4.56 FROM DUAL;

CREATE TABLE candy (candy_name, price) AS
SELECT 'A', 0.12 FROM DUAL UNION ALL
SELECT 'B', 0.23 FROM DUAL UNION ALL
SELECT 'C', 0.34 FROM DUAL;

And the procedure:

CREATE PROCEDURE sell_random_candy
IS
BEGIN
  LOOP
    MERGE INTO kids dst
    USING (
      SELECT k.ROWID rid,
             c.price
      FROM   kids k
             INNER JOIN candy c
             ON (c.price <= k.money)
      ORDER BY DBMS_RANDOM.VALUE
      FETCH FIRST ROW ONLY
    ) src
    ON (src.rid = dst.ROWID)
    WHEN MATCHED THEN
      UPDATE
      SET money = dst.money - src.price;
    EXIT WHEN SQL%ROWCOUNT = 0;
  END LOOP;
END;
/

Then after running the procedure, the kids table may randomly contain:

KID_NAME MONEY

Alice .06

Beryl .03

Carol .08

Which is not enough to buy any more candy.

db<>fiddle here

How to use a WHILE loop until all values in a column to be zero? pl/sql

1 Answers1