please i need help with this Database project and i would hope you show me all s
ID: 3709470 • Letter: P
Question
please i need help with this Database project and i would hope you show me all steps
Run the script, C7_BBcreate.sql, to create the tables required for this lab.
Create a package named Program10_PKG containing two overloaded procedures. Both procedures should use the same DML to query the BB_SHOPPER table for the following data: First name, Last name, City, State, Phone, and e-mail address. The first procedure must use the Shopper ID to search for the record in the table. The second procedure must use the last name for the search. Do not print any data in the procedure. Use parameters to return the data to the calling program unit. Include a NO_DATA_FOUND exception handler to display a message that no record was found. Test the package three times, with an anonymous block. Call the procedure with shopper ID of 23, then 30, and then with a last name of Ratman
Explanation / Answer
CREATE OR REPLACE PACKAGE BODY Program10_PKG AS
CREATE OR REPLACE PROCEDURE get_details
(IN_SHOPPER_ID IN BB_SHOPPER.SHOPPER_ID%TYPE) IS
BEGIN
SELECT first_name,last_name,city,state,phone,email from BB_SHOPPER where shopper_id=IN_SHOPPER_ID;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such datafound!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
CREATE OR REPLACE PROCEDURE get_details
(IN_LAST_NAME IN BB_SHOPPER.LAST_NAME%TYPE) IS
BEGIN
SELECT firstname,lastname,city,state,phone,email from BB_SHOPPER where last_name=IN_LAST_NAME;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such datafound!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
END;
BEGIN
Program10_PKG.get_details(23);
Program10_PKG.get_details(30);
Program10_PKG.get_details('Ratman');
END;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.