Win a copy of Micro Frontends in Action this week in the Server-Side JavaScript and NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

Stored Prodedure

 
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
 
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 .
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • 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).
 
If tomatoes are a fruit, then ketchup must be a jam. Taste this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic