Динамический SQL в Oracle. Пример

В рамках программы восстановления утраченных знаний, написал пример использования dbms_sql:

DECLARE
  cSQL NUMBER; —Идентификатор курсора
  vSubjOld NUMBER := 000000; —Старый ID
  vSubjNew NUMBER := 111111; —Новый ID
  vSQL VARCHAR2(1000);
  vID NUMBER;
  vUpdateSQL VARCHAR2(1000);
  vRet NUMBER;
BEGIN
  —Курсор по таблицам с полями которые нужно обработать
  FOR c_tab IN (SELECT DISTINCT tb.TABLE_NAME, cl.COLUMN_NAME
                  FROM user_tab_columns cl,
                       user_tables tb
                  WHERE cl.column_name IN (‘SUBJECT_ID’,’SUBJ_ID’,’DECLARANT_ID’,’PAYER_ID’,’CARRIER_ID’,’BROKER_ID’)
                    AND tb.TABLE_NAME=cl.TABLE_NAME
                    AND tb.TABLE_NAME NOT LIKE ‘EXP_%’
                    AND tb.TABLE_NAME NOT LIKE ‘LEG_%’
                    AND tb.table_name NOT LIKE ‘TMP_%’) LOOP
    dbms_output.put_line(c_tab.table_name||’.’||c_tab.column_name);
    vUpdateSQL := ‘update ‘||c_tab.table_name||’ ‘||
                  ‘set ‘||c_tab.column_name||’ = :1 ‘||
                  ‘where ‘||c_tab.column_name||’ = :2 ‘;
    —открыть курсор
    cSQL:=dbms_sql.open_cursor;
    BEGIN
      vSQL:=’select id ‘||
            ‘from ‘||c_tab.table_name||’ ‘||
            ‘where ‘||c_tab.column_name||’ = :subj_id’;
      dbms_sql.parse(cSQL, vSQL, dbms_sql.native);
      —биндим переменные
      dbms_sql.bind_variable(cSQL, ‘subj_id’, vSubjOld);
      —Определяем колонки
      dbms_sql.define_column(cSQL,1,vID);
      —запускаем
      vRet := dbms_sql.execute(cSQL);
      LOOP
        —В Oracle 11g есть чудесная функция dbms_sql.to_refcursor позволяющая сделать все проще без всех этих условий
        IF dbms_sql.fetch_rows(cSQL)>0 THEN
          dbms_sql.column_value(cSQL,1,vID);
          dbms_output.put_line(‘ID: ‘||vID);
          —Меняем
          EXECUTE IMMEDIATE vUpdateSQL USING vSubjNew, vSubjOld;
        ELSE
          EXIT;
        END IF;
      END LOOP;
      dbms_sql.close_cursor(cSQL);
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
        dbms_sql.close_cursor(cSQL);
    END;     
  END LOOP;
  —…
END;

Реклама
Рубрика: Oracle | Оставить комментарий

Установка русской MSDN Library на английскую VS2010, продолжение

К сожелению выяснилось, что после установки библиотеки, описанной в предыдущей статье, не работает обновление через интернет, а так же добавление новых (например, доку по Silverlight’у) и удаление (!) старых разделов. Обидно, но студию пришлось удалить полностью, потом руками удалить HelpLibrary и потом студию поставить обратно и накатить SP1. LanguagePack на Help не трогал, видимо он остался жить в системе какой-то своей жизнью. Дальше процесс такой:

1. Правим ярлык “Manage Help Settings” как написано в предыдущей статье

2. Устанавливаем русские библиотеки из интернета

3. Правим queryManifest.?.xml: копи-пастим весь каталог с русскими библиотеками и в одной копии меняем атрибут productLocale на “EN-US”

4. Запускаем студию, проверяем. Все работает.

Снимок

Рубрика: Development | Метки: , | 3 комментария

Установка русской MSDN Library на английскую Visual Studio 2010

Вот лично у меня локализованные среды разработки вызывают резкую антипатию (ставил ради интереса русскую Visual Studio 2008 долго искал где там Debug Подмигивающая рожица), чего нельзя сказать о документации (хотя она тоже бывает переведена так, что лучше бы и не переводили). Если с 2008 студией все было просто – ставим английскую студию без MSDN потом ставим MSDN от русской студии, то с 2010 студией не все так просто, но не невозможно.

Для установки понадобится: установленная Visual Studio 2010 ENG без библиотек Help’а, дистрибутив Visual Studio 2010 RUS (можно скачать trial, устанавливать ее мы все равно не собираемся), notepad Подмигивающая рожица

1. Распаковываем iso с русской VS2010, вытаскиваем оттуда папки Help и ProductDocumentation

