Stored Prodedure

[francis varkey]

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]

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

create or replace procedure xxmy_proc(p_val1 in varchar2, x_val1 out varchar2) is
begin
--some substr operation here 
end xxmy_proc;

You may use function in order to use it in sql.

[francis varkey]

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]

Also need to get their BMI .

[Martin Vajsar]

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).