Write a stored procedure called Vehicle_Details ( LicenseNumber IN VARCHAR ) tha
ID: 3583743 • Letter: W
Question
Write a stored procedure called Vehicle_Details(LicenseNumber IN VARCHAR) that has one input parameter LicenseNumber as indicated and when called, will display details about the vehicle including model, make, year, and the manager who is currently using it. The following is an example of how to call the procedure and the expected output: (3 points) ( desplay details are from two different tables)
SQL> EXEC Vehicle_Details(‘TEXAS ABC-739’)
Output should be.,
Model=Mustang
Make=Ford
Year=02
First Name=Franklin
Last Name=Wong
I wrote this question but it did not work ( it gives me all the assigned vichle )
create or replace procedure Vehicle_Details(LicenseNumber IN VARCHAR) IS
Model COMPANY_VEHICLE.Model%TYPE;
Make COMPANY_VEHICLE.Make%TYPE;
Year COMPANY_VEHICLE.Year%TYPE;
Fname EMPLOYEE.Fname%TYPE;
Lname EMPLOYEE.Lname%TYPE;
cursor Vehicle_cursor is
SELECT Model ,Make , Year ,Fname,Lname
FROM COMPANY_VEHICLE, Employee
where assignee=ssn;
begin
open Vehicle_cursor;
loop
fetch Vehicle_cursor into Model, Make,Year , Fname , Lname;
exit when Vehicle_cursor%notfound;
dbms_output.put_line('Model=' || Model );
dbms_output.put_line('Make=' || Make );
dbms_output.put_line('Year=' || Year);
dbms_output.put_line('First Name=' ||Fname);
dbms_output.put_line('Last Name=' || Lname);
end loop;
close Vehicle_cursor;
end;
The output I have is:
Procedure VEHICLE_DETAILS compiled
Model=Mustang
Make=Ford
Year=2
First Name=Franklin
Last Name=Wong
Model=XJS
Make=Jaguar
Year=5
First Name=James
Last Name=Borg
Model=Sonata
Make=Hyundai
Year=7
First Name=Jennifer
Last Name=Wallace
PL/SQL procedure successfully completed.
Company_vehicle table
LicenseNumber
Model
Make
Year
Assignee
TEXAS ABC-739
Mustang
Ford
02
333445555
TEXAS RSK-629
XJS
Jaguar
05
888665555
TEXAS TQR-515
Sonata
Hyundai
07
987654321
TEXAS OPU-010
Camry
Toyota
04
null
TEXAS FGT-111
Cavalier
Chevrolet
08
null
TEXAS YHF-235
Elantra
Hyundai
09
null
TEXAS TQY-689
Avalon
Toyota
06
null
+ The employee table
FNAME M LNAME SSN DNO
------------------------------ - ------------------------------ --------- --------- ----
James E Borg 888665555 1
Franklin T Wong 333445555 5
John B Smith 123456789 5
Jennifer S Wallace 987654321 4
Alicia J Zelaya 999887777 4
Ramesh K Narayan 666884444 5
Joyce A English 453453453 5
LicenseNumber
Model
Make
Year
Assignee
TEXAS ABC-739
Mustang
Ford
02
333445555
TEXAS RSK-629
XJS
Jaguar
05
888665555
TEXAS TQR-515
Sonata
Hyundai
07
987654321
TEXAS OPU-010
Camry
Toyota
04
null
TEXAS FGT-111
Cavalier
Chevrolet
08
null
TEXAS YHF-235
Elantra
Hyundai
09
null
TEXAS TQY-689
Avalon
Toyota
06
null
Explanation / Answer
Answer:
In the above code the procedure fetching the data for matched assignees in employee table. so you are ignoring the input Licence number taken for fetching.
So now i just change the select query where we are fetching first vehicle licence number matched data and then the matched assginee for that vehicle. so one more condition added in the select query.
create or replace procedure Vehicle_Details(LNumber IN VARCHAR) IS
Model COMPANY_VEHICLE.Model%TYPE;
Make COMPANY_VEHICLE.Make%TYPE;
Year COMPANY_VEHICLE.Year%TYPE;
Fname EMPLOYEE.Fname%TYPE;
Lname EMPLOYEE.Lname%TYPE;
cursor Vehicle_cursor is
SELECT Model ,Make , Year ,Fname,Lname
FROM COMPANY_VEHICLE, Employee
where assignee=ssn and LicenceNumber=LNumber;
begin
open Vehicle_cursor;
loop
fetch Vehicle_cursor into Model, Make,Year , Fname , Lname;
exit when Vehicle_cursor%notfound;
dbms_output.put_line('Model=' || Model );
dbms_output.put_line('Make=' || Make );
dbms_output.put_line('Year=' || Year);
dbms_output.put_line('First Name=' ||Fname);
dbms_output.put_line('Last Name=' || Lname);
end loop;
close Vehicle_cursor;
end;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.