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

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 dicreasing until all kids money := 0. How can i express it with while loop?

CodePudding user response：

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

KID_NAME	MONEY
Alice	.06
Beryl	.03
Carol	.08

Which is not enough to buy any more candy.

db<>fiddle here