Updating a table once using array key and values php
When the procedure is being executed it simply loops through the content of the collection and performs the insert statement for every element until it reaches the end of the collection. Finally, you need to modify the PHP code to make it call and execute the above procedure.
Assume that another customer has just created a new order with the order id = 2.
arr_items.count LOOP INSERT INTO ORDER_ITEMS (order_line_id,order_id,item_id) VALUES (ORDER_LINE_SQ.nextval,order_id,arr_items(i)); END LOOP; END; / As you can see, the procedure above requires two parameters: the order id, which is shared between all the items within the same order, and the collection arr_itmes of type v_arr.
This collection will hold the list of ids of all the items that come with given order.
Index-by tables have no upper bounds and can only exist in PL/SQL and not in the database.
Depending on the amount of data inserted, this operation may become a bottleneck that slows the application and degrades the user's experience.The Customers table holds the list of customers, the Items table holds the inventory, the Orders table lists each customer's order details, and the Order_Items table contains the list of items per order.Refer to the ERD below for more details: CREATE TABLE CUSTOMERS ( CUSTOMER_ID NUMBER NOT NULL, CUSTOMER_NAME VARCHAR2 (100) NOT NULL, CONSTRAINT PK_CUSTOMERS PRIMARY KEY ( CUSTOMER_ID ) ) ; / CREATE TABLE ITEMS ( ITEM_ID NUMBER NOT NULL, ITEM_DESCRIPTION VARCHAR2 (50) NOT NULL, ITEM_PRICE NUMBER NOT NULL, CONSTRAINT PK_ITEMS PRIMARY KEY ( ITEM_ID ) ) ; / CREATE TABLE ORDERS ( ORDER_ID NUMBER NOT NULL, ORDER_DATE DATE NOT NULL, CUSTOMER_ID NUMBER, CONSTRAINT PK_ORDERS PRIMARY KEY ( ORDER_ID ) ) ; ALTER TABLE ORDERS ADD CONSTRAINT FK_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS (CUSTOMER_ID) ; / CREATE TABLE ORDER_ITEMS ( ORDER_LINE_ID NUMBER NOT NULL, ORDER_ID NUMBER NOT NULL, ITEM_ID NUMBER NOT NULL, CONSTRAINT PK_ORDER_ITEMS PRIMARY KEY ( ORDER_LINE_ID ) ) ; ALTER TABLE ORDER_ITEMS ADD CONSTRAINT FK_ITEM FOREIGN KEY (ITEM_ID) REFERENCES ITEMS (ITEM_ID) ; ALTER TABLE ORDER_ITEMS ADD CONSTRAINT FK_ORDER FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID) ; / As you can see, every time the customer finalizes the order we have to insert order details into the Orders table and insert a row for every item in the shopping cart into the Order_Items table.Refer to the PHP Manual for complete list of collection functions.If you are using PHP 5.1.2 you can implement the above functionality without creating the collection object by using OCI8 new function oci_bind_array_by_name.