PL/SQL многоразовая динамическая программа sql для задач того же типа, но с другой таблицей и столбцом

Спасибо за ответ, ребята. Я вроде решил мою проблему.

Раньше я пытался обновить данные с помощью курсора ref в динамическом SQL, используя "where current of", но теперь я знаю, что это не сработает.

Затем я попытался использовать% rowtype для хранения обоих 'id' и 'clob' в одной переменной для будущего обновления, но оказалось, что слабый курсор ref не может использовать эту привязку типа.

После этого я попытался использовать запись в качестве возврата курсора ref, и это не работает на слабом курсоре.

В конце я создал еще один курсор для извлечения "id" отдельно вместе с курсором для получения "clob" в то же время, а затем обновил таблицу с этим идентификатором.

Сейчас я работаю над задачей очистки данных Oracle и у меня есть требование, как показано ниже:

В нем 38 таблиц (возможно, в будущем будет больше), и в каждой таблице есть один или несколько столбцов, тип которых - Clob. Мне нужно найти другое ключевое слово в этих столбцах и в соответствии с логикой вернуть двоичную метку столбца и сохранить его в новом столбце.

Например, есть таблица "myTable1", которая имеет 2 столбца Clob "clob1" и "clob2". Я хотел бы найти ключевое слово "небо" из этих столбцов и сохранить "0" (если не найден) или "1" (если найден) в двух новых столбцах "clob1Sky", "clob2Sky".

Я знаю, смогу ли я написать это статическим способом, который обеспечит более высокую эффективность, но мне приходится каждый раз модифицировать его для этих очень похожих задач. Я хочу сэкономить некоторое время на этом, поэтому я пытаюсь записать его повторно и не привязывать к определенной таблице.

Но я столкнулся с некоторой проблемой при написании программы. Моя программа, как показано ниже:

create or replace PACKAGE body LABELTARGETKEYWORD 
    as
    /**
    @param varcher tableName: the name of table I want to work on
    @param varchar colName: the name of clob column
    @param varchar targetWord: the word I want to find in the column
    @param varchar newColName: the name of new column which store label of clob 
    */ 
    PROCEDURE mainProc(tableName varchar, colName varchar,targetWord varchar,newColName varchar2)
    as
        type c_RecordCur is ref cursor;
        c_sRecordCur c_recordCur;
        /*other variables*/
    begin
        /*(1) check whether column of newColName exist
          (2) if not, alter add table of newColName
          (3) open cursor for retrieving clob
          (4) loop cursor
              (5) update set the value in newColName accroding to func labelword return
          (6) close cursor and commit*/
    end mainProc;

    function labelWord(sRecord VARCHAR2,targetWord varchar2) return boolean...
    function ifColExist(tableName varchar2,newColName varchar2) return boolean...
END LABELTARGETKEYWORD;

Большинство DML и DDL написаны в динамическом формате SQL.

Проблема заключается в том, что когда я пишу часть (5), я замечаю, что предложение "Где текущий из" не может использоваться в операторе ref или динамическом SQL-выражении. Поэтому я должен изменить план.

Я попытался использовать запись (rowid, label) для сохранения результата и изменения таблицы позже (таблица будет использоваться только двумя людьми в моей группе, поэтому проблем с блокировкой и изменениями данных не возникнет). Но я нахожу, потому что я пытаюсь использовать динамический sql, поэтому на самом деле мне нужно определить ref курсор с возвратом определенного% rowtype и, в основном, всех других переменных% type в динамическом SQL-выражении. Что заставляет меня чувствовать, что в моем методе что-то не так.

Мой вопрос:

  1. Есть ли способ определить% type в динамическом sql? Тип привязки к переменной в динамическом SQL?

  2. Кто-нибудь может дать мне подсказку, как написать эту (5) часть в динамическом SQL?

  3. Разве я не должен разрабатывать свою программу таким образом?

  4. Разве это не способ, как использовать динамический SQL или PLSQL?

Я очень плохо знаком с PL/SQL. Большое спасибо.

Источник

3 ответа

Решение

Согласно совету Тома Кайта, чтобы сделать это в одном утверждении, если это можно сделать в одном утверждении, я бы попытался использовать один UPDATE утверждение первое:

CREATE TABLE mytable1 (id NUMBER, clob1 CLOB, 
  clob2 CLOB, clob1sky NUMBER, clob2sky NUMBER ) 
LOB(clob1, clob2) STORE AS SECUREFILE (ENABLE STORAGE IN ROW);

INSERT INTO mytable1(id, clob1, clob2) 
SELECT object_id, object_name, object_type FROM all_objects
 WHERE rownum <= 10000;

CREATE OR REPLACE 
PROCEDURE mainProc(tableName VARCHAR2, colName VARCHAR2, targetWord VARCHAR2, newColName VARCHAR2)
IS
  stmt VARCHAR2(30000);
BEGIN
  stmt := 'UPDATE '||tableName||' SET '||newColName||'=1 '||
          'WHERE DBMS_LOB.INSTR('||colName||','''||targetWord||''')>1';
  dbms_output.put_line(stmt);
  EXECUTE IMMEDIATE stmt;
END mainProc;
/

Итак, называя это с mainProc('MYTABLE1', 'CLOB1', 'TAB', 'CLOB1SKY'); подает заявление

UPDATE MYTABLE1 SET CLOB1SKY=1 WHERE DBMS_LOB.INSTR(CLOB1,'TAB')>1

который, кажется, делает трюк:

SELECT * FROM mytable1 WHERE clob1sky=1;

id  clob1    clob2  clob1sky clob2skiy
33  I_TAB1   INDEX  1   
88  NTAB$    TABLE  1   
89  I_NTAB1  INDEX  1   
90  I_NTAB2  INDEX  1   
...
Источник

Я не уверен с вашим вопросом: если предполагается, что это задание будет выполняться ежедневно или ежечасно, выполнение запроса будет очень дорогостоящим. Одна вещь, которую вы можете сделать - положить все свои clob данные в файл и сохранить его на своем сервере (я думаю, это должно быть Linux). затем вы можете создать сценарий оболочки и запланировать выполнение задания gerp введите нужное значение и "если найдено, обновите таблицу".

Источник

Я думаю, что вы должны подходить к проблеме по-другому: 1. Найдите все нужные вам столбцы:

CURSOR k_clobs
select  table_name, column_name from dba_tab_cols where data_type in ('CLOB','NCLOB');

Или 2 курсора (вы можете создать свой запрос, если у вас более 1 CLOB на таблицу:

 CURSOR k_clobs_table
             select  DISTINCT table_name from dba_tab_cols where data_type in ('CLOB','NCLOB');

CURSOR k_clobs_columns(table_namee varchar(255)) is
         select column_name from dba_tab_cols where data_type in ('CLOB','NCLOB') and table_name = table_namee;

Теперь вы на 100% уверены, что столбец, который вы проверяете, является clob, поэтому вам не нужно беспокоиться о типе данных;)

Я не уверен, чего вы хотите достичь, но я надеюсь, что это может вам помочь.

Источник
Другие вопросы по тегам