2. Из папки Help устанавливаем Language Pack (HelpSetupLP_x64_RUS.exe или HelpSetupLP_x86_RUS.exe в зависимости от системы)

3. В папке ProductDocumentation правим файл HelpContentSetup.msha заменяя значения ru-ru на en-us

4. Правим ярлык “Microsoft Visual Studio 2010\Visual Studio Tools\Manage Help Settings – ENU” в поле “Объект” меняем параметр /locale с en-US на ru-RU

image

5. Запускаем Manage Help Settings (должно запуститься на русском языке) и выбираем “Установить содержимое с диска”

6. В качестве “Расположения носителя справки” выбираем правленный HelpContentSetup.msha

image

7. Выбираем что хотим установить и давим “Обновить”

image

8. Залезаем в папку с установленным хелпом (по-умолчанию c:\ProgramData\Microsoft\HelpLibrary) и  бекапим её

9. Заходим в catalogs\VS\100 там 2 папки: EN-US и RU-RU, переносим все из EN-US в RU-RU

10. Заходим в manifest и правим файл queryManifest.3.xml методом удаления строк как показано на картинке ниже

image

11. Запускаем студию, вызываем Help

image

12. Убеждаемся что все работает, убиваем бэкапы

PS: Вполне может быть, что какой-то шаг я пропустил, потому, что папка с заготовленной ProductDocumentation у меня была подготовленна задолго до написания этой статьи.

PS2: Вполне возможно, что шаг 9 является лишним

UPD: Продолжение

Рубрика: Development | Метки: , | 4 комментария

Трассировка в Oracle

Для себя, чтобы не забыть как это делается.

Трассировка SQL:

1) Включаем трассировку ALTER SESSION SET SQL_TRACE = TRUE;
2) Выполняем запрос
3) Выключаем трассировку
4) Профилируем трассировочный файл (каталог в параметре user_dump_dest) командой
tkprof <in_file> <out_file> explain=plsql/oracle sort=<exeela/plsela/fchela>

Трассировка PL/SQL

0) Создаем под SYS’ом трассировочные представления (файл <home>rdbmsadmintracetab)
1)
ALTER SESSION SET PLSQL_DEBUG = TRUE;
2)
ALTER [PROCEDURE | FUNCTION | PACKAGE] <name> COMPILE DEBUG [BODY];
3) DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.<level>
) где level: trace_<all/enabled>_calls, trace_<all/enabled>_exceptions, trace_<all/enabled>_sql, trace_<all/enabled>_lines
4) Выполняем код, который хотим трассировать
5) Выключаем трассировку процедурой DBMS_TRACE.CLEAR_PLSQL_TRACE
6) Смотрим представления SYS.PLSQL_TRACE_RUNS и SYS.PLSQL_TRACE_EVENTS

Рубрика: Oracle | 2 комментария

Удивительное рядом

Сегодня роясь в Google обнаружил, что моя древняя (как всегда недоделанная и заброшенная, по причине потери актуальности) поделка очутилась на Softpedia.com (там же обнаружился мой CommentRemover – к нему даже скриншот сделали) и Famouswhy.com. Видимо кому-то по вкусу пришлась…

Note

Рубрика: поделки | Оставить комментарий

Борьба с мутантами в Oracle

Странно, что никто из читающих мой блог этого не заметил (а некоторые, я знаю точно, должны были). Дело в том что метод описанный мной в посте Оптимизация “исторического” отчета в Oracle вызывает мутации таблиц (ORA-4091). Далее я, в кратце опишу как с этим бороться (интересующиеся могут посмотреть решение у Тома Кайта тут, суть моего решения такая же, но немного отличается, в частности, я инициализирую пакетную переменную явно, а не присваиваю ей пустую переменную).

Триггер на котором происходит мутация:
create or replace trigger detail_table1_rest
after insert or delete or update on detail_table1
for each row
begin
  —MAIN_ID — ID из MAIN_TABLE в дочерних таблицах
  update main_table
    set rest=CalcRest(:new.main_id, trunc(sysdate)),  —Тут предполагается, что функция CalcRest ходит не только по дочерним таблицам, но и по MAIN_TABLE, отсюда и мутации
        rest_date=sysdate
    where id=:new.main_id;
end;

Пример функции CalcRest (очень сильно упрощенный вариант):
create or replace function CalcRest(pid in number, ReportDate in date) return number is
  Result number;
begin
  —Изначальная сумма
  select summa
    into vSumma
    from main_table
    where id=pid;
  —Собираем суммы потраченного
  select sum(summa)
    into vSumma2
    from detail_table
    where main_id=pid;
  Result:=vSumma-vSumma2;
  return(Result);
end CalcRest;

Добавляем пакет (только спецификацию, без тела) с публичной переменной:
create or replace package pck_rest is
  type rest_tbl is table of
