4. Write PLISQL or T-SQL procedures to accomplish the following tasks: Obtain th
ID: 3719173 • Letter: 4
Question
4. Write PLISQL or T-SQL procedures to accomplish the following tasks: 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 LLAST_NAME. Obtain the number of persons, customer number, and customer last name for every reservation whose ID currently is stored in LRESERVATION ID. Place these values in the variables _ NUM_PERSONS, I_CUSTOMER NUM, and I _LAST_NAME, respectively Output the contents of I NUM PERSONS, LCUSTOMER_NUM, and I_LAST_NAME. Add a row to the GUIDE table. a. b. c. d. Change the last name of the guide whose number is stored in I_GUIDE_NUM to the e. Delete the guide whose number is stored in I_GUIDE_NUM. number of persons, and trip price for every reservation whose customer number is stored value currently found in I_LAST_NAME. 5. Write PLISQL or T-SQL procedures to retrieve and output the reservation ID, trip ID in I_CUSTOMER_NUM.Explanation / Answer
hey, please follow below, I have implemented above pl/sql scripts below.
4)
a) You can take input from the user for I_GUIDE_NUM, or you can hard code it.
First Way: Accepting input from user.
set serveroutput on;
accept I_GUIDE_NUM prompt 'Enter Guide number';
declare
I_FIRST_NAME guide.first_name%type;
I_LAST_NAME guide.last_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(I_GUIDE_NUM || ' ' || I_FIRST_NAME || ' ' || I_LAST_NAME);
end;
/
Second Way: Harcoding I_GUIDE_NUM.
set serveroutput on;
declare
I_FIRST_NAME guide.first_name%type;
I_LAST_NAME guide.last_name%type;
I_GUIDE_NUM guide.guide_num%type := 'AM01';
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(I_GUIDE_NUM || ' ' || I_FIRST_NAME || ' ' || I_LAST_NAME);
end;
/
b)
set serveroutput on;
declare
I_RESERVATION_ID reservation.reservation_id%type := 1600001;
I_NUM_PERSONS reservation.num_persons%type;
I_CUSTOMER_NUM reservation.customer_num%type;
I_LAST_NAME customer.last_name%type;
begin
select r.NUM_PERSONS, r.CUSTOMER_NUM, c.LAST_NAME into I_NUM_PERSONS, I_CUSTOMER_NUM, I_LAST_NAME from RESERVATION r join CUSTOMER c on r.CUSTOMER_NUM=c.CUSTOMER_NUM;
dbms_output.put_line(I_NUM_PERSONS || ' ' || I_CUSTOMER_NUM || ' ' || I_LAST_NAME);
end;
/
c)
set serveroutput on;
accept v_guide_num prompt 'Enter Guide Number';
accept v_lastName prompt 'Enter Last Name';
accept v_firstName prompt 'Enter First Name';
accept v_address prompt 'Enter Address';
accept v_city prompt 'Enter City';
accept v_state prompt 'Enter State';
accept v_postalCode prompt 'Enter Postal Code';
accept v_telephone prompt 'Enter Phone number';
accept v_hire_date date prompt 'Enter HIREDATE(25-JAN-1992)';
begin
insert into guide values('&v_guide_num', '&v_lastName','&v_firstName','&v_address',
'&v_city','&v_state','&v_postalCode','&v_telephone','&v_hire_date');
commit;
end;
/
d)
set serveroutput on;
declare
I_GUIDE_NUM guide.guide_num%type := 'AM01';
I_LAST_NAME guide.last_name%type := 'Tyson';
begin
update guide set last_name = I_LAST_NAME where GUIDE_NUM=I_GUIDE_NUM;
commit;
end;
/
e)
set serveroutput on;
declare
I_GUIDE_NUM guide.guide_num%type := 'AM01';
begin
delete from guide where GUIDE_NUM=I_GUIDE_NUM;
commit;
end;
/
5)
set serveroutput on;
accept v_customer_num prompt 'Enter Customer Num';
declare
cursor res_cur is
select reservation_id,trip_id,num_persons,trip_price from reservation where customer_num='&v_customer_num';
res_rec res_cur%rowtype;
begin
open res_cur;
loop
fetch res_cur into res_rec;
exit when res_cur%notfound;
DBMS_OUTPUT.PUT_LINE('Reservation Id: '||res_rec.reservation_id);
DBMS_OUTPUT.PUT_LINE('Trip Id: '||res_rec.trip_id);
DBMS_OUTPUT.PUT_LINE('Number of Persons: '||res_rec.num_persons);
DBMS_OUTPUT.PUT_LINE('Trip Price: '||res_rec.trip_price);
DBMS_OUTPUT.PUT_LINE('---------------------------------------------');
end loop;
end;
/
As In this part there can be more than one rows, hence we need to loop through the cusrsor to print multiple rows.
If you have any doubts, you may ask me in the comments section.
Thanks and Happy Chegging :)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.