Project (use Oracle) ( Create function project_function that will the number of

Question

Project (use Oracle) (

Create function project_function that will the number of aircrafts and the number of scheduled hours between specified date and the end of the year. select project_function(sysdate) from dual will display the number of aircrafts and the number of scheduled hours between today and the end of the year.

Provide code of your function here

Provide code for aircraft and aircraft booking table and insert statements to insert the data to test the code

write a function that will have this select statement inside. SELECT COUNT(AP_ID) INTO num_aircrafts, SUM(RESERVE_HRS) INTO num_hours FROM SCHEDULE WHERE RESERVE_START_DTE BETWEEN date_in AND TO_DATE ('12/31/2018', 'mm/dd/yyyy');

Explanation / Answer

As no database schema is given, so we will use some dummy tables as follows:

Table – Aircraft

Aircraft_ID

AircraftName

Comapany

Table –Schedule

Ap_ID

Aircraft_Id

Reserve_Hrs

ReserveDate

create Table Aircraft

(

Aircraft_ID Integer primary key,

AircraftName varchar2(40),

Company varchar2(40)

);

create Table Schedule

(Ap_ID Integer,

Aircraft_Id Integer,

Reserve_Hrs Integer,

ReserveDate date,

foreign key(Aircraft_id) references Aircraft(Aircraft_ID))

;

Insertions

insert into aircraft values(101,'Boeing 11','ABC');

insert into aircraft values(102,'Boeing 211','XYZ');

insert into aircraft values(103,'AirCArrier','ABC');

insert into aircraft values(104,'AirCArrier111','ABC');

insert into schedule values

(20001,101,5,to_date('2018-07-04','yyyy-dd-mm'));

insert into schedule values

(20001,102,3,to_date('2018-21-05','yyyy-dd-mm'));

insert into schedule values

(20002,103,6,to_date('2018-07-09','yyyy-dd-mm'));

insert into schedule values

(20002,104,1,to_date('2018-31-12','yyyy-dd-mm'));

following is the code

First create type to get two values out of function
create or replace type my_values as object
( hrs int,
num int
);
/
Follwing is the function:

create or replace function project_function
(date_in IN Date)
return my_values
AS
num_hours number;
num_aircrafts number;
begin
SELECT COUNT(Ap_ID) INTO num_aircrafts FROM SCHEDULE WHERE ReserveDate BETWEEN date_in AND TO_DATE ('12/31/2018', 'mm/dd/yyyy');
select SUM(Reserve_Hrs) INTO num_hours FROM SCHEDULE WHERE ReserveDate BETWEEN date_in AND TO_DATE ('12/31/2018', 'mm/dd/yyyy');
return my_values(num_hours,num_aircrafts);
end;
/

we will get number of hours and number of aircrafts using

select project_function(sysdate) from dual

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.