Oracle PL/SQL for simple use of nested tables

From: David <dfairman16_at_hotmail.com>
Date: 10 Jun 2002 04:48:08 -0700
Message-ID: <b4cefdce.0206100348.23e60edb@posting.google.com>

Okay gurus - first up the example I've described below is completely contrived. But in principle the model is accurate, that is someone else has created this nested table structure and unfortunately has only done half the job. Updating the table is performed by (hundreds) of individual SQL statements sent by client applications on many workstations. Here are the basics.

CREATE TABLE tblIngredients
  (
   ingredientID NUMBER(4) PRIMARY KEY,    ingredientName VARCHAR2(20)
  )
/

INSERT INTO tblIngredients VALUES (1001,'Apples');
INSERT INTO tblIngredients VALUES (1010,'Pears');
INSERT INTO tblIngredients VALUES (1011,'Flour');
INSERT INTO tblIngredients VALUES (1012,'Milk');
INSERT INTO tblIngredients VALUES (1013,'Beef');
INSERT INTO tblIngredients VALUES (2040,'Cream');
INSERT INTO tblIngredients VALUES (2041,'Carrot');
INSERT INTO tblIngredients VALUES (2042,'Potato');
INSERT INTO tblIngredients VALUES (3142,'Pastry');
INSERT INTO tblIngredients VALUES (3187,'Peas');
INSERT INTO tblIngredients VALUES (6019,'Sugar');

CREATE OR REPLACE TYPE typIngredients AS OBJECT   (
   ingredientname VARCHAR2(20)
  );
/

CREATE TYPE lstIngredients AS TABLE OF typIngredients; /

CREATE TABLE tblMenu
  (

    menuID       NUMBER(4),
    recipe       VARCHAR2(3000),

    inventory lstIngredients
  )
   NESTED TABLE inventory STORE AS tblInventoryStock;

--menu for fruit tart, menuID 1

INSERT INTO tblMenu (menuID,inventory)
 VALUES (1,lstIngredients(typIngredients('Apples'))); INSERT INTO tblMenu (menuID,inventory)
 VALUES (1,lstIngredients(typIngredients('Cream'))); INSERT INTO tblMenu (menuID,inventory)
 VALUES (1,lstIngredients(typIngredients('Pastry'))); UPDATE tblMenu
 SET recipe='Bake pastry for 25 mins at 120o, remove from oven ....'  WHERE menuID=1;

--menu for roast beef, menuID 5

INSERT INTO tblMenu (menuID,inventory)
 VALUES (5,lstIngredients(typIngredients('Beef')));

INSERT INTO tblMenu (menuID,inventory)
 VALUES (5,lstIngredients(typIngredients('Potato')));

UPDATE tblMenu
  SET recipe='Simmer beef at 70o for 2 hours ...'   WHERE menuID=5;

What I'd like is an Oracle PL/SQL procedure that does this all for me. Although I could modify such a procedure, writing it is beyond my ability. So if anyone is up to a little homework, or a brainteaser, then what is the PL/SQL that would yield my result. What I'd like is something close to a simple call to a procedure with a single set of parameters like (ingredients delimited by /):

--menu for roast beef, menuID 5

CALL UpdateMenuTable(5,'Simmer beef at 70o for 2 hours ...','/Beef/Potato/');

--menu for fruit tart, menuID 1

CALL UpdateMenuTable(1,'Bake pastry for 25 mins at..','/Apples/Cream/Pastry/');

Many thanks
David Received on Mon Jun 10 2002 - 06:48:08 CDT