SQL> select * from FILMS;

FNAME        FKST         FNOM               FBUD
------------ ------------ ------------ ----------
Операция Ы   Союз         К                   100
Белое солнце Союз         Б                   200
Золушка      Союз         С                    50
Салогонщики  Союз         К                   100
Вий          Союз         Ж                   100

SQL> select * from NOMIN;

NNOM               NKOL     NPRICE
------------ ---------- ----------
Б                     1       9000
К                     2       1000
С                     1       2000
Ж                     1       1500

SQL> create or replace procedure TUPIK (NAG in varchar2) is
  2   begin
  3    for r in (
  4         select FNAME from FILMS f
  5         where f.FNOM in (select nnom
  6               from (select nnom,rank() over (order by (NKOL*NPRICE) DESC) rn
  7                     from nomin n
  8                     where nnom<>NAG)
  9               where rn=1)) loop
 10       dbms_output.put_line(r.fname);
 11    end loop;
 12  end TUPIK;
 13  /

Procedure created.

SQL> exec TUPIK('K');
Белое солнце

PL/SQL procedure successfully completed.

SQL> exec TUPIK('Б');
Операция Ы
Золушка
Салогонщики

PL/SQL procedure successfully completed.

SQL>

SELECT  MAX(FNAME) KEEP(DENSE_RANK LAST ORDER BY NKOL*NPRICE) FNAME
  FROM  FILMS F,
        NOMIN N
  WHERE NNOM != NAG
    AND FNOM = NNOM;

SELECT  MAX(FNAME) KEEP(DENSE_RANK LAST ORDER BY NKOL*NPRICE) FNAME
  FROM  FILMS F,
        NOMIN N
  WHERE NNOM != NAG
    AND FNOM = NNOM;