my table fields like this:

and the formula is: weight/(height*height)

So my question is how can I sort my data after the calculation formula? I want to create an anonymous block. Inside the block, I will create a variable then i will set the result and finally I will sort by the result. But I don't know actually how can I do this?

CodePudding user response：

If you want to just display the result, then a loop (through the table) with dbms_output.put_Line call does the job:

SQL> select * from p;

        ID NAME       HEIGHT     WEIGHT
---------- ------ ---------- ----------
         1 Little          2        110
         2 Foot          1.8         90
         3 Asked         1.6         70

SQL> set serveroutput on;
SQL> begin
  2    for cur_r in (select name,
  3                         round(weight / (height * height), 1) result
  4                  from p
  5                  order by result)
  6    loop
  7      dbms_output.put_line(cur_r.name ||': '|| cur_r.result);
  8    end loop;
  9  end;
 10  /
Asked: 27.3
Little: 27.5
Foot: 27.8

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

You don't need PL/SQL for this and can do it in SQL.

Either use the formula in the ORDER BY clause:

SELECT id,
       name,
       height,
       weight,
       ROUND(weight / height / height, 3)
FROM   table_name
ORDER BY
       ROUND(weight / height / height, 3)

Which, for the sample data:

CREATE TABLE table_name (id, name, height, weight) AS
SELECT 1, 'Alice', 1.5,  60 FROM DUAL UNION ALL
SELECT 2, 'Betty', 1.6, 120 FROM DUAL UNION ALL
SELECT 3, 'Carol', 1.7,  72 FROM DUAL UNION ALL
SELECT 4, 'Debra', 1.8, 150 FROM DUAL;

Outputs:

ID	NAME	HEIGHT	WEIGHT	ROUND(WEIGHT/HEIGHT/HEIGHT,3)
3	Carol	1.7	72	24.913
1	Alice	1.5	60	26.667
4	Debra	1.8	150	46.296
2	Betty	1.6	120	46.875

Or, give the column an alias and then use the alias in the ORDER BY clause:

SELECT id,
       name,
       height,
       weight,
       ROUND(weight / height / height, 3) AS bmi
FROM   table_name
ORDER BY bmi

Which, for the same sample data, outputs:

ID	NAME	HEIGHT	WEIGHT	BMI
3	Carol	1.7	72	24.913
1	Alice	1.5	60	26.667
4	Debra	1.8	150	46.296
2	Betty	1.6	120	46.875

If you do want to use PL/SQL then you can wrap it in a CURSOR or use BULK COLLECT INTO to put it into a collection.

db<>fiddle here