首页 文章

PL / SQL可重用动态sql程序，用于相同类型的任务但不同的表和列

提问于
浏览 次
1

谢谢回复家伙 . 我有点解决了我的问题 .

我曾经尝试使用“where current of”在动态SQL中使用ref cursor更新数据，但我现在知道它不起作用 .

然后我尝试使用％rowtype将'id'和'clob'存储在一个变量中以供将来更新，但结果是弱的ref游标也不能使用该类型绑定 .

之后我尝试使用record作为ref游标的返回，这对弱光标也不起作用 .

最后，我创建了另一个光标来单独检索'id'和光标以同时检索'clob'然后用该id更新表 .

我现在正在开发Oracle数据清理任务，并且具有如下要求：

有38个表（将来可能会更多），每个表都有一个或多个列，其类型为Clob . 我需要在这些列中找到不同的关键字，并根据列的逻辑返回二进制标签并将其存储在新列中 .

例如，有一个表'myTable1'，它有2个Clob列'clob1'和'clob2' . 我想从这些列中找到关键字'sky'，并在两个新列'clob1Sky'，'clob2Sky'中存储'0'（如果没有找到）或'1'（如果找到） .

我知道我是否可以在静态方式上编写它，这将提供更高的效率，但我必须每次都为那些非常相似的任务修改它 . 我想节省一些时间，所以我试图以可重用的方式编写它，而不是绑定到某个表 .

但是我在编写程序时遇到了一些问题 . 我的程序如下：

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）部分时，我注意到'where current of'子句不能用于ref游标或动态sql语句 . 所以我必须改变计划 .

我尝试使用记录（rowid，label）来存储结果并稍后更改表 . （该表仅由我的组中的两个人使用，因此不存在锁定和数据更改的问题） . 但我发现因为我试图使用动态sql所以实际上我必须定义引用游标，返回某些％rowtype和基本上所有其他变量，动态sql语句中的％type . 这让我觉得我的方法有问题 .

我的问题是：

  • 如果有办法在动态sql中定义％类型？在动态SQL中将类型绑定到变量？

  • 有人能给我一个如何在动态SQL中编写（5）部分的提示吗？

  • 我不应该这样设计我的程序吗？

  • 这不是如何使用动态SQL或PLSQL的方式吗？

我是PL / SQL的新手 . 非常感谢你 .

oracle plsql dynamic-sql ref-cursor

3 回答

  • 1

    我不确定你的问题 - 如果这个工作假设每天或每小时运行，那么通过它运行查询将是非常昂贵的 . 您可以做的一件事 - 将所有 clob 数据放在一个文件中并将其保存在您的服务器中（我猜它必须是linux） . 然后，您可以创建一个shell脚本并安排作业运行 gerp 命令并获取所需的值和"if found then update your table" .

    回复于
  • 0

    根据Tom Kyte 's advice, to do it in one statement if it can be done in one statement, I' d首先尝试使用单个 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   
...
    回复于
  • 0

    我认为你应该以另一种方式解决问题：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，因此您不必担心数据类型;）

    我不确定你想要达到什么目标，但我希望它可以帮助你 .

    回复于

相关问题