number;
 
rests rest_tbl; –Табличная переменная в которую будем сохранять id записей в main_table для которых надо пересчитать
end pck_rest;

Добавляем statement-level триггер в котором инициализируем переменную:
create or replace trigger detail_table1_rest_bfr
  before insert or update or delete on detail_table1
begin
  pck_rest.rests:=pck_rest.rest_tbl(); –Явная инициализация переменной через конструктор
end detail_table1_rest_bfr;

Изменяем row-level триггер:
create or replace trigger detail_table1_rest
after insert or delete or update on detail_table1
for each row
declare
  vID number;
  vInitID number;
  function CheckID (pId in number) return number is –функция для проверки дубликатов в табличной переменной, нужно это для того, чтобы потом не пересчитывать и не обновлять одну и туже запись несколько раз
    Result number:=0;
  begin
     for i in 1..pck_rest.rests.count loop
      if pck_rest.rests(i)=pId then
        Result:=pId;
      end if;
    end loop;
    return Result;
  end;

begin
  if inserting then
    vInitID:=:new.main_id;
  else
    vInitID:=:old.main_id;
  end if;
  —Проверяем на дубли

  vID:=CheckID(vInitID);
  —Если дублей не найдено добавляем новое значение

  if vID=0 then
    pck_rest.rests.extend;
    pck_rest.rests(pcg_rest.rests.last):=vInitID;
  end if;
end;

Добавляем сохраняющий statement-level триггер:
create or replace trigger detail_table1_rest_aft
  after insert or update or delete on detail_table1
declare
  vRest number;
begin
  for i in 1..pck_rest.rests.count loop
    —Пересчитываем
    vRest:=CalcRest(pck_rest.rests(i),trunc(sysdate));
    —Сохраняем
    update main_table
        set rest=vRest,
             rest_date=trunc(sysdate)
        where id=pck_rest.rests(i);
  end loop;
end;

Мутанты побеждены!

Рубрика: Oracle | Оставить комментарий

Оптимизация “исторического” отчета в Oracle

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

CREATE TABLE MAIN_TABLE
(
  ID NUMBER NOT NULL,
  SUMMA NUMBER NOT NULL
,CONSTRAINT MAIN_TABLE_PK PRIMARY KEY (ID) ENABLE
);

У этой таблицы есть куча дочерних таблиц и функция расчета некоего остатка денег
CalcRest(id in number,ReportDate in date), собирающая информацию по всем дочерним
таблицам на определенную дату и довольно сложно ее обрабатывающая. Нам нужно получить
отчет содержащий ID, SUMMA и результат CalcRest.

Итерация 0

select id, summa, CalcRest(id,:ReportDate) rest
  from main_table;
Предположим, что в таком виде отчет считается 10 часов – все время уходит на запуск 
CalcRest для каждой из 10000 записей.

Итерация 1

Добавляем к таблице поле для сохранения последнего значения остатка и триггеры для всех 
дочерних таблиц для обновления этого поля:

ALTER TABLE MAIN_TABLE
  ADD ("REST" NUMBER DEFAULT 0 NOT NULL);

create or replace trigger detail_table1_rest
after insert or delete or update on detail_table1
for each row
begin
  —MAIN_ID — ID из MAIN_TABLE в дочерних таблицах
  update main_table
    set rest=CalcRest(:new.main_id, trunc(sysdate))
    where id=:new.main_id;
end;

Теперь запрос для отчета выглядит так:
select id, summa, 
(case when :ReportDate=trunc(sysdate) then rest
else CalcRest(id, :ReportDate) end) rest
from main_table;

Что существенно ускоряет работу отчета, если мы его формируем на текущую дату, для всех остальных дат – 10 часов.

Итерация 2

Добавляем поле с датой изменения остатка и модифицируем триггера:

ALTER TABLE MAIN_TABLE
  ADD ("REST_DATE" DATE DEFAULT sysdate NOT NULL);

create or replace trigger detail_table1_rest
after insert or delete or update on detail_table1
for each row
begin
  —MAIN_ID — ID из MAIN_TABLE в дочерних таблицах
  update main_table
    set rest=CalcRest(:new.main_id, trunc(sysdate)),
        rest_date=sysdate
    where id=:new.main_id;
end;

И запрос:

select id, summa,
(case when :ReportDate=trunc(sysdate) or :ReportDate>rest_date then rest
else CalcRest(id, :ReportDate) end) rest
from main_table;

В результате CalcRest вызывается только для записей у которых остаток менялся после той даты на которую мы формируем отчет.

PS: Получилось немного сумбурно, но данный алгоритм реально работает.

Рубрика: Uncategorized | Оставить комментарий