Write PL/SQL a. Obtain the first name and last name, separated by a space, of th
ID: 3715377 • Letter: W
Question
Write PL/SQL
a. Obtain the first name and last name, separated by a space, of the guide whose number currently is stored in I_GUIDE_NUM. Place these values in the variables I_FIRST_NAME and I_LAST_NAME. Output the contents of I_GUIDE_NUM, I_FIRST_NAME, and I_LAST_NAME.
b. Obtain the number of persons, customer number, and customer last name for every reservation whose ID currently is stored in I_RESERVATION_ID. Place these values in the variables I_NUM_PERSONS, I_CUSTOMER_NUM and I_ LAST_NAME, respectively. Output the contents of I_NUM_PERSONS, I_CUSTOMER_NUM and I_LAST_NAME.
c. Add a row to the GUIDE table.
d. Change the last name of the guide whose number is stored in I_GUIDE_NUM to the value currently found in I_LAST_NAME.
e. Delete the guide whose number is stored in I_GUIDE_NUM.
Explanation / Answer
--a
CREATE PROCEDURE GUIDENAME (I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE)
I_LAST_NAME GUIDE.LAST_NAME%TYPE;
I_FIRST_NAME GUIDE.FIRST_NAME%TYPE;
BEGIN
SELECT FIRST_NAME,LAST_NAME INTO I_FIRST_NAME,I_LAST_NAME FROM GUIDE WHERE GUIDE_NUM=I_GUIDE_NUM;
DBMS_OUTPUT.PUT_LINE(RTRIM(I_FIRST_NAME) ||' '||RTRIM(I_LAST_NAME));
END;
/
/*b. Obtain the number of persons, customer number, and customer last name for
every reservation whose ID currently is stored in I_RESERVATION_ID.
Place these values in the variables I_NUM_PERSONS, I_CUSTOMER_NUM and I_LAST_NAME, respectively.
Output the contents of I_NUM_PERSONS, I_CUSTOMER_NUM and I_LAST_NAME.*/
CREATE PROCEDURE SP_eservationDetails
@I_RESERVATION_ID int
AS
BEGIN
DECLARE @I_NUM_PERSONS INT
DECLARE @I_CUSTOMER_NUM INT
DECLARE @I_LAST_NAME (20)
SET @I_NUM_PERSONS = SELECT NUM_PERSONS FROM ORDERS WHERE NUM_PERSONS =@I_NUM_PERSONS
SET @I_CUSTOMER_NUM = SELECT CUSTOMER_NUM FROM ORDERS WHERE CUSTOMER_NUM =@I_CUSTOMER_NUM
SET @I_LAST_NAME= SELECT LAST_NAME FROM GUIDE WHERE LAST_NAME =@I_LAST_NAME
SELECT @I_NUM_PERSONS,@I_CUSTOMER_NUM,@I_LAST_NAME
END
--c. Add a row to the GUIDE table.
CREATE PROCEDURE InsertGuide
As
BEGIN
INSERT INTO GUIDE (GUIDE_NUM,FIRST_NAME,LAST_NAME) VALUES(100,'John','Betty');--Need to fil as per table
END
--d. Change the last name of the guide whose number is stored in I_GUIDE_NUM to the value currently found in I_LAST_NAME.
UPDATE GUIDE
SET LAST_NAME=@I_LAST_NAME
WHERE GUIDE_NUM=@I_GUIDE_NUM;
--e. Delete the guide whose number is stored in I_GUIDE_NUM.
DELETE FROM GUIDE WHERE GUIDE_NUM=@I_GUIDE_NUM
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.