使用数据库的原生内置函数提升sql函数的效率_Oracle

使用数据库的原生内置函数提升sql函数的效率: 发布时间：2020-06-09编辑：脚本学堂

使用数据库的原生内置函数提升sql函数的效率

案例：在字符串类型字段中求目标子串出现的次数（在oracle 9i下测试）
1.建测试数据。建表test_tbl(含字符串类型的字段content),插入10W条记录。

复制代码代码如下:

--建表      

   create table test_tbl (id number,content varchar2(1000))；

   --插入10W条测试记录

   begin

      dbms_random.seed(12345678);

      for i in 1 .. 100000

      loop

        insert into test_tbl values(i, dbms_random.string('L',dbms_random.value(10,1000)));

      end loop;

      commit;

    end;

2.创建自定义函数一（采用循环截子串的方式实现，如果截到的子串等于目标子串则出现次数加1。）

复制代码代码如下:

create or replace function f1(str_pattern in varchar2,str_mother in varchar2)

   return number

   is

    i number := 1;

    cnt number := 0;

    len_pattern number := length(str_pattern);

    len_mother number := length(str_mother);

   begin

    while(i <= len_mother)

      loop

        if(substr(str_mother, i ,len_pattern) = str_pattern) then

          cnt := cnt + 1;

          i := i+ len_pattern;

         else

         i := i+1;

        end if;

      end loop;

      return cnt;

    end;

3.创建自定义函数二(循环使用Instr函数利用occurrence参数实现，循环一次Intr函数得到一个位置pos值，并让occurrence加1。如果pos值不为零，表示目标子串第occurrence次在母串中存在；反之表示再也不存在了，于是退出循环。最后occurrence-1即为所得)
    Intr函数说明：Instr(string, substring, position, occurrence) 其中
        string：代表源字符串；
        substring：代表想聪源字符串中查找的子串；
        position：代表查找的开始位置，该参数可选的，默认为 1；
       occurrence：代表想从源字符中查找出第几次出现的substring，该参数也是可选的，默认为1；
       返回值为：查找到的字符串的位置。

复制代码代码如下:

create or replace function f2(str_pattern in varchar2,str_mother in varchar2)

  return number

  is

    pos number;

    occurrence number := 1;

   begin

      loop

         pos := instr(str_mother,str_pattern,1, occurrence);

         exit when pos = 0;

         occurrence := occurrence + 1;

       end loop;

       return occurrence - 1;

      end;

3.测试效率
select count(*) from test_tbl where f1('abc',content) > 1
--用时59.223 S
select count(*) from test_tbl where f2('abc',content) > 1
--用时2.016 S
select count(*) from test_tbl where f1('a',content) > 10
--用时59.453 S
select count(*) from test_tbl where f2('a',content) > 10
--用时8.36 S

4.总结：很好的利用内置函数，科学的把内置函数放到合理的位置能很好的提高效率

5.后记
对于该案例，如果目标子串含有多个字符用f2效率较高，如果目标子串所含字符很少比如就一个字符,建议用简便办法：1.在母字符串中把木目标字串替换为空('')得到新字符串。2.利用 (母串长度-新串长度)/目标子串长度得到的就是子串的出现次数。

上一篇：su: /bin/bash: Permission denied错误的解决方法
下一篇：oracle的启动脚本

与使用数据库的原生内置函数提升sql函数的效率有关的文章

本文标题：使用数据库的原生内置函数提升sql函数的效率
本页链接：http://www.jb200.com/db/3678.html

浏览排行

栏目分类

热点文章

使用数据库的原生内置函数提升sql函数的效率