10 Replies - 1878 Views - Last Post: 25 August 2011 - 01:39 PM Rate Topic: -----

#1 Danied  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 25-August 11

Who can help on pl/sql create store procedure on this question?

Posted 25 August 2011 - 11:08 AM

The following are the records in CUSTOMER:

CustomerID Name Phone EmailAddress
1 Bob Blessing 206.555.3345 Bob@somewhere.com
2 Blaire Jackson 206.555.6645 Blaire@somewhere.com
3 Richard O’Connell 212.555.1145 Dick@somewhere.com
4 Eleanore Justice 209.555.0001 EJustice@somewhere.com
5 Bob Smathers 987.555.1109 Bobs@somewhere.com

The following are the records in STOVE:

SerialNumber Version DateOfManufacture
10 FiredNow 10-May-01
11 FiredAlways 12-May-01
12 FiredNow 15-May-01
13 FiredNow 15-May-01
14 FiredAlways 20-May-00


The following are the records in REGISTRATION:

CustomerID SerialNumber RDate
1 10 22-JUN-01
2 11 22-JUL-01
3 12 05-AUG-01
4 13 05-Aug-01
5 14 22-AUG-01

The following are the records in STOVE_REPAIR:

RepairInvoiceNumber SerialNumber RepairDate Description Cost CustomerID
RepairIDseq 11 22-DEC-01 Fixed burner 75.00 2
RepairIDseq 14 03-JAN-02 Fixed Oven 150.00 5

========================================================================================================
Now i need to create a stored procedure to register a stove. Assume that the procedure receives the customer's name, phone, email address, and stove serial number.
a. If the customer already exists in the database (name, phone, and email match), use that customer's CustomerID for the REGISTRATION. Otherwise, create a new CUSTOMER row for the customer.
b. Assume that a stove with the input serial number already exists in the database. If not, print an error and roll back changes to the CUSTOMER table. Code and test your procedure.

The command line for an existing customer with a valid serial number is:

EXEC Register_Stove ('Bob Blessing', '206.555.3345', 'Bob@somewhere.com', 11);


The command line for an existing customer with an invalid serial number is:

EXEC Register_Stove ('Bob Blessing', '206.555.3345', 'Bob@somewhere.com', 22);


The command line for a new customer with a valid serial number is:

EXEC Register_Stove ('Barb Olsen', '206.555.4456', 'Barb@somewhere.com', 12);

===========================================================================================
So, this is my answer but it's wrong. Some1 can help me on this? Please.. urgent..

CREATE OR REPLACE PROCEDURE register_stove
(current_custName IN VARCHAR2(50),
current_custPhone IN VARHCAR2,
current_c_e_add IN VARCHAR2,
current_custSNUm IN NUMBER)

IS
CURSOR c1 IS
select CustomerID
FROM CUSTOMER
WHERE Name = current_custName
AND Phone = current_custPhone
AND EmailAddress = current_custSNUm;

BEGIN
IF c1%notfound THEN
INSERT INTO CUSTOMER(CustomerID, Name, Phone, EmailAddress)
VALUES(CustIDseq.NEXTVAL,current_custName,current_custPhone,current_c_e_add);

ELSE
INSERT INTO REGISTRATION(CustomerID, SerialNumber, RDate)
VALUES(CustID, CustSNum, TO_DATE('SYSDATE','DD-MM-YY'));


END IF;

END;

==========================================================================

Is This A Good Question/Topic? 0
  • +

Replies To: Who can help on pl/sql create store procedure on this question?

#2 cassiopeia  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 86
  • Joined: 03-April 11

Re: Who can help on pl/sql create store procedure on this question?

Posted 25 August 2011 - 12:06 PM

@So, this is my answer but it's wrong

can you give more specific about your "wrong" ? is it something that computer produce through the messages ? or something like logical problem?

looking to your script, i think you should put () in there :

select CustomerID
FROM CUSTOMER
WHERE (Name = current_custName)
AND (Phone = current_custPhone)
AND (EmailAddress = current_custSNUm);


sometime if you dont put that mark, computer will produce a wrong result.

-cass-

This post has been edited by cassiopeia: 25 August 2011 - 12:08 PM

Was This Post Helpful? 0
  • +
  • -

#3 Danied  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 25-August 11

Re: Who can help on pl/sql create store procedure on this question?

Posted 25 August 2011 - 12:13 PM

icic.. yep.. it's logic and somehow the coding format also got problem i think.. i still not familiar with this pl/sql procedure..
Was This Post Helpful? 0
  • +
  • -

