Динамический 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. Добавьте в закладки постоянную ссылку.

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

w

Connecting to %s