# Thread: Store Function

1. Registered User
Join Date
Oct 2011
Posts
16

## Unanswered: Store Function

Does anyone know how to write an stored function? i need to write one that

-Receives the Manufacturer and PRODUCT identifying the product sold as well as the Quantity purchased by the customer
-Calculates the value of the product sold (looks up the cost of the product in the products table and
multiplies by the quantity sold)
- Returns the calculated value

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
post CREATE TABLE statements for all relevant tables.

3. Registered User
Join Date
Oct 2011
Posts
16
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));
Last edited by JuankyKong; 11-24-11 at 01:18.

4. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
Who designed this interesting collection of tables?

>Receives the Manufacturer and PRODUCT identifying the product sold as well as the Quantity purchased by the customer
I don't see any common column that provides ability to join any table to any other table.
How do you know what product any customer purchased?
Yes you have CUSTOMER table & PRODUCT table, but no ability to relate or join these 2 tables.

5. Registered User
Join Date
Oct 2011
Posts
16

## mmmm

Just a second i think im missing something here !

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•