File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Oracle/OAS and the fly likes Stored Prodedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Stored Prodedure" Watch "Stored Prodedure" New topic

Stored Prodedure

francis varkey
Ranch Hand

Joined: Sep 13, 2005
Posts: 169
I need to write a stored procedure. Here is the scenario. I have a table with field name "value". This filed contains values in foramt - " height:1.60,weight:67 ". and so on.

I have to select the name of students , whose body mass index is less than 20 .

BMI = weight/height*height.

Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
So what is your question? If you dont know how to create a procedure just google it, but this may give you a head start

You may use function in order to use it in sql.
francis varkey
Ranch Hand

Joined: Sep 13, 2005
Posts: 169

I want to select the name of students whose BMI is less than 20. The formula for BMI is , BMI=weight/(height*height). That is , need to select the students name based on some calculation.
francis varkey
Ranch Hand

Joined: Sep 13, 2005
Posts: 169
Also need to get their BMI .
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

First of all, there is something terribly wrong with your data model. Your table needs columns HEIGHT and WEIGHT of data type NUMBER, as well as NAME and other meaningful columns with proper data types, not some esoteric VALUE VARCHAR2(...) that can contain everything imaginable.

Of course it is possible to parse the text field and extract your values, but this would have severe performance problems. It might work nicely for a hundred or thousand of rows (and honestly, which developer has created more data to test his solution), but as the volume of data grows, the deficiencies of the flawed nonexistent data model start to appear and they'll quickly become unsolvable.

Even if this is just a exercise, I'd say you should not continue in it. It'd be completely useless exercise. Encoding values into single database column as you've shown should definitely be avoided at all times. You should never implement it in real system, therefore you don't need to practice it.

When you redesign your table to contain meaningful columns, the solution becomes very easily. There is actually no need for stored procedure at all, this can (and should) be done in pure SQL. If you don't know how, you need to learn at least the basics of SQL. This would be actually one of the most basic task with SQL and I'm not going to give you the solution anyway (NotACodeMill, you know).
I agree. Here's the link:
subject: Stored Prodedure
It's not a secret anymore!