Hello, this is what we have currently created:
It is a book ordering and referencing database, we need to create several procedures, we have created one already, this is below the main initialisation of the tables.
if you have oracle with +workbook then run it. It runs fine.. this is not our problem but an overview our problem is the procedure and we need advice on creating two other procedures:
----------------------------------------------------------
DROP TABLE books;
-- creates the book table
CREATE TABLE books (
ISBN char(15) CONSTRAINT pk_ISBN PRIMARY KEY,
Title varchar2(50) not null,
Author varchar2(25)not null,
DOP char(8)not null,
Price char(8),
Sales char(5)
);
-- inserts the book informationm
INSERT INTO books (ISBN,Title,Author,DOP,Price,Sales)
VALUES ('0-596-00441-9','SQL for Reptiles','Dr J Bean','01/05/84','12.00','');
INSERT INTO books (ISBN,Title,Author,DOP,Price,Sales)
VALUES ('0-596-66789-9','Dogs And Fried Fish','Ted Dougal','05/12/98','16.99','');
INSERT INTO books (ISBN,Title,Author,DOP,Price,Sales)
VALUES ('0-666-66766-9','Smut','Harry Chapin','07/10/77','22.99','');
INSERT INTO books (ISBN,Title,Author,DOP,Price,Sales)
VALUES ('0-334-57642-1','Windows','Bill Gates','15/08/03','44.99','');
INSERT INTO books (ISBN,Title,Author,DOP,Price,Sales)
VALUES ('0-396-66673-3','Metro Dreams','Lydia McTammy','06/05/22','6.99','');
DROP TABLE Customers;
-- creates the customer table
CREATE TABLE Customers (
Cust_ID char(15) CONSTRAINT pk_Cust_ID PRIMARY KEY,
Title varchar2(5) CONSTRAINT title_check CHECK (title IN ('Mr','Mrs','Ms','Dr','Sir','Miss','Lord'))NOT NULL,
Forename varchar2(25),
Surname varchar2(25)NOT NULL,
Address varchar2(50),
Contact_No number(11) NOT NULL
);
-- inserts the customer information
INSERT INTO Customers (Cust_ID,Title,Forename,Surname,Address,Contact_No)
VALUES ('1','Mr','Bruce','Milne','6 Crimon Place, Aberdeen, AB10 1RY','01224643124');
INSERT INTO Customers (Cust_ID,Title,Forename,Surname,Address,Contact_No)
VALUES ('2','Mr','Doug','Rae','Spring Garden, Aberdeen, AB10 1FG','01224645487');
INSERT INTO Customers (Cust_ID,Title,Forename,Surname,Address,Contact_No)
VALUES ('3','Mr','Liam','Harper','The Cottage, Torry, Aberdeen, AB6 1RE','01224987134');
INSERT INTO Customers (Cust_ID,Title,Forename,Surname,Address,Contact_No)
VALUES ('4','Miss','Megan','De Costa','8 Church St, Edzell, DD9 1TQ','01356648733');
INSERT INTO Customers (Cust_ID,Title,Forename,Surname,Address,Contact_No)
VALUES ('5','Ms','Jo','Meldrum','8 South Esk St, Brechin, DD9 6HH','01324344124');
DROP TABLE Supplier;
-- creates the supplier table
CREATE TABLE Supplier (
Name varchar2(25),
Address varchar2(30),
Contact_No number(11)
);
-- inserts the supplier information
INSERT INTO Supplier (Name,Address,Contact_No)
VALUES ('Random House','44 Gillcroft Road, Lincoln','01546569874');
INSERT INTO Supplier (Name,Address,Contact_No)
VALUES ('Red Brick','33 Mango Towers, London','05447636352');
INSERT INTO Supplier (Name,Address,Contact_No)
VALUES ('O`Reilly','12 Bongo Heights, London','05467739352');
DROP TABLE Books_Sold
-- creates the books_sold table
CREATE TABLE Books_Sold (
Cust_ID CHAR(15) CONSTRAINT fk_Cust_ID REFERENCES Customers (Cust_ID)
ON DELETE CASCADE,
ISBN char(15) CONSTRAINT fk_ISBN REFERENCES Books (ISBN)
ON DELETE CASCADE,
Sale_Date DATE
);
-- shows what the tables contain
SELECT * FROM Books;
SELECT * FROM Customers;
SELECT * FROM Supplier;
-----------------------------------------------------
This is one of our procedures which changes the address of a customer; though it is absract and does not have any input, just variables.. we just need to know how to make the compiling correct.
--------------------------------------------------------
set serveroutput on
create or replace procedure address_change(
cust_id IN customer.customer_id%TYPE,
cust_id IN varchar2)
RETURN varchar2 IS
new_address varchar2;
BEGIN
UPDATE customer_record
SET address=new_address
WHERE id=cust_id;
exception
when others then
null;
end;
-------------------------------------------------------
We need advice on the procedure above, and also ideas on how to make procedures for:
calculating the total number of sales for a book
and
displaying a suppliers details from a given address.
any help or advice on this would be much appreciated.
We only ask for advice, ideas and examples.. no complete solutions please.
Thanks
