Oracle是否提供从已解析的字符串/字符串行返回多个子字符串的方法? [英] Does Oracle provide the way of returning multiple substrings from a string/clob row that has been parsed?

问题描述

我知道有REGEXP_函数，但是当简单地应用于字符串var时，这些函数最多返回1行.我知道您可以在WHERE子句中使用它，但是我需要一种处理大型字符串/文本/clob vars而不是表的方法，因此我想知道某个函数是否可以某种方式返回多个子字符串(我在想什么像explode()或-甚至更好-PHP中的preg_match().

正如APC所建议的那样，我提供了一个示例字符串以及我想获得的结果示例. 就像我在下面的评论中说的那样，我想要这样获取功能/程序主体(某些软件包中的功能/程序):

I know that there are REGEXP_ functions, but this ones return maximum 1 row when simply applied to a string var. I know that you can use it in a WHERE clause, but I need a way of dealing with large strings/text/clob vars not tables, so I would like to know if some function can return multiple substrings somehow (I am thinking at something like the explode() or - even better - preg_match() in PHP).

As APC suggested I am providing a sample string and examples of outcomes that I would like to get.. Like I said in the comments bellow, I wand to get the functions/procedures bodies (functions/procedures that are part of some packages) like this:

STRING:

  create or replace PACKAGE BODY export_db IS
  FUNCTION o_functie(ceva NUMBER) return boolean IS
  BEGIN
    RETURN null;
  END;
  FUNCTION o_functie(ceva NUMBER, altceva VARCHAR2) return boolean IS
  BEGIN
    RETURN null;
  END;
  PROCEDURE export_db_tabele IS
    v_ddl CLOB;
  BEGIN
      FOR c IN(SELECT object_type,object_name FROM user_objects where object_type IN ( 'TABLE')) LOOP
        v_ddl := v_ddl || dbms_metadata.get_ddl(c.object_type, c.object_name)||';'||CHR(13)||CHR(10);   
      END LOOP; 
      INSERT INTO dbexport(tipobiect, ddltext) VALUES ('tabele', v_ddl);
  END;
  PROCEDURE export_db_restrictii IS
    v_ddl CLOB;
  BEGIN
      FOR c IN(SELECT constraint_name FROM user_constraints) LOOP
        v_ddl := v_ddl || dbms_metadata.get_ddl('CONSTRAINT', c.constraint_name)||';'||CHR(13)||CHR(10);   
      END LOOP; 
      INSERT INTO dbexport(tipobiect, ddltext) VALUES ('restrictii', v_ddl);
  END;
  PROCEDURE export_db_secvente IS
    v_ddl CLOB;
  BEGIN
      FOR c IN(SELECT sequence_name FROM user_sequences) LOOP
        v_ddl := v_ddl || dbms_metadata.get_ddl('SEQUENCE', c.sequence_name)||';'||CHR(13)||CHR(10);   
      END LOOP; 
      INSERT INTO dbexport(tipobiect, ddltext) VALUES ('secvente', v_ddl);
  END;
  PROCEDURE export_db_proceduri IS
    v_ddl CLOB;
  BEGIN
      FOR c IN(SELECT OBJECT_NAME FROM user_objects up WHERE object_type = 'PROCEDURE') LOOP 
        v_ddl := v_ddl || dbms_metadata.get_ddl('PROCEDURE', c.OBJECT_NAME)||CHR(13)||CHR(10);   
      END LOOP;  
      INSERT INTO dbexport(tipobiect, ddltext) VALUES ('proceduri', v_ddl);
  END;
  PROCEDURE export_db_functii IS
    v_ddl CLOB;
  BEGIN
      FOR c IN(SELECT OBJECT_NAME FROM user_objects uo WHERE object_type = 'FUNCTION' ) LOOP 
         v_ddl := v_ddl || dbms_metadata.get_ddl('FUNCTION', c.OBJECT_NAME)||CHR(13)||CHR(10);   
      END LOOP; 
      INSERT INTO dbexport(tipobiect, ddltext) VALUES ('functii', v_ddl);
  END;
  PROCEDURE export_db_pachete IS
    v_ddl CLOB;
  BEGIN
      FOR c IN(SELECT OBJECT_NAME FROM user_objects uo WHERE object_type = 'PACKAGE' ) LOOP 
        v_ddl := v_ddl || dbms_metadata.get_ddl('PACKAGE', c.OBJECT_NAME)||CHR(13)||CHR(10);   
      END LOOP;   
      INSERT INTO dbexport(tipobiect, ddltext) VALUES ('pachete', v_ddl);
  END;
  PROCEDURE export_db_declansatoare IS
    v_ddl CLOB;
  BEGIN
      FOR c IN(SELECT OBJECT_NAME FROM user_objects uo WHERE object_type = 'TRIGGER' ) LOOP 
        v_ddl := v_ddl || dbms_metadata.get_ddl('TRIGGER', c.OBJECT_NAME)||CHR(13)||CHR(10);   
      END LOOP; 
      INSERT INTO dbexport(tipobiect, ddltext) VALUES ('declansatoare', v_ddl);
  END;
END;

结果将是:

例如:

  FUNCTION o_functie(ceva NUMBER, altceva VARCHAR2) return boolean IS
  BEGIN
    RETURN null;
  END;

和

PROCEDURE export_db_secvente IS
    v_ddl CLOB;
  BEGIN
      FOR c IN(SELECT sequence_name FROM user_sequences) LOOP
        v_ddl := v_ddl || dbms_metadata.get_ddl('SEQUENCE', c.sequence_name)||';'||CHR(13)||CHR(10);   
      END LOOP; 
      INSERT INTO dbexport(tipobiect, ddltext) VALUES ('secvente', v_ddl);
  END;

如果您知道获取这些过程/函数的任何其他方法，我很高兴放弃解析所有这些内容-根据我所知道的-没有选择这样做的方法...即使是从user_source，user_procedures表或其他表格中也是如此. ..

IF you know any other method of geting those procedures/functions I am glad to give up parsing all this - from what I know - there is no select to do that... not even from user_source, user_procedures tables or other...

推荐答案

类似这样的事情:

CREATE OR REPLACE FUNCTION explode(longline varchar)
  RETURN sys.dbms_debug_vc2coll PIPELINED
IS  
  pos PLS_INTEGER;
  lastpos PLS_INTEGER;
  element varchar(2000);
BEGIN
   lastpos := 1;
   pos := instr(longline, ',');

   while pos > 0 loop
      element := substr(longline, lastpos, pos - lastpos);
      lastpos := pos + 1;
      pos := instr(longline, ',', lastpos);
      pipe row(element);
   end loop;

   if lastpos <= length(longline) then
      pipe row (substr(longline, lastpos));
   end if;

   RETURN;
END;  
/

可以这样使用:

SQL> select * from table(explode('1,2,3'));

COLUMN_VALUE
---------------------------------------------
1
2
3
SQL>

如果您不在11.x上，则需要自己定义返回类型:

If you are not on 11.x you need to define the return type yourself:

create type char_table as table of varchar(4000);

并将函数声明更改为:

CREATE OR REPLACE FUNCTION explode(longline varchar)
  RETURN char_table pipelined
.....

这篇关于Oracle是否提供从已解析的字符串/字符串行返回多个子字符串的方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！