#4 Danied  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 25-August 11

Re: Who can help on pl/sql create store procedure on this question?

Posted 25 August 2011 - 12:27 PM

This is the proper table that i want to show.

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5874
  • View blog
  • Posts: 12,754
  • Joined: 16-October 07

Re: Who can help on pl/sql create store procedure on this question?

Posted 25 August 2011 - 12:57 PM

View Postcassiopeia, on 25 August 2011 - 03:06 PM, said:

i think you should put () in there ...sometime if you dont put that mark


This is simply wrong. Save the parens for where they're needed. Putting them everywhere obfuscates their purpose.

Just glancing at this, I see a c1%notfound but there was never a fetch, so that would be an issue. You can select count into a variable and check that, avoiding the messy cursor stuff. There a number of ways for checking for row existence without cursors.
Was This Post Helpful? 0
  • +
  • -

#6 cassiopeia  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 86
  • Joined: 03-April 11

Re: Who can help on pl/sql create store procedure on this question?

Posted 25 August 2011 - 12:59 PM

1. its not logic problem if you still have format problem. you should solve format problem first. you still not clear about your "wrong".

2. have you tried your script after you put () mark ?

This post has been edited by cassiopeia: 25 August 2011 - 01:01 PM

Was This Post Helpful? 0
  • +
  • -

#7 Danied  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 25-August 11

Re: Who can help on pl/sql create store procedure on this question?

Posted 25 August 2011 - 01:05 PM

yep. i tried put the mark () already but still got error. now i try add the fetch into the coding. =)
Was This Post Helpful? 0
  • +
  • -

#8 cassiopeia  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 86
  • Joined: 03-April 11

Re: Who can help on pl/sql create store procedure on this question?

Posted 25 August 2011 - 01:10 PM

View Postbaavgai, on 25 August 2011 - 12:57 PM, said:

View Postcassiopeia, on 25 August 2011 - 03:06 PM, said:

i think you should put () in there ...sometime if you dont put that mark


This is simply wrong. Save the parens for where they're needed. Putting them everywhere obfuscates their purpose.


of course not, (in my experience) whenever i have 2 or more where clouse and i dont put this mark (), then sometime the computer produce error result in logical way and not after i put that mark. and i dont say that by putting that mark will automatically solve the problem.

-cass-

This post has been edited by cassiopeia: 25 August 2011 - 01:12 PM

Was This Post Helpful? 0
  • +
  • -

#9 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5874
  • View blog
  • Posts: 12,754
  • Joined: 16-October 07

Re: Who can help on pl/sql create store procedure on this question?

Posted 25 August 2011 - 01:14 PM

Just looking at this...
INSERT INTO REGISTRATION(CustomerID, SerialNumber, RDate) 
	VALUES(
		CustID, -- where does this magic value come from?  you probably do need that fetch
		CustSNum, -- another mystery variable.
		TO_DATE('SYSDATE','DD-MM-YY') -- this is both wrong and redundant, try sysdate or trunc(sysdate)
		);


Was This Post Helpful? 0
  • +
  • -

#10 Danied  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 25-August 11

Re: Who can help on pl/sql create store procedure on this question?

Posted 25 August 2011 - 01:21 PM

hmmm.. i noe what u mean but how? can teach me or show me the solution? xD
Was This Post Helpful? 0
  • +
  • -

#11 cassiopeia  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 86
  • Joined: 03-April 11

Re: Who can help on pl/sql create store procedure on this question?

Posted 25 August 2011 - 01:39 PM

View Postbaavgai, on 25 August 2011 - 01:14 PM, said:

Just looking at this...
INSERT INTO REGISTRATION(CustomerID, SerialNumber, RDate) 
	VALUES(
		CustID, -- where does this magic value come from?  you probably do need that fetch
		CustSNum, -- another mystery variable.
		TO_DATE('SYSDATE','DD-MM-YY') -- this is both wrong and redundant, try sysdate or trunc(sysdate)
		);



not only that, i think there will be problem with data flow too. according to the logic of the script, new user will be created base on this :

select CustomerID
FROM CUSTOMER
WHERE (Name = current_custName)
AND (Phone = current_custPhone)
AND (EmailAddress = current_custSNUm);


so .. lets assume if new customer has same name, and same phone and email i.e. phone="-" and email="-" then system will recognize as one person that already had in database instead as the new one.
my solution in very simple way, dont register new customer through this procedure :D

This post has been edited by cassiopeia: 25 August 2011 - 01:41 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1