n this assignment, we use the same database that you have been using for the labs and the assignment 1. Note: For each query in your assignment, make sure you handle the errors and display the proper message including the error code and the error message. try{ ... } catch (SQLException& sqlExcp) { cout createConnection(user, pass, constr); Remember to terminate and close the connection and the environment when your program terminates. env->terminateConnection(conn); Environment::terminateEnvironment(env); After executing the statements make sure you terminate the statement. conn->terminateStatement(stmt); You will implement the following Oracle stored procedures and C++ functions: Stored Procedures find_customer (customer_id IN NUMBER, found OUT NUMBER); This procedure has an input parameter to receive the customer ID and an output parameter named found. This procedure looks for the given customer ID in the database. If the customer exists, it sets the variable found to 1. Otherwise, the found variable is set to 0. To check if your query in the find_customer() procedure returns a row, you can check the no_data_found exception in the EXCEPTION block. EXCEPTION WHEN no_data_found THEN found := 0; find_product (product_id IN NUMBER, price OUT products.list_price%TYPE); This procedure has an input parameter to receive the product ID and an output parameter named price. This procedure looks for the given product ID in the database. If the product exists, it stores the product’s list_price in the variable price. Otherwise, the price variable is set to 0. EXCEPTION WHEN no_data_found THEN price := 0; add_order (customer_id IN NUMBER, new_order_id OUT NUMBER) This procedure has an input parameter to receive the customer ID and an output parameter named new_order_id. To add a new order for the given customer ID, you need to generate the new order Id. To calculate the new order Id, find the maximum order ID in the orders table and increase it by 1. This procedure inserts the following values in the orders table: new_order_id customer_id (input parameter) 'Shipped' (The value for the order status) 56 (The sales person ID) sysdate (order date which is the current date) add_order_item (orderId IN order_items.order_id%type, itemId IN order_items.item_id%type, productId IN order_items.product_id%type, quantity IN order_items.quantity%type, price IN order_items.unit_price%type) This procedure has five IN parameters. It stores the values of these parameters to the table order_items. C++ Functions int mainMenu(); The mainMenu() function returns an integer value which is the selected option by the user from the menu. This function displays the following menu options: 1) Login 0) Exit Prompt the user to choose an option. If the user enters the wrong value, ask the user to enter an option again until the user enters a valid options. See the following example: ******************** Main Menu ******************** 1) Login 0) Exit Enter an option (0-1): 5 ******************** Main Menu ******************** 1) Login 0) Exit You entered a wrong value. Enter an option (0-1): If the user chooses option 1, ask the user to enter customer ID to login. To see if the customer with the entered ID exists, call the Oracle stored procedure find_customer(). IF the value of the output parameter in the procedure is 1, let the customer to continue. If the value of the output parameter found is 0, call the mainMenu() functions again and asks the customer to login again. Continue this process until the user chooses the option 0 to exit or enters a valid customer ID. int customerLogin(Connection* conn, int customerId); Before you call this function, prompt the user to enter the customer ID. Call this function in the main() function if the user chooses the login option from the main menu. This function receives an integer value as a customer ID and checks if the customer does exist in the database. This function returns 1 if the customer exists. If the customer does not exists, this function returns 0 and the main menu is displayed. To validate the customer ID call the find_customer() stored procedure in this function. See the following example: ******************** Main Menu ******************** 1) Login 0) Exit Enter an option (0-1): 1 Enter the customer ID: 1000 The customer does not exist. ******************** Main Menu ******************** 1) Login 0) Exit Enter an option (0-1): 1 Enter the customer ID: 44 -------------- Add Products to Cart -------------- Enter the product ID: int addToCart(Connection* conn, struct ShoppingCart cart[]); If the customerLogin() functions return 1 (The customer ID exists), call this function.
In this assignment, we use the same
Stored Procedures find_customer (customer_id IN NUMBER, found OUT NUMBER); This procedure has an input parameter to receive the customer ID and an output parameter named found. This procedure looks for the given customer ID in the database. If the customer exists, it sets the variable found to 1. Otherwise, the found variable is set to 0. To check if your query in the find_customer() procedure returns a row, you can check the no_data_found exception in the EXCEPTION block. EXCEPTION WHEN no_data_found THEN found := 0;
find_product (product_id IN NUMBER, price OUT products.list_price%TYPE); This procedure has an input parameter to receive the product ID and an output parameter named price. This procedure looks for the given product ID in the database. If the product exists, it stores the product’s list_price in the variable price. Otherwise, the price variable is set to 0. EXCEPTION WHEN no_data_found THEN price := 0; add_order (customer_id IN NUMBER, new_order_id OUT NUMBER) This procedure has an input parameter to receive the customer ID and an output parameter named new_order_id. To add a new order for the given customer ID, you need to generate the new order Id. To calculate the new order Id, find the maximum order ID in the orders table and increase it by 1. This procedure inserts the following values in the orders table: new_order_id customer_id (input parameter) 'Shipped' (The value for the order status) 56 (The sales person ID) sysdate (order date which is the current date) add_order_item (orderId IN order_items.order_id%type, itemId IN order_items.item_id%type, productId IN order_items.product_id%type, quantity IN order_items.quantity%type, price IN order_items.unit_price%type) This procedure has five IN parameters. It stores the values of these parameters to the table order_items. C++ Functions int mainMenu(); The mainMenu() function returns an integer value which is the selected option by the user from the menu. This function displays the following menu options: 1) Login 0) Exit Prompt the user to choose an option. If the user enters the wrong value, ask the user to enter an option again until the user enters a valid options.
See the following example: ******************** Main Menu ******************** 1) Login 0) Exit Enter an option (0-1): 5 ******************** Main Menu ******************** 1) Login 0) Exit You entered a wrong value. Enter an option (0-1): If the user chooses option 1, ask the user to enter customer ID to login. To see if the customer with the entered ID exists, call the Oracle stored procedure find_customer(). IF the value of the output parameter in the procedure is 1, let the customer to continue. If the value of the output parameter found is 0, call the mainMenu() functions again and asks the customer to login again. Continue this process until the user chooses the option 0 to exit or enters a valid customer ID. int customerLogin(Connection* conn, int customerId); Before you call this function, prompt the user to enter the customer ID. Call this function in the main() function if the user chooses the login option from the main menu. This function receives an integer value as a customer ID and checks if the customer does exist in the database. This function returns 1 if the customer exists. If the customer does not exists, this function returns 0 and the main menu is displayed. To validate the customer ID call the find_customer() stored procedure in this function. See the following example: ******************** Main Menu ******************** 1) Login 0) Exit Enter an option (0-1): 1 Enter the customer ID: 1000 The customer does not exist. ******************** Main Menu ******************** 1) Login 0) Exit Enter an option (0-1): 1 Enter the customer ID: 44 -------------- Add Products to Cart -------------- Enter the product ID:
int addToCart(Connection* conn, struct ShoppingCart cart[]); If the customerLogin() functions return 1 (The customer ID exists), call this function.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
