Вычисление номера элемента в отсорированном массиве в SQL

Столкнулся с такой задачей: нужно для каждой строки результата работы SQL-запроса вывести номер этой строки внутри отсортированного по неким параметрам массива. Чтобы было понятно, о чем речь, приведу пример. Есть таблица с данными:
val
-------
7
3
1
5
9
4

Нужно вывести эти числа, не используя ORDER BY или еще каких SQL-инструкций  и вычилсить их номер в отсортированном массиве. Результат должен быть такой:
val   idx
--------------
7    |   4
3    |   1
1    |   0
5    |   3
9    |   5
4    |   2

Столбец "index" должен быть вычислен. Что есть номер элемента в отсортированном по возрастанию массиве? - это число элементов, которые меньше его. Если таких элементов нет - то значит данный элемент самый маленький и будет стоять первым и иметь индекс 0. Таким образом, получаем первый вариант запроса:

 SELECT
     tbl.value as val
    (SELECT
         count(*)
     FROM
         tbl tbl2
     WHERE
        tbl2.value < val ) as idx
FROM
    tbl

Это будет работать, но возникает проблема, если есть повторяющиеся числа. Тогда некоторые элементы будут иметь одинаковый индекс, а нам это не подходит. Что делать в таком случае? Я использовал значение первичного ключа как дополнительный параметр для сравнения: если два значения равны, то сравниваем их первичные ключи - уж они-то точно будут разными. Улучшенный вариант предыдущего запроса будет выглядеть так:


SELECT
     tbl.id  as myid
     tbl.value as val
    (SELECT
         count(*)
     FROM
         tbl tbl2
     WHERE
        (tbl2.value < val)
        OR
        (tbl2.value = val AND tbl2.id < myid) ) as idx
FROM
    tbl

Последний вариант и был успешно использован.

Процедуры и триггеры в Oracle

Процедуры
Ниже приведен пример кода процедуры. Сампример немного громоздок, т.к. взят непосредственно из рабочей базы без каких-либо модификаций и упрощений:

create or replace procedure jlb_set_kr_by_phone
is
 is_region_found NUMERIC(1,0);
 pers sch2.jlb_pays_temp2.account%TYPE;
 vcode_count NUMERIC(5,0);
 valid_vcode NUMERIC(5,0);
begin
 --перебираем лицевые
 FOR c1 IN (SELECT phone
    FROM sch2.jlb_pays_temp2
    WHERE phone IS NOT NULL AND account IS NULL) LOOP
   
  is_region_found := 0;
  vcode_count := 0;
  --ищем код района лицевого счета
  FOR c2 IN (SELECT
      z.vcode,
      c.npers
     FROM
      BS.BSSUBSACCT SA,
      bs.bstechnic t,
      bs.bstechsubs ts,
      bs.bsclient c,
      bs.vbszone z
     WHERE
      sa.nsubs = c.nclient_id
      and ts.nsubs=sa.nsubs
      and t.ntech_id=ts.ntech
      and t.vcode = c1.phone
      and c.nzone = z.nzone_id) LOOP
   --признак физ. или юр. лица  
   pers := c2.npers;
   --запомним найденный код р-на
   valid_vcode := c2.vcode;  
   --укажем, что код р-на такого лицевого был найден
   is_region_found := 1;
   --считаем кол-во найденных р-нов    
   vcode_count := vcode_count + 1; 
  END LOOP; --end c2

  --если все в порядке
  IF is_region_found = 1 AND vcode_count = 1 AND pers IS NOT NULL THEN
   UPDATE sch2.jlb_pays_temp2 p
   SET p.is_invalid = 0, p.reg_code = valid_vcode
   WHERE p.phone = c1.phone;
  END IF;
  --если не найдено кодов района (т.е. телефон не найден ни в одном из р-нов)
  IF is_region_found = 0 THEN
   UPDATE sch2.jlb_pays_temp2 p
   SET p.is_invalid = 6
   WHERE p.phone = c1.phone;
  END IF;
  --если неск. кодов района - тоже ошибка
  IF is_region_found = 1 AND vcode_count > 1 THEN
   UPDATE sch2.jlb_pays_temp2 p
   SET p.is_invalid = 7
   WHERE p.phone = c1.phone;
  END IF;
  --если это юрлицо
  IF pers IS NULL THEN
   UPDATE sch2.jlb_pays_temp2 p
   SET p.is_invalid = 1
   WHERE p.phone = c1.phone;
  END IF;
 
 END LOOP; --end c1

 commit;

end jlb_set_kr_by_phone;

* This source code was highlighted with Source Code Highlighter.

В вышеприведенном примере, если говорить коротко, в таблице выставляются соостветствующие телефонам коды районов.

Триггеры
Иногда достаточно удобно использовать триггеры. Допустим, у нас есть таблица файлов платежей, у записей которой есть потомки - платежи, хранящиеся в таблице платежей. Необходимо, чтобы при удалении файла автоматически удалялись и все платежи, связанные с ним. Ниже представлен простой пример:

--таблица файлов
create table jlb_files(
 file_id number(10) PRIMARY KEY USING INDEX,
 bank_id number(10),
 file_name varchar2(50),
 file_date DATE,
 upload_date DATE,
 total_pays_cnt NUMERIC(9, 0),
 total_pays_sum NUMERIC(10, 2)
);

--таблица оплат
create table jlb_pays(
 pay_id number(10) PRIMARY KEY USING INDEX,
 file_id number(10),
 account varchar2(20),
 pay_sum NUMERIC(9,2),
 pay_date DATE,
);

--триггер
CREATE OR REPLACE TRIGGER jlb_files_before_delete
BEFORE DELETE
ON jlb_files
FOR EACH ROW
DECLARE
BEGIN
 DELETE FROM jlb_pays p
 WHERE :old.file_id = p.file_id;
END;

* This source code was highlighted with Source Code Highlighter.

Очевидно, что jlb_files - это таблица файлов, а jlb_pays - это таблица дочерних платежей. При удалении файла из jlb_files автоматически удаляются все платежи с соответствующим file_id. В теле триггера видно, что old представляет собой удаляемую строку.
http://www.techonthenet.com/oracle/triggers/before_delete.php