How to insert multiple items using an insert query in Oracle PL/SQL stored procedure?

Question

I've created a stored procedure which has two insert queries, one inserts data into the orders table and the other inserts data into the order_item table. I want the order_item insert query to insert mutiple items, I've managed to get it inserting one item but unsure how to insert multiple items into the order_item table and also how to execute the procedure.

CREATE OR REPLACE PROCEDURE new_order
-- define the variables for orders and order_item tables
(
    -- variables for the orders table
    p_order_id IN INT, 
    p_order_num IN CHAR,
    p_name IN CHAR, 
    p_email IN CHAR,
    p_address IN VARCHAR2,
    p_city IN VARCHAR2,
    p_province IN VARCHAR2,
    p_postcode IN VARCHAR2,
    p_telephone IN NUMBER,
    p_total IN NUMBER,
    p_order_date IN DATE,
    -- variables for the order_item table
    p_order_item_id IN INT,
    p_product_id IN INT,
    p_seller_id IN INT,
    p_sub_order_number IN CHAR,
    p_quantity IN INT,
    p_condition IN CHAR,
    p_unit_price IN NUMBER, 
    p_cost_charge IN NUMBER
)
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE ('Insert attempted');

    -- Insert data into the order table
    INSERT INTO orders(
        order_id, 
        order_number, 
        billing_name, 
        billing_email, 
        billing_address, 
        billing_city, 
        billing_province, 
        billing_postcode, 
        billing_telephone,
        billing_total,
        order_date)
    values(
        p_order_id, 
        p_order_num,
        p_name,
        p_email,
        p_address,
        p_city,
        p_province,
        p_postcode,
        p_telephone,
        p_total,
        p_order_date
        );

    -- Insert data into the order_item table
    -- Loop through the order items based on the order_id
    INSERT INTO order_item(
        order_item_id,
        order_id,
        product_id,
        seller_id,
        sub_order_number,
        quantity,
        condition,
        unit_price,
        cost_charge
        )
    values(
        p_order_item_id,
        p_order_id,
        p_product_id,
        p_seller_id,
        p_sub_order_number,
        p_quantity,
        p_condition,
        p_unit_price,
        p_cost_charge
        );

    COMMIT;

    DBMS_OUTPUT.PUT_LINE ('Insert succeeded');

    EXCEPTION
     WHEN others THEN   
        DBMS_OUTPUT.PUT_LINE ('Insert rejected');
        DBMS_OUTPUT.PUT_LINE ('SQL Error Code:  ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE ('SQL Error Message:  ' || SQLERRM); 
        ROLLBACK;
END;
/

Didn't we cover how to call this very procedure at some length in your earlier question? https://stackoverflow.com/questions/67044667/how-to-execute-stored-procedure-in-oracle-that-contains-two-insert-statements It would normally make more sense to have one procedure that creates an `order` and another procedure that is called in a loop to insert the `order_item` rows. You could pass in a bunch of collections rather than scalar values for all the `order_item` parameters if you want to create multiple `order_item` rows. Or a collection of objects. — Justin Cave, Apr 15 '21 at 13:04
@JustinCave that worked but only if the order had one item, I'm trying to get it working so the procedure can insert multiple items based on the order_id — Jal, Apr 15 '21 at 13:10

score 0 · Answer 1 · answered Apr 15 '21 at 13:02

0

You can use a function to add order detail which return the order_id upon successful insert. Then use that order id to insert into the order_items table using a procedure.

CREATE OR REPLACE FUNCTION INSERT_ORDER RETURN VARCHAR2 IS
    --order specific attributes
    
    BEGIN
    
    --insert into order
    --RETURN order_id;
    
 END INSERT_ORDER;


 CREATE OR REPLACE PROCEDURE INSERT_ORDER_ITEMS(
    --order items specific attributes
    
 BEGIN
    
    --insert into order items
    --COMMIT;
    
 END INSERT_ORDER_ITEMS;
    
 --You can call this within single method/procedure as:
    
 DECLARE
    
 order_id_ NUMBER;
    
 BEGIN
    
    order_id_ := INSERT_ORDER(--attrbiutes);
    FOR i IN i..order_items_list
    LOOP
      INSERT_ORDER_ITEMS(order_id_, --rest of order details);
      COMMIT;
    END LOOP;
 END;

answered Apr 15 '21 at 13:02

Indunil Withana

176
1
8

Having a few issues trying to implement this approach especially when I define the attributes for example `p_name IN CHAR` I get this error `Encountered the symbol "IN" when expecting one of the following: constant exception table long double ref char time timestamp interval date binary national character nchar` – Jal Apr 15 '21 at 13:23
Can be due to many issues. Make sure you define the length as well :) – Indunil Withana Apr 15 '21 at 13:44
Ok thank, I'm bit confused by `FOR i IN i..order_items_list` am I meant to define `order_items_list` somewhere I'm quite new to Oracle PL/SQL. – Jal Apr 15 '21 at 14:06
https://stackoverflow.com/questions/35231757/pl-sql-use-list-variable-in-where-in-clause check comments on defining a type. – Indunil Withana Apr 15 '21 at 14:20
I see so using a Nested-Relational Design? – Jal Apr 15 '21 at 14:31
Yes i believe so. – Indunil Withana Apr 16 '21 at 02:14
Still struggling to implement this technique, I've created two separate procedures as you've shown above. I've tried using the types as you've recommended but not quite grasping the concept. In that for loop what if the item is different and has different values, I'd like to pass in different item data rather than just the one. – Jal Apr 16 '21 at 12:40

How to insert multiple items using an insert query in Oracle PL/SQL stored procedure?

1 Answers1