Results 1 to 5 of 5

Thread: Store Function

  1. #1
    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. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post CREATE TABLE statements for all relevant tables.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    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. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    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
  •