Oracle 处理时间

嗨，汤姆，

我已经用plsql编写了一个小的函数来获取业务名称，将其分解成单词，并将它们存储在一个表中。将每个单词与另一个表中的保留字进行比较。如果它存在于保留字表中，则应替换为保留字表中的首字母缩写。目前我正处于分割商业名称和取用年龄的阶段。我不知道如何使它的效率和减少处理时间。我将非常感谢你的指导和支持。

我的表脚本和插入语句如下所示：

   CREATE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(100)


CREATE TABLE TEMP_TIT
(
  IDX            NUMBER(20),
  Business_NAME  VARCHAR2(255 BYTE))

   insert into temp_tit values (2, 'Bib Stillwell Camberwell');
   insert into temp_tit values (1, 'Tropical Prestige');
   insert into temp_tit values (3, 'Ballarat Bmw');

CREATE TABLE TEMP_TT
(
  IDX      NUMBER,
  WORD     VARCHAR2(250 BYTE),
  ACRONYM  VARCHAR2(250 BYTE),
  EXAMPLE  VARCHAR2(250 BYTE)
)


insert into temp_tt values (165,    'Bmw',    'BMW',    'BMW Dealer')

CREATE OR REPLACE FUNCTION fn_tit
  (
   pr_colname   in varchar2)

  return varchar2 deterministic

  as


   TYPE FIELDCurTyp IS REF CURSOR;

   c_match_word   FIELDCurTyp;
   tab_cur         FIELDCurTyp;

   sqlSt       VARCHAR2 (500);
  pr_acronym    VARCHAR2 (500);
  pr_word   VARCHAR2 (500);
   m_string     varchar2(250);
  v_words1      varchar2_tt := varchar2_tt();
  v_position    NUMBER;


begin


 m_string:=initcap(pr_colname);

 if trim(m_string) is not null then

      LOOP
                EXIT WHEN m_string IS NULL;
                v_position := INSTR (m_string, ' ' );
                v_words1.EXTEND;
                v_words1(v_words1.COUNT) := LTRIM (RTRIM (SUBSTR (m_string, 1, v_position - 1)));
                m_string := SUBSTR (m_string , v_position + 1);
  
  --dbms_output.put_line('exception returned ');

             END LOOP;

 end if;

  open c_match_word for SELECT * FROM TABLE (CAST (v_words1 AS varchar2_tt));
                   

  
   loop
    FETCH c_match_word INTO pr_word;

          EXIT WHEN c_match_word%NOTFOUND OR c_match_word%NOTFOUND IS NULL;

    
     dbms_output.put_line('exception returned '||pr_word);

    


   end loop;




dbms_output.put_line('exception returned '||m_string);

RETURN  m_string;



 EXCEPTION WHEN NO_DATA_FOUND THEN

  DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');


  WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE(SQLERRM);





end;
/

executed function

 update TEMP_TiT set 
       Business_NAME=FN_TIT(Business_NAME);

我还没有完成我的功能，由于处理时间很高，我无法看到该功能的输出，请您建议我如何才能使其有效。

先谢谢你，

我讨厌你的代码：

当不存在数据时异常，则

DBMS_OUTPUT.PUT_line （'未找到数据'） ；


当别人那么

DBMS_OUTPUT.PUT_line(SQLERRM) ；




当其他人- -不跟后引发或引发应用程序错误- -在你的代码中几乎肯定是一个错误- -时，肯定是一个错误。

但是，请尝试纯SQL方法。

这使用的技术来自：

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:13912710295209

替代“伎俩”


http://asktom.oracle.com/Misc/varying-in-lists.html

生成数据

和
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:15637744429336#68891781372516

把数据重新组装起来。


大多数情况下，如果您正在更新每行，您应该创建一个新表-所以这就是我正在做的。

If you don't want to create a new table 和you want to waste time, you can use the SELECT of my query in an MERGE against your table to update them all (not recommended)

ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> set linesize 1000
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop table words;

Table dropped.

ops$tkyte%ORA11GR2> create table words
  2  as
  3  select column_value w
  4    from table ( sys.odciVarchar2List( 'Bib', 'Stillwell', 'Camberwell', 'Tropical', 'Presitige', 'Ballarat', 'Bmw', 'Abc', 'Def' ) )
  5  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop table temp_tit;

Table dropped.

ops$tkyte%ORA11GR2> create table temp_tit
  2  as
  3  select rownum idx, w1 || ' ' || w2 || ' ' || w3 business_name
  4      from (select w w1 from words order by dbms_random.random ),
  5           (select w w2 from words order by dbms_random.random ),
  6           (select w w3 from words order by dbms_random.random )
  7    where rownum <= 50000
  8  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop TABLE TEMP_TT;

Table dropped.

ops$tkyte%ORA11GR2> CREATE TABLE TEMP_TT
  2  (
  3    IDX      NUMBER,
  4    WORD     VARCHAR2(250 BYTE),
  5    ACRONYM  VARCHAR2(25 BYTE),
  6    EXAMPLE  VARCHAR2(250 BYTE)
  7  );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into temp_tt values (165,    'Bmw',    'BMW',    'BMW Dealer');

1 row created.

ops$tkyte%ORA11GR2> insert into temp_tt values (166,    'Abc',    'ABC',    'BMW Dealer');

1 row created.

ops$tkyte%ORA11GR2> insert into temp_tt values (166,    'Def',    'DEF',    'BMW Dealer');

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> column word format a15
ops$tkyte%ORA11GR2> column bname format a25
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop table new_temp_tit;

Table dropped.

ops$tkyte%ORA11GR2> set timing on
ops$tkyte%ORA11GR2> create table new_temp_tit
  2  as
  3  select *
  4    from (
  5  with data
  6  as
  7  (
  8  select idx, to_number( substr( column_value, 1, 3 ) ) r, substr( column_value, 4 ) word
  9    from (select idx, ' ' || replace( replace ( replace(business_name,' ',' @'),'@ ',''),' @',' ') || ' ' txt
 10            from temp_tit ),
 11         table( cast( multiset( select to_char(rownum,'fm000') ||
 12                                       trim( substr (txt,
 13                                          instr (txt, ' ', 1, level  ) + 1,
 14                                          instr (txt, ' ', 1, level+1)
 15                                              - instr (txt, ' ', 1, level) -1 ) )
 16                                       as token
 17                                     from dual
 18                                  connect by level <= length(txt)-length(replace(txt,' ',''))-1
 19                    ) as sys.odcivarchar2List ) )
 20  ),
 21  data2
 22  as
 23  (
 24  select data.idx, data.r, data.word, temp_tt.acronym, nvl(temp_tt.acronym,data.word) new_word
 25    from data, temp_tt
 26   where data.word = temp_tt.word (+)
 27  )
 28  select idx, substr( max( sys_connect_by_path( new_word, ' ' ) ), 2 ) bname
 29    from data2
 30   start with r = 1
 31  connect by prior idx = idx 和prior r = r-1
 32  group by idx
 33  )
 34  /

Table created.

Elapsed: 00:00:00.27
ops$tkyte%ORA11GR2> set timing off