Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    8

    Red face Unanswered:

    <deleted>
    Last edited by appoooh; 10-29-03 at 09:11.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Recursion in PL/SQL

    Originally posted by appoooh
    This is from an exercise in Oracle 8 programming - a primer. The idea of it is to get us to use recursion to copy all pizza ingredients and their sub-ingredients to a tempory table. For example, a 'pizza_mex' contains pizza_base, pizza_sauce, jallopino, capsicum and mushroom. pizza_base contains butter, flour and cheese while pizza_sauce contains tomato and garlic so after calling

    exec get_items('pizza_mex');

    temp_table should look like

    INGREDIENT
    --------------------
    butter
    flour
    cheese
    tomato
    garlic
    pizza_base
    pizza_sauce
    jallopino
    capsicum
    mushroom

    Now I've been able to write a procedure that copies the base ingredients into temp_table, but I can't work out how to modify it so that it calls itself and copies the sub ingredients into temp_table also. Could someone give me a hand?

    CREATE OR REPLACE PROCEDURE get_items
    (item IN pizza_items.name%TYPE)
    AS
    TYPE ingredient_cursor_type IS REF CURSOR;
    ingredient_cursor_var ingredient_cursor_type;
    TYPE ingredient_table_type IS TABLE OF temp_table.ingredient%TYPE
    INDEX BY BINARY_INTEGER;
    ingredient_table ingredient_table_type;
    v_item pizza_items.name%TYPE;
    x BINARY_INTEGER := 0;
    BEGIN
    OPEN ingredient_cursor_var
    FOR SELECT name
    FROM pizza_items
    WHERE used_in = item;
    FETCH ingredient_cursor_var into v_item;
    WHILE ingredient_cursor_var%FOUND LOOP
    x := x + 1;
    ingredient_table (x) := v_item;
    FETCH ingredient_cursor_var into v_item;
    END LOOP;
    FOR c IN 1 .. x LOOP
    INSERT INTO temp_table VALUES (ingredient_table (c));
    END LOOP;
    CLOSE ingredient_cursor_var;
    END get_items;
    /

    -- table declarations

    CREATE TABLE PIZZA_ITEMS(id NUMBER PRIMARY KEY,
    name VARCHAR2(20),
    used_in VARCHAR2(20));
    CREATE SEQUENCE pizza_sequence START WITH 1001;

    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'pizza_base', 'pizza_mex');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'pizza_base', 'pizza_anch');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'pizza_sauce', 'pizza_anch');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'pizza_sauce', 'pizza_mex');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'anchovy_topping', 'pizza_anch');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'tomato', 'pizza_sauce');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'anchovy', 'anchovy_topping');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'salt', 'anchovy_topping');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'jallopino', 'pizza_mex');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'capsicum', 'pizza_mex');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'mushroom', 'pizza_mex');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'garlic', 'pizza_sauce');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'butter', 'pizza_base');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'flour', 'pizza_base');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'cheese', 'pizza_base');

    CREATE TABLE temp_table(ingredient varchar2(20));
    Have a look at this recursive procedure I pulled out of a program of mine. It starts at the top of a hierarchy table like yours and prints out all the descendants as an indented list.

    Code:
      PROCEDURE funtree
        ( p_fun   ci_functions%ROWTYPE
        , p_level NUMBER DEFAULT 0
        )
      IS
      BEGIN
        -- Output this row
        p.tr
          ( LPAD( '.  ', p_level*3, '.  ' ) || p_fun.function_label
          , LPAD( '.  ', p_level*3, '.  ' ) || p_fun.short_definition
           );
        -- Process descendants
        FOR r IN 
          ( SELECT * FROM ci_functions
            WHERE parent_reference = p_fun.id
            ORDER BY function_label
          )
        LOOP
          funtree( r, p_level+1 );
        END LOOP;
      END funtree;

  3. #3
    Join Date
    Sep 2003
    Posts
    4

    Re: Recursion in PL/SQL

    Wooo isn't this rich, posting a homework question in a public forum. Hello, unidentified, fellow COMP224 student from Macquarie University. The due date and time has passed now, so I'll post my little solution.

    Code:
    CREATE OR REPLACE PROCEDURE get_items(item IN pizza_items.name%type) AS
       TYPE local_tracker IS REF CURSOR;
       pizza_tracker local_tracker;
       ingredient_tracker pizza_items%ROWTYPE; 
    
       TYPE  ingredients_table IS TABLE OF pizza_items.name%TYPE
       INDEX BY BINARY_INTEGER;
       short_list ingredients_table;
       short_list_dex BINARY_INTEGER;
    
    BEGIN
       --get the cursors 
       OPEN pizza_tracker FOR SELECT * FROM  pizza_items WHERE used_in = item;
       --fill out the table
       FETCH pizza_tracker INTO ingredient_tracker;
       short_list_dex := 1;
       LOOP
          EXIT WHEN pizza_tracker%NOTFOUND;     
          get_items(ingredient_tracker.name);
          short_list(short_list_dex) := ingredient_tracker.name;
          short_list_dex := short_list_dex + 1;          
          FETCH pizza_tracker INTO ingredient_tracker;   
       END LOOP;  
       short_list_dex := short_list.FIRST;
       --put the records into the table
       LOOP
          EXIT WHEN short_list.EXISTS(short_list_dex) = FALSE;
          INSERT INTO temp_table VALUES(short_list(short_list_dex));
          short_list_dex := short_list.NEXT(short_list_dex);      
       END LOOP;
       COMMIT;      
       CLOSE pizza_tracker;
    END;
    / .

    Originally posted by appoooh
    This is from an exercise in Oracle 8 programming - a primer. The idea of it is to get us to use recursion to copy all pizza ingredients and their sub-ingredients to a tempory table. For example, a 'pizza_mex' contains pizza_base, pizza_sauce, jallopino, capsicum and mushroom. pizza_base contains butter, flour and cheese while pizza_sauce contains tomato and garlic so after calling

    exec get_items('pizza_mex');

    temp_table should look like

    INGREDIENT
    --------------------
    butter
    flour
    cheese
    tomato
    garlic
    pizza_base
    pizza_sauce
    jallopino
    capsicum
    mushroom

    Now I've been able to write a procedure that copies the base ingredients into temp_table, but I can't work out how to modify it so that it calls itself and copies the sub ingredients into temp_table also. Could someone give me a hand?

    CREATE OR REPLACE PROCEDURE get_items
    (item IN pizza_items.name%TYPE)
    AS
    TYPE ingredient_cursor_type IS REF CURSOR;
    ingredient_cursor_var ingredient_cursor_type;
    TYPE ingredient_table_type IS TABLE OF temp_table.ingredient%TYPE
    INDEX BY BINARY_INTEGER;
    ingredient_table ingredient_table_type;
    v_item pizza_items.name%TYPE;
    x BINARY_INTEGER := 0;
    BEGIN
    OPEN ingredient_cursor_var
    FOR SELECT name
    FROM pizza_items
    WHERE used_in = item;
    FETCH ingredient_cursor_var into v_item;
    WHILE ingredient_cursor_var%FOUND LOOP
    x := x + 1;
    ingredient_table (x) := v_item;
    FETCH ingredient_cursor_var into v_item;
    END LOOP;
    FOR c IN 1 .. x LOOP
    INSERT INTO temp_table VALUES (ingredient_table (c));
    END LOOP;
    CLOSE ingredient_cursor_var;
    END get_items;
    /

    -- table declarations

    CREATE TABLE PIZZA_ITEMS(id NUMBER PRIMARY KEY,
    name VARCHAR2(20),
    used_in VARCHAR2(20));
    CREATE SEQUENCE pizza_sequence START WITH 1001;

    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'pizza_base', 'pizza_mex');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'pizza_base', 'pizza_anch');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'pizza_sauce', 'pizza_anch');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'pizza_sauce', 'pizza_mex');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'anchovy_topping', 'pizza_anch');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'tomato', 'pizza_sauce');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'anchovy', 'anchovy_topping');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'salt', 'anchovy_topping');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'jallopino', 'pizza_mex');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'capsicum', 'pizza_mex');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'mushroom', 'pizza_mex');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'garlic', 'pizza_sauce');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'butter', 'pizza_base');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'flour', 'pizza_base');
    INSERT INTO pizza_items VALUES (pizza_sequence.NEXTVAL, 'cheese', 'pizza_base');

    CREATE TABLE temp_table(ingredient varchar2(20));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •