Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Question Unanswered: Help with PL/SQL Coding

    Hello, this is what we have currently created:
    It is a book ordering and referencing database (easy to follow), 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

  2. #2
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55

    Re: Help with PL/SQL Coding

    Originally posted by farflungfish
    --------------------------------------------------------
    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
    Thanks
    R u sure u can make Procedure with RETRN ... ???
    I don't think so.
    You should have function for return variable or use PORCEDURE with IN OUT

    PHP Code:
    set serveroutput on
    create 
    or replace procedure address_change(
    cust_id IN customer.customer_id%TYPE,
    cust_id IN varchar2
    new_address OUT varchar2IS
      BEGIN
        UPDATE customer_record
        SET address
    =new_address
        WHERE id
    =cust_id;
      
    exception
        when others then
          null
    ;
      
    end;
    end address_change
    Or like this :
    PHP Code:
    set serveroutput on
    create 
    or replace function address_change(
    cust_id IN customer.customer_id%TYPE,
    cust_id IN varchar2) RETURN Varchar2 IS
    new_address  varchar2 
    := null;
      
    BEGIN
        UPDATE customer_record
        SET address
    =new_address
        WHERE id
    =cust_id;
      
    exception
        when others then
          null
    ;
      
    end;
      RETURN 
    new_address;
    end address_change

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd suggest you to recreate SUPPLIER table; I prefer tables with primary keys defined (which this one doesn't have). It could be some kind of an ID (populated from a sequence, for example).

    Why would I do it that way? I think it is better to fetch suplier's data using its ID than address. Are you sure you'll ALWAYS spell "12 Bongo Heights, London" correctly? What if you omit a coma between "Heights" and "London"? Or misspell something?

    However, if you think you're happy with your table, fine.

    To display supplier's details, write:
    a) a function. IN parameter would be of a CHAR type and should be able to contain full address, or - if you alter the table as I suggested - NUMBER. You could return CHAR - concatenated name, address and contact number.
    b) a procedure. There you'd use both IN and OUT parameter(s) as Venderic suggested.


    To display number of sales for a book, write a function. ISBN should be IN parameter, while you'd return NUMBER (simple SELECT COUNT(*) FROM BOOKS_SOLD statement).


    Also, normalize the BOOKS table (move Author to its own table and leave its ID in the BOOKS table).

    The same goes for the CUSTOMERS and SUPPLIER tables - I'd like to see address separated in (at least) two other tables - STREET and TOWN (for example).
    Last edited by Littlefoot; 04-16-04 at 08:30.

Posting Permissions

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