基于blob字段值的Oracle SQL分组

Question

这可能是不可能的

我有一个包含XML的blob表，我想根据blob的内容获得一个计数，有一种方法可以在一个查询中完成它，而不是执行

select count(*)
from MyTable
where dbms_lob.instr(bitstream, utl_raw.CAST_TO_RAW('ObjA'), 1, 1) > 0

select count(*)
from MyTable
where dbms_lob.instr(bitstream, utl_raw.CAST_TO_RAW('ObjB'), 1, 1) > 0

select count(*)
from MyTable
where dbms_lob.instr(bitstream, utl_raw.CAST_TO_RAW('ObjC'), 1, 1) > 0

select count(*)
from MyTable
where dbms_lob.instr(bitstream, utl_raw.CAST_TO_RAW('ObjD'), 1, 1) > 0

select count(*)
from MyTable
where dbms_lob.instr(bitstream, utl_raw.CAST_TO_RAW('ObjE'), 1, 1) > 0

Answer 1

像这样的东西会起作用吗？

将要查找的值存储到CTE中，然后将该CTE加入到您的表中。

WITH
   temp (obj)
   AS
      (SELECT *
         FROM TABLE (sys.odcivarchar2list ('ObjA',
                                           'ObjB',
                                           'ObjC',
                                           'ObjD',
                                           'ObjE')))
  SELECT t.obj, COUNT (*)
    FROM mytable e
         JOIN temp t
            ON DBMS_LOB.INSTR (bitstream,
                               UTL_RAW.cast_to_raw (t.obj),
                               1,
                               1) > 0
GROUP BY t.obj;