Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Stored Prodedure

 
francis varkey
Ranch Hand
Posts: 169
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
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.

Thanks
 
Fatih Keles
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 169
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 169
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also need to get their BMI .
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic