QuestionQuestion

Create a PL/SQL program that handles the order placement for the application. When customer Newton decides to buy, all the items in his shopping cart that are not in the wish list will be included in a purchase order. The following actions take place:

1. If there are items in the shopping cart for Newton, a new row is inserted in the ORDERS table. Increment the order ID of the new row accordingly.

2. For each item in the SHOPPING_CART_ITEMS that is in Newton’s buying list (not in the wish list) do the following:

a. A corresponding item is created in the ORDER_ITEMS table. All these newly added order items refer to the order created in step 1.

b. The quantity in INVENTORY_ITEMS table is updated accordingly

3. All the items in the SHOPPING_CART_ITEMS table that were just ordered are removed   

Add a few output lines using DBMS_OUTPUT.PUT_LINE function to indicate how your program progresses through the list of actions.

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

set serveroutput on
DECLARE
item_nums NUMBER;
order_id NUMBER;
cust_id NUMBER;
-- we will iterate over rows which are got from below query
cursor item_cur is
    select si.PRODID, si.QUANTITY
      from SHOPPING_CART_ITEMS si inner join CUSTOMERS c
      on si.CUSTID = c.ID where c.NAME like '%Newton';
-- iterator   
item_l item_cur%ROWTYPE;
BEGIN
DBMS_OUTPUT.put_line('Program starts');
DBMS_OUTPUT.put_line(' ');...

By purchasing this solution you'll be able to access the following files:
Solution.sql.

$60.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats