How to loop from the first to the last value of a table in PL/SQL-CodePudding

What can I write to get all the id numbers of a table from the first until the last one?

CREATE OR REPLACE FUNCTION PRICE(
      L_CUST_ID PRICELIST.CUSTOMERS_ID%TYPE,
      L_ITEMS_ID PRICELIST.ITEMS_ID%TYPE,
      P_PRICELIST_PRICE NUMBER)
)
  RETURNS NUMBER AS
BEGIN 
 FOR I IN 1..< last id of customers> LOOP
   FOR I IN 1..< last id of items> LOOP
     INSERT INTO PRICE
      (CUSTOMERS_ID, ITEMS_ID, P_PRICE)
      VALUES
      ( L_CUST_ID, L_ITEMS_ID,ROUND(dbms_random.value(0,15),4)); 
   END LOOP;
 END LOOP;
RETURN L_CUST_ID;
END PRICE;

CodePudding user response：

You really don't want to use nested loops; for small data sets, you won't notice any difference, but - if tables contain many rows, processing row-by-row is slow-by-slow and performance will suffer.

Therefore, why wouldn't you just cross join those tables? Here's an example.

Sample tables:

SQL> select * From customers;

CUSTOMERS_ID NAME
------------ ------
           1 Little
           2 Foot

SQL> select * From items;

  ITEMS_ID NAME
---------- -------
        10 Gloves
        11 Matches
        12 Bottle

Insert cross-joined data:

SQL> insert into price (customers_id, items_id, p_price)
  2  select c.customers_id, i.items_id, round(dbms_random.value(0, 15), 4)
  3  from customers c cross join items i;

6 rows created.

Result:

SQL> select * from price;

CUSTOMERS_ID   ITEMS_ID    P_PRICE
------------ ---------- ----------
           1         10    11,5653
           1         11     8,9442
           1         12     4,4143
           2         10     5,3387
           2         11     14,602
           2         12    14,2723

6 rows selected.

SQL>