Alguien sabe algo de store procedures? necesito saber si alguien podria: crear una peqena function store que de:
-reciba el valor de MFR y PRODUCT identificando el producto vendido tant como la QUANTITY comprada por el cliente
-Calcule el valor de el producto vendido (osea que busque el costo del producto en la tabla de producto y multiplique eso por la cantidad vendida
-Nos de el total del valor calculado
aqui estan las tablas:
CREATE TABLE Customers
(CustNum NUMBER(10) NOT NULL,
Company VARCHAR2(20) NOT NULL,
CustRep NUMBER(10),
CreditLimit NUMBER(6),
CONSTRAINT CustomersPK
PRIMARY KEY (CustNum));
CREATE TABLE SalesReps
(SalesRep NUMBER(10) NOT NULL,
Name VARCHAR2(15) NOT NULL,
Age NUMBER(3),
RepOffice NUMBER(10),
Title VARCHAR2(15),
HireDate DATE NOT NULL,
Manager NUMBER(10),
Quota NUMBER(10),
Sales NUMBER(12,2) NOT NULL,
CONSTRAINT SalesrepsPK
PRIMARY KEY (Salesrep));
CREATE TABLE Offices
(Office NUMBER(10) NOT NULL,
CIty VARCHAR2(15) NOT NULL,
Region VARCHAR2(10) NOT NULL,
Mgr NUMBER(10),
Target NUMBER(10),
Sales NUMBER(12,2) NOT NULL,
CONSTRAINT OfficesPK
PRIMARY KEY (Office));
CREATE TABLE Orders
(OrderNum NUMBER(10) NOT NULL,
OrderDate DATE NOT NULL,
Cust NUMBER(10),
Rep NUMBER(10),
Mfr CHAR(3) NOT NULL,
Product CHAR(5) NOT NULL,
Qty NUMBER(5) NOT NULL,
Amount NUMBER(9,2) NOT NULL,
CONSTRAINT OrdersPK
PRIMARY KEY (OrderNum));
CREATE TABLE Products
(Mfr CHAR(3) NOT NULL,
Product CHAR(5) NOT NULL,
Description VARCHAR2(20) NOT NULL,
Price NUMBER(9,2) NOT NULL,
QtyOnHand NUMBER(5),
CONSTRAINT ProductsPK
PRIMARY KEY (Mfr, Product));
