create a stored procedure called STATS_BY_VEHICLE_TYPE that receives an input parameter of the concatenated make and model
Question:
create a stored procedure called STATS_BY_VEHICLE_TYPE that receives an input parameter of the concatenated make and model and then calls your two user-defined functions VEHICLES_BY_VEHICLE_TYPE and DOLLARS_BY_VEHICLE_TYPE. Your stored procedure must return the results of the two functions' executions via OUT parameters. Execute your stored procedure for the same sample input values used earlier to demonstrate that it works correctly.
CREATE TABLE VEHICLES(
VIN NUMBER (12) NOT NULL,
TYPE VARCHAR2 (12) NOT NULL,
MODEL VARCHAR2 (20) NOT NULL,
MAKE VARCHAR2(20) NOT NULL,
wholesale_cost NUMBER (10),
wherfrom VARCHAR2(20),
trade_ID VARCHAR2(10),
CONSTRAINTvechicles_pk PRIMARY KEY (VIN)
);
CREATE TABLE SALES_FACT( Sale_Day DATE NOT NULL,
Vehicle_Code INTEGER(12)NOT NULL, Plan_Code VARCHAR(10) NOT NULL,
Dealer_ID INTEGER(6), Not null
CONSTRAINT SalesFact_PK PRIMARY KEY (Sale_Day , Vehicle_Code , Plan_Code , Dealer_ID),
FOREIGN KEY (Sale_Day) REFERENCES TIMES (sale_day),
FOREIGN KEY (Vehicle_code) REFERENCES VEHICLES (VIN),
FOREIGN KEY (Plan_Code) REFERENCES FINANCING_PLANS (plan_ID),
FOREIGN KEY (Dealer_ID) REFERENCES DEALERSHIPS (DEALER_ID) );
Database Systems Design Implementation and Management
ISBN: 978-1285196145
11th edition
Authors: Carlos Coronel, Steven